Results 1 to 9 of 9

Thread: Please help

  1. #1
    Join Date
    Mar 2013
    Posts
    8

    Unanswered: Please help

    Hi,

    I am getting an error from this query and I do not no why. The error is:

    "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0901 - SQL system error".

    Each query alone is working but when I try to join I get this error. Here is the code:

    WITH
    MASTER
    AS
    (
    SELECT

    IDISC, TCAT, IDSCO,
    SUM(TVLS) AS SALES_UAH,
    SUM(TTRNQ) AS SALES_QTY

    FROM GALF6HK.IT
    LEFT JOIN GALF6HK.II ON TCAT = ICAT

    WHERE (TTYPE In ('BD','BC'))
    AND (TSAL='Y')
    AND (TYPE Not In ('T'))
    AND (TTRN Between 20120101 And 20121231)

    GROUP BY IDISC, TCAT, IDSCO
    ),

    PL
    AS
    (
    SELECT
    DDCAT,

    MAX(CASE WHEN DDPRN = 1 THEN DDVUL ELSE 0 END) AS PL1,
    MAX(CASE WHEN DDPRN = 2 THEN DDVUL ELSE 0 END) AS PL2


    FROM GALF6HK.DD

    GROUP BY DDCAT

    HAVING MAX(CASE WHEN DDPRN = 1 THEN DDVUL ELSE 0 END)>0
    OR MAX(CASE WHEN DDPRN = 2 THEN DDVUL ELSE 0 END)>0

    )

    SELECT
    IDISC, TCAT, IDSCO,
    SALES_UAH,
    SALES_QTY

    FROM MASTER
    LEFT JOIN PL ON DDCAT = TCAT

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Check your iSeries DB2 SQL documentation. On that platform, there are certain restrictions when using Common Table Expressions with OUTER Joins.
    Please make sure you refer to the document corresponding to your version

    ---
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2013
    Posts
    8

    Thanks for the prompt response

    But I read in the IBM reference and could not found there the answer.

    How can I solve the problem?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SELECT
    IDISC, TCAT, IDSCO,
    SALES_UAH,
    SALES_QTY

    FROM MASTER
    LEFT JOIN PL ON DDCAT = TCAT
    Why do you want to LEFT JOIN PL?
    It may be useless.
    Because, no difference in results between without LEFT JOIN and with LEFT JOIN.
    Rationale:
    (1) All selected columns are of MASTER cte(common-table-expression).
    (2) DDCAT(column of PL cte) in ON condition is UNIQUE. Because grouped by DDCAT in PL.

  5. #5
    Join Date
    Mar 2013
    Posts
    8
    Thanks I need to add to master pl1 colomn and pl2 column from pl
    Since ddcat in original file is not unique I transposed the data and grouped by ddcat. I forgot to add in the original post in the select section those two colomns (pl1 and pl2).
    I need a solution to add to master cte result the result of pl

    Thanks in adva.ce

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about those examples?

    Example 1:
    Note: MASTER was not changed essentially, only removed unnecessary parentheses and reformatted.
    Code:
    WITH
     MASTER AS (
    SELECT IDISC
         , TCAT
         , IDSCO
         , SUM(TVLS)  AS SALES_UAH
         , SUM(TTRNQ) AS SALES_QTY
     FROM  GALF6HK.IT
     LEFT  JOIN
           GALF6HK.II
      ON   TCAT = ICAT 
     WHERE TTYPE In ('BD','BC')
       AND TSAL  = 'Y'
       AND TYPE Not In ('T')
       AND TTRN Between 20120101 And 20121231
     GROUP BY
           IDISC
         , TCAT
         , IDSCO
    )
    , PL AS (
    SELECT DDCAT
         , MAX(CASE WHEN DDPRN = 1 THEN DDVUL ELSE 0 END) AS PL1
         , MAX(CASE WHEN DDPRN = 2 THEN DDVUL ELSE 0 END) AS PL2
     FROM  GALF6HK.DD
     WHERE DDVUL > 0
       AND DDPRN IN (1 , 2)
     GROUP BY
           DDCAT
    ) 
    SELECT M.IDISC
         , M.TCAT
         , M.IDSCO
         , M.SALES_UAH
         , M.SALES_QTY
         , P.PL1
         , P.PL2
     FROM  MASTER AS M
     LEFT  JOIN
           PL     AS P
      ON   P.DDCAT = M.TCAT
    ;

    Example 2:
    Code:
    WITH
     MASTER AS (
    SELECT IDISC
         , TCAT
         , IDSCO
         , SUM(TVLS)  AS SALES_UAH
         , SUM(TTRNQ) AS SALES_QTY
     FROM  GALF6HK.IT
     LEFT  JOIN
           GALF6HK.II
      ON   TCAT = ICAT 
     WHERE TTYPE In ('BD','BC')
       AND TSAL  = 'Y'
       AND TYPE Not In ('T')
       AND TTRN Between 20120101 And 20121231
     GROUP BY
           IDISC
         , TCAT
         , IDSCO
    )
    SELECT IDISC
         , TCAT
         , IDSCO
         , SALES_UAH
         , SALES_QTY
         , PL1
         , PL2
     FROM  MASTER
     LEFT  JOIN
           LATERAL
           (SELECT MAX(CASE WHEN DDPRN = 1 THEN DDVUL ELSE 0 END) AS PL1
                 , MAX(CASE WHEN DDPRN = 2 THEN DDVUL ELSE 0 END) AS PL2
             FROM  GALF6HK.DD
             WHERE DDCAT = TCAT
               AND DDVUL > 0
               AND DDPRN IN (1 , 2)
           ) AS PL
      ON   0=0
    ;
    Last edited by tonkuma; 03-21-13 at 06:33. Reason: Replace whole of Example 2. Replace PL in Example 2 with GALF6HK.DD.

  7. #7
    Join Date
    Mar 2013
    Posts
    8
    Thanks I solved the problem
    The problem was in the ODBC I found it and now it works

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by GuyZommer View Post
    Thanks I solved the problem
    The problem was in the ODBC I found it and now it works
    Good stuff.

    Can you give us info on what the problem was with ODBC and how you fixed it.
    Someone else coming to the forum with similar problem will find your experience useful

    Thanks
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Mar 2013
    Posts
    8
    Yes,

    I had to problems:

    1. In ODBC - The solution was creating new ODBC with different name then I delete the previous ODBC and rename the new ODBC to the "old name". Im that way I ensured that the new ODBC has the default definition.
    Something was wrong in the original ODBC I located the problem by trying running the same query in different computer with the same operation system and the same iSeries version. In that computer it works except problem with alias that I solved later see 2.

    2. The Second problem was with the alias. I added a case command for the description and then it solved the alias problem.

    case when idsco = '' then idsco else idsco end as idsco

    stange but this solved the problem with alias

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •