Home » SQL & PL/SQL » SQL & PL/SQL » decode syntax in Stored Procedure (Oracle 9i)
decode syntax in Stored Procedure [message #356292] Thu, 30 October 2008 04:03 Go to next message
Rantoun
Messages: 8
Registered: October 2008
Location: Lebanon
Junior Member

I am having trouble compiling a stored procedure that contains a decode function with the following syntax

DECODE(x, search, result, default) --does NOT compile


however it DOES compile when i use only three parameters

DECODE(x, search, result) -- compiles 


any ideas about this issue?!?
Re: decode syntax in Stored Procedure [message #356293 is a reply to message #356292] Thu, 30 October 2008 04:05 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's never given me any problems.
How about you show as the actual code used plus the exact syntax error.
Re: decode syntax in Stored Procedure [message #356297 is a reply to message #356292] Thu, 30 October 2008 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
any ideas about this issue?!?

Yes many but they all depend on the actual error.

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: decode syntax in Stored Procedure [message #356300 is a reply to message #356297] Thu, 30 October 2008 04:26 Go to previous messageGo to next message
Rantoun
Messages: 8
Registered: October 2008
Location: Lebanon
Junior Member

Ok here is the stored procedure... btw, the select statement does execute in SQL Plus, but it doesn't compile in a stored procedure?!?!?

(v2 in out sys_refcursor) as
begin

open v2 for 

select VehicleID, VehicleGroupID, TripID, DeviceDate, community, Speed,
					decode (COMMUNITY , LCOMMUNITY , null, row_num) rn				       
				from
				(
					select  VehicleID, VehicleGroupID, TripID, DeviceDate, community, Speed,
						lag(COMMUNITY) over (order by vehicleID, deviceDate) lCOMMUNITY,
						row_number()over (partition by VehicleID order by deviceDate) row_num
					from
					(
						select  VehicleID, VehicleGroupID, TripID, DeviceDate, Speed,
							LSTREET.NAME_EN community
						from
							resourceslochistory, SDEUSER.Lebanon_Zones LSTREET
						where
							VehicleGroupID = 'VGROUP999' and
							DeviceDate between to_date('29/Jul/2008 19:00:00', 'dd/mon/yyyy hh24:mi:ss') and
								 	   to_date('29/Jul/2008 19:30:59', 'dd/mon/yyyy hh24:mi:ss') and
							MsgType in ('I', 'S') and numSat > 0 and
							vehicleId in ('VA12', 'VA15') and
						     	sdo_nn(LSTREET.shape, GEOMETRY, 'sdo_num_res=1') = 'TRUE'
						order by vehicleID, deviceDate
					)
				 )
				
;
	
end;


the Error is
Line # = 7 Column # = 26 Error Text = PL/SQL: ORA-12714: invalid national character set specified

[Updated on: Thu, 30 October 2008 04:27]

Report message to a moderator

Re: decode syntax in Stored Procedure [message #356307 is a reply to message #356300] Thu, 30 October 2008 04:45 Go to previous messageGo to next message
Rantoun
Messages: 8
Registered: October 2008
Location: Lebanon
Junior Member

ok.. new info

It is not the 4-parameter decode that is not compiling, it is the actual paramater itself in

decode (COMMUNITY , LCOMMUNITY , null, row_num) rn

when I replace LCOMMUNITY by 1 for example, it compiles!!! This is weird!
Re: decode syntax in Stored Procedure [message #356308 is a reply to message #356307] Thu, 30 October 2008 04:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
To pinpoint it further:
What if you just select lcommunity, without the whole decode?
Re: decode syntax in Stored Procedure [message #356311 is a reply to message #356300] Thu, 30 October 2008 04:59 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Check the below code in a stored procedure
Quote:

Select DECODE(dummy, 'X', 'a', null) dual
It will work.

I dont think this is the problem.


Please Have a look at the below function
Quote:

sdo_nn(LSTREET.shape, GEOMETRY, 'sdo_num_res=1')




Regards,
Oli


[Updated on: Thu, 30 October 2008 05:01]

Report message to a moderator

Re: decode syntax in Stored Procedure [message #356312 is a reply to message #356311] Thu, 30 October 2008 05:01 Go to previous messageGo to next message
Rantoun
Messages: 8
Registered: October 2008
Location: Lebanon
Junior Member

ok i think it has to do with the different data types of the fields I am comparing.

COMMUNITY is of type NVARCHAR2 when i retrieve it from the first inner sql statement; however LCOMMUNITY is a calculated field.

so could this be the reason?!?! If so, how to force it to compare the two fields even if with different data types?!?
Re: decode syntax in Stored Procedure [message #356313 is a reply to message #356312] Thu, 30 October 2008 05:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
LCommunity is the community of a previous row, so I'd say it would have the same datatype..

Unless lag() can't return a nvarchar
Re: decode syntax in Stored Procedure [message #356315 is a reply to message #356313] Thu, 30 October 2008 05:12 Go to previous messageGo to next message
Rantoun
Messages: 8
Registered: October 2008
Location: Lebanon
Junior Member

you have a point! is there a way to check the retrieved data type of LCOMMUNITY ?
Re: decode syntax in Stored Procedure [message #356316 is a reply to message #356292] Thu, 30 October 2008 05:14 Go to previous messageGo to next message
fairgame
Messages: 29
Registered: October 2008
Junior Member
use CAST function to change the datatype...

viz.,CAST(LCOMMUNITY as NVARCHAR2)

check this http://techonthenet.com/oracle/functions/cast.php
Re: decode syntax in Stored Procedure [message #356318 is a reply to message #356316] Thu, 30 October 2008 05:23 Go to previous message
Rantoun
Messages: 8
Registered: October 2008
Location: Lebanon
Junior Member

HORRAYYYYY...

Thank you all for your suggestions and ideas! It finally worked. What a relief!

casted LCOMMUNITY before using it in DECODE.

CAST (lag(COMMUNITY) over (order by vehicleID, deviceDate) as NVARCHAR2(200)) LCOMMUNITY 


guess the title of this topic is not valid anymore! it has nothing to do with decode syntax!
Previous Topic: Connect By is looping
Next Topic: merge statment
Goto Forum:
  


Current Time: Sat Dec 10 00:52:00 CST 2016

Total time taken to generate the page: 0.11714 seconds