Skip to Content
author's profile photo Former Member
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

2 Answers

  • Posted on Apr 30, 2015 at 04:46 AM

    /* 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

    Add comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 30, 2015 at 01:57 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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