Query to get all Sales

[Former Member]

I want to make a query to get DocTotal and DocDate from ORDR and OINV. Both of them should be joined withOUSR to get the field U_NAME (Created_By).

The thing is, I want all entries from ORDR which aren't cancelled and from OINV I want only the values which are not based on an order...

This query returns the DocEntry values for all the invoices which aren't cancelled nor based on an order:

SELECT DISTINCT T1.DocEntry AS FACTURA, T2.DocEntry AS ENTREGA, T5.DocEntry AS PEDIDO

FROM OINV  T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN DLN1 T2 ON T2.DocEntry = T1.BaseEntry

LEFT JOIN ODLN T3 ON T3.DocEntry = T2.DocEntry

LEFT JOIN RDR1 T4 ON (T4.DocEntry = T1.BaseEntry and T1.BaseType='17') or T4.DocEntry = T2.baseentry

LEFT JOIN ORDR T5 ON T5.DocEntry = T4.DocEntry

WHERE T5.DocEntry IS NULL

AND T0.CANCELED = 'N' AND t1.TARGETTYPE <> '14'

ORDER BY 1 ASC

This one was kind of ok but it didn't work:

SELECT (CASE WHEN OINV.DocEntry IN

(SELECT T0.DocEntry

FROM OINV  T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN DLN1 T2 ON T2.DocEntry = T1.BaseEntry

LEFT JOIN ODLN T3 ON T3.DocEntry = T2.DocEntry

LEFT JOIN RDR1 T4 ON (T4.DocEntry = T1.BaseEntry and T1.BaseType='17') or T4.DocEntry = T2.baseentry

LEFT JOIN ORDR T5 ON T5.DocEntry = T4.DocEntry

INNER JOIN OUSR T6 ON T0.UserSign = T6.USERID

WHERE T5.DocEntry IS NULL AND T0.CANCELED = 'N' AND T1.TARGETTYPE <> '14')

THEN T0.DocTotal, T0.DocDate, T6.U_NAME FROM

OINV T0 INNER JOIN OUSR T6 ON T0.UserSign = T6.USERID ELSE

T5.DocTotal, T5.DocDate, T6.U_Name FROM

ORDR T5 INNER JOIN OUSR T6 ON T5.UserSign = T6.USERID

WHERE T5.CANCELED = 'N'

END

Then I thought about temporary tables...

BEGIN TRAN

DECLARE @FACT TABLE(FACTID NVARCHAR(5));

INSERT INTO @FACT (FACTID)

SELECT DISTINCT T0.DocEntry

FROM OINV  T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN DLN1 T2 ON T2.DocEntry = T1.BaseEntry

LEFT JOIN ODLN T3 ON T3.DocEntry = T2.DocEntry

LEFT JOIN RDR1 T4 ON (T4.DocEntry = T1.BaseEntry and T1.BaseType='17') or T4.DocEntry = T2.baseentry

LEFT JOIN ORDR T5 ON T5.DocEntry = T4.DocEntry

INNER JOIN OUSR T6 ON T0.UserSign = T6.USERID

WHERE T5.DocEntry IS NULL AND T0.CANCELED = 'N' AND T1.TARGETTYPE <> '14'

SELECT * FROM @FACT

IF (OINV.DocEntry IN (SELECT FACTID FROM @FACT) (SELECT A.DocTotal, A.DocDate, B.U_NAME FROM OINV A INNER JOIN OUSR B ON A.UserSign = B.USERID)

ELSE (SELECT C.DocTotal, C.DocDate, B.U_NAME FROM ORDR C INNER JOIN OUSR B ON C.UserSign = B.USERID)

ROLLBACK TRAN

The problem with this one is that it doesn't recognize OINV.DocEntry as a field, and if I use $[OINV.DocEntry] it doesn't work either...

What do you think? Is this possible? How can I do it?

Thanks!

[Former Member · Accepted solution]

Hi Richard,

SELECT T1.[DocEntry], T2.[U_NAME], T3.[DocEntry], T5.[DocEntry], T7.[U_NAME] FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry LEFT JOIN OUSR T2 ON T0.UserSign = T2.USERID LEFT join DLN1 T3 on T3.[BaseEntry]  = t1.docentry and  T3.[BaseLine] =  T1.[LineNum] INNER JOIN ODLN T4 ON T3.DocEntry = T4.DocEntry LEFT join RDR1 T5 on (T5.[BaseEntry] = t3.docentry and  T5.[BaseLine] =  T3.[LineNum]  and T1.BaseType='17' ) or ( T5.[BaseEntry]  = t1.docentry and  T5.[BaseLine] =  T1.[LineNum]) INNER JOIN ORDR T6 ON T5.DocEntry = T6.DocEntry INNER JOIN OUSR T7 ON T6.UserSign = T7.USERID WHERE T0.[CANCELED] ='N' and t1.TARGETTYPE <> '14'

Hope this help

--

--

Regards::::

Atrul Chakraborty

[Former Member · Accepted solution]

Hi Atrul!

It returns an empty resultset, do you have another idea?

Thanks!

[Former Member]

Hi!

I am trying this:

BEGIN TRAN

DECLARE @FACT TABLE(FACTID NVARCHAR(5));

INSERT INTO @FACT (FACTID)

(SELECT DISTINCT T0.DocEntry

        FROM OINV  T0

            INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

            LEFT JOIN DLN1 T2 ON T2.DocEntry = T1.BaseEntry

            LEFT JOIN ODLN T3 ON T3.DocEntry = T2.DocEntry

            LEFT JOIN RDR1 T4 ON (T4.DocEntry = T1.BaseEntry and T1.BaseType='17') or T4.DocEntry = T2.baseentry

            LEFT JOIN ORDR T5 ON T5.DocEntry = T4.DocEntry

            INNER JOIN OUSR T6 ON T0.UserSign = T6.USERID

            WHERE T5.DocEntry IS NULL AND T0.CANCELED = 'N' AND T1.TARGETTYPE <> '14')

SELECT * FROM @FACT

IF EXISTS ( (SELECT A.DocTotal, A.DocDate, B.U_NAME FROM OINV A INNER JOIN OUSR B ON A.UserSign = B.USERID)

ELSE (SELECT C.DocTotal, C.DocDate, B.U_NAME FROM ORDR C INNER JOIN OUSR B ON C.UserSign = B.USERID

WHERE OINV.DocEntry IN (SELECT FACTID FROM @FACT)

)

ROLLBACK TRAN

But I get a Syntax Error on "ELSE"

How can I get it right?

[kothandaraman_nagarajan]

Hi,

Follow below sample for IF EXIST ELSE statement. I think you need to some condition before ELSE.

if EXISTS

(select * from authors where au_id = '172-32-1176') Print 'Record exits - Update'

ELSE

Print 'Record doesn''t exist - Insert'

Thanks & Regards,

Nagarajan

[kothandaraman_nagarajan]

Hi,

Try this:

SELECT T1.[DocEntry], T2.[U_NAME], T3.[DocEntry], T5.[DocEntry], T7.[U_NAME] FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OUSR T2 ON T0.UserSign = T2.USERID left join DLN1 T3 on T3.[BaseEntry]  = t1.docentry and  T3.[BaseLine] =  T1.[LineNum] INNER JOIN ODLN T4 ON T3.DocEntry = T4.DocEntry left join RDR1 T5 on (T5.[BaseEntry] = t3.docentry and  T5.[BaseLine] =  T3.[LineNum]  and T1.BaseType='17' ) or ( T5.[BaseEntry]  = t1.docentry and  T5.[BaseLine] =  T1.[LineNum]) INNER JOIN ORDR T6 ON T5.DocEntry = T6.DocEntry INNER JOIN OUSR T7 ON T6.UserSign = T7.USERID WHERE T0.[CANCELED] ='N' and t1.TARGETTYPE <> '14'

Thanks & Regards,

Nagarajan

[Former Member]

Thank you Nagarajan!
Unfortunately this returns an empty resultset. Do you know how to fix it?

[kothandaraman_nagarajan]

Hi,

Try this:

SELECT T1.[DocEntry], T2.[U_NAME], T3.[DocEntry], T5.[DocEntry], T7.[U_NAME] FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry left JOIN OUSR T2 ON T0.UserSign = T2.USERID left join DLN1 T3 on T3.[BaseEntry]  = t1.docentry and  T3.[BaseLine] =  T1.[LineNum] INNER JOIN ODLN T4 ON T3.DocEntry = T4.DocEntry left join RDR1 T5 on (T5.[BaseEntry] = t3.docentry and  T5.[BaseLine] =  T3.[LineNum]  and T1.BaseType='17' ) or ( T5.[BaseEntry]  = t1.docentry and  T5.[BaseLine] =  T1.[LineNum]) INNER JOIN ORDR T6 ON T5.DocEntry = T6.DocEntry left JOIN OUSR T7 ON T6.UserSign = T7.USERID WHERE T0.[CANCELED] ='N' and t1.TARGETTYPE <> '14' group by T1.[DocEntry], T2.[U_NAME], T3.[DocEntry], T5.[DocEntry], T7.[U_NAME]

Thanks & Regards,

Nagarajan

[Former Member]

Hi!

It also returns an empty resultset