Break On functionality in DB2? 1

[notadba]

Does anyone know a way to run a 'break on' (Oracle) style function in DB2 SQL - UDB 7.1 - running command through sql only not a reporting tool

Trying to eliminate redundant fields in output eg to get a result like

Column1 Column2 Column 3
EMPL1 name1 attribute1
attribute2
EMPL2 Name2 attribute1
EMPL3 Name3 attribute1

etc

 

[blom0344]

The answer is yes and no. (AFAIK) It is possible to get rid of the redundant fields in the way that you describe, but unfortunaly SQL expects us to tell how the output is sorted.
So there is the cinch, you still need a column to sort the output by and that ****** 's up the whole break-thing.

My best attempt:

SELECT DUMMY,X,Y,#ROWN FROM
((SELECT DUMMY,X,Y,#ROWN FROM
(Select field1 AS DUMMY,field1 AS X,field2 AS Y,
RANK() OVER (PARTITION BY field1 ORDER BY field2) AS #ROWN from TABLE) TEMP
WHERE #ROWN = 1)
UNION ALL
(SELECT DUMMY,X,Y,#ROWN FROM
(Select field1 AS DUMMY,'' AS X,'' AS Y,
RANK() OVER (PARTITION BY field1 ORDER BY field2) AS #ROWN from TABLE) TEMP2
WHERE #ROWN <> 1)) TEMP3
ORDER BY DUMMY,#ROWN

This gives output as the break, but with the dummy field to use the order by on.
Hope someone will better this

T. Blom
Information analyst
tbl@shimano-eu.com

 

[notadba]

Hi blom0344 , had a shot at using your code, but I think we have a DB2 version issue. I have been getting a bizarre array of errors - mostly relating to brackets. Have seen issues with Db2 when using an aliased field in sub-selects.

I'll give you a star anyway as it is an interesting approach to solving the problem. I'll keep hacking at the code and see if I can get it to work - however the SQL manual for 7.1 doesn't mention the use of rank or partition.

Might just have to put it in an SQR and use the on break function there.

 

[sathyarams]

I think the RANK() function is categorized as OLAP Function and is documented in a different guide ...

It has been available since Version 6 ..

Yes .. all these are on Linux, Unix and Windows platforms ..

Are you using Os/390 db2 ?

http://www.db2times.com/modules.php?name=News&file=article&sid=28

may help ...

Cheers
Sathyaram


More DB2 questions answered at

http://www.dbforums.com/f8

 

[blom0344]

Sathyaram,

That's a valuable link to db2times........
I tend to forget there are other DB2 flavor's that possibly lack latest OLAP functions. They are my little pets


T. Blom
Information analyst
tbl@shimano-eu.com

 

[notadba]

Sathyaram

We are on OS/390, and seem to be getting the problem with the line containing the rank/partition syntax. Keeps failing on one of the open brackets.

I checked out the link - which was helpful - but raises a question. Does the rank function only work with numerics? I am trying to use the rank function from Blom0344's example to rank varchar/non-numerics for the purpose of removing redundant column values.

Not critical - was more an item of frustration