Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    1

    Unanswered: Getting ORA-00933 when trying to join tables

    Hello,

    I'm trying to join tables in Oracle and I'm getting the dreaded ORA-00933 error on execution. I am trying to build an xml file based on the result set of the query in a dll file. The following is my sql statement.

    SQL = SQL & "SELECT booth_master.*, show_master_boothtypes.boothtype_name,"
    SQL = SQL & " (SELECT DISTINCT FEATURE_CERTIFICATION_MASTER.BOOTH_ID FROM FEATURE_CERTIFICATION_MASTER"
    SQL = SQL & " WHERE FEATURE_CERTIFICATION_MASTER.FEATURE_STATUS = 1 AND FEATURE_CERTIFICATION_MASTER.BOOTH_ID = BOOTH_MASTER.BOOTH_ID) AS CERTIFICATION"
    SQL = SQL & " FROM booth_master, show_master_boothtypes"
    SQL = SQL & " WHERE booth_master.BOOTHTYPE_ID = show_master_boothtypes.BOOTHTYPE_ID"
    SQL = SQL & " AND booth_master.booth_status = 1"
    SQL = SQL & " AND CONCAT(';',CONCAT(pavilion_ids,';')) LIKE '%;" & OraDynaset1("pavilion_id") & ";%'"

    Set OraDynaSet2 = OraDB.CreateDynaset(SQL, 0)


    The statement worked prior to me added the secondary select statement for certification. When I run the statement using TOAD it runs fine. What am I doing wrong? I'm new to Oracle.

    Any help would be greatly appreciated.

    Tracey

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Getting ORA-00933 when trying to join tables

    Check the value of OraDynaset1("pavilion_id") - I suspect it may contain an apostrophe - e.g. "It's Friday". So the last condition would then become something like:

    AND CONCAT(';',CONCAT(pavilion_ids,';')) LIKE '%;It's Friday;%'

    This will confuse Oracle. The solution is to double up any embedded apostrophes like this:

    AND CONCAT(';',CONCAT(pavilion_ids,';')) LIKE '%;It''s Friday;%'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •