GETTING AN ERROR WHILE RE-USING PARTAILLY CREATED COLUMN IN SQL

[Albatross]

Hi There!
The below query i wrote for a requirement. The thing is 2 columns RECEIPT(C) & ISSUE (D) were not there in my DB so i'hv created those using 2 columns named DEBIT/CREDIT (A) & BALANCE QUANTITY(B) MSEG.MENGE , Like if D/C is 'S' then the bal.quantity value should be stored in 'receipt' field and if D/C is 'H' then the bal.quantity value should store in 'Issue' field. Now i got the receipt and issue field values correctly but balance quantity field values are not correct becoz there are some calculations involved in that. In that calculation i have to reuse the receipt field but when i'm using RECEIPT field while creating the balance quantity field i'm getting an error ( INVALID COLUMN) can anybody give me a suggestion? Error is at 3rd and 4th case statements.
NOTE : RECEIPT AND ISSUE FIELDS I HAVE POPULATED USING THE ABOVE LOGIC

select  distinct T001L.LGORT as sloc,MKPF.BUDAT as Year,MKPF.XBLNR as ReferenceDoc,MKPF.XABLN as GRGISlip,
MSEG.BWART as MovementType,MSEG.SHKZG as DC,
 
(select case MSEG.SHKZG when 'S' then MSEG.MENGE else 0.0 end) as Receipt,
 
(select case MSEG.SHKZG when 'H' then MSEG.MENGE else 0.0 end) as Issue,
 
MSEG.DMBTR as Amount,MSEG.MENGE as BalQuantity,
(select case MSEG.SHKZG WHEN 'S' THEN SUM(BalQuantity+Receipt)else 0.0 end)as BalQuantity,
 
(select case MSEG.SHKZG WHEN 'H' THEN MSEG.MENGE,Issue(MSEG.MENGE-Issue)else 0.0 end)as BalQuantity
MBEW.VERPR as PricePerUnit, MBEW.SALK3 as BalanceAmount,
t156t.BTEXT as Comment 
  from mara
 
INNER join mseg  on mara.MATNR=mseg.MATNR AND mara.MEINS=mseg.MEINS AND mara.BSTME=mseg.BSTME
 AND mara.KUNNR=mseg.KUNNR

INNER  join t001l on t001l.LGORT=mseg.LGORT AND t001l.WERKS=mseg.WERKS
 AND t001l.LIFNR=mseg.LIFNR AND t001l.KUNNR=mseg.KUNNR

INNER  join mkpf on mkpf.MBLNR=mseg.MBLNR AND  mkpf.MJAHR=mseg.MJAHR

INNER join mbew on mbew.MATNR=MARA.MATNR AND MBEW.LVORM=MARA.LVORM

INNER  join mard on mard.MATNR=MSEG.MATNR 
--AND mard.PSTAT=mbew.PSTAT 
AND mard.LVORM=mbew.LVORM
AND mard.LFGJA=mbew.LFGJA AND mard.LFMON=mbew.LFMON

INNER JOIN t156t
 
     ON  t156t.bwart = mseg.bwart
 
     AND t156t.sobkz = mseg.sobkz
 
     AND t156t.kzbew = mseg.kzbew
 
     AND t156t.kzzug = mseg.kzzug
 
     AND t156t.kzvbr = mseg.kzvbr
 
where
 
MARA.MATNR='1L90MHVB10250A'
 
and (MKPF.budat between '20200101' and '20231231')
 
and MSEG.BUKRS='VN01' and MSEG.WERKS='VNSD' AND t001l.lgort='RM01'and t156t.SPRAS='E' ;

[Sandra_Rossi]

As I said one week ago: "You can find by yourself why the query gives duplicate lines, by removing one table at a time till you get unique lines: it's the last table removed which makes the duplicate lines."

But I guess there's something you don't understand in my reply, because you keep duplicating your question (3rd time now) without asking any precision on any part of my reply...

[Albatross]

No @Sandra kindly read my question once again.....it's not at all about duplicates. Actually i'hv created a partial column and when i'm reusing it in the next case statement it is throwing error like 'INVALID COLUMN'. I need to spend some time to post and it's not a simple thing no? So if it's an duplicate issue then y am i again created a new post for the same issue.

[Sandra_Rossi]

LOL apologies!!! You have lots of issues with this query 😉

[Albatross]

Don't apologize buddy @Sandra... As i have so much requirements to be satisfied in a single query exhausted little bit. And u r seeing the same query again u r thinking like i'm asking the same doubt again and again. I understood. Thanks alot for ur quick response. 🙂

[Sandra_Rossi · Accepted solution]

You cannot reuse previous fields, you must indicate again the same formulas:

select
(select case MSEG.SHKZG when 'S' then MSEG.MENGE else 0.0 end) as Receipt,
(select case MSEG.SHKZG WHEN 'S' THEN ( MSEG.MENGE + MSEG.MENGE ) else 0.0 end) as BalQuantity,
...

NB: I didn't fix the other numerous errors of your query (like defining BalQuantity twice)

NB 2: you'd better do one simple query first, test it, then add complexity, test it again, etc.

[Albatross · Accepted solution]

Thanks my dear @Sandra sure i'll try this method.