| Working one query but trouble with some extension please help me [message #413450] |
Wed, 15 July 2009 15:40  |
aswani7
Messages: 14
Registered: July 2009
|
Junior Member |
|
|
Hello All,
i have below query which is working fine, (it returns all rows that matched with 101 facility Num)
define fecval=0
select *
FROM hsi.ChartQueueTxLog cq, hsi.Chart c
WHERE
c.FacilityNum in
(case
When &fecval = 0 then 101
else 108
END)
AND cq.ChtNum = c.ChtNum
AND cq.ExitTime != TO_DATE('1964-01-01','YYYY-MM-DD')
AND cq.AvailableTime != TO_DATE('1964-01-01','YYYY-MM-DD')
AND cq.ExitTime >= cq.AvailableTime
now how can i change the above statment to give results
but i would like to add one more value to inlist
i.e if &fecval=0 then 101,102,103 else 108 how can i achive this
i tried with below query i got error
select *
FROM hsi.ChartQueueTxLog cq, hsi.Chart c
WHERE
c.FacilityNum in
(case
When &fecval = 0 then 101,102,103
else 108
END)
AND cq.ChtNum = c.ChtNum
AND cq.ExitTime != TO_DATE('1964-01-01','YYYY-MM-DD')
AND cq.AvailableTime != TO_DATE('1964-01-01','YYYY-MM-DD')
AND cq.ExitTime >= cq.AvailableTime
Can you please help me to get the results of 101,102,103 please
Thanks in advance
Asita
|
|
|
|
|
|
| Re: Working one query but trouble with some extension please help me [message #413475 is a reply to message #413450] |
Wed, 15 July 2009 23:34  |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
From your 2 previous topics:
| Michel Cadot wrote on Wed, 15 July 2009 07:49 | Before your next question, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Regards
Michel
|
| Michel Cadot wrote on Wed, 15 July 2009 17:08 | This is a FAQ, search for "varying inlist".
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Regards
Michel
|
[Updated on: Wed, 15 July 2009 23:35] Report message to a moderator |
|
|
|
|
|
| Re: Working one query but trouble with some extension please help me [message #413609 is a reply to message #413511] |
Thu, 16 July 2009 12:58 |
aswani7
Messages: 14
Registered: July 2009
|
Junior Member |
|
|
Thanks JRow Bottom
thanks for your help,
however here what i am looking is
thinks that &faceval is a value that is entered by user if user enters the 0 then it includes in condition facilitynum in (101,102,103) else (if user enters other than 0) then it needs to be take facility num in (101) only
this is what i am trying to accomplish
i tried to check in varying list , but it is huge for me, it shows all pl/sql stuff , it seems to be i am unable to find answer for ages with those
once again thank you for your sugesstion
please kindly help me anyone
Best Regards
asita
|
|
|
|
|
|
| Re: Working one query but trouble with some extension please help me [message #413611 is a reply to message #413609] |
Thu, 16 July 2009 13:00 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
| aswani7 wrote on Thu, 16 July 2009 19:58 | thinks that &faceval is a value that is entered by user if user enters the 0 then it includes in condition facilitynum in (101,102,103) else (if user enters other than 0) then it needs to be take facility num in (101) only
|
That's exactly what JRowbottom's suggestion does. At least when, as I assume the last "101" should really be "108", as it was in your previous posts.
[Updated on: Thu, 16 July 2009 13:02] Report message to a moderator |
|
|
|
| Re: Working one query but trouble with some extension please help me [message #413620 is a reply to message #413611] |
Thu, 16 July 2009 15:09 |
aswani7
Messages: 14
Registered: July 2009
|
Junior Member |
|
|
Thnaks Thomas
i checked it and it is working fine as i'm expected
Really Thanks to JRowBottom, 
please help this doubt so that it will get closed the issue,
in extension to this when &fecval=3 then i would like to select ALL values
(
(c.FacilityNum in (101,102,103) AND &fecval = 0)
OR c.FacilityNum = 108 AND &fecval = 1
OR c.FacilityNum = ALL AND &fecval = 3
)
here in the above statement all means (i have in table 101,102,103.......... 134) and also this list may go further if i mention individual numbers then i need to change query everytime that when a new facilitynumber adds to table
so is there any possible way to just get ALL values when the &Fecval = 3
Please Help me
Thanks in advance
Best Regards
[Updated on: Thu, 16 July 2009 15:09] Report message to a moderator |
|
|
|
| Re: Working one query but trouble with some extension please help me [message #413621 is a reply to message #413620] |
Thu, 16 July 2009 15:16 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Your post is *STILL* not formatted. Haven't you read the forum guide yet?
And your brackets are wrong. You would have to put () around the different "or" parts, so that the AND is evaluated before the ORs.
Basically it could work like this :
WHERE ( (c.FacilityNum in (101,102,103) and &fecval=0)
OR (c_facilitynum = 108 and &fecval = 1) )
OR (c_facilitynum = 109 and &fecval = 2) )
OR ( &fecval = 3 )
)
when you want every c_facilitynum returned with &fecval = 3.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
