Skip to Content
author's profile photo Former Member
Former Member

Query to get all Sales

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!

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jun 06, 2014 at 07:58 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 06, 2014 at 12:38 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 06, 2014 at 11:22 PM

    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?

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.