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
 

 

Peculiar query

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







PostPosted: Sat Dec 10, 2005 8:45 am    Post subject: Peculiar query
Reply with quote

Dear buddy,

this is a peculiar db2 query I was asked in one interview.
___________________________________________________________
there is a employee table having three columns. those columns are

NAME | SALARY | FLAG
aa..........20.............y
bb..........45............n
xs..........23............y
we.........34.............n
df..........11.............y
tv..........20.............y
a flag is simply 'Y' if salary is paid, and 'N' if not paid.
___________________________________________________________
Then my question is, unfortunately the person who updated the table made wrong.
He should set all 'y's inplace of all 'n's and Vise versa
How to make this correct with a single query.
___________________________________________________________
my table should be like this.
NAME | SALARY | FLAG
aa..........20.............n
bb..........45............y
xs..........23............n
we.........34.............y
df..........11.............n
tv..........20.............n
Back to top

andy6703

New User


Joined: 25 Jul 2005
Posts: 14

PostPosted: Sat Dec 10, 2005 11:43 am    Post subject:
Reply with quote

Hey buddy,

One solution is when u retrieve the table the values will be as per your need which is shown below:

SELECT NAME, SALARY, FLAG,
CASE FLAG
WHEN 'Y' THEN 'N'
ELSE 'Y'
END
FROM EMPLOYEE

This query may not update the table but while retrieving values will meet your current requirements.

Hope this helps.

God Bless!
Back to top
View user's profile Send private message
Jag

New User


Joined: 07 Dec 2005
Posts: 30
Location: India

PostPosted: Sun Dec 11, 2005 11:58 am    Post subject: Hi friend
Reply with quote

you have to follow three steps to get the work done..

1) First write a select query to get the employee names whose salary falg is "N"

Select empname
from emptable
where salaryfalg='N';


2)Update the table to set the salary flag to "N" whose flag is "y" earlier

Update emptable
set salaryfalg="N"
where salaryfalg="y";

This will set the salary flag to "n" whose flg is "y" before.By the query half work is done Right?

3) NOW set the salary falg of the employess (retrieved from 1 step) to "y"

update emptable
set salaryfalg='Y'
where empname="......(empnames fom first step)"


This will complete your job..But if anyone know better small process suggest the person who asked this ? as well as me too tyo improve my skills :-)

Cheers,
Jag.
Back to top
View user's profile Send private message
Guest







PostPosted: Tue Dec 13, 2005 12:17 am    Post subject: Re: Peculiar query
Reply with quote

to andy.

HI buddy thanks for the reply. For selecting it is okay. But how to update?

to jag.

hey thanks for the reply. Imagine yourself when there are millions of records.
Back to top
EnjoyMF

New User


Joined: 27 May 2005
Posts: 88

PostPosted: Tue Dec 13, 2005 11:59 am    Post subject:
Reply with quote

Hi Guys

I suppose we can also use the below query for this particular query, this solves our purpose in a single query,
this can be used in a program or spufi or QMF

update employee
set FLAG =
case
when FLAG = 'y' then 'n'
when area = 'n' then 'y'
end
;


Cheers
Prasad
Back to top
View user's profile Send private message
Guest







PostPosted: Tue Dec 13, 2005 10:53 pm    Post subject: Re: Peculiar query
Reply with quote

hi MR. enjoy,

hope that u r answer will solve the purpose.

Thanks
tvmkishore icon_lol.gif
Back to top
bhaskarbale

New User


Joined: 13 Dec 2005
Posts: 1

PostPosted: Wed Dec 14, 2005 11:04 am    Post subject:
Reply with quote

i want to know quary that will give second highest salary from emp table
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Wed Dec 14, 2005 11:58 am    Post subject: second highest salary
Reply with quote

Hi frnd,

SELECT MAX(SALARY) FROM EMP WHERE SALARY<(SELECT MAX(SALARY) FROM EMP);

Hope it works
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 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
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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