Home » SQL & PL/SQL » SQL & PL/SQL » Right syntax (Oracle 8i)
Right syntax [message #318722] Wed, 07 May 2008 14:26 Go to next message
apps_user
Messages: 35
Registered: May 2008
Member
I am storing select statement in a variable and using execute_immediate to execute that.
When I have only 'VP' in the following decode statement it didnot compile.
Once I put the ''VP'' in decode it compiled.
Is the syntaxt ok?
Here is the code I am calling:
'decode(d.DEPT_NAME,||''VP''||,d.DEPT_NAME,NULL) COMPANY,'||


Thanks in advance.
Re: Right syntax [message #318724 is a reply to message #318722] Wed, 07 May 2008 14:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is VP a variable or a constant?
In first case, change '' to ' and move them in the other side of ||
In second case, remove the ||

regards
Michel
Re: Right syntax [message #318725 is a reply to message #318722] Wed, 07 May 2008 15:06 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Michel
VP is a value in dept_name field.

The psuedologic is if the value of DEPT_NAME is 'VP' then display else it should show up null in the report.

It should be this if I run from the sqlplus.
select
decode(d.DEPT_NAME,'VP',d.DEPT_NAME,NULL)


Please advise.
Re: Right syntax [message #318726 is a reply to message #318725] Wed, 07 May 2008 15:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So, in your expression, it is a constant and you are in the second case.

Regards
Michel
Re: Right syntax [message #318727 is a reply to message #318722] Wed, 07 May 2008 15:20 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Thanks Michel.
I am using || to concatenate other select conditions.
That should be ok.
That means my code is right.
Re: Right syntax [message #318729 is a reply to message #318727] Wed, 07 May 2008 15:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
That means my code is right.

Which one?
The one you posted is not.
You have to modify it in the way I said.

Regards
Michel
Re: Right syntax [message #318772 is a reply to message #318722] Wed, 07 May 2008 23:05 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Michel
Quote:

In second case, remove the ||



This is not required as i am using like the following:
'decode(d.DEPT_NAME,||''VP''||,d.DEPT_NAME,NULL) COMPANY,'||
'e.emp_name '||

Re: Right syntax [message #318798 is a reply to message #318772] Thu, 08 May 2008 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I precise, "remove || around the constant".

Or better count the ' in your string and think each one does.
If you don't understand what they do, use dbms_output to display what is the string in the end and analyze it.

Regards
Michel
Re: Right syntax [message #318857 is a reply to message #318798] Thu, 08 May 2008 04:48 Go to previous messageGo to next message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
Always use CHR(39) as a substitue to single quote (') while using excute immediate statements.

- Elaiyavel
Re: Right syntax [message #318859 is a reply to message #318857] Thu, 08 May 2008 04:51 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements003.htm#i42617

Regards

Raj
Re: Right syntax [message #318908 is a reply to message #318857] Thu, 08 May 2008 07:32 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
elaiyavel wrote on Thu, 08 May 2008 11:48
Always use CHR(39) as a substitue to single quote (') while using excute immediate statements.

- Elaiyavel

Why? What's wrong with single quotes?
Re: Right syntax [message #318943 is a reply to message #318908] Thu, 08 May 2008 08:39 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Littlefoot wrote on Thu, 08 May 2008 14:32
elaiyavel wrote on Thu, 08 May 2008 11:48
Always use CHR(39) as a substitue to single quote (') while using excute immediate statements.

- Elaiyavel

Why? What's wrong with single quotes?

It does not know how to handle them.

Regards
Michel

Previous Topic: Need help in SQL query using rownum
Next Topic: Organizing series of Dates into two different columns
Goto Forum:
  


Current Time: Sun Dec 04 18:27:22 CST 2016

Total time taken to generate the page: 0.19198 seconds