Skip to Content
0
Jul 21, 2020 at 12:37 AM

Get DocNum from either OQUT and ORDR

99 Views

Hi all,

I have the following query for my crystal reports;

SELECT DISTINCT T0.custmrName, T0.callID,T0.BpShipAddr, T1.Name, T0.createDate,T0.subject,T0.manufSN, T0.internalSN,T0.itemCode,T0.itemName, CAST(T0."resolution" AS varchar(MAX)),T0.BPContact, T0.BPCellular, T2.firstName, T2.lastName
FROM OSCL T0
LEFT JOIN OSCT T1 ON T0.callType = T1.callTypeID
LEFT JOIN OHEM T2 ON T0.technician = T2.empID
LEFT JOIN SCL4 T3 ON T3.SrcvCallID = T0.callID

Now i also need to include either the quote number from a linked quote or the sales order from a linked sales order. If a linked quote does not exist then it should get the DocNum from the sales order, however if a quote exists it will always grab it from the linked quote (and not the sales order if both exist).

My issue is that I need to use INNER JOIN for both documents otherwise i get an extra record if more than one exist, if i use inner joins for both though then it displays no information. What would be the best way of going about this?

My current solution (which is a horrendous one at that) was to use subreports for both, but then if they both exist then it shows both which is not ideal.

As always any help is appreciated.