02-25-2024 4:37 PM - edited 02-25-2024 4:37 PM
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' ;
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
75 | |
9 | |
8 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.