Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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';
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?
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
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';
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).
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.
Thanks, I'll give it a try.