Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

How to Right Align the output of amount with currency?

Hi,

I have the following SQL query, which basically is the duplication of the SAP standard Item List. The only difference is, the stock on hand is only for Warehouse 01.

In the output, I manage to display eg: AUD 1321.00. However, it is aligned to the center. Is there a way that I can make it right align like the standard report? Appreciate your help.

SELECT T0.[ItemCode], T0.[ItemName], (select T100.OnHand from OITW T100 where T100.ItemCode=T0.ItemCode and T100.WhsCode='01') as 'InStock',T0.[CodeBars], T1.[ItmsGrpNam], T2.[FirmName], T0.[InvntryUom],

('AUD'+ replicate(' ',16-len(T0.[LstEvlPric])) + Convert (char(18),(cast ((round(T0.[LstEvlPric], 2)) as decimal(16,2)))) ) as 'Last Eval Price', (T0.[LastPurCur]+ replicate(' ',16-len(T0.[LastPurPrc])) + Convert (char(18),(cast ((round(T0.[LastPurPrc], 2)) as decimal(16,2)))) ) as 'Last Pur. Price',

(SELECT T200.[Currency] + replicate(' ',16-len(T200.Price)) + Convert (char(18),(cast ((round(T200.[Price], 2)) as decimal(16,2)))) FROM ITM1 T200 INNER JOIN OPLN T201 ON T200.PriceList = T201.ListNum where T200.PriceList=1 and T200.ItemCode=T0.ItemCode) as 'L 1',

(SELECT T200.[Currency] + replicate(' ',16-len(T200.Price)) + Convert (char(18),(cast ((round(T200.[Price], 2)) as decimal(16,2)))) FROM ITM1 T200 INNER JOIN OPLN T201 ON T200.PriceList = T201.ListNum where T200.PriceList=2 and T200.ItemCode=T0.ItemCode) as 'L 2',

(SELECT T200.[Currency] + replicate(' ',16-len(T200.Price)) + Convert (char(18),(cast ((round(T200.[Price], 2)) as decimal(16,2)))) FROM ITM1 T200 INNER JOIN OPLN T201 ON T200.PriceList = T201.ListNum where T200.PriceList=3 and T200.ItemCode=T0.ItemCode) as 'L 3',

(SELECT T200.[Currency] + replicate(' ',16-len(T200.Price)) + Convert (char(18),(cast ((round(T200.[Price], 2)) as decimal(16,2)))) FROM ITM1 T200 INNER JOIN OPLN T201 ON T200.PriceList = T201.ListNum where T200.PriceList=4 and T200.ItemCode=T0.ItemCode) as 'L 4',

(SELECT T200.[Currency] + replicate(' ',16-len(T200.Price)) + Convert (char(18),(cast ((round(T200.[Price], 2)) as decimal(16,2)))) FROM ITM1 T200 INNER JOIN OPLN T201 ON T200.PriceList = T201.ListNum where T200.PriceList=5 and T200.ItemCode=T0.ItemCode) as 'L 5',

(SELECT T200.[Currency] + replicate(' ',16-len(T200.Price)) + Convert (char(18),(cast ((round(T200.[Price], 2)) as decimal(16,2)))) FROM ITM1 T200 INNER JOIN OPLN T201 ON T200.PriceList = T201.ListNum where T200.PriceList=6 and T200.ItemCode=T0.ItemCode) as 'Price List 6',

(SELECT T200.[Currency] + replicate(' ',16-len(T200.Price)) + Convert (char(18),(cast ((round(T200.[Price], 2)) as decimal(16,2)))) FROM ITM1 T200 INNER JOIN OPLN T201 ON T200.PriceList = T201.ListNum where T200.PriceList=7 and T200.ItemCode=T0.ItemCode) as 'Price List 7',

(SELECT T200.[Currency] + replicate(' ',16-len(T200.Price)) + Convert (char(18),(cast ((round(T200.[Price], 2)) as decimal(16,2)))) FROM ITM1 T200 INNER JOIN OPLN T201 ON T200.PriceList = T201.ListNum where T200.PriceList=8 and T200.ItemCode=T0.ItemCode) as 'Price List 8',

(SELECT T200.[Currency] + replicate(' ',16-len(T200.Price)) + Convert (char(18),(cast ((round(T200.[Price], 2)) as decimal(16,2)))) FROM ITM1 T200 INNER JOIN OPLN T201 ON T200.PriceList = T201.ListNum where T200.PriceList=9 and T200.ItemCode=T0.ItemCode) as 'Price List 9',

(SELECT T200.[Currency] + replicate(' ',16-len(T200.Price)) + Convert (char(18),(cast ((round(T200.[Price], 2)) as decimal(16,2)))) FROM ITM1 T200 INNER JOIN OPLN T201 ON T200.PriceList = T201.ListNum where T200.PriceList=10 and T200.ItemCode=T0.ItemCode) as 'Price List 10'

FROM OITM T0 INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN OMRC T2 ON T0.FirmCode = T2.FirmCode

Former Member

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question