2 Unlike Tables in Details

[MrArmstrong]

I am running CR XI, I need to know if there is anyway to show the details of two different tables. They are not tables that should be linked together but they have similar data. Example:

Table 1- Customer Orders
Customer Orders.Partnumber
Customer Orders.Qty
Customer Orders.Price
Customer Orders.Date
Customer Orders.Name

Table 2- Company Orders
Company Orders.Partnumbers
Company Orders.Qty
Company Orders.Price
Company Orders.Date
Company Orders.Name

I am trying to create a (Total on Order Report), that will have a list of Orders for the Customer and our Company on one report.

 

[lbass]

What kind of display are you envisioning? You could insert one of the tables in a subreport, and potentially link it to the main report on the part number, where the table containing all the part numbers is the main report table.

Or you could use a command with a union statement that where you would create a field that identifies the records as company vs customer records and which merges the two sets of fields into one set. You could then use conditional formulas to compare back and forth.

-LB

 

[MrArmstrong]

Here is an example:
Part Number Qty Price Date Customer Type
490445-98 63 87 12/6/2005 Tool Room Company
145E3569-1 1 45 8/7/2006 Bill Johnson Customer
114S5589-7 5 98 9/6/2007 Emily Thomas Customer
4500-963 25 875 11/7/2009 Avionics Shop Company


Here is an example of how I want it to look, picturing this in Crystal Reports. I want them all to show up in the details, and the way to know which table they are would be by the Type field.

 

[lbass]

Then set up the command something like this:

Select 'Customer' as Type, Customer Orders.`Partnumber`,Customer Orders.`Qty`, Customer Orders.`Price`, Customer Orders.`Date`, Customer Orders.`Name`
From Customer Orders
Where //add your criteria here if any
Union All
Select 'Company' as Type, Company Orders.`Partnumber`,Company Orders.`Qty`, Company Orders.`Price`, Company Orders.`Date`, Company Orders.`Name`
From Company Orders
Where //add your criteria here if any

If you have any selection criteria, they belong in the where clauses in both components of the union all. The punctuation will be specific to your datasource. If you are unsure, take a look at the "Show SQL Query" in an existing report using the same datasource. The syntax for setting up the type field might be different, too. In Oracle, you would use:

'Company' "Type"

...instead of 'Company' as Type.

-LB

 

[MrArmstrong]

I am still trying to learn this SQL stuff, this is the SQL statements from these to reports that are just alike:

SELECT "SO_HDR"."CUST_REF3", "SO_HDR"."DOC_NO", "SO_HDR"."DOC_TYPE",
"SO_LINE"."PARTNUMBER", "SO_LINE"."QORDER", "PO_LINE"."DOC_NO",
"SO_LINE"."STATUS", "SO_LINE"."UNITP", "SO_LINE"."SUBTOTAL",
"SO_HDR"."CUST_REF4", "STOCK"."STK_TYPE", "STOCK"."CATEGORY",
"SO_LINE"."ADDED_DTE", "STOCK"."MFG"
FROM "GDB_01_BOEING3"."dbo"."STOCK" "STOCK" FULL OUTER JOIN
("GDB_01_BOEING3"."dbo"."PO_LINE" "PO_LINE" FULL OUTER JOIN
("GDB_01_BOEING3"."dbo"."SO_HDR" "SO_HDR" INNER JOIN
"GDB_01_BOEING3"."dbo"."SO_LINE" "SO_LINE" ON
"SO_HDR"."DOC_NO"="SO_LINE"."DOC_NO") ON
("PO_LINE"."CONV_DOC"="SO_LINE"."DOC_NO") AND
("PO_LINE"."CONV_LN"="SO_LINE"."LINE")) ON
"STOCK"."PARTNUMBER"="SO_LINE"."PARTNUMBER"
WHERE ("SO_LINE"."STATUS"='O' OR "SO_LINE"."STATUS"='P') AND
"PO_LINE"."DOC_NO" IS NULL AND "SO_LINE"."QORDER">0 AND
"SO_HDR"."CUST_REF3" LIKE '0%'
UNION ALL SELECT "RFQ_HDR"."CUST_REF3", "RFQ_HDR"."DOC_NO",
"RFQ_HDR"."DOC_TYPE", "RFQ_LINE"."PARTNUMBER", "RFQ_LINE"."QORDER",
"RFQ_LINE"."UNITP", "RFQ_LINE"."SUBTOTAL", "RFQ_LINE"."STATUS",
"PO_LINE"."DOC_NO", "RFQ_HDR"."CUST_REF4", "STOCK"."STK_TYPE",
"STOCK"."CATEGORY", "RFQ_LINE"."ADDED_DTE", "STOCK"."MFG"
FROM "GDB_01_BOEING3"."dbo"."STOCK" "STOCK" FULL OUTER JOIN
("GDB_01_BOEING3"."dbo"."PO_LINE" "PO_LINE" FULL OUTER JOIN
("GDB_01_BOEING3"."dbo"."RFQ_HDR" "RFQ_HDR" INNER JOIN
"GDB_01_BOEING3"."dbo"."RFQ_LINE" "RFQ_LINE" ON
(("RFQ_HDR"."DOC_NO"="RFQ_LINE"."DOC_NO") AND
("RFQ_HDR"."ACCTNO"="RFQ_LINE"."ACCTNO")) AND
("RFQ_HDR"."SUBC"="RFQ_LINE"."SUBC")) ON
("PO_LINE"."CONV_DOC"="RFQ_LINE"."DOC_NO") AND
("PO_LINE"."CONV_LN"="RFQ_LINE"."LINE")) ON
"STOCK"."PARTNUMBER"="RFQ_LINE"."PARTNUMBER"
WHERE "RFQ_HDR"."CUST_REF3" LIKE '0%' AND "RFQ_LINE"."QORDER">0 AND
"PO_LINE"."DOC_NO" IS NULL AND "RFQ_LINE"."STATUS"<>'CV'

I receievd this message when it tried to get the data:

Failed to retrieve data from database
Details:42000:[Microsoft][SQL Server]{ODBC SQL Server Driver][SQL Server] Error converting data type varchar to numeric[Database Vendor Code:8114]

Just as a test I tried running the portion above the union, it worked ;then I ran only the portion below the union, it worked. It is something with the union.

 

[lbass]

These fields are in the wrong order in the second half of your union statement:

"RFQ_LINE"."UNITP",
"RFQ_LINE"."SUBTOTAL",
"RFQ_LINE"."STATUS",
"PO_LINE"."DOC_NO",

They should be in this order:

"PO_LINE"."DOC_NO",
"RFQ_LINE"."STATUS",
"RFQ_LINE"."UNITP",
"RFQ_LINE"."SUBTOTAL",

The fields on each half of the union have to exactly correspond and be of the same datatype as the field in the corresponding position.

-LB