How Do I write a query for three Database Field SQL queries as the Datasource
Good day!
I am having trouble trying to write the SQL statement to join the SQL Tables in my Database Expert. I have 3 tables. (1)Base, (2)OOHEADNotes, and (3) OOLINENotes. The 3 tables are SQL statements off one database, which is a DB2.
My goal is to have a text label field in the report with the SQLformula 'OrderHeadNotes' which would be like:
SELECT OOHEADNOTES.OATX60 FROM OOHEADNOTES as OO
INNER JOIN BASE as B ON (OO.OATXID=B.OATXID or OO.OAPOTX=B.OAPOTX or OO.OAPRTX=B.OAPRTX)
I need the actual Notes field (OATX60) based on the PK's of BASE (OATXID,OAPOTX,OAPRTX) joined to the FK's of OOHEADNotes(OATXID, OAPRTX, OAPOTX).
I have included the report.
OpenOrders-AddOrderHeadNotes-Ph3.sql
I am having trouble trying to write the SQL statement to join the SQL Tables in my Database Expert. I have 3 tables. (1)Base, (2)OOHEADNotes, and (3) OOLINENotes. The 3 tables are SQL statements off one database, which is a DB2.
My goal is to have a text label field in the report with the SQLformula 'OrderHeadNotes' which would be like:
SELECT OOHEADNOTES.OATX60 FROM OOHEADNOTES as OO
INNER JOIN BASE as B ON (OO.OATXID=B.OATXID or OO.OAPOTX=B.OAPOTX or OO.OAPRTX=B.OAPRTX)
I need the actual Notes field (OATX60) based on the PK's of BASE (OATXID,OAPOTX,OAPRTX) joined to the FK's of OOHEADNotes(OATXID, OAPRTX, OAPOTX).
I have included the report.
OpenOrders-AddOrderHeadNotes-Ph3.sql
ASKER
Here go. I included 2 screenshots. (1) is the TableDefinitions. (2) is ReportExample. In TableDefinitions, the upper list of fields missing the header belong to table "Base".
In ReportExample, the TextLabel listing "OOHeadNotes Here" is where I need to list the resultset of the OrderHead Notes.
I will then do the same for OOLineNotes, but with the explanation of how to format the SQL correctly I can add the LineNotes myself.
I need to return all occurrences of field (OOHEADNOTES.OHEADNOTES) for every Order Number( Base.OBORNO) and list them into the TextLabel in the report.
Here is how I would write it in plain SQL. But Crystal doesn't like it.
The join is interesting, because an OrderNo (OBORNO) has three different versions of notes. Notice the three join options.
SELECT OO.OHEADNotes FROM OOHEADNOTES as OO
INNER JOIN BASE as B ON ( OO.OATXID=B.OATXID or OO.OAPOTX=B.OAPOTX or OO.OAPRTX=B.OAPRTX )
Even if the SQL doesn't fit, can you give me an example of how to write the above SQL in the formula editor.
One other issue, in the book "Crystal Reports XI Official Guide" p.271 mentions to "Right-Click the SQL Expressions item in the Field Explorer and select 'New' from the dropdown"...
My problem is- I am also using CRXI ver.2, but I have no 'SQL Expression' in my Field Explorer.
Thanks
ReportExample.png
TableDefinitions.png
In ReportExample, the TextLabel listing "OOHeadNotes Here" is where I need to list the resultset of the OrderHead Notes.
I will then do the same for OOLineNotes, but with the explanation of how to format the SQL correctly I can add the LineNotes myself.
I need to return all occurrences of field (OOHEADNOTES.OHEADNOTES) for every Order Number( Base.OBORNO) and list them into the TextLabel in the report.
Here is how I would write it in plain SQL. But Crystal doesn't like it.
The join is interesting, because an OrderNo (OBORNO) has three different versions of notes. Notice the three join options.
SELECT OO.OHEADNotes FROM OOHEADNOTES as OO
INNER JOIN BASE as B ON ( OO.OATXID=B.OATXID or OO.OAPOTX=B.OAPOTX or OO.OAPRTX=B.OAPRTX )
Even if the SQL doesn't fit, can you give me an example of how to write the above SQL in the formula editor.
One other issue, in the book "Crystal Reports XI Official Guide" p.271 mentions to "Right-Click the SQL Expressions item in the Field Explorer and select 'New' from the dropdown"...
My problem is- I am also using CRXI ver.2, but I have no 'SQL Expression' in my Field Explorer.
Thanks
ReportExample.png
TableDefinitions.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good Call. I am taking your opinion and redoing the SQL. I just did a Recursive SQL statement to 'pivot' all the rows of Notes into one row. Thank you for the solution though!
your description is hard to follow