Skip to Content
avatar image
Former Member

How to Display the Credit value in new Credit Column

Hi,

This is my SQLQuery.

SELECT distinct OINV.DocDueDate AS 'Due date ',OCRD.GroupNum AS 'Payment Terms',(OINV.Doctotal)- (OCRD.balance) as 'Credit 7'

FROM OINV INNER JOIN

OCRD ON OINV.CardCode = OCRD.CardCode

WHERE (OINV.DocDueDate BETWEEN '04/20/2011' and '06/06/2011' and OINV.CardCode='c00032')

union all

SELECT distinct ORCT.DocDueDate AS 'Due date',OCRD.GroupNum AS 'Payment Terms',(ORCT.NoDocSum)- (OCRD.balance) as 'Credit 7'

FROM ORCT INNER JOIN

OCRD ON ORCT.CardCode = OCRD.CardCode

WHERE (ORCT.DocDueDate BETWEEN '04/20/2011' and '06/06/2011' and ORCT.CardCode ='c00032')

-


when i execute this Query ,then it will display :- Date = "2011-05-21" , Payment terms = "7 " and Credit 7 = "-74660.00"

Now i want to add two more Columns Credit 5 and Credit 9 in this Query and

I want if Payment terms =5 , the it should display the Credit in another new column Credit 5

and if Payment terms =9 , the it should display the Credit in another new column Credit 9

and if Credit displays in one Credit column then other Credit Column's value becomes zero.

Kindly give me the solution to solve the above problem

Regards

Rahul

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Jun 06, 2011 at 12:00 PM

    Hi Rahul,

    Try this:

    SELECT distinct T1.DocDueDate 'Due date',

    T0.GroupNum 'Payment Terms',

    (T1.Doctotal- T0.balance) 'Credit 5',

    (T2.Doctotal- T0.balance) 'Credit 7',

    (T3.Doctotal- T0.balance) 'Credit 9'

    FROM OCRD T0

    LEFT JOIN OINV T1 ON T1.CardCode = T0.CardCode AND T1.GroupNum = 5

    LEFT JOIN OINV T2 ON T2.CardCode = T0.CardCode AND T2.GroupNum = 7

    LEFT JOIN OINV T3 ON T3.CardCode = T0.CardCode AND T3.GroupNum = 9

    WHERE T1.DocDueDate BETWEEN [%0\] and [%1\] and T1.CardCode='[%2\]'

    I just show you the first part. If works, you can try union the other part.

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Try this:

      SELECT distinct T1.DocDueDate 'Due date',

      T0.GroupNum 'Payment Terms',

      CASE WHEN T0.GROUPNUM = 5 THEN (T1.Doctotal- T0.balance) ELSE 0 END 'Credit 5',

      CASE WHEN T0.GROUPNUM = 7 THEN (T1.Doctotal- T0.balance) ELSE 0 END 'Credit 7',

      CASE WHEN T0.GROUPNUM = 9 THEN (T1.Doctotal- T0.balance) ELSE 0 END 'Credit 9'

      FROM OCRD T0

      LEFT JOIN OINV T1 ON T1.CardCode = T0.CardCode

      WHERE T1.DocDueDate BETWEEN [%0\] and [%1\] and T1.CardCode='[%2\]'

  • avatar image
    Former Member
    Jun 07, 2011 at 08:10 AM

    HI ,

    I tried first half of this at the back end and it worked. You may try this.

    SELECT T1.[DocDueDate] ,

    CASE WHEN T0.GROUPNUM = 9 THEN (T1.DocTotal - T0.BALANCE) ELSE 0 END AS Test,

    CASE WHEN T0.GROUPNUM = 10 THEN (T1.DOCTOTAL -T0.BALANCE) ELSE 0 END AS Ttest1

    FROM OCRD T0 INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode

    WHERE T1.DocDueDate BETWEEN '04/20/11' AND '06/06/11'

    AND T1.CardCode = 'CM0247'

    Thanks

    Malhaar

    Add comment
    10|10000 characters needed characters exceeded