Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
Click Here
|
Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
|
Crystal with table joins |
Author |
Message |
jdc |
|
Group | : Members |
Posts | : 4 |
Joined | : 2009-02-06 |
|
Profile |
|
Posted : 2009-02-19 07:52:47 |
I am currently using QODBC 9.00.00.253 with Crystal Professional XI Release 2. I am very familiar with Crystal and table linking but am having a problem with QBE V9.
I am using the PO line table and need to get additional item information so I tried a left outer join from the PO line table to the Item table on the PurchaseOrderLine.PurchaseOrderLineItemRefListID = Item.ListID. I also tried an equal join (which I really fo not want).
I am not getting any matches. Can you please tell me what I am doing wrong? Thanks so much!
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2009-02-19 10:54:37 |
Make sure auto-linking is off, otherwise Crystal Reports will link any columns matches it can find and you will never get any data. |
|
|
|
jdc |
|
Group | : Members |
Posts | : 4 |
Joined | : 2009-02-06 |
|
Profile |
|
Posted : 2009-02-19 11:09:03 |
Thanks Tom - I cleared all links when I pulled in the tables and then created my own link and still no data. Any other suggestions? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2009-02-19 17:08:02 |
Not really, you haven't even said what SQL Statement Crystal Reports generated. This works using VB Demo:
SELECT PurchaseOrderLine.PurchaseOrderLineCustomerRefFullName as Type, PurchaseOrderLine.TxnDate as Date, PurchaseOrderLine.DueDate as "Deliv Date", PurchaseOrderLine.RefNumber as "Num", PurchaseOrderLine.VendorRefFullName as SourceName, PurchaseOrderLine.PurchaseOrderLineItemRefListID as ItemCode, PurchaseOrderLine.PurchaseOrderLineItemRefFullName as Item, PurchaseOrderLine.PurchaseOrderLineQuantity as Qty, PurchaseOrderLine.PurchaseOrderLineReceivedQuantity as Rcvd, PurchaseOrderLine.PurchaseOrderLineRate as Cost, PurchaseOrderLine.TotalAmount as Amount, Item.SalesPrice as SalesPrice FROM PurchaseOrderLine NOSYNC LEFT OUTER JOIN Item NOSYNC ON PurchaseOrderLine.PurchaseOrderLineItemRefListID =Item.ListID |
|
|
|
jdc |
|
Group | : Members |
Posts | : 4 |
Joined | : 2009-02-06 |
|
Profile |
|
Posted : 2009-02-20 00:58:28 |
Sorry Tom. Here is the code generated by Crystal:
SELECT "PurchaseOrderLine"."VendorRefFullName", "PurchaseOrderLine"."TxnDate", "PurchaseOrderLine"."PurchaseOrderLineItemRefListID", "PurchaseOrderLine"."PurchaseOrderLineQuantity", "Item"."PurchaseCost", "PurchaseOrderLine"."DueDate", "PurchaseOrderLine"."PurchaseOrderLineReceivedQuantity", "PurchaseOrderLine"."PurchaseOrderLineAmount", "PurchaseOrderLine"."PurchaseOrderLineRate", "PurchaseOrderLine"."RefNumber" FROM "PurchaseOrderLine" "PurchaseOrderLine" LEFT OUTER JOIN "Item" "Item" ON "PurchaseOrderLine"."PurchaseOrderLineItemRefListID"="Item"."ListID" ORDER BY "PurchaseOrderLine"."VendorRefFullName", "PurchaseOrderLine"."RefNumber"
BTW, I am getting data, but nothing from the Item table. I am also receiving no error message. Thanks Tom! |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2009-02-20 09:59:58 |
Run a report on just the ITEM table. If you get no results, rebuild the optimized ITEM table by doing:
SELECT * from ITEM verify |
|
|
|
jdc |
|
Group | : Members |
Posts | : 4 |
Joined | : 2009-02-06 |
|
Profile |
|
Posted : 2009-02-20 11:30:14 |
I ran the query:
SELECT * from ITEM verify
and it did not change the results. I then played around with the Crystal report and if I remove all of the SORTS and GROUP BY information, the tables do in fact join successfully and I get the results I am looking for. If I just add a SORT on one field, the join does NOT work. Also, if I try to GROUP BY one field, the join foes NOT work.
I am really puzzled here because in the format it is in, it is not acceptable. Could this issue be caused by the fact that I am running Crystal XI Release 2?
Thanks Tom! |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2009-02-20 13:12:43 |
Sorry, I don't have any experience with Release 2. The other databases I work with don't work with Release 2, so I've had to stay away from it myself. But that doesn't matter. Just use the SQL using Add Command instead.
Creating a Report with SQL or an existing QuickBooks Report For sp_reports (see QODBC sp_report) or manual SQL statements, Highlight “Add Command” and move the right panel.
The following window will appear. Type or paste your SQL statement or sp_report command as shown, then click “OK”
Your SQL statement is now entered; (to edit, just right click on “Command”). Click “OK”
The fields as specified in your SQL statement, are now available for selection in your report.
|
|
|
|
|