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: 10256
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: 1281
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: 1281
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Need help in building a logic Benchwarmer All Other Mainframe Topics 4 Wed Feb 22, 2017 2:49 am
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm


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