Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Distinct issue

  1. #1
    Join Date
    Nov 2008
    Posts
    9

    Unanswered:

    Reply ASAP

  2. #2
    Join Date
    Nov 2008
    Posts
    9

    Distinct issue

    In a table a column length is 3000 , i select this column 6 times and used distinct ( it is must for me ) .. error displayed is "THERE IS NO MESSAGE TEXT TO CORRESPONDING TO SQL ERROR -136 " .. this issue i am getting in DB2 ( executing in AQT ) .... do u have sulution ???? it is important now for me . pls reply ASAP ...

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    can you display the table layout and the query being used
    AQT ??
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Nov 2008
    Posts
    9
    Select Distinct Var.txt,var.txt,var.txt,var.txt,var.txt From Var

    Txt Length = Varchar(3000) ... If I Use One More Column ( No It Is 5 Columns) It Throws That Errpr

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You care to tell us which version of DB2 you are using on which platform? I guess it is DB2 for z/OS because there exists SQLCODE -136. Reading the description explains the issue: http://publib.boulder.ibm.com/infoce...codes/n136.htm

    Regarding your statement, it doesn't make much sense to me to duplicate the same value for each row. What would that be good for? Can you explain it to us? Even if this is as strange as your version, you could do that:
    Code:
    WITH t(text) AS ( SELECT DISTINCT var.txt FROM var )
    SELECT text, text, text, text, text, text FROM t
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Nov 2008
    Posts
    9
    SURE STOLZE ...

    In my query i have selected 25 columns , varchar(3000) column is used in case of 7 times .. select distinct is used ... so sort key length will exceed too long ... i just gave the sample query issue where it comes.. even i have converted with CTe .. it does not work ....

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why do you need so long sort key?

  8. #8
    Join Date
    Nov 2008
    Posts
    9
    LONG SORT KEY IS INTERNALLY PROCESSED... but i need to use distinct in select clause , my ouput need this ....

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Let's take a step back: what is the problem that you try to solve in your application? I have the feeling that all who answered here agree that your request doesn't make a lot of sense generally. So it would be good to know why you want to do something like that. So far, you only told us what you want to achieve - but not why.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I will make my questions more practical.
    Frst, it would be very rare the two 3000 byte text are completely same.
    OK, it would be sometimes, you want get the result of guaruteed unique values.
    Second, why you want repeat same result in your query?
    It would be enough to get one column result and later use that in somewhere in your application.

    In my query i have selected 25 columns , varchar(3000) column .....
    Anyhow, I could get DISTINCT 25 columns by the following query.
    Code:
    WITH
     TestData(text) AS (
    VALUES
     CAST('abc' AS VARCHAR(3000) )
    ,CAST('uvwxyz' AS VARCHAR(3000) )
    ,CAST('uvwxyz' AS VARCHAR(3000) )
    )
    /* End of test data */
    SELECT var.text, var.text, var.text, var.text, var.text
         , var.text, var.text, var.text, var.text, var.text
         , var.text, var.text, var.text, var.text, var.text
         , var.text, var.text, var.text, var.text, var.text
         , var.text, var.text, var.text, var.text, var.text
      FROM (SELECT DISTINCT
                   text
              FROM TestData
           ) AS var
    ;

  11. #11
    Join Date
    Nov 2008
    Posts
    9
    ok fine .. if that is case mean we can use CTE ...

    but i have this kind of case mean ....

    SELECT DISTINCT CASE
    WHEN NT.NT_SUB = 'SN'
    THEN TABLE1.VAR_TABLE1
    ELSE ( '' )
    END
    AS "SYNOPSIS",

    CASE
    WHEN NT.NT_SUB = 'CV'
    THEN TABLE1.VAR_TABLE1
    ELSE ( '' )
    END
    AS "ALLEGED COVERAGE",

    CASE
    WHEN NT.NT_SUB = 'DMIN'
    THEN TABLE1.VAR_TABLE1
    ELSE ( '' )
    END
    AS "DESCRIPTION OF MATTERS & ISSUES",

    CASE
    WHEN NT.NT_SUB = 'STN'
    THEN TABLE1.VAR_TABLE1
    ELSE ( '' )
    END
    AS "STRATEGY",

    CASE
    WHEN NT.NT_SUB = 'NN'
    THEN TABLE1.VAR_TABLE1
    ELSE ( '' )
    END
    AS "NEGOTIATIONS",

    CASE
    WHEN NT.NT_SUB = 'UP'
    THEN TABLE1.VAR_TABLE1
    ELSE ( '' )
    END
    AS "UPDATE SINCE LAST REPORT"
    FROM TABLE1

  12. #12
    Join Date
    Nov 2008
    Posts
    9
    with this select column some more column have included , also group by is included.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I found that Stolze already answered.
    In his example, DISTINCT in CTE and No DISTINCT(it means ALL was implied) in final SELECT.
    It would be same meaning with my example in which DISTINCT in sub-query and (implied)ALL in outmost SELECT.

    Here is a rewritten example for your last example.
    (You include NT.NT_SUB in CASE expressions. Although you included only TABLE1 in your FROM clause, I guessed that you joined NT with TABLE1.
    But, it's not sure. So, I included the column nt_sub in TABLE1.)
    Code:
    WITH
    /* Test data */
     Table1(nt_sub, var_table1) AS (
    VALUES
     ('SN',   CAST('synopsis ...'                        AS VARCHAR(3000)) )
    ,('CV',   CAST('ALLEGED COVERAGE ...'                AS VARCHAR(3000)) )
    ,('DMIN', CAST('DESCRIPTION OF MATTERS & ISSUES ...' AS VARCHAR(3000)) )
    ,('STN',  CAST('STRATEGY ...'                        AS VARCHAR(3000)) )
    ,('NN',   CAST('NEGOTIATIONS ...'                    AS VARCHAR(3000)) )
    ,('UP',   CAST('UPDATE SINCE LAST REPORT ...'        AS VARCHAR(3000)) )
    ,('ADD1', CAST('Some text ...'                       AS VARCHAR(3000)) )
    ,('ADD2', CAST('Another terxt ...'                   AS VARCHAR(3000)) )
    )
    /* End of Test data */
    SELECT
           CASE
           WHEN NT_SUB = 'SN' THEN
                VAR_TABLE1
           ELSE ''
           END  AS "SYNOPSIS"
        ,  CASE
           WHEN NT_SUB = 'CV' THEN
                VAR_TABLE1
           ELSE ''
           END  AS "ALLEGED COVERAGE"
        ,  CASE
           WHEN NT_SUB = 'DMIN' THEN
                VAR_TABLE1
           ELSE ''
           END  AS "DESCRIPTION OF MATTERS&ISSUES"
        ,  CASE
           WHEN NT_SUB = 'STN' THEN
                VAR_TABLE1
           ELSE ''
           END  AS "STRATEGY"
        ,  CASE
           WHEN NT_SUB = 'NN' THEN
                VAR_TABLE1
           ELSE ''
           END  AS "NEGOTIATIONS"
        ,  CASE
           WHEN NT_SUB = 'UP' THEN
                VAR_TABLE1
           ELSE ''
           END  AS "UPDATE SINCE LAST REPORT"
        ,  CASE
           WHEN NT_SUB = 'ADD1' THEN
                VAR_TABLE1
           ELSE ''
           END  AS "ADDed one"
        ,  CASE
           WHEN NT_SUB = 'ADD2' THEN
                VAR_TABLE1
           ELSE ''
           END  AS "ADDed two"
      FROM (SELECT DISTINCT
                   NT_SUB
                 , VAR_TABLE1
              FROM TABLE1
           ) AS Q
    ;
    Last edited by tonkuma; 11-25-08 at 10:57.

  14. #14
    Join Date
    Nov 2008
    Posts
    9
    Absolutely you are correct ....
    But why i am adding distinct in select clause is .. in select statement some more columns ( 8) need to be added as i said .... anyway u can filter this column .. finally this distinct need to be applied ....if u applied again .. "THERRE IS NO MESSSAGE TEXT CORRESPONDING TO SQL ERROR -136"
    -136 SAYS , DIStinct key generates SORT KEY... SO THIS LENGTH IS TOO LONG ....

    Some issue related to think ::: if qry execute what shud be row size ?? is there any limitation ??? where it is available??? how the memory space occupy in this scenario ????

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    But why i am adding distinct in select clause is .. in select statement some more columns ( 8) need to be added as i said ....
    What is the issue for the following example?
    Code:
    SELECT
           CASE
           WHEN NT_SUB = 'SN' THEN
                VAR_TABLE1
           ELSE ''
           END  AS "SYNOPSIS"
           .....
           .....
         , col_extra_1
         , col_extra_2
           .....
           .....
         , col_extra_8
      FROM (SELECT DISTINCT
                   NT_SUB
                 , VAR_TABLE1
                 , col_extra_1
                 , col_extra_2
                   .....
                   .....
                 , col_extra_8
              FROM TABLE1
           ) AS Q
    ;

Posting Permissions

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