OraFAQ Forum: SQL & PL/SQL » Anyone know if Double Quotes in Column Name Can be replace with Alternate Character???

Home » SQL & PL/SQL » SQL & PL/SQL » Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? (

) 1 Vote

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543396]

Tue, 14 February 2012 10:20

borioca
Messages: 9
Registered: February 2012
Location: USA

Junior Member

Hi everyone this is my first post.
I'm wondering if anyone knows of a way to replace the Double Quotes used to enclose column names with an alternative character.
This is the SQL I have now that Works!

select (case when CUST is null then "/BIC/Z_SUPPLNT" else NM1 end) CMPNTSUPSRCE
from TBL1, TBL2
where "/BIC/Z_MAJVEND"=CUST(+) and Material = '1ABCD456'
order by Material

But would like to do something along these lines below but keep getting error "ORA-00936: missing expression". Any Advice or suggestions would be greatly appreciated.

select (case when CUST is null then chr(34)||/BIC/Z_SUPPLNT||chr(34) else NM1 end) CMPNTSUPSRCE
from TBL1, TBL2
where chr(34)||/BIC/Z_MAJVEND||chr(34)=CUST(+) and Material = '1ABCD456'
order by Material

[Updated on: Tue, 14 February 2012 10:26]

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543398 is a reply to message #543396]

Tue, 14 February 2012 10:25

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I'm wondering if anyone knows of a way to replace the Double Quotes used to enclose column names with an alternative character.

No way.

Quote:

This is the SQL I have now that Works!

The query you posted should work in Oracle (unless '1ABCD456' is the name of a column).

Use SQL*Plus and copy and paste your session, the WHOLE session.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543399 is a reply to message #543398]

Tue, 14 February 2012 10:27

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Ah! you modified your post when I was posting myself.
Your second query is wrong.

What do you want to do?
Explain in details.
And post the SQL*Plus session as I asked.

Regards
Michel

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543400 is a reply to message #543398]

Tue, 14 February 2012 10:28

borioca
Messages: 9
Registered: February 2012
Location: USA

Junior Member

Please look at the updated SQL. I originally posted the wrong SQL.

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543401 is a reply to message #543400]

Tue, 14 February 2012 10:31

borioca
Messages: 9
Registered: February 2012
Location: USA

Junior Member

I need to create this SQL script on the Fly through VB but I can not because the Double quotes is giving me problems on the Visual Basic end of things. I need to pass the Char value chr(34) for the double quotes into the sql and then be able to execute it. This SQL is constantly changing on the fly based on the Material number!

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543404 is a reply to message #543401]

Tue, 14 February 2012 10:52

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

So just escape the quote in VB to get the wrong design working a few weeks longer.

(It will break on other things one day of course, for example once you have a material number longer than 30 characters)

Edit: Changed the link to something that actually has info on how it could work.

[Updated on: Tue, 14 February 2012 11:02]

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543412 is a reply to message #543404]

Tue, 14 February 2012 11:47

borioca
Messages: 9
Registered: February 2012
Location: USA

Junior Member

Thomas,
I could of used an escape in VB but it wouldn't/didn't solve my problem when I attempt to pass the SQL script. Would like to use the Oracle chr() function or something similar to create/run the sql on the Oracle side rather than the VB side. Here is the vb code if I'm using if anyone wants to add/comment on the VB code even though this is an Oracle FAQ.

strSQLvndr = "select (case when CUST is null then " & Chr(34) & "/BIC/Z_SUPPLNT" & Chr(34) & " else NM1 end) CMPNTSUPSRCE " & _
"from TBL1, TBL2 where " & Chr(34) & "/BIC/Z_MAJVEND" & Chr(34) & "=CUST(+) and Material = '" & strPNvalue & "' order by Material "

Also Tried but didn't work!
strSQLvndr = "select (case when CUST is null then " & Chr(34) & "/BIC/Z_SUPPLNT" & Chr(34) & " else NM1 end) CMPNTSUPSRCE " & _
"from TBL1, TBL2 where ""/BIC/Z_MAJVEND""=CUST(+) and Material = '" & strPNvalue & "' order by Material "

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543414 is a reply to message #543412]

Tue, 14 February 2012 11:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

as a general rule SQL statements do NOT contain double quote marks; CHR(34).

in plain text string here post actual SQL statement you desire to be executed.

[Updated on: Tue, 14 February 2012 11:51]

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543416 is a reply to message #543412]

Tue, 14 February 2012 11:56

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What does "didn't work" means?

You should post your question in a VB forum as this has NOTHING to do with Oracle. It is just your lack of knowing how to put a " inside a VB string.

For the Oracle side
Material = '" & strPNvalue & "'
is a BUG, one the biggest in SQL you can do.
Please read:
Application Developer's Guide - Fundamentals

Regards
Michel

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543420 is a reply to message #543416]

Tue, 14 February 2012 12:03

borioca
Messages: 9
Registered: February 2012
Location: USA

Junior Member

Michel,
I could of easily posted this on VB forum if I wanted a VB answer but I'm looking to see if Plain and Simply CAN the double quotes in an Oracle Select Statement be substituted with the Oracle chr() function or something similar. I'm NOT asking about VB if you look at the title of the post!!! Juse a simple question about ORACLE. If you have an ORACLE suggestion or solutions please provide otherwise YOUR NEGATIVE comments ARE NOT welcome!!!

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543421 is a reply to message #543420]

Tue, 14 February 2012 12:07

borioca
Messages: 9
Registered: February 2012
Location: USA

Junior Member

BlackSwan,
I posted in the original post the SQL I want to execute but would like to substitute the required double quotes to run the original SQL with something different.

select (case when CUST is null then "/BIC/Z_SUPPLNT" else NM1 end) CMPNTSUPSRCE
from TBL1, TBL2
where "/BIC/Z_MAJVEND"=CUST(+) and Material = '1ABCD456'
order by Material

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543423 is a reply to message #543421]

Tue, 14 February 2012 12:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

borioca wrote on Tue, 14 February 2012 10:07

BlackSwan,
I posted in the original post the SQL I want to execute but would like to substitute the required double quotes to run the original SQL with something different.

select (case when CUST is null then "/BIC/Z_SUPPLNT" else NM1 end) CMPNTSUPSRCE
from TBL1, TBL2
where "/BIC/Z_MAJVEND" =CUST(+) and Material = '1ABCD456'
order by Material

when you execute SQL above in sqlplus, what is the results?

what exactly does "/BIC/Z_MAJVEND" represent?

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543425 is a reply to message #543423]

Tue, 14 February 2012 12:17

borioca
Messages: 9
Registered: February 2012
Location: USA

Junior Member

I have put double quotes around the /BIC/Z_MAJVEND as that is the Column names in the Oracle table I'm trying to query. I have no control of those tables and why they used that nameing convention for the column names but is the only way I know of to query those particular column names.

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543427 is a reply to message #543425]

Tue, 14 February 2012 12:21

borioca
Messages: 9
Registered: February 2012
Location: USA

Junior Member

when I try to run the second script in the original post I get an error "ORA-00936: missing expression". I would like to run something similar to below in Oracle utilizing the Oracle chr() function or other.

select (case when CUST is null then chr(34)||/BIC/Z_SUPPLNT||chr(34) else NM1 end) CMPNTSUPSRCE
from TBL1, TBL2
where chr(34)||/BIC/Z_MAJVEND||chr(34)=CUST(+) and Material = '1ABCD456'
order by Material

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543428 is a reply to message #543427]

Tue, 14 February 2012 12:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> I would like to run something similar to below in Oracle utilizing the Oracle chr() function or other.
You are required to provide valid syntax if you want any SQL statement to be executed.
You are not allowed to create your own new syntax on the fly.

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543429 is a reply to message #543420]

Tue, 14 February 2012 12:38

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

CAN the double quotes in an Oracle Select Statement be substituted with the Oracle chr() function or something similar

And I already told you, twice, no; but maybe no is a word you can't read!!! (I also can post exclamation marks)

Regards
Michel

[Updated on: Tue, 14 February 2012 12:38]

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543431 is a reply to message #543428]

Tue, 14 February 2012 12:41

borioca
Messages: 9
Registered: February 2012
Location: USA

Junior Member

BlackSwan,
So it sounds like your saying the double quotes utilized in Oracle for object or alias names Can NOT be substituted/altered?!?!? If this is the case then Thank You for your quick response, unlike Michel's NON contributive comments.

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543434 is a reply to message #543431]

Tue, 14 February 2012 12:43

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Thank You for your quick response, unlike Michel's NON contributive comments.

So you didn't my FIRST answers:

Michel Cadot wrote on Tue, 14 February 2012 17:25

Quote:
I'm wondering if anyone knows of a way to replace the Double Quotes used to enclose column names with an alternative character.

No way.

Michel Cadot wrote on Tue, 14 February 2012 17:27

Ah! you modified your post when I was posting myself.
Your second query is wrong.

Is this NON contributive or is this for you a LACK of reading?

Regards
Michel

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543436 is a reply to message #543434]

Tue, 14 February 2012 12:46

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Waiting for your apologies... (if you have enough courage and honesty to admit you were wrong.)

Regards
Michel

Report message to a moderator

Re: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character??? [message #543459 is a reply to message #543436]

Tue, 14 February 2012 22:53

javed.khan
Messages: 340
Registered: November 2006
Location: Banglore

Senior Member

khans.jak@gmail.com

Dear borioca,

Be polite; write diplomatically; even in a declaration of war one observes the rules of politeness.

I am a member of this forum from a long time now and its like my home for mentors. Its not a place to just drop by snatch your answer and go away.. come.. we will welcome.. discuss and we will help.. but be polite. Its a facility not your birth right to get your solution here. Please make peace no war.we are professionals isnt it.

Javed K

Report message to a moderator

Previous Topic:	About Stored prcedure
Next Topic:	ORA-03114

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Mar 20 17:51:16 CDT 2026