Skip to Content
0
Jun 07, 2022 at 01:46 AM

Shows blank or dash symbol instead of 0 value

293 Views

Hi all,

how can i amend my code if for example column SA NAME if my users didn't key in anything then it will show dash symbol instead of blank or 0 ?

Can someone help me? Thank you

SELECT 
cast(T5.[SlpName] as varchar) as Saleperson,
case when t14.PymntGroup ='Net 30 days' then 'Com Ac'
when t14.PymntGroup='Net COD' then 'Cash Ac'
when t14.PymntGroup='30 days' then 'Com Ac'
when t14.PymntGroup='45 days' then 'Com Ac'
end as 'Terms',
cast(t7.docdate as date) as 'Inv Date',
cast(t7.docduedate as date) as 'Due Date',
cast(T7.DocNum as varchar) as 'Inv No.',
cast(T3.DocNum as varchar) as 'DO No.',
cast(T7.CardCode as varchar) as 'Debtor Code',
cast(T7.CardName as varchar(250)) as 'Debtor',
cast(T6.Dscription as varchar(100)) as 'Model',
cast(t6.Quantity as varchar) as 'Quantity',
cast(t6.text as varchar (200)) as 'Chasis No.',
cast (t17.name as varchar) as 'Attn',
cast(T7.DocTotal-T7.PaidToDate as decimal(18,2)) AS 'Balance Unpaid',
cast(T12.DocDate as date)as 'PaymentDate',
cast(t12.counterref as varchar) as 'Cheque No.',
cast(T12.DocNum as varchar) AS 'OR Receipt No.',
cast(T11.SumApplied as decimal(18,2))as 'Payment Collect Amount',
cast(t7.u_actualpriceamt as decimal (18,2)) as 'Actual Price',
cast(t7.DocTotal + t7.DiscSum - t7.VatSum as decimal (18,2)) as 'Customer Price',
cast (t6.TotalSumSy as decimal (18,2))as 'Customer Price - Taxable',
cast(t7.VatSum as decimal (18,2)) as 'SST 10%',
cast(t7.DocTotal - t7.VatSum + t7.VatSum as decimal(18,2))as 'Customer Pric Inc 10 %',
cast(t7.U_ActualPriceAmt + t7.VatSum as decimal(18,2)) as 'Actual Price Include 10%',
cast (t7.U_SA_Comm as decimal (18,2)) as 'SA Comm',
cast(t7.U_SA_Name as varchar) as 'SA Name' ,
cast(t7.U_SA_IC as varchar) as 'SA IC No.',
cast(t7.U_units as varchar) as 'Cars (Units)',
cast (t7.U_part as varchar) as 'Loose Part/Rawm(RM)',
cast(t6.U_colour as varchar) as 'Color',
case when t6.U_colour='Black Nappa' then '0500F26'
when t6.U_colour='Black' then '0500'
when t6.U_colour='Red Barracuda' then '0512'
when t6.U_colour='DK Graphite' then '1021'
when t6.U_colour='Charcoal' then '1237'
when t6.U_colour='Ice Grey' then '1406'
when t6.U_colour='Dark Brown' then '2063'
when t6.U_colour='Ivory' then '3117'
when t6.U_colour='Sand Stone Nappa' then '3444F26'
when t6.U_colour='Sand Stone' then '3444'
when t6.U_colour='Red' then '4403'
when t6.U_colour='Bordeaux Red' then '4737'
when t6.U_colour='Grey Nappa' then '4797F26'
when t6.U_colour='Opal' then '4798'
when t6.U_colour='Ameretto' then '4815'
when t6.U_colour='Mustang Brown' then '4816'
when t6.U_colour='Truffle Brown' then '4817'
when t6.U_colour='Chesnut' then '4818'
when t6.U_colour='Brown Nappa' then '4819F26'
when t6.U_colour='Dark Blue' then '5044'
when t6.U_colour='Lt. Stone Beige' then '7076'
when t6.U_colour='Cream Nappa' then '7087F26'
else t6.U_colour
end as 'Color Code',

cast(t6.U_PO as varchar) as 'PO No.',
cast(t7.NumAtCard as varchar) as 'COF No.',
cast(t20.DocNum as varchar) as 'CN No.'
from INV1 T6
LEFT JOIN OINV T7 ON T6.DocEntry = T7.DocEntry
LEFT JOIN RCT2 T11 ON T11.DocEntry = T7.DocEntry
LEFT JOIN ORCT T12 ON T11.DocNum = T12.DocEntry
LEFT JOIN RCT1 T13 ON T12.DocEntry = T13.DocNum

inner join OCTG t14 on t7.GroupNum=t14.GroupNum
inner join OCRD t15 on t7.CardCode=t15.CardCode
INNER JOIN CRD1 T16 ON T15.CardCode = T16.CardCode and t16.LineNum=0
left JOIN OCPR T17 ON T7.CntctCode= T17.CntctCode
inner join oitm t18 on t6.ItemCode=t18.ItemCode
--credit memo
left join RIN1 t19 on t7.DocEntry =t19.BaseEntry and t6.LineNum=t19.BaseLine and t6.ItemCode=t19.ItemCode
left join ORIN t20 on t19.DocEntry=t20.DocEntry
INNER JOIN OSLP T5 ON t7.SlpCode = T5.[SlpCode]
left join dln1 t4 on t6.BaseEntry=t4.DocEntry
left join ODLN t3 on t4.DocEntry=t3.DocEntry

WHERE t18.InvntItem='Y' and t7.SlpCode in (1,2,3) --and t7.DocDate >='[%0]' and t7.DocDate<= '[%1]' --and t12.Canceled <> 'Y'
and t7.DocNum='46649'
group by t7.DocNum,t20.DocNum,t5.SlpName,t14.PymntGroup,t7.docdate,t7.DocDueDate,t7.CardCode,t7.CardName,
t6.Dscription,cast(t6.text as varchar (200)), cast (t17.name as varchar),cast(T7.DocTotal-T7.PaidToDate as decimal(18,2)),T12.DocDate,
T12.DocNum,T11.SumApplied,t12.counterref,t7.u_actualpriceamt,cast(t7.DocTotal + t7.DiscSum - t7.VatSum as decimal (18,2)),
t6.price,t7.VatSum,T11.SumApplied,cast(t7.U_ActualPriceAmt + t7.VatSum as decimal(18,2)),t7.U_SA_Comm,t7.U_SA_Name,
t7.U_SA_IC,t6.U_colour,t6.U_PO,t7.NumAtCard,t20.DocNum,t3.DocNum,cast(t7.DocTotal - t7.VatSum + t7.VatSum as decimal(18,2)),cast(t6.Quantity as varchar),
cast(t7.U_units as varchar),cast (t7.U_part as varchar), cast (t6.TotalSumSy as decimal (18,2))
ORDER BY T7.DocDate