Solved

How Do I write a query for three Database Field SQL queries as the Datasource

Posted on 2011-03-15
4
504 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:leason2
  • 2
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35141820
could you provide the tables definition with sample data and the desired output?
your description is hard to follow
0
 

Author Comment

by:leason2
ID: 35142476
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
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 35143328
SQL Expressions only work with SQL databases and only if you are using the tables directly.  If you are using a COmmand then you can't use a SQL expression.

Also a SQL Expression can only return 1 value so I don't think it would do what you want.

Did you try that SQL in the COMMAND?
What was the problem?

The SQL you provided was much different.

Is the issue trying to join the 2 select statements?

mlmcc
0
 

Author Closing Comment

by:leason2
ID: 35143754
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!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Crystal reports - finding a hidden reference 4 68
Crystal 2013 - Using Can Grow adds spaces in text 9 54
Crystal Reports--SubReport 3 49
AS400 BRMS - none enrolled tape 6 38
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now