Small doubt in query.

Question

Small doubt in query.

Joy Smith San

SSC-Insane

Points: 24877
More actions
March 21, 2011 at 10:27 am

#157493

Hi All,
Have a query to select many fields by linking many tables.Now I need to modify this query a bit.
Rquirement is that, value of one of the field should be decided dynamicaly if it satisfies various conditions
in someother table (this table is not used in the query now.) For example, if the field value is 'MANGO' and
it has an entry and it satisfies the various criterias in the other table (above mentioned table) then
it should be takem as 'APPLE', Otherwise that actual field value shoule be selected.
I used "CASE ELSE" in the select list, but it dint work the way I want.
Can anyone give me an example pls.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

your on the right track; a case statement can help you filter your resulting value;

it might not be obvious, but a CASE statement can have more than one test , or can return the results of a separate case statement;

you did not provide ANY relevant info to what you are after, so I'm just going to give a generic CASE statemetn as an example, to see if that pushes you towards your goal

CASE

WHEN sValue = 'MANGO' AND GrowingSeason = '2' AND District = 'South'

THEN 'Mango'

WHEN sValue = 'MANGO' AND GrowingSeason = '1'

THEN 'Apple'

WHEN District = 'North'

THEN CASE

WHEN OtherCriteria = 1

THEN 'Pears'

WHEN OtherCriteria = 2 AND ExportCountry='Peru'

THEN 'Grapes'

ELSE 'Cherries'

END

ELSE 'Bananas'

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

Thanks !

My current query is as below.

selectC.[SV], AG.[AN], AG.[PV], C.[gid]

fromComp C

joinAgnt AG on AG.gid = C.gid

joinIdnt ID on ID.gid = C.gid

Where1 = 1

andAG.[PV] = '4.6' or AG.[AN] like 'Agnt'

andAG.[PV] = '4.3' or AG.[AN] like 'SW Agnt'

andAG.[PV] = '5.2' or AG.[AN] like 'Applcnt Mtr'

-------------------------------------------------------------

Now my requirement is that, when AG.[PV] = '4.3' and that record's 'GID' has got any entry in another table 'tbl_EXEC'

(GID is the foreign key) then AG.[PV] should be taken as '4.6'.

Hope my requirement is clear now

Thanks.