PL by Segment

[kbasher]

Would any one help me to understand what's the problem with the below query are:

/* SELECT FROM [dbo].[JDT1] */
DECLARE FDT TimeStamp;
FDT := /* JDT1."DocDate" */'[%0]';

/* SELECT FROM [dbo].[JDT1] */
DECLARE TDT TimeStamp;
TDT := /* JDT1."DocDate" */'[%1]';

select OACT."AcctCode",OACT."AcctName", OACT."OverCode",OACT."ValidFor",OACT."ValidFrom", case when MC."Amt" is null then 0 else MC."Amt" end as "Motorcycle", case when SP."Amt" is null then 0 else SP."Amt" end as "Spareparts" from OACT
left outer join (select JDT1."Account" as "GLCode", SUM(JDT1."Debit"-JDT1."Credit") as "Amt" from JDT1 where JDT1."RefDate" between :FDT and :TDT and jdt1."ProfitCode" = 'BSG0001' group by JDT1."Account" order by JDT1."Account") MC on MC."GLCode"=OACT."AcctCode"
left outer join (select JDT1."Account" as "GLCode", SUM(JDT1."Debit"-JDT1."Credit") as "Amt" from JDT1 where JDT1."RefDate" between :FDT and :TDT and jdt1."ProfitCode" = 'BSG0002' group by JDT1."Account" order by JDT1."Account") SP on SP."GLCode"=OACT."AcctCode"
Where OACT."Levels" = '6' and OACT."AcctCode" Like '4%%'
union all
select OACT."AcctCode",OACT."AcctName", OACT."OverCode",OACT."ValidFor",OACT."ValidFrom", case when MC."Amt" is null then 0 else MC."Amt" end as "Motorcycle", case when SP."Amt" is null then 0 else SP."Amt" end as "Spareparts" from OACT
left outer join (select JDT1."Account" as "GLCode", SUM(JDT1."Debit"-JDT1."Credit") as "Amt" from JDT1 where JDT1."RefDate" between :FDT and :TDT and jdt1."ProfitCode" = 'BSG0001' group by JDT1."Account" order by JDT1."Account") MC on MC."GLCode"=OACT."AcctCode"
left outer join (select JDT1."Account" as "GLCode", SUM(JDT1."Debit"-JDT1."Credit") as "Amt" from JDT1 where JDT1."RefDate" between :FDT and :TDT and jdt1."ProfitCode" = 'BSG0002' group by JDT1."Account" order by JDT1."Account") SP on SP."GLCode"=OACT."AcctCode"
Where OACT."Levels" = '6' and OACT."AcctCode" Like '5%%'
union all
select OACT."AcctCode",OACT."AcctName", OACT."OverCode",OACT."ValidFor",OACT."ValidFrom", case when MC."Amt" is null then 0 else MC."Amt" end as "Motorcycle", case when SP."Amt" is null then 0 else SP."Amt" end as "Spareparts" from OACT
left outer join (select JDT1."Account" as "GLCode", SUM(JDT1."Debit"-JDT1."Credit") as "Amt" from JDT1 where JDT1."RefDate" between :FDT and :TDT and jdt1."ProfitCode" = 'BSG0001' group by JDT1."Account" order by JDT1."Account") MC on MC."GLCode"=OACT."AcctCode"
left outer join (select JDT1."Account" as "GLCode", SUM(JDT1."Debit"-JDT1."Credit") as "Amt" from JDT1 where JDT1."RefDate" between :FDT and :TDT and jdt1."ProfitCode" = 'BSG0002' group by JDT1."Account" order by JDT1."Account") SP on SP."GLCode"=OACT."AcctCode"
Where OACT."Levels" = '6' and OACT."AcctCode" Like '6%%'
union all
select OACT."AcctCode",OACT."AcctName", OACT."OverCode",OACT."ValidFor",OACT."ValidFrom", case when MC."Amt" is null then 0 else MC."Amt" end as "Motorcycle", case when SP."Amt" is null then 0 else SP."Amt" end as "Spareparts" from OACT
left outer join (select JDT1."Account" as "GLCode", SUM(JDT1."Debit"-JDT1."Credit") as "Amt" from JDT1 where JDT1."RefDate" between :FDT and :TDT and jdt1."ProfitCode" = 'BSG0001' group by JDT1."Account" order by JDT1."Account") MC on MC."GLCode"=OACT."AcctCode"
left outer join (select JDT1."Account" as "GLCode", SUM(JDT1."Debit"-JDT1."Credit") as "Amt" from JDT1 where JDT1."RefDate" between :FDT and :TDT and jdt1."ProfitCode" = 'BSG0002' group by JDT1."Account" order by JDT1."Account") SP on SP."GLCode"=OACT."AcctCode"
Where OACT."Levels" = '6' and OACT."AcctCode" Like '7%%'
union all
select OACT."AcctCode",OACT."AcctName", OACT."OverCode",OACT."ValidFor",OACT."ValidFrom", case when MC."Amt" is null then 0 else MC."Amt" end as "Motorcycle", case when SP."Amt" is null then 0 else SP."Amt" end as "Spareparts" from OACT
left outer join (select JDT1."Account" as "GLCode", SUM(JDT1."Debit"-JDT1."Credit") as "Amt" from JDT1 where JDT1."RefDate" between :FDT and :TDT and jdt1."ProfitCode" = 'BSG0001' group by JDT1."Account" order by JDT1."Account") MC on MC."GLCode"=OACT."AcctCode"
left outer join (select JDT1."Account" as "GLCode", SUM(JDT1."Debit"-JDT1."Credit") as "Amt" from JDT1 where JDT1."RefDate" between :FDT and :TDT and jdt1."ProfitCode" = 'BSG0002' group by JDT1."Account" order by JDT1."Account") SP on SP."GLCode"=OACT."AcctCode"
Where OACT."Levels" = '6' and OACT."AcctCode" Like '8%%'
union all
select OACT."AcctCode",OACT."AcctName", OACT."OverCode",OACT."ValidFor",OACT."ValidFrom", case when MC."Amt" is null then 0 else MC."Amt" end as "Motorcycle", case when SP."Amt" is null then 0 else SP."Amt" end as "Spareparts" from OACT
left outer join (select JDT1."Account" as "GLCode", SUM(JDT1."Debit"-JDT1."Credit") as "Amt" from JDT1 where JDT1."RefDate" between :FDT and :TDT and jdt1."ProfitCode" = 'BSG0001' group by JDT1."Account" order by JDT1."Account") MC on MC."GLCode"=OACT."AcctCode"
left outer join (select JDT1."Account" as "GLCode", SUM(JDT1."Debit"-JDT1."Credit") as "Amt" from JDT1 where JDT1."RefDate" between :FDT and :TDT and jdt1."ProfitCode" = 'BSG0002' group by JDT1."Account" order by JDT1."Account") SP on SP."GLCode"=OACT."AcctCode"
Where OACT."Levels" = '6' and OACT."AcctCode" Like '9%%'
order by OACT."AcctCode";

Whenever I run this query, its shows different errors like-

• 'Document' (RDOC)
• 'User-Defined Values' (CSHS)
• 'Backend scheduling job' (OBSJ)

But same query running well when I use static dates like -

/* SELECT FROM [dbo].[JDT1] */
DECLARE FDT TimeStamp;
FDT := /* JDT1."DocDate" */'2024.08.01';

/* SELECT FROM [dbo].[JDT1] */
DECLARE TDT TimeStamp;
TDT := /* JDT1."DocDate" */'2024.08.31';

[kbasher · Accepted solution]

I found my mistake. I was using wrong field name. Correct one should be like-

/* SELECT FROM [dbo].[JDT1] */
DECLARE FDT TimeStamp;
FDT := /* JDT1."RefDate" */'[%0]';

/* SELECT FROM [dbo].[JDT1] */
DECLARE TDT TimeStamp;
TDT := /* JDT1."RefDate" */'[%1]';

Query is running well now. Sorry for the inconvenience.