Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: Outer Join problem

    I am stuck with the problem for so long. Could you please help.
    Some how, I need to get around AND EXISTS ( Sub Query)

    SELECT nl.title,
    COALESCE(CHAR(ac.currentcy_date,'MM/DD/YYYY'),' '),
    COALECE(CHAR(ORA8.ADD_MONTHS(ac.currentcy_date, co.currentcy_interval),'MM/DD/YYYY'),'Needs Initial Review'),
    n.menu_position

    FROM navbar_links nl,
    navbars n,
    ap_profile ap,
    company co,
    cy_review_sections crs RIGHT OUTER JOIN currentcy_review ac RIGHT OUTER JOIN
    ON ac.applID = 3
    ON ac1.cy_section_link_id = crs.cy_section_link_id
    AND crs.cy_section_link_id = nl.link_id
    AND nl.link_id = n.link_id
    AND n.navbar_id = 9
    and ap.appl_id = 3
    AND co.company_id = ap.company_id
    AND EXISTS
    (SELECT 'x'
    FROM ap_profile ac2, company co2
    WHERE ac2.appl_id = 3
    AND co2.company_id = ac2.company_id
    AND co2.currentcy_interval > 0 AND co2.currentcy_interval is not null)

    I get the following Error:

    SQL0010N The string constant beginning with "x' FROM ap_profile
    ac2, company co2 WHERE ac2.appl_id = 3 AND co2.comp" does not
    have an ending string delimiter.

    Explanation:

    The statement contains a string constant, beginning with
    "<string>", that is not terminated properly.

    The statement cannot be processed.

    User Response:

    Examine the statement for missing apostrophes in the indicated
    string constant.

    sqlcode : -10

    sqlstate : 42603

    The sub query when I run separately runs perfect:

    db2 => SELECT 'x' FROM ap_profile ac2, company co2 WHERE ac2.appl_id = 3 AND co2
    .company_id = ac2.company_id AND co2.currentcy_interval > 0 AND co2.currentcy_in
    terval is not null

    1
    -
    x

    1 record(s) selected.

    I have difficulty putting this into query above.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Outer Join problem

    Originally posted by sunitasorathia
    (SELECT 'x'
    FROM ap_profile ac2, company co2
    Try putting "FROM" on the same line with "SELECT" in the file. I think I had a similar problem before; CLP sometimes has difficulties with special characters being at the end of the line...
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2003
    Posts
    7
    Thank you very much, I indeed tried that but it gives me the same error.
    Really appreciate your valuable time.

  4. #4
    Join Date
    Dec 2002
    Posts
    134

    Re: Outer Join problem

    Hi,

    It seems strange to me that you have following :

    RIGHT OUTER JOIN currentcy_review ac
    RIGHT OUTER JOIN
    ON ac.applID = 3
    ON ac1.cy_section_link_id = crs.cy_section_link_id

    It should be something like:
    RIGHT OUTER JOIN currentcy_review ac
    ON ac.applID = 3
    RIGHT OUTER JOIN some_table_name ac1
    ON ac1.cy_section_link_id = crs.cy_section_link_id

    regards,
    dmitri

Posting Permissions

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