Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Posts
    5

    Unanswered: ORA-00933: SQL command not properly ended (UNION?)

    I have the following statement that is running on Oracle 8.1.7.2.0 on a Unix Solaris box:

    -----------------------------------------------
    select c.first_name,
    c.last_name,
    c.customer_type,
    b.user_id,
    b.id_type,
    c.ssn,
    c.phone,
    c.email_address,
    c.status,
    d.associated_customer
    FROM cms_commission_policy_xref a, cms_customer c, cms_commission_customer_xref d
    WHERE a.policy_number = '$FI Policy Number$'
    AND a.policy_number_domain = '$Policy Number Domain$'
    AND a.status = 1
    AND a.commission_number = d.commission_number
    AND d.status = 1
    AND d.associated_customer = c.associated customer
    AND c.last_name LIKE '$Customer Name$%' (+)
    UNION ALL
    select c.first_name,
    c.last_name,
    c.customer_type,
    b.user_id,
    b.id_type,
    c.ssn,
    c.phone,
    c.email_address,
    c.status,
    d.associated_customer
    FROM cms_policyaccess a, cms_customer c
    WHERE (a.policy_number = '$Policy$' or a.sf_policy_number = '$Policy$')
    AND a.policy_number_domain = '$Policy Number Domain$'
    AND a.status = 0
    AND a.associated_customer = c.associated_customer
    AND c.last_name LIKE '$Customer Name$%' (+)
    ORDER BY last_name, first_name
    -----------------------------------------------
    I am getting "ORA-00933: SQL command not properly ended". Here is a similar query that works fine:

    -----------------------------------------------
    select c.first_name,
    c.last_name,
    c.customer_type,
    b.user_id,
    b.id_type,
    c.ssn,
    c.phone,
    c.email_address,
    c.status,
    d.associated_customer
    FROM cms_commission_policy_xref a, cms_customerid b, cms_customer c, cms_commission_customer_xref d
    WHERE a.policy_number = '$Policy$'
    AND a.policy_number_domain = '$Policy Number Domain$'
    AND a.status = 1
    AND a.commission_number = d.commission_number
    AND d.status = 1
    AND d.associated_customer = c.associated_customer
    AND d.associated_customer = b.associated_customer (+)
    UNION ALL
    SELECT c.first_name,
    c.last_name,
    c.customer_type,
    b.user_id,
    b.id_type,
    c.ssn,
    c.phone,
    c.email_address,
    c.status,
    a.associated_customer
    FROM cms_policyaccess a, cms_customer c, cms_customerid b
    WHERE (a.policy_number = '$Policy$' or a.sf_policy_number = '$Policy$')
    AND a.policy_number_domain = '$Policy Number Domain$'
    AND a.status = 0
    AND a.associated_customer = c.associated_customer
    AND a.associated_customer = b.associated_customer (+)
    ORDER BY last_name, first_name
    -----------------------------------------------

    The only difference I can see offhand is the LIKE being used. This one has me stumped. Thanks in advance for your help.

    - Kelly Logan

  2. #2
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    It's probably complaining about the outer join sequence for comparing the table to a 'literal':
    AND c.last_name LIKE '$Customer Name$%' (+)
    remove the outer join.
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  3. #3
    Join Date
    Jun 2004
    Posts
    5
    I've tried with and without the right join (+). The weird thing is that there's no problem with the join in the second query.

  4. #4
    Join Date
    Jun 2004
    Posts
    5
    Hmm. . .wait, what do you mean that the join is comparing a table to a 'literal'? Isn't this a union between two tables (created by the queries)? You may need to explain this a bit more - I'm trying to fix someone else's code.

  5. #5
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    It may be that you didn't remove the reference to the d.associated_customer from the 2nd part of the union, see below:


    select c.first_name,
    c.last_name,
    c.customer_type,
    b.user_id,
    b.id_type,
    c.ssn,
    c.phone,
    c.email_address,
    c.status,
    d.associated_customer
    FROM cms_commission_policy_xref a, cms_customer c, cms_commission_customer_xref d
    WHERE a.policy_number = '$FI Policy Number$'
    AND a.policy_number_domain = '$Policy Number Domain$'
    AND a.status = 1
    AND a.commission_number = d.commission_number
    AND d.status = 1
    AND d.associated_customer = c.associated customer
    AND c.last_name LIKE '$Customer Name$%' (+) -- This compares column
    --last_name from table alias c with a variable value (or literal),
    --not a table, which is different than the SQL that is working.
    UNION ALL
    select c.first_name,
    c.last_name,
    c.customer_type,
    b.user_id,
    b.id_type,
    c.ssn,
    c.phone,
    c.email_address,
    c.status,
    d.associated_customer -- you didn't remove d.assciated_customer
    FROM cms_policyaccess a, cms_customer c -- but you did remove d alias
    WHERE (a.policy_number = '$Policy$' or a.sf_policy_number = '$Policy$')
    AND a.policy_number_domain = '$Policy Number Domain$'
    AND a.status = 0
    AND a.associated_customer = c.associated_customer
    AND c.last_name LIKE '$Customer Name$%' (+)
    ORDER BY last_name, first_name
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  6. #6
    Join Date
    Jun 2004
    Posts
    5
    It has got to be something simple like that that I'm still missing. Here's the current version (which is still getting the same error message).

    --------------------------------------------------------
    select c.first_name,
    c.last_name,
    c.customer_type,
    b.user_id,
    b.id_type,
    c.ssn,
    c.phone,
    c.email_address,
    c.status,
    d.associated_customer
    FROM cms_commission_policy_xref a, cms_customerid b, cms_customer c, cms_commission_customer_xref d
    WHERE a.policy_number = '$FI Policy Number$'
    AND a.policy_number_domain = '$Policy Number Domain$'
    AND a.status = 1
    AND a.commission_number = d.commission_number
    AND d.status = 1
    AND d.associated_customer = c.associated customer
    AND c.last_name LIKE '$Customer Name$%' (+)
    UNION ALL
    select c.first_name,
    c.last_name,
    c.customer_type,
    b.user_id,
    b.id_type,
    c.ssn,
    c.phone,
    c.email_address,
    c.status,
    c.associated_customer
    FROM cms_policyaccess a, cms_customerid b, cms_customer c
    WHERE (a.policy_number = '$Policy$' or a.sf_policy_number = '$Policy$')
    AND a.policy_number_domain = '$Policy Number Domain$'
    AND a.status = 0
    AND a.associated_customer = c.associated_customer
    AND a.associated_customer = b.associated_customer
    AND c.last_name LIKE '$Customer Name$%'
    ----------------------------------------------------------

    I changed that last column in the second half to reference the c alias instead of d. I also tried removing the LIKE statements entirely, also with no change in error.

  7. #7
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    The first part of your query is now doing a cartesian join with table alias b, since you removed reference to it from the predicate (where b.something = )

    select c.first_name,
    c.last_name,
    c.customer_type,
    b.user_id,
    b.id_type,
    c.ssn,
    c.phone,
    c.email_address,
    c.status,
    d.associated_customer
    FROM cms_commission_policy_xref a, cms_customerid b, cms_customer c, cms_commission_customer_xref d
    -- you don't have b.something = a or c or d something
    WHERE a.policy_number = '$FI Policy Number$'
    AND a.policy_number_domain = '$Policy Number Domain$'
    AND a.status = 1
    AND a.commission_number = d.commission_number
    AND d.status = 1
    AND d.associated_customer = c.associated customer
    AND c.last_name LIKE '$Customer Name$%' (+)

    What are you using to run this? sqlplus?

    Try running each piece of the union individually, use sqlplus and set autotrace on explain and see what it is doing. It may help to also copy and paste the actual message (or screen shot) so the experts can see what is happening.
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  8. #8
    Join Date
    Jun 2004
    Posts
    5

    Solved!!!

    Thank you very much for your help. I finally had to tear the entire query down table by table and build it back up to see what was happening. (Unfortunately, I did not have SQL PLUS available so I could not trace it as you suggested.)

    It appears that it was the order of the statements. When I put all of the single statement matches first and their table to table matches afterwards, it worked.

    Here's the working version:
    select c.first_name,
    c.last_name,
    c.customer_type,
    b.user_id,
    b.id_type,
    c.ssn,
    c.phone,
    c.email_address,
    c.status,
    d.associated_customer
    FROM cms_commission_policy_xref a, cms_customerid b, cms_customer c, cms_commission_customer_xref d
    WHERE c.last_name LIKE '$Customer Name$%'
    AND a.policy_number = '$FI Policy Number$'
    AND a.policy_number_domain = '$Policy Number Domain$'
    AND a.commission_number = d.commission_number
    AND d.status = 1
    AND c.associated_customer = b.associated_customer
    AND d.associated_customer = c.associated_customer (+)
    UNION ALL
    select c.first_name,
    c.last_name,
    c.customer_type,
    b.user_id,
    b.id_type,
    c.ssn,
    c.phone,
    c.email_address,
    c.status,
    c.associated_customer
    FROM cms_policyaccess a, cms_customerid b, cms_customer c
    WHERE (a.policy_number = '$Policy$' or a.sf_policy_number = '$Policy$')
    AND a.policy_number_domain = '$Policy Number Domain$'
    AND a.status = 0
    AND c.last_name LIKE '$Customer Name$%'
    AND a.associated_customer = c.associated_customer
    AND a.associated_customer = b.associated_customer

Posting Permissions

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