Get DocNum from either OQUT and ORDR

[lsauser]

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.

[lsauser]

To further clarify about my problem with this one;

I need to know that if a linked Sales Quote exists then it should display the quote number from that, if however a sales quote does not exist then it will display the order number. However, if both exist then it will display the sales quote number and not the order number.

I have a subreport as well for the parts on the quote/order and struggling to solve a similar issue. If a quote exists it should display the parts on that quote, if not then the parts on the order. If both exist then it should display the parts on the quote and not the order.

Ideally I would like to do this without subreports if possible.

[Johan_H]

Hi Nick,

You can try using subqueries in a COALESCE function. Something like this:

COALESCE((select DocNum from OQUT where etc.), (select DocNum from ORDR where etc.), -1) AS [Quote/Order nr.]

Regards,

Johan

[lsauser]

Hey johan.hakkesteegt

Thanks for your input. I have never used the COALESCE function before. when i try and use it i get an error message saying subquery returned more than 1 value. This is not permitted when the subquery follow (all the comparables) or used as an expression.

[Johan_H]

Hi Nick,

That means that there are multiple Quotes and/or Orders connected to your Service Call, or you need to edit the where clauses of the subqueries. One approach is to use TOP 1:

COALESCE((select top 1 DocNum from OQUT where etc.),(select top 1 DocNum from ORDR where etc.),-1) AS [Quote/Ordernr.]

However, in case you expect there to be multiple Quotes and/or Orders per Service Call, you would only ever get to see one.

If you expect there to be multiple Quotes and/or Orders per Service Call, the question is, are Orders drawn from Quotes, or are they possibly made separately? In case of separate documents, you will have to think about how to relate them to each other, before we can fix the query.

Regards,

Johan