Question Re: GETTING AN ERROR WHILE RE-USING PARTAILLY CREATED COL... in Technology Q&A

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

Albatross — Sun, 25 Feb 2024 16:37:51 GMT

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' ;

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

Sandra_Rossi — Mon, 26 Feb 2024 06:14:05 GMT

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...

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

Albatross — Mon, 26 Feb 2024 06:40:38 GMT

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.

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

Sandra_Rossi — Mon, 26 Feb 2024 07:18:53 GMT

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

Re: GETTING AN ERROR WHILE RE-USING PARTAILLY CREATED COL...

Sandra_Rossi — Mon, 26 Feb 2024 07:23:59 GMT

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.

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

Albatross — Mon, 26 Feb 2024 07:58:18 GMT

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. 🙂

Re: GETTING AN ERROR WHILE RE-USING PARTAILLY CREATED COL...

Albatross — Mon, 26 Feb 2024 07:59:12 GMT

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