cancel
Showing results for 
Search instead for 
Did you mean: 

Unable to pass date parameter to sub-query through query generator - sap b1

Former Member
0 Kudos

I have a query for which i have to pass date as a parameter to subquery to generate report. I am unable to do this. I am using SAP B1 hana 9.1 PL05. Please revert back with your suggestion.... I want this to be done using query itself. The coding is as follows:

Select A."PrjCode", A."PrjName", A."CardName",  (sum(A."Current") + sum(A."Payments")) as "Current", sum(A."Retention") as "Retention", sum(A."Advance") as "Advance"

from

(

SELECT  ifnull(T1."PrjCode", '04012') as "PrjCode", ifnull(T1."PrjName",'General') as "PrjName", T2."CardCode", T2."CardName",

(Case when T0."TransType" = '46' and ifnull(T0."Ref3Line",'0') <> '2'  then sum(T0."Debit" -  T0."Credit") else 0 end) as "Payments",

(Case when ifnull(T0."Ref3Line",'0') <> '2'  and T0."TransType" <> '46'  then sum(T0."Debit" -  T0."Credit") else 0 end) as "Current",

(Case when ifnull(T0."Ref3Line",'0') = '2' then sum(T0."Debit" -  T0."Credit") else 0 end) as "Retention",

(Case when (T0."TransType" = '204' or T0."TransType" = '19') and  T0."ContraAct" ='114050001' then sum(T0."Debit" -  T0."Credit") else 0 end) as "Advance"


FROM JDT1 T0 

Left JOIN OPRJ T1 ON T0."Project" = T1."PrjCode"

INNER JOIN OCRD T2 ON T2."CardCode" = T0."ShortName"

INNER JOIN OCRG T3 ON T2."GroupCode" = T3."GroupCode"

--WHERE T2."CardType" = 'S' AND T3."GroupName" like '%Supplier%'

WHERE T2."CardCode" = 'V0015'

GROUP BY  T1."PrjCode", T1."PrjName", T2."CardCode", T2."CardName",  T0."Ref3Line", T0."TransType", T0."RefDate", T0."ContraAct"

) A group by A."PrjCode", A."PrjName", A."CardName"

having ((sum(A."Current") + sum(A."Payments"))+sum(A."Retention") +sum(A."Advance")) <> 0

order by A."PrjCode", A."PrjName", A."CardName"

Thanks,

Santhosh

Accepted Solutions (0)

Answers (2)

Answers (2)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this query:

Select A."PrjCode", A."PrjName", A."CardName",  (sum(A."Current") + sum(A."Payments")) as "Current", sum(A."Retention") as "Retention", sum(A."Advance") as "Advance"

from

(

SELECT  ifnull(T1."PrjCode", '04012') as "PrjCode", ifnull(T1."PrjName",'General') as "PrjName", T2."CardCode", T2."CardName",

(Case when T0."TransType" = '46' and ifnull(T0."Ref3Line",'0') <> '2'  then sum(T0."Debit" -  T0."Credit") else 0 end) as "Payments",

(Case when ifnull(T0."Ref3Line",'0') <> '2'  and T0."TransType" <> '46'  then sum(T0."Debit" -  T0."Credit") else 0 end) as "Current",

(Case when ifnull(T0."Ref3Line",'0') = '2' then sum(T0."Debit" -  T0."Credit") else 0 end) as "Retention",

(Case when (T0."TransType" = '204' or T0."TransType" = '19') and  T0."ContraAct" ='114050001' then sum(T0."Debit" -  T0."Credit") else 0 end) as "Advance"


FROM JDT1 T0

Left JOIN OPRJ T1 ON T0."Project" = T1."PrjCode"

INNER JOIN OCRD T2 ON T2."CardCode" = T0."ShortName"

INNER JOIN OCRG T3 ON T2."GroupCode" = T3."GroupCode"

--WHERE T2."CardType" = 'S' AND T3."GroupName" like '%Supplier%'

WHERE T2."CardCode" = 'V0015' AND T0."RefDate" BETWEEN [%0] and [%1]

GROUP BY  T1."PrjCode", T1."PrjName", T2."CardCode", T2."CardName",  T0."Ref3Line", T0."TransType", T0."RefDate", T0."ContraAct"

) A

group by A."PrjCode", A."PrjName", A."CardName"

having ((sum(A."Current") + sum(A."Payments"))+sum(A."Retention") +sum(A."Advance")) <> 0

order by A."PrjCode", A."PrjName", A."CardName"

Thanks.

Former Member

Thanks to all....

I managed to get it right. The query is as follows:

Select A."PrjCode", A."PrjName", A."CardName",  (sum(A."Current") + sum(A."Payments")) as "Current", sum(A."Retention") as "Retention", sum(A."Advance") as "Advance"

from

(

SELECT  ifnull(T1."PrjCode", '04012') as "PrjCode", ifnull(T1."PrjName",'General') as "PrjName", T2."CardCode", T2."CardName",

(Case when T0."TransType" = '46' and ifnull(T0."Ref3Line",'0') <> '2'  then sum(T0."Debit" -  T0."Credit") else 0 end) as "Payments",

(Case when ifnull(T0."Ref3Line",'0') <> '2'  and T0."TransType" <> '46'  then sum(T0."Debit" -  T0."Credit") else 0 end) as "Current",

(Case when ifnull(T0."Ref3Line",'0') = '2' then sum(T0."Debit" -  T0."Credit") else 0 end) as "Retention",

(Case when (T0."TransType" = '204' or T0."TransType" = '19') and  T0."ContraAct" ='114050001' then sum(T0."Debit" -  T0."Credit") else 0 end) as "Advance"

FROM JDT1 T0 

Left JOIN OPRJ T1 ON T0."Project" = T1."PrjCode"

INNER JOIN OCRD T2 ON T2."CardCode" = T0."ShortName"

INNER JOIN OCRG T3 ON T2."GroupCode" = T3."GroupCode"

WHERE T2."CardType" = 'S' AND T3."GroupName" like '%Supplier%'  and T0."RefDate" <= (SELECT MAX(Z0."RefDate") from JDT1 Z0 WHERE Z0."RefDate" <= '[%1]')

GROUP BY  T1."PrjCode", T1."PrjName", T2."CardCode", T2."CardName",  T0."Ref3Line", T0."TransType", T0."RefDate", T0."ContraAct"

) A group by A."PrjCode", A."PrjName", A."CardName"

having ((sum(A."Current") + sum(A."Payments"))+sum(A."Retention") +sum(A."Advance")) <> 0

order by A."PrjCode", A."PrjName", A."CardName"

Thanks,

Santhosh

frank_wang6
Active Contributor
0 Kudos

/* SELECT FROM OSRT P1 */

DECLARE @FROM AS DATE

/* WHERE */

SET @FROM = /* P1.FromDate */ '[%1]'

/* SELECT FROM OSRT P2 */

DECLARE @TO AS DATE

/* WHERE */

SET @TO = /* P2.ToDate */ '[%2]';

add this part at the beginning of your query, and then you can use @FROM and @TO in your query

Former Member
0 Kudos

Hello Frank,

Thanks for your immediate response.

Well I tried the declare statement in the query generator screen... It doesn't work. No error message nor the action has been completed.

Please note that I am using this query generator to connect to SAP B1 HANA 9.1 PL05.

Still stuck up with the issue... Basically, I am trying to generate a BP balance report having retention and the current dues as of a particular date to match up with the financial reports.

Any additional info could be of more help...

Thanks anyway!

Santhosh

frank_wang6
Active Contributor
0 Kudos

Sorry, I didnt notice u r using HANA version, the one I wrote is for MS SQL.

I believe HANA has slightly different syntax than MSSQL.

You might check the difference here. Since I dont have HANA to test, can not do this for u.

Frank