Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Oracle SQL Syntax

Options
  • 19-10-2009 4:54pm
    #1
    Registered Users Posts: 1,830 ✭✭✭


    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 & "'"


Comments

  • Registered Users Posts: 21,257 ✭✭✭✭Eoin


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


  • Registered Users Posts: 7,468 ✭✭✭Evil Phil


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


  • Registered Users Posts: 1,830 ✭✭✭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


  • Registered Users Posts: 2,150 ✭✭✭dazberry


    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.


  • Registered Users Posts: 7,468 ✭✭✭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


  • Advertisement
  • Registered Users Posts: 3,721 ✭✭✭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


  • Registered Users Posts: 1,830 ✭✭✭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 ||


  • Registered Users Posts: 7,468 ✭✭✭Evil Phil


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


Advertisement