Oracle SQL Syntax

CountingCrows

Hi,

Just a quick question that hopefully might save me some time routing through my Oracle book.

This statement works fine with SQL but Oracle doesn't like it. Any idea why? The + join??

strSQL = "Select key_id,[Category]+';'+[SubCategory]+';'+[Status]+';'+[Planogram] FROM Plano_Key WHERE [Category]+';'+[SubCategory]+';'+[Status]+';'+[Planogram] = '" & strPath & "'"

Eoin

Can you post the actual SQL statement that is generated? I presume that you're generating it through ASP or something?

Evil Phil

It might be the semi-colons, I dunno PL/SQL, but aren't they an end of statement character?

CountingCrows

eoin wrote: »

Can you post the actual SQL statement that is generated? I presume that you're generating it through ASP or something?

This is the statement I'm attempting to run.

Evil Phil wrote: »

It might be the semi-colons, I dunno PL/SQL, but aren't they an end of statement character?

I need a semi-colon in the formatting, so that the return looks like

field1,field2;field3;field4 etc

dazberry

CountingCrows wrote: »

strSQL = "Select key_id,[Category]+';'+[SubCategory]+';'+[Status]+';'+[Planogram] FROM Plano_Key WHERE [Category]+';'+[SubCategory]+';'+[Status]+';'+[Planogram] = '" & strPath & "'"

select key_id, Category||";"||SubCategory||";"||Status||";"||Plangram from Plano_Key where...

D.

Evil Phil

I think owen is asking you to give us the value of strSQL once its been populated. Semicolons are generally termination characters in SQL and I'd imagine they are in Oracle.

Also the statement looks all kinds of screwed up to me. You where clause is going to look like this to Oracle (I've inserted the value Phil):

WHERE [Category]+';'+[SubCategory]+';'+[Status]+';'+[Planogram] = 'PHIL'

That is not well formed SQL unless Oracle do things in a very different way from most RDBMS'.

I need a semi-colon in the formatting, so that the return looks like

field1,field2;field3;field4 etc

If this is asp or vbscript then the sql is going to return a recordset - you'll have to traverse this and generate a string with the required formatting after you've executed the query and populated the recordset. You won't be able to format the returned values with the SQL.

What I suggest is this, update your code with the additional line below. Run the code, I'm assuming this is asp, and then post that the result you get on screen. We'll have a better idea of what your trying to achieve if you do.

strSQL = "Select key_id,[Category]+';'+[SubCategory]+';'+[Status]+';'+[Planogram] FROM Plano_Key WHERE [Category]+';'+[SubCategory]+';'+[Status]+';'+[Planogram] = '" & strPath & "'"
Response.Write strSQL

E39MSport

You can use commas and semi colons in your output without issue.
SQL> SELECT 'A'||';'||'B'
2 FROM
3 DUAL;
'A'
---
A;B
1 SELECT 'A'||','||'B'
2 FROM
3* DUAL
SQL> /
'A'
---
A,B

For your statement (not sure what you're trying to do in the where clause), try:-
strSQL =
"
Select
key_id ||','||
[Category]||';'||
[SubCategory]||';'||
[Status]||';'||
[Planogram]
FROM Plano_Key
WHERE
[Category]+';'+[SubCategory]+';'+[Status]+';'+[Planogram] = '" & strPath & "'"

Not sure either what your + symbol is tryng to achieve in the statement. Assume its a concat.

Be interesting to see what the outcome of Evil Phil's request is.

K

CountingCrows

Great stuff - thanks!

SQL below did the trick, the join/concat was indeed the problem.


Select pln_id,Category||';'||Banner||';'||Segements_Per_Section||';'||Group_Name||';'||Planogram FROM Planograms

string joins:
SQL +
Oracle ||

Evil Phil

Okay, didn't know any of this. You learn something new everyday.