Query across multiple databases on same server

  • Status: Closed
  • Hadiah: $300
  • Penyertaan diterima: 8
  • Pemenang: sopholos

Ringkasan Peraduan

1. We have a database server with multiple databases on it (all have the same schema, different data)
lets call it company A and company B

2. We are looking for a way to query across all the databases or some databases

3. The end user would select company A,B,AB,AC

4. Needs to be easy to configure, as more databases may be added at any time. This data access must be real-time.

5. We envision having a function written in Delphi that would dynamically restructures the SQL in such a way to enable this functionality

Say, as an example, you have an application that inserts orders - each application has its own DB running as a separate company . What we are then looking for is an efficient way for a single application to then access the order information in all the other databases in order to run reports as one company.

This is an Example of SQL we now use.

Select [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL], Year([login to view URL]) [Year], Month([login to view URL]) [Month], Sum([login to view URL]) Sales
From TicketHD h, Customer c
Where [login to view URL] Between '01/01/2015' And '06/30/2015'
And c.Tel=[login to view URL]
Group By 1, 2, 3, 4, 5, 6, 7, 8, 9;

I would test each solution on this example.

The database server we are using is Advantage database.
[login to view URL]

I believe any generic solution would work.
Maybe fireDac could do the trick somehow

Kemahiran Disyorkan

Maklum balas Majikan

“Did a great job would hire again. ”

Gambar Profil ProgramDelphi, United States.

Penyertaan teratas dari peraduan ini

Lihat Lagi Entri

Papan Penjelasan Umum

  • mlnvasko
    mlnvasko
    • 3 years ago

    Hi,

    • 3 years ago
    1. mlnvasko
      mlnvasko
      • 3 years ago

      If its only about view the data, I would:
      1/ create separate DB view, to have it together in one place (but as a view to original DB's) with using union + add managment fields (Comp. A, B, .. and other necessary fields)
      or
      2/ Create DWH to merge all data to separeate DB and build another app (reports, app, ... up tp you) to see the data together over it

      • 3 years ago
    2. mlnvasko
      mlnvasko
      • 3 years ago

      then you can easy select from the view as you are used to do

      • 3 years ago
  • davidgrs
    davidgrs
    • 3 years ago

    depends on the fields, this can be resolve using union not problem. Pls send us an example that you need.

    • 3 years ago
  • fb557935755b9f2
    fb557935755b9f2
    • 3 years ago

    It's can be easy solved if you use Oracle DB. Just use:
    select * from Schema1.table1
    union all
    select * from Schema2.table1
    ...

    • 3 years ago
    1. sopholos
      sopholos
      • 3 years ago

      It can be easily solved that way even on MySQL, but our contest holder use some RDBMS from 90s %)

      • 3 years ago
  • ProgramDelphi
    Penganjur Peraduan
    • 3 years ago

    delphi

    • 3 years ago
  • BlackLotos2006
    BlackLotos2006
    • 3 years ago

    Delphi, C#, C++, C Builder, SQL, Interbase/Firebird, Oracle, MS SQL Server, MY SQL, Pascal, MS Access, HTML, WMI, XML, PHP, Bootstrap, JQuery

    • 3 years ago
  • expertcoder1vw
    expertcoder1vw
    • 3 years ago

    Hi,

    • 3 years ago
    1. ProgramDelphi
      Penganjur Peraduan
      • 3 years ago

      hi

      • 3 years ago
  • WebInfoSolutions
    WebInfoSolutions
    • 3 years ago

    I am not sure why you should be using separate database for separate Companies. As mentioned in the requirement that schema will be same but data will vary. So in this case. I would have used a Mater table with Company information and each will have their own referenced data.

    • 3 years ago
    1. ProgramDelphi
      Penganjur Peraduan
      • 3 years ago

      This is the way it is setup

      • 3 years ago
  • ProgramDelphi
    Penganjur Peraduan
    • 3 years ago

    It could cost more then 2500.00 for a 50 user license. Abusively there is a niche for this type of RDBMS it is now owned by SYBASE and they are coming out with a new ver. now

    • 3 years ago
    1. videando
      videando
      • 3 years ago

      If YES, only will be necesary implement a TQuery descendant with some added properties (database list, ...) for builing (dynamicly) join statements to create (using a masked query) the final query.

      • 3 years ago
    2. ProgramDelphi
      Penganjur Peraduan
      • 3 years ago

      are you going to place an entry ?

      • 3 years ago
  • ProgramDelphi
    Penganjur Peraduan
    • 3 years ago

    in this case the TEL is key in both tables. in all reports i could indicate what the key field is.

    • 3 years ago
  • kukhtinigor
    kukhtinigor
    • 3 years ago

    Hi. I solved this issue next way.
    1. of course you have to some keys in different databases to join Customers.
    2. i entered table with fields Company (A, B) and connection strings (''), So to add new company with new database you have to add new record to this table
    3. User choice date period and Company (Check Box)
    4. I create memory table (TClientDataSet as example or TmemDataSet) and fill with static fields (LastName, Name, Address, City, State, Zip, Tel, [Year], [Month]) and SumFields (Summ_A, Summ_B etc(how many database i have choosen))
    5. open query one by one and fill this dataset with data

    • 3 years ago
    1. videando
      videando
      • 3 years ago

      No memory table, please!!!

      • 3 years ago
  • gyk
    gyk
    • 3 years ago

    Sorry guys, but I have to say what such a data organisation is from the last century. Recently it is being done by separating company data with one field in the company table and filtering by company data, but not joining multiple tables to one table at db client level!

    • 3 years ago
    1. sopholos
      sopholos
      • 3 years ago

      And I must to say that using such RDBMS - last century %). SQL Server, Oracle and even MySQL can do cross-db queries. SQL Server can also do cross-server queries and even cross RDBMS-queries (!). But reality too pain to use this features on some projects.

      • 3 years ago
    2. sopholos
      sopholos
      • 3 years ago

      btw, very interesting to know how much costs this ADS?

      • 3 years ago
  • gyk
    gyk
    • 3 years ago

    But of course, customer always right :)

    • 3 years ago
  • ProgramDelphi
    Penganjur Peraduan
    • 3 years ago

    The Customer table is a list of customers with a PK TEL and TicketHD is a Table of sales details with a FK TEL. Sales Details from customer in company A may be in the TicketHD table of company B

    • 3 years ago
  • expertcoder1vw
    expertcoder1vw
    • 3 years ago

    Can you post this as a regular project? I already did this before. and have a working solution. (Delphi XE7).

    • 3 years ago
    1. ProgramDelphi
      Penganjur Peraduan
      • 3 years ago

      There is more then one way to do this. The benefit of a contest for me is I could see before hand who has the best solution and what really works. If you have done this before this would make it easier for you

      • 3 years ago
  • kukhtinigor
    kukhtinigor
    • 3 years ago

    very simple decision for not very large data size (near 200 mB). In other case it would be better to create local file.

    • 3 years ago
    1. ProgramDelphi
      Penganjur Peraduan
      • 3 years ago

      as you said, this may work with small files with bigger files I believe filling the memory table with all the data and then ruining the query on it would be a slow processes.

      • 3 years ago

Tunjukkan lebih banyak komen

Bagaimana mula dengan peraduan

  • Paparkan peraduan anda

    Paparkan Peraduan Anda Cepat dan mudah

  • Dapatkan berjuta penyertaan

    Dapatkan Bertan-tan Penyertaan Dari serata dunia

  • Anugerahkan penyertaan terbaik

    Anugerahkan penyertaan terbaik Muat turun fail-fail - Mudah!

Paparkan Peraduan Sekarang atau Sertai kami Hari Ini!