cancel
Showing results for 
Search instead for 
Did you mean: 

How to Display the Credit value in new Credit Column

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member218051
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

Hi Gordon,

There are only two tables in the First Query :- OINV And OCRD,

But the solution that u have given , it show four tables :- t0, t1, t2, t3

so, Kindly let me know how can i join these two tables so that i get the required data.

Thanks

Rahul

Former Member
0 Kudos

One table can be linked more than one time. Here is the case. Have you tried?

Former Member
0 Kudos

How can i do this ,Tell me the complete solution with Query

Thanks

Rahul

Former Member
0 Kudos

Just copy my query and try. It works.

Former Member
0 Kudos

Hi,

Thanks, Your solution is really working, but now i have one question

i want if GroupNum =7 then ,it should display the data in Credit 7 Column and rest Credit 5 and Credit 9 should be hide (Credit 5 and Credit 9 columns should not be shown in the data)

Similarly, if GroupNum =5 then, it should display the data is Credit 5 Column and rest Credit 7 and Credit 9 should be hide

Kindly give me the solution for this problem.

Regards

Rahul

Former Member
0 Kudos

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\]'