Distinct issue

DB2_NEW · #1 (**permalink**) 11-24-08, 06:22

Reply ASAP

DB2_NEW · #2 (**permalink**) 11-24-08, 06:22

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 ...

przytula_guy · #3 (**permalink**) 11-24-08, 06:47

can you display the table layout and the query being used
AQT ??

DB2_NEW · #4 (**permalink**) 11-24-08, 07:02

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

stolze · #5 (**permalink**) 11-24-08, 07:38

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

DB2_NEW · #6 (**permalink**) 11-24-08, 07:53

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 ....

tonkuma · #7 (**permalink**) 11-24-08, 08:31

Why do you need so long sort key?

DB2_NEW · #8 (**permalink**) 11-24-08, 08:40

LONG SORT KEY IS INTERNALLY PROCESSED... but i need to use distinct in select clause , my ouput need this ....

stolze · #9 (**permalink**) 11-24-08, 11:39

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.

tonkuma · #10 (**permalink**) 11-24-08, 16:12

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.

Quote:

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
;

DB2_NEW · #11 (**permalink**) 11-25-08, 05:15

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

DB2_NEW · #12 (**permalink**) 11-25-08, 05:28

with this select column some more column have included , also group by is included.

tonkuma · #13 (**permalink**) 11-25-08, 08:30

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
;

DB2_NEW · #14 (**permalink**) 11-25-08, 08:59

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 ????

tonkuma · #15 (**permalink**) 11-25-08, 10:06

Quote:

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
;

Internet Services

Web Development

Digital Marketing

Consumer Tech