Create a selection for database

[#1]

I am loading data from 2 different although identical sql databases. Pulling combined sales and inventory data.

I want to create a select option for the database itself. I currently have select fields for "customer" item" "Dates" etc.

The dababase name is the company name so my new select would be "company" Is this possible?

I have attached a sample of a from statement for each DatabaseA & DatabaseB

[swuehl]

I am not sure if I've understood your requirement completely.

What about DISCONNECT'ing after your first SELECT and then CONNECT to the second DB?

[#3]

I want to be able to get data combined from both or either from within the view. So, if I'm understanding your suggestion, that won't work. thanks

[tresesco · Accepted solution]

You can try like this:

FinalTable:

Load

          *,

           'DatabaseA' as Company ;

SQL
SELECT CardName,

DocDate as SODate,

Month(DocDate) as SOMonth,

Year(DocDate) as SOYear,

Day(DocDate) as SODay,

Indicator,

DocEntry,

DocNum as SONumber,

CardCode as BusPartner,

DocStatus,

U_ShipDate,

Month(U_ShipDate) as ShipMonth,

Year(U_ShipDate) as ShipYear,

NumAtCard

FROM DatabaseA.dbo.ORDR

Where DocStatus = 'O';

Load

          *,

           'DatabaseB' as Company ;

SQL
SELECT CardName,

DocDate as SODate,

Month(DocDate) as SOMonth,

Year(DocDate) as SOYear,

Day(DocDate) as SODay,

Indicator,

DocEntry,

DocNum as SONumber,

CardCode as BusPartner,

DocStatus,

U_ShipDate,

Month(U_ShipDate) as ShipMonth,

Year(U_ShipDate) as ShipYear,

NumAtCard

FROM DatabaseB.dbo.ORDR

Where DocStatus = 'O';

[swuehl]

The data from the two sources should be autoconcatenated in QV when reading in (Or you can force the concatenation into one table using CONCATENATE prefix).

[#6]

I have those connect statements and the data combines fine. I was looking for a select to choose one or the other.

I think I found another way of distinguishing which company the data is coming from. Thanks anyway.

[#7]

Thanks, I'll give it a try.