Solved

SQL Query Pattern in DB2

Posted on 2009-07-02
16
491 Views
Last Modified: 2013-12-07
Hello
I need a SQL Output as below

Company        Name          Age
A                     abc              31
                       bcd              33
                       xyz              34
B                     123              44
                       234             55
                       345              33

Please help
0
Comment
Question by:Senz79
  • 9
  • 7
16 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24769740
what do you mean by this output?
which version of db2 are you using?
you can try this:

select company1 as company, name, age
from (
  select *, case when rown=1 then company else '' end as company1 from (
  select company, name, age, rownumber() over(partition by company) as rown
  from yourTable ) t1
  ) t2
order by company,rown
0
 
LVL 4

Author Comment

by:Senz79
ID: 24769772
can you help me with the fields below

DATE ('20' || substr("Timestamp",2,2) || '-' || substr("Timestamp",4,2) || '-' || substr("Timestamp",6,2)) as "Date",
TIME (substr("Timestamp",8,2) || '.' || substr("Timestamp",10,2) || '.' || substr ("Timestamp",12,2)) as "Time",
"Server_Name"  from "NT_System_H"

based on the server_name i want to group the result.

0
 
LVL 4

Author Comment

by:Senz79
ID: 24769774
i am using DB2 version 9
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 24769800
when you want to group by server_name you need to decide which  function to apply to every other column such as date - the min / max / avg function?
0
 
LVL 4

Author Comment

by:Senz79
ID: 24769815
I didnt get that ...... In case i want a output pattern i discussed.... how can that help
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24769822
can you post here the output you got?
0
 
LVL 4

Author Comment

by:Senz79
ID: 24770143
I wanted a output like

Company        Name          Age
A                     abc              31
                       bcd              33
                       xyz              34
B                     123              44
                       234             55
                       345              33
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24770209
i know that
but what is the output you get from my statement?
0
 
LVL 4

Author Comment

by:Senz79
ID: 24770378
db2 => select "Server_Name" , "LAT_User_Name" from ( select case when "rown"=1 then "Server_Name" else '' end as "Server_Name" from (se
lect "Server_Name" , "LAT_User_Name", rownumber() over(partition by "Server_Name") as "rown" from "NT_System_H" ) t1) t2 order by "Serv
er_Name","rown"

SQL0206N  "LAT_User_Name" is not valid in the context where it is used.
SQLSTATE=42703
db2 =>

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24770477
try

select "Server_Name" , "LAT_User_Name"
from ( select case when "rown"=1 then "Server_Name" else '' end as "Server_Name", *
 from (select "Server_Name" , "LAT_User_Name", rownumber() over(partition by "Server_Name" order by "LAT_User_Name") as "rown" from "NT_System_H" ) t1) t2
order by "Server_Name","rown"
0
 
LVL 4

Author Comment

by:Senz79
ID: 24770500
db2 => select "Server_Name" , "LAT_User_Name" from ( select case when "rown"=1 then "Server_Name" else '' end as "Server_Name", * from
(select "Server_Name" , "LAT_User_Name", rownumber() over(partition by "Server_Name" order by "LAT_User_Name") as "rown" from "NT_Syste
m_H" ) t1) t2 order by "Server_Name","rown"

SQL0104N  An unexpected token "*" was found following "nd as "Server_Name",".
Expected tokens may include:  "<select_sublist>".  SQLSTATE=42601
db2 =>
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24770515
select "Server_Name1" , "LAT_User_Name"
from ( select case when "rown"=1 then "Server_Name" else '' end as "Server_Name1", "Server_Name" , "LAT_User_Name", rown
from
(select "Server_Name" , "LAT_User_Name", rownumber() over(partition by "Server_Name" order by "LAT_User_Name") as "rown" from "NT_Syste
m_H" ) t1) t2 order by "Server_Name","rown"
0
 
LVL 4

Author Comment

by:Senz79
ID: 24770530
db2 => select "Server_Name1" , "LAT_User_Name" from ( select case when "rown"=1 then "Server_Name" else '' end as "Server_Name1", "Serv
er_Name" , "LAT_User_Name", rown from (select "Server_Name" , "LAT_User_Name", rownumber() over(partition by "Server_Name" order by "LA
T_User_Name") as "rown" from "NT_System_H" ) t1) t2 order by "Server_Name","rown"

SQL0206N  "ROWN" is not valid in the context where it is used.  SQLSTATE=42703
db2 =>
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 250 total points
ID: 24770539
select "Server_Name1" , "LAT_User_Name","rown" from ( select case when "rown"=1 then "Server_Name" else '' end as "Server_Name1", "Serv
er_Name" , "LAT_User_Name", rown from (select "Server_Name" , "LAT_User_Name", rownumber() over(partition by "Server_Name" order by "LA
T_User_Name") as "rown" from "NT_System_H" ) t1) t2 order by "Server_Name","rown"

0
 
LVL 4

Author Comment

by:Senz79
ID: 24770547
still the same error
db2 => select "Server_Name1" , "LAT_User_Name","rown" from ( select case when "rown"=1 then "Server_Name" else '' end as "Server_Name1"
, "Server_Name" , "LAT_User_Name", rown from (select "Server_Name" , "LAT_User_Name", rownumber() over(partition by "Server_Name" order
 by "LAT_User_Name") as "rown" from "NT_System_H" ) t1) t2 order by "Server_Name","rown"
SQL0206N  "ROWN" is not valid in the context where it is used.  SQLSTATE=42703
db2 =>

0
 
LVL 4

Author Comment

by:Senz79
ID: 24770556
select "Server_Name1" , "LAT_User_Name","rown" from ( select case when "rown"=1 then "Server_Name" else '' end as "Server_Name1", "Server_Name" , "LAT_User_Name", "rown" from (select "Server_Name" , "LAT_User_Name", rownumber() over(partition by "Server_Name" order by "LAT_User_Name") as "rown" from "NT_System_H" ) t1) t2 order by "Server_Name","rown"


GOT IT !!!! THANKS A MILLION :)
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Extract the first word (before the , ) 2 48
Determine Who is Runnig my Bash Shell Script 4 80
Shell Script on AIX 7 96
run sql script from putty 4 35
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question