Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Help with a join/query Post Reply Post New Topic
Page  of 2 Next >>
Author Message
igendreau
Newbie
Newbie


Joined: 15 May 2008
Online Status: Offline
Posts: 12
Quote igendreau Replybullet Topic: Help with a join/query
     Posted: 21 Sep 2009 at 11:24am

I have two tables that I need to join: OE_DETAILS and NOTES.  The two are logically joined by the field "OrderNo" which exists in each table.  The other field that matters is NOTES.NotesCode.  What I need to do is display all records from OE_DETAILS and a corresponding record from NOTES.

The problem is that I need a specific record from the NOTES table.  There can be 100 different notes for a given order.  I need all records from OE_DETAILS and the record from NOTES where NOTES.OrderNo = OE_DETAILS.OrderNo and NOTES.NotesCode = "OENote"
 
However, I want to pull all the OE_DETAILS records whether there is a corresponding Note or not.  Basically: "Pull all the Order Details.  If there is a corresponding note for this order with the code 'OENote", use it.  If not, leave it blank."
 
Clear as mud?  Pulling my hair out trying to get this to work.  All ears if anyone has a solution.  Thanks!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Sep 2009 at 12:36pm

You can do this in a view or stored procedure or a crystal Command as an outer join from OE_Details to a sub query on NOTES that limits it tp only the "OENote" records. e.g.

Select OE_DETAILS.field1, OE_DETAILS.field2, etc. , Notes.field1, notes.field2....
FROM OE_Details
left outer join
(Select *
from NOTES
WHERE NOTES.NOTESCODE='oeNote') Notes on OE_DETAILS.orderNo=Notes.OrderNo
IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 21 Sep 2009 at 7:22pm
try like this

Select OE_DETAILS.field1, OE_DETAILS.field2, etc. , Notes.field1, notes.field2....
FROM OE_Details
left outer join Notes on OE_DETAILS.orderNo=Notes.OrderNo and NOTES.NOTESCODE='oeNote'

HTH,
Jyothi
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Sep 2009 at 8:02pm
Jyothi,
Won't that still exclude records from OE_Details where there is a mtaching record in NOTES that does not have Notescode='oeNote'?
IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 21 Sep 2009 at 8:22pm
NO.It wont exclude.

Both the queries return same results.
Please let me know if i am wrong

Thanks,
Jyothi
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Sep 2009 at 9:22pm

I believe in your query if there is a set of records with the same OrderNo in NOTES that has no instance of notescode='oeNote' that 'matching' orderno will be excluded from the returned data.

It will left outerjoin first then apply the where clause to that joined set. THis can omit records under the circumstance that I gave.
 
If you do the where clause in a statement inside another statement it applies the where clause first and limits that table which can be left outer joined and not omit any records from the OE table.
Make sense? 


Edited by DBlank - 21 Sep 2009 at 9:24pm
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Sep 2009 at 9:30pm
if the circumstance I gave where there is at least one match between the tables and without having the occurance of the "oenote' row never happens in this data set then an easy solution would be to change the existing crystal table join to an outer join and change the select statment to:
isnull(NOTES.NotesCode) or NOTES.NotesCode = "OENote"


Edited by DBlank - 21 Sep 2009 at 9:32pm
IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 21 Sep 2009 at 10:05pm
in my quey NOTES.NOTESCODE='oeNote' condition is in left outer join not in where clause

can you please explain with an example data why it won't work?

thanks
Jyothi


IP IP Logged
SunilDutt
Newbie
Newbie


Joined: 21 Sep 2009
Online Status: Offline
Posts: 1
Quote SunilDutt Replybullet Posted: 21 Sep 2009 at 10:14pm

Hi DBlank,

I think Jyothi met the requirement.

Also, your outer join logic is a bit confusing with ISNULL.

Can you give it in detail?

 
IP IP Logged
igendreau
Newbie
Newbie


Joined: 15 May 2008
Online Status: Offline
Posts: 12
Quote igendreau Replybullet Posted: 22 Sep 2009 at 6:24am
Okay, not sure if I made things easier or worse  by giving you the scaled down version, so let's get complex! lol  Here is the SQL behind my current report, which has worked great up to this point:
 
 SELECT "HM_BACKLOG_DETAILS"."ENTITY", "HM_BACKLOG_DETAILS"."SUB_ENTITY", "HM_BACKLOG_DETAILS"."CUSTOMER_NO", "HM_BACKLOG_DETAILS"."CUSTOMER_LOC", "HM_BACKLOG_DETAILS"."CUSTOMER_NAME", "HM_BACKLOG_DETAILS"."SALESPERSON_NO", "HM_BACKLOG_DETAILS"."SALESPERSON_NO_1", "HM_BACKLOG_DETAILS"."SALESPERSON_NO_2", "HM_BACKLOG_DETAILS"."SALESPERSON_NO_3", "HM_BACKLOG_DETAILS"."PROJECT_NO", "HM_BACKLOG_DETAILS"."ORDER_NO", "HM_BACKLOG_DETAILS"."CUST_PO_NO", "HM_BACKLOG_DETAILS"."LINE_NO", "HM_BACKLOG_DETAILS"."ITEM_NO", "HM_BACKLOG_DETAILS"."ITEM_DESCRIPTION", "HM_OE_LINE_RECEIVING"."VENDOR_NO", "AP_VENDOR_MASTER"."DES1", "HM_OE_LINE_RECEIVING"."PO_NO", "HM_OE_LINE_RECEIVING"."PO_LINE_NO", "PR_EMP_MASTER"."DES1", "OE_HDR"."LEAD_SOURCE_CODE", "OE_LINE"."QTY_ORD", "OE_LINE"."MOD_UNIT_PRICE", "OE_LINE"."UNIT_COST", "HM_OE_LINE_INVOICE"."QTY_INVOICED", "HM_BACKLOG_DETAILS"."UNIT_COST", "HM_OE_LINE_RECEIVING"."QTY_REC", "HM_OE_LINE_SHIPPING"."QTY_SHIP", "OE_HDR"."PROJECT_MANAGER", "OE_HDR"."UD_FIELD_1", "OE_HDR"."ORD_DATE", "OE_LINE"."DATE_CREATED", ABS(HM_BACKLOG_DETAILS.QTY_ORDERED), NVL(ABS(HM_OE_LINE_INVOICE.QTY_INVOICED),0)
 FROM   (((((("KHAMELEON"."HM_BACKLOG_DETAILS" "HM_BACKLOG_DETAILS" LEFT OUTER JOIN "KHAMELEON"."HM_OE_LINE_RECEIVING" "HM_OE_LINE_RECEIVING" ON "HM_BACKLOG_DETAILS"."ORDER_NO"="HM_OE_LINE_RECEIVING"."ORD_NO" AND "HM_BACKLOG_DETAILS"."LINE_NO"="HM_OE_LINE_RECEIVING"."LINE_NO") LEFT OUTER JOIN "KHAMELEON"."HM_OE_LINE_SHIPPING" "HM_OE_LINE_SHIPPING" ON "HM_BACKLOG_DETAILS"."ORDER_NO"="HM_OE_LINE_SHIPPING"."ORD_NO" AND "HM_BACKLOG_DETAILS"."LINE_NO"="HM_OE_LINE_SHIPPING"."LINE_NO") LEFT OUTER JOIN "KHAMELEON"."HM_OE_LINE_INVOICE" "HM_OE_LINE_INVOICE" ON "HM_BACKLOG_DETAILS"."ORDER_NO"="HM_OE_LINE_INVOICE"."ORD_NO" AND "HM_BACKLOG_DETAILS"."LINE_NO"="HM_OE_LINE_INVOICE"."LINE_NO") INNER JOIN "KHAMELEON"."OE_LINE" "OE_LINE" ON "HM_BACKLOG_DETAILS"."ORDER_NO"="OE_LINE"."ORD_NO" AND "HM_BACKLOG_DETAILS"."LINE_NO"="OE_LINE"."LINE_NO") LEFT OUTER JOIN "KHAMELEON"."AP_VENDOR_MASTER" "AP_VENDOR_MASTER" ON "HM_OE_LINE_RECEIVING"."VENDOR_NO"="AP_VENDOR_MASTER"."VENDOR_NO") INNER JOIN "KHAMELEON"."OE_HDR" "OE_HDR" ON "OE_LINE"."ORD_NO"="OE_HDR"."ORD_NO") LEFT OUTER JOIN "KHAMELEON"."PR_EMP_MASTER" "PR_EMP_MASTER" ON "OE_HDR"."PROJECT_MANAGER"="PR_EMP_MASTER"."EMP_NO"                     
 WHERE  ABS(HM_BACKLOG_DETAILS.QTY_ORDERED)>NVL(ABS(HM_OE_LINE_INVOICE.QTY_INVOICED),0) AND ABS (HM_BACKLOG_DETAILS.QTY_ORDERED) > NVL(ABS(HM_OE_LINE_INVOICE.QTY_INVOICED),0)                   
 ORDER BY HM_BACKLOG_DETAILS."ORDER_DATE" ASC,      HM_BACKLOG_DETAILS."ORDER_NO" ASC,      HM_BACKLOG_DETAILS."LINE_NO" ASC    

Now, to this source I need to add the following:
I need to SELECT "NOTE_CODE", "SUBJECT_DES1", "NOTE" from table CT_NOTES where NOTE_CODE = "OENOTE"

and table CT_NOTES.ORD_NO is joined to HM_BACKLOG_DETAILS.ORDER_NO (but I need to return all records in the Backlog table, whether or not there is an OENOTE note found in CT_NOTES.  I just don't want to return any NOTE_CODES with other values)
 
How's that for confusing? lol  Thanks again for all the help.
IP IP Logged
Page  of 2 Next >>
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.