Home » SQL & PL/SQL » SQL & PL/SQL » sql case tool
sql case tool [message #288153] Sat, 15 December 2007 02:38 Go to next message
annu-agi
Messages: 199
Registered: July 2005
Location: Karachi
Senior Member

hi experts

i want to display those dealer which are plan to visit on a specific day .. which is in p_day
if p_day is then then show visit1 is not null
for that i use case tool
and it gives me error

[code
old 7: when &p_day=1 then visit1 is not null
new 7: when 2=1 then visit1 is not null
when 2=1 then visit1 is not null
*
ERROR at line 7:
ORA-00905: missing keyword
[/code]

select a.d_code, a.name, B.CODE STAT_CD ,B.DESCRIPT STAT_NM, c.code ROUTE_CD, C.DESCRIPT ROUTE_NM,
A.VISIT1,A.VISIT2,A.VISIT3,A.VISIT4,A.VISIT5,A.VISIT6,A.VISIT7,A.PHONE, A.ADDRESS1||' '||
A.ADDRESS2||' '||A.ADDRESS3
   from 
(SELECT d_code, name, 
(case
 when &p_day=1  then visit1 is not null
end), 
PHONE,  ADDRESS1, ADDRESS2, ADDRESS3 FROM DEALER_INFO) A,
---FROM DEALER_INFO WHERE (VISIT1||VISIT2||VISIT3||VISIT4||VISIT5||VISIT6||VISIT7) IS NOT NULL ) a,
(select CODE, DESCRIPT  from sld_info WHERE KEYFIELD='T') B,
(select CODE, STATION, DESCRIPT from sld_info WHERE KEYFIELD='R') c
where 
B.CODE=SUBSTR(D_cODE,1,2) AND
B.CODE='01' AND
c.station=B.CODE AND
C.CODE=SUBSTR(D_CODE,3,6) AND
C.CODE IN (SELECT PICKLIST_VALUES FROM TMP_DATA WHERE IP_aDDRESS='192.168.1.29' AND SESSION_ID=12)
ORDER BY 1



what is missing in it .. so many time i use this .. but this time i dont know where i m doing mistake



any idea, any suggustions



regards

anwer
Re: sql case tool [message #288155 is a reply to message #288153] Sat, 15 December 2007 02:44 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

if p_day is then then show visit1 is not null
You should use single quotes, as you should treat that a character literal.
(SELECT d_code, name, 
(case
 when &p_day=1  then 'visit1 is not null'
 end),
By
Vamsi
Re: sql case tool [message #288157 is a reply to message #288155] Sat, 15 December 2007 03:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The original query does not make sense, nor does the solution provided. (since the case is not aliased, it is 'unreachable' for the outer select)

Annu-agi, please try to explain what you are trying to achieve with the line that errors out.
Re: sql case tool [message #288161 is a reply to message #288155] Sat, 15 December 2007 03:43 Go to previous messageGo to next message
annu-agi
Messages: 199
Registered: July 2005
Location: Karachi
Senior Member

sorry for the missing code

first see the data
D_CODE          V V V V V V V
--------------- - - - - - - -
01PIB40-029
01PIB40-032
01PIB40-034
01PIB40-035
01PIB40-036
01PIB40-038
01PIB40-041
01PIB40-045         Y     Y
01PIB40-046         Y     Y
01SBZ41-002
01SBZ41-003
01SBZ41-004
01SBZ41-006     Y     Y
01SBZ41-007
01SBZ41-008
01SBZ41-009
01SBZ41-010
01SBZ41-011
01SBZ41-012     Y     Y
01SBZ41-013     Y     Y
01SBZ41-014

now lets suppose if my p_day is 3 then i got these record
D_CODE          V V V V V V V
--------------- - - - - - - -
01PIB40-045         Y     Y
01PIB40-046         Y     Y

no matter it shows another day also

for that im using case tool
and my coding is
[code]
select d_code,
(
case
when &p_day = 1 then visit1 is not null
end
),
(
case
when &p_day = 2 then visit2 is not null
end
),
(
case
when &p_day = 3 then visit3 is not null
end
)
from dealer_info
[code]
which is equalant to
select * from dealer where visit1 is not null

but if my query is variable dependent i would like to see the results on the bases of specific day . then
i must use
if p_day=1 then visit1 is not null
end
if p_day=2 then visist2 is not null
end
such like condisions



any suggustion and idea
Re: sql case tool [message #288162 is a reply to message #288161] Sat, 15 December 2007 03:49 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

can you describe your table and post some sample data.its not clear as to what you are trying to do.

regards,

[Updated on: Sat, 15 December 2007 03:51]

Report message to a moderator

Re: sql case tool [message #288165 is a reply to message #288153] Sat, 15 December 2007 04:16 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Do you mean something like (equivalent to your second example)
SELECT *
FROM dealer_info
WHERE (&&p_day.=1 AND visit1 IS NOT NULL)
   OR (&&p_day.=2 AND visit2 IS NOT NULL)
   OR (&&p_day.=3 AND visit3 IS NOT NULL)
(filter rows with filled column related to given parameter)?
Previous Topic: Help with query
Next Topic: length of SQL query
Goto Forum:
  


Current Time: Sat Dec 03 13:53:18 CST 2016

Total time taken to generate the page: 0.04453 seconds