Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following Script which runs fine, but I am not receiving the expected output of all columns having data. Is the structure incorrect?
sabrTicketing:
Select
Event_Code as sabrEVENTCDE,
Event_Desc as sabrEVENTDESC,
Usage_Event_Date as sabrUSAGEEVENTDTE,
(Noncomp_Sale_Qty + Noncomp_Return_Qty) as sabrSOLDQTY,
(Comp_Sale_Qty + Comp_Return_Qty) as sabrCOMPQTY,
(Noncomp_Sale_Qty + Noncomp_Return_Qty + Comp_Sale_Qty + Comp_Return_Qty) as sabrTTLQTY,
(Sale_Amount + Return_Amount) as sabrREV
From
(Select
Event.Display_Order
, Event.Event_Code as Event_Code
, Event.Description as Event_Desc
, To_Char (Uevent.Event_Date, 'MM/DD/YYYY') As Usage_Event_Date
, Count (Case When Ticket.Price <> 0 And Oli.Transaction_Type_Code In ('SA','ES','CS')
Then Ticket.Ticket_Id End) As Noncomp_Sale_Qty
, Count (Case When Ticket.Price <> 0 And Oli.Transaction_Type_Code In ('RT','ER')
Then Ticket.Ticket_Id End) * -1 As Noncomp_Return_Qty
, Count (Case When Ticket.Price = 0 And Oli.Transaction_Type_Code In ('SA','ES','CS')
Then Ticket.Ticket_Id End) As Comp_Sale_Qty
, Count (Case When Ticket.Price = 0 And Oli.Transaction_Type_Code In ('RT','ER')
Then Ticket.Ticket_Id End) * -1 As Comp_Return_Qty
, Nvl (Sum (Case When Oli.Transaction_Type_Code In ('SA','ES','CS')
Then Ticket.Price End),0.00) As Sale_Amount
, Nvl (Sum (Case When Oli.Transaction_Type_Code In ('RT','ER')
Then Ticket.Price End),0.00) * -1 As Return_Amount
From hsbcr.Ticket Ticket
Inner Join hsbcr.Order_Line_Item Oli
On Ticket.Order_Line_Item_Id = Oli.Order_Line_Item_Id
Or Ticket.Remove_Order_Line_Item_Id = Oli.Order_Line_Item_Id
Inner Join HSBCR.Event Event
On Oli.Event_Id = Event.Event_Id
Inner Join hsbcr.Event Uevent --Different alias to distinguish the usage event.
On Oli.Usage_Event_Id = Uevent.Event_Id
Inner Join HSBCR.Order_Transaction Ot
On Oli.Order_Id = Ot.Order_Id And Oli.Transaction_Id = Ot.Transaction_Id
Where Oli.Market_Type_Code = 'P'
And Nvl(Ot.Order_Trxn_Assoc_Type_Code,'X') <> 'SI'
AND event.event_id >= '5039' and event.event_id <= '5079'
Group By Event.Display_Order
, Event.Event_Code
, Event.Description
, Uevent.Event_Date
, event.event_id
Order By event.event_id, Event.Display_Order, Uevent.Event_Date, Event.Event_Code);
LEFT JOIN (sabrTicketing)
sabrProjections:
LOAD
eventDATE AS SABRUSAGEEVENTDTE,
[Event Code] AS sabrEVENTCDE,
projectedSales AS sabrPROJSALES,
projREV AS sabrPROJREV,
projCOMP AS sabrPROJCOMP,
adjPROJSALES AS sabrADJSALES,
adjPROJCOMP AS sabrADJCOMP,
adjPROJREV AS sabrADJREV
FROM (ooxml, embedded labels, table is Sheet1);
LEFT JOIN (sabrTicketing)
SabresTicketingNew:
LOAD
SABREVENTCDE,
SABREVENTDESC,
SABRUSAGEEVENTDTE,
SABRSOLDQTY,
SABRCOMPQTY,
SABRTTLQTY,
SABRREV,
sabrPROJSALES,
sabrPROJREV,
sabrPROJCOMP,
sabrADJSALES,
sabrADJCOMP,
sabrADJREV,
'sabr' AS brandNME
RESIDENT sabrTicketing;
DROP TABLE sabrTicketing;
I meant linking as a step in debugging process. I assume your key values are not matching, for example dates vs. timestamps.
More specifically, in the output table, SabresTicketingNew, the columns:
sabrPROJSALES,
sabrPROJREV,
sabrPROJCOMP,
sabrADJSALES,
sabrADJCOMP,
sabrADJREV,
are all displaying as '-'
Seems like you are using event code and date as key, right?
Try linking your tables instead of joining, then check the keys for matching values.
Stefan -
Linking is fine, however ultimately I am attempting to create one giant table through concatenation. I will be replicating this script for several other data sources
I meant linking as a step in debugging process. I assume your key values are not matching, for example dates vs. timestamps.
I tried not joining and just linked on event code and there were no issues linking the table.
What about SABRUSAGEEVENTDTE?
Same result
So if you link the two tables by the two fields (the same time), you get a correct linkage?
Then the JOIN of the two tables should work, too.
I used SABREVENTCDE as the field to join and this seems to function