Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    24

    Unanswered: Problem with Count Distinct using multiple columns

    Hi,

    I am succesful in doing below query and it returns me the count.

    SELECT count(DISTINCT S_ITEMI|| S_IDCNG) FROM EREIMP.ODTEST AS ODTEST JOIN EREIMP.PLTEST AS PLTEST ON ODTEST.ITEM_OBJID = PLTEST.ITEM_OBJID WHERE ODTEST.Q_PMSYNC='Y' AND ODTEST.S_DC='RD' AND ODTEST.Q_DATEPMSYNC < ODTEST.Q_LASTMOD;

    but when I am trying to do i m getting error
    SELECT count(DISTINCT ODTEST.ITEM_OBJID||S_ITEMI|| S_IDCNG) FROM EREIMP.ODTEST AS ODTEST JOIN EREIMP.PLTEST AS PLTEST ON ODTEST.ITEM_OBJID = PLTEST.ITEM_OBJID WHERE ODTEST.Q_PMSYNC='Y' AND ODTEST.S_DC='RD' AND ODTEST.Q_DATEPMSYNC < ODTEST.Q_LASTMOD;

    SQL0440N No authorized routine named "||" of type "FUNCTION" having
    compatible arguments was found.
    Any inputs how can i do it ?

    Thanks
    Prashant

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    SELECT count(DISTINCT (ODTEST.ITEM_OBJID||S_ITEMI|| S_IDCNG)) FROM EREIMP.ODTEST AS ODTEST JOIN EREIMP.PLTEST AS PLTEST ON ODTEST.ITEM_OBJID = PLTEST.ITEM_OBJID WHERE ODTEST.Q_PMSYNC='Y' AND ODTEST.S_DC='RD' AND ODTEST.Q_DATEPMSYNC < ODTEST.Q_LASTMOD;
    Assuming the fact that ITEM_OBJID, S_ITEMI and S_IDCNG are all either VARCHAR or CHAR.... Else the same needs to be converted into one....
    IBM Certified Database Associate, DB2 9 for LUW

  3. #3
    Join Date
    Mar 2009
    Posts
    24
    Quote Originally Posted by nick.ncs
    Assuming the fact that ITEM_OBJID, S_ITEMI and S_IDCNG are all either VARCHAR or CHAR.... Else the same needs to be converted into one....
    S_ITEMI & S_IDCNG are character but ITEM_OBJID IS BIGINT. What to do now?

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by cprash.aggarwal
    SELECT count(DISTINCT ODTEST.ITEM_OBJID||S_ITEMI|| S_IDCNG) FROM EREIMP.ODTEST AS ODTEST JOIN EREIMP.PLTEST AS PLTEST ON ODTEST.ITEM_OBJID = PLTEST.ITEM_OBJID WHERE ODTEST.Q_PMSYNC='Y' AND ODTEST.S_DC='RD' AND ODTEST.Q_DATEPMSYNC < ODTEST.Q_LASTMOD;

    SQL0440N No authorized routine named "||" of type "FUNCTION" having
    compatible arguments was found.
    Try the following:
    Code:
    SELECT count(*) FROM
    (SELECT DISTINCT O.ITEM_OBJID, S_ITEMI, S_IDCNG
     FROM EREIMP.ODTEST AS O
            INNER JOIN EREIMP.PLTEST AS P ON O.ITEM_OBJID = P.ITEM_OBJID
     WHERE O.Q_PMSYNC='Y' AND O.S_DC='RD' AND O.Q_DATEPMSYNC < O.Q_LASTMOD) X
    This avoids a (potentially expensive) concat and type conversion.
    And it's probably easier to read/understand.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Mar 2009
    Posts
    24
    Quote Originally Posted by Peter.Vanroose
    Try the following:
    Code:
    SELECT count(*) FROM
    (SELECT DISTINCT O.ITEM_OBJID, S_ITEMI, S_IDCNG
     FROM EREIMP.ODTEST AS O
            INNER JOIN EREIMP.PLTEST AS P ON O.ITEM_OBJID = P.ITEM_OBJID
     WHERE O.Q_PMSYNC='Y' AND O.S_DC='RD' AND O.Q_DATEPMSYNC < O.Q_LASTMOD) X
    This avoids a (potentially expensive) concat and type conversion.
    And it's probably easier to read/understand.
    This didn't work , but i tried this and it worked.

    With test as ( SELECT DISTINCT ODTEST.ITEM_OBJID, S_ITEMI, S_IDCNG FROM EREIMP.ODTEST AS ODTEST JOIN EREIMP.PLTEST AS PLTEST ON ODTEST.ITEM_OBJID = PLTEST.ITEM_OBJID WHERE ODTEST.Q_PMSYNC='Y' AND ODTEST.S_DC='RD' AND ODTEST.Q_DATEPMSYNC < ODTEST.Q_LASTMOD) SELECT count(*) FROM test

    Thanks

  6. #6
    Join Date
    Mar 2009
    Posts
    24
    Quote Originally Posted by cprash.aggarwal
    This didn't work , but i tried this and it worked.

    With test as ( SELECT DISTINCT ODTEST.ITEM_OBJID, S_ITEMI, S_IDCNG FROM EREIMP.ODTEST AS ODTEST JOIN EREIMP.PLTEST AS PLTEST ON ODTEST.ITEM_OBJID = PLTEST.ITEM_OBJID WHERE ODTEST.Q_PMSYNC='Y' AND ODTEST.S_DC='RD' AND ODTEST.Q_DATEPMSYNC < ODTEST.Q_LASTMOD) SELECT count(*) FROM test

    Thanks
    HI i wanted to use this in Stored procedure
    but the following dowsnt work , cant we use temp tables in SP's?

    With test as ( SELECT DISTINCT ODTEST.ITEM_OBJID, S_ITEMI, S_IDCNG FROM EREIMP.ODTEST AS ODTEST JOIN EREIMP.PLTEST AS PLTEST ON ODTEST.ITEM_OBJID = PLTEST.ITEM_OBJID WHERE ODTEST.Q_PMSYNC='Y' AND ODTEST.S_DC='RD' AND ODTEST.Q_DATEPMSYNC < ODTEST.Q_LASTMOD) SELECT count(*) INTO num_rows FROM test;

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can not use Common Table Expression with SELECT INTO statement.
    DB2 distinguishes SELECT statement and SELECT INTO statement.

    I wondered why Peter Vanroose's query did not work.
    Would you give me error messages which you got?

Posting Permissions

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