Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Logic for a $ql query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
abnikant

New User


Joined: 12 Jan 2007
Posts: 9
Location: Noida

PostPosted: Wed Oct 07, 2009 12:15 pm    Post subject: Logic for a $ql query
Reply with quote

LogiQ # 3

In an organization, the quality audit team rates projects on 4 quality parameters. These quality audits take place every quarter for every project.
Assume that the audit team entered the results of audit meetings into a table, like as shown below. If Prm value is Y it means the project was rated fair (pass) on that parameter.
Quarter Year Project Prm1 Prm2 Prm3 Prm4
1 2008 abc y n y n
2 2008 abc y y n y
3 2008 abc y y y y
4 2008 abc n y n y
5 2008 xyz n n n n
6 2008 xyz y n n y
7 2008 xyz y y n y
8 2008 xyz y n y y

Now at the end of the year, audit team has to generate a report for senior management which will show how many times a certain project faired (passed) on each parameter in a year. You being the quality audit team member have been asked to get the same using a single SELECT statement.

How would you do it?

The desire out for project ABC should look like:

Year Project Prm1 Count Prm2 Count Prm3 Count Prm4 Count
2008 ABC 3 2 2 3

Note: You need not display the table heading as part of result i.e. Year, Project, Prm1 Count etc.

select count(*) from table where Prm1 = 'y' and Prm2 = y and prm3 =y and Prm4 = y.


Will this query help.
SELECT COUNT(*) FROM D9331COS.NFH_DATA WHERE NFH_PROC_FLG = 'H'

I tried for another table but by this count(*) it will show only that colum which we are putting in where codition ..

please help to resolve
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10210
Location: italy

PostPosted: Wed Oct 07, 2009 12:20 pm    Post subject: Reply to: query
Reply with quote

see
http://ibmmainframes.com/viewtopic.php?p=210158#210158
Back to top
View user's profile Send private message
abnikant

New User


Joined: 12 Jan 2007
Posts: 9
Location: Noida

PostPosted: Wed Oct 07, 2009 12:27 pm    Post subject: Re: query
Reply with quote

abnikant wrote:
LogiQ # 3

In an organization, the quality audit team rates projects on 4 quality parameters. These quality audits take place every quarter for every project.
Assume that the audit team entered the results of audit meetings into a table, like as shown below. If Prm value is Y it means the project was rated fair (pass) on that parameter.
Quarter Year Project Prm1 Prm2 Prm3 Prm4
1 2008 abc y n y n
2 2008 abc y y n y
3 2008 abc y y y y
4 2008 abc n y n y
5 2008 xyz n n n n
6 2008 xyz y n n y
7 2008 xyz y y n y
8 2008 xyz y n y y

Now at the end of the year, audit team has to generate a report for senior management which will show how many times a certain project faired (passed) on each parameter in a year. You being the quality audit team member have been asked to get the same using a single SELECT statement.

How would you do it?

The desire out for project ABC should look like:

Year Project Prm1 Count Prm2 Count Prm3 Count Prm4 Count
2008 ABC 3 2 2 3

Note: You need not display the table heading as part of result i.e. Year, Project, Prm1 Count etc.

select count(*) from table where Prm1 = 'y' and Prm2 = ‘y’ and prm3 =’y’ and Prm4 = ‘y’.


Will this query help.
SELECT COUNT(*) FROM D9331COS.NFH_DATA WHERE NFH_PROC_FLG = 'H'

I tried for another table but by this count(*) it will show only that colum which we are putting in where codition ..

please help to resolve



HI i need to display all the table heading with all the fields. But as per your query it will show those fields which are in where codition field.. please suggest
Back to top
View user's profile Send private message
bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Wed Oct 07, 2009 3:25 pm    Post subject:
Reply with quote

Use this query for your result.

select year
,project
,sum(prm1) as prm_Count1
,sum(prm2) as prm_Count2
,sum(prm3) as prm_Count3
,sum(prm4) as prm_Count4
from
(select year
,project
,Case when prm1 = 'Y' then
1
else
0
end as prm1
,Case when prm2 = 'Y' then
1
else
0
end as Prm2
,Case when prm3 = 'Y' then
1
else
0
end as Prm3
,Case when prm4 = 'Y' then
1
else
0
end as Prm4
from project1) proj group by
year,project;
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Oct 07, 2009 8:07 pm    Post subject:
Reply with quote

bhairon singh rathore wrote:
Use this query for your result.
...


plz don't
Back to top
View user's profile Send private message
bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Thu Oct 08, 2009 12:15 pm    Post subject:
Reply with quote

Yes you can use modified query given by GUYC but idea behind that query is same icon_lol.gif icon_evil.gif

http://ibmmainframes.com/viewtopic.php?p=210158#210158
Back to top
View user's profile Send private message
abnikant

New User


Joined: 12 Jan 2007
Posts: 9
Location: Noida

PostPosted: Thu Oct 08, 2009 12:18 pm    Post subject: thanks it's done
Reply with quote

thanks it's working know...
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Oct 08, 2009 12:26 pm    Post subject:
Reply with quote

bhairon singh rathore wrote:
Yes you can use modified query given by GUYC but idea behind that query is same icon_lol.gif icon_evil.gif

idea is the same, but performance is different.
1st will
- scan the the table
- make a worktable as big as the original
- sort & scan & sum the worktable

mine will
-scan & sum the table (via an index)
Back to top
View user's profile Send private message
bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Thu Oct 08, 2009 3:13 pm    Post subject:
Reply with quote

thanks for providing optimized query
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 1 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us