Entries missing in one db that appear in another

[abatblack]

Line items from sales orders that get deleted disappear from the sales_order.items database and get placed in a sales_order.cancelled_items database.
If its a missing line in items it will be in cancelled.
The two db's are tied together by the sales_order.header db.
I need a report that can list all line items of a sales order in order, some appearing in the items db and some appearing in the cancelled items db.

SO.Items:
SO number line_no part number
40009 1 p1
40009 3 p2
40009 5 p3

SO.Cancelled:
40009 2 p4
40009 4 p5

I want to end up with a list of the (original before cancellations) sales order - with a note to show which are cancelled lines:
cancelled?
40009 1 p1 n
40009 2 p4 y
40009 3 p2 n
40009 4 p5 y
40009 5 p3 n

Sometimes line item no 1 can be a cancelled item, there can be a number of consecutive lines that get cancelled.

Any suggestions please.

 

[lbass]

Please identify your CR version and also show what the SQL query looks like when you add the three tables to one report (just so I can see the actual field and database names). Please also identify the type of datasource.

-LB

 

[abatblack]

Many thanks for the reply.

I'm using CR11.
SQL query is:

SELECT "SO_Header"."SO_No", "SO_Items"."SO_Line_No", "SO_Items"."Part_No", "SO_Cancellations"."SO_Line_No", "SO_Cancellations"."Part_No"
FROM ("Javelin"."sales"."SO_Header" "SO_Header" INNER JOIN "Javelin"."sales"."SO_Items" "SO_Items" ON "SO_Header"."SO_No"="SO_Items"."SO_No") LEFT OUTER JOIN "Javelin"."sales"."SO_Cancellations" "SO_Cancellations" ON "SO_Header"."SO_No"="SO_Cancellations"."SO_No"
WHERE "SO_Header"."SO_No"=N'40009'

This query produces a nonsensical report with duplicate entries from SO_Items for every entry in SO_cancellations - but it does show you the links.

Database type is an OLE DB (ADO)
Provider SQLOLEDB

 

[lbass]

Use a command as your sole datasource, using a query like this:

SELECT 'Original' "Type",
"SO_Header"."SO_No",
"SO_Items"."SO_Line_No",
"SO_Items"."Part_No",
FROM (
"Javelin"."sales"."SO_Header" "SO_Header"
INNER JOIN "Javelin"."sales"."SO_Items" "SO_Items" ON
"SO_Header"."SO_No"="SO_Items"."SO_No"
)
WHERE "SO_Header"."SO_No"=N'40009'

Union All

SELECT 'Cancellations' "Type",
"SO_Cancellations"."SO_No",
"SO_Cancellations"."SO_Line_No",
"SO_Cancellations"."Part_No"
FROM "Javelin"."sales"."SO_Cancellations" "SO_Cancellations"
WHERE "SO_Cancellations"."SO_No"=N'40009'

This will merge the fields in the same ordinal position in the select statement on each side of the union all. You can then use {command.type} to indicate whether a particular record is a cancellation or the original order. You can sort on {Command.SO_Line_No}.

-LB