Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2009
    Posts
    18

    Unanswered: How to use XMLQUERY function in ON clause for Join statement

    I get the following problem when i tried to create the view using the following statement

    CREATE OR REPLACE VIEW TAFJV_FBNK_CUSTOMER_POSITION as
    SELECT a.RECID, a.XMLRECORD "THE_RECORD"
    ,a.RECID "ID"
    ,XMLCAST(XMLQUERY('$d/row/c1[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "DEAL_CCY"
    ,XMLCAST(XMLQUERY('$d/row/c2[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "DEAL_AMOUNT"
    FROM
    "FBNK_CUSTOMER_POSITION" a
    LEFT OUTER JOIN "F_CATEGORY" b
    ON b.RECID = XMLCAST(XMLQUERY('$d/row/c15[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000))

    sqlcode: -338

    sqlstate: 42972

    The sqlcode says that An ON clause associated with a JOIN operator or in a MERGE statement is not valid .On futher analysis, i could find that XMLQuery function has the restriction that cannot be specified as a part of ON clause that is associated with a JOIN operator.

    Is there any other way to perform outer join on XMLQuery function?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try XMLTABLE() instead.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2009
    Posts
    18

    How to use XMLQUERY function in ON clause for Join statement

    I tried to use the XMLTABLE in the on clause as follows

    CREATE OR REPLACE VIEW TAFJV_FBNK_CUSTOMER_POSITION as
    SELECT a.RECID, a.XMLRECORD "THE_RECORD"
    ,a.RECID "ID"
    ,XMLCAST(XMLQUERY('$d/row/c1[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "DEAL_CCY"
    ,XMLCAST(XMLQUERY('$d/row/c2[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "DEAL_AMOUNT"
    FROM
    "FBNK_CUSTOMER_POSITION" a
    LEFT OUTER JOIN "F_CATEGORY" b
    ON b.RECID = XMLTABLE('$d/row' passing a.XMLRECORD as "d"
    COLUMNS
    TEST VARCHAR(4000) PATH 'c2')


    I get the SQL -104 error code that the statement is incorrect. Is it possible use select statement in a ON Clause?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That's not what I meant. You use XMLTABLE() to represent your XML data as a, well, table, then join that table with whatever you need on whatever column makes sense.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Sep 2009
    Posts
    18

    How to use XMLQUERY function in ON clause for Join statement

    thanks for your reply.

    Is it possible to do all these things in a single query. Can you please re write the above query?

Posting Permissions

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