Home » SQL & PL/SQL » SQL & PL/SQL » Working one query but trouble with some extension please help me
Working one query but trouble with some extension please help me [message #413450] Wed, 15 July 2009 15:40 Go to next message
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 #413455 is a reply to message #413450] Wed, 15 July 2009 15:59 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
This is a FAQ. Search for "varying inlist".
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 #413511 is a reply to message #413450] Thu, 16 July 2009 03:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hint: If your current logic makes something dificult, try using a different approach.

I'd rewrite your code like this:
WHERE (   (c.FacilityNum in (101,102,103) and &fecval=0)
       OR (c_facilitynum = 108 and &fecval != 0))
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 Go to previous messageGo to next message
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 #413610 is a reply to message #413455] Thu, 16 July 2009 13:00 Go to previous messageGo to next message
aswani7
Messages: 14
Registered: July 2009
Junior Member
Hello Thomas,

it seems i am unable to get from varying list, it shows huge information with plsql


my problem here is a simple query with some tricky condition

Please Help Me

Thanks & 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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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, Razz


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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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.
Re: Working one query but trouble with some extension please help me [message #413623 is a reply to message #413621] Thu, 16 July 2009 15:32 Go to previous message
aswani7
Messages: 14
Registered: July 2009
Junior Member


Thomas Thank You Very much,

it is working very fine

i appreciate your help,

apologize for code formatting , i will go through the formatting of the forum ,

Thanks To J Row Bottom & Thanks to All ,


Best Regards
asita
Previous Topic: Connect by error
Next Topic: dynamic sql not working
Goto Forum:
  


Current Time: Sun Dec 04 20:26:01 CST 2016

Total time taken to generate the page: 0.10240 seconds