Left outer join with case

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Sascha.Moellering@gmail.com
    #1

    Left outer join with case

    Hi,

    I receive the error code SQL0338N if I try to compile this statement
    (part of the statement):

    .... left outer join lateral
    (SELECT * FROM LIZSYSABA.VWZL0 359TBS WHERE tbs_name = CASE WHEN
    MC.type_txt = 'ZAB' THEN 'BII' ELSE 'STD' END) AS TB1
    on CASE WHEN MC.fixed_date_d at IS NULL THEN cast('01.01.200 7' as date)
    + MC.rel_shift_NR DAY ELSE MC.fixed_date_d at END
    = TB1.TBS_START ...

    Any hints?

    Thanks in advance,
    Sascha

    Tags: None
    • Lennart
      #2
      Re: Left outer join with case

      Sascha.Moelleri ng@gmail.com wrote:
      Hi,
      >
      I receive the error code SQL0338N if I try to compile this statement
      (part of the statement):
      >
      .... left outer join lateral
      (SELECT * FROM LIZSYSABA.VWZL0 359TBS WHERE tbs_name = CASE WHEN
      MC.type_txt = 'ZAB' THEN 'BII' ELSE 'STD' END) AS TB1
      on CASE WHEN MC.fixed_date_d at IS NULL THEN cast('01.01.200 7' as date)
      + MC.rel_shift_NR DAY ELSE MC.fixed_date_d at END
      = TB1.TBS_START ...
      >
      Any hints?
      >
      Thanks in advance,
      Sascha
      >
      [lelle@53dbd181 lelle]$ db2 "? SQL0338N"


      SQL0338N An ON clause associated with a JOIN operator or in a
      MERGE statement is not valid.

      Explanation:

      An ON clause associated with a JOIN operator or in a MERGE
      statement is not valid for one of the following reasons.

      o The ON clause cannot include any subqueries.

      o Column references in an ON clause must only reference columns
      of tables that are in the scope of the ON clause.

      o Scalar fullselects are not allowed in the expressions of an
      ON clause.

      o A function referenced in an ON clause of a full outer join
      must be deterministic and have no external action.

      o A dereference operation (->) cannot be used.

      o A SQL function or SQL method cannot be used.

      o The ON clause cannot include an XMLQUERY or XMLEXISTS
      expression.



      The statement cannot be processed.

      User Response:

      Correct the ON clause to reference appropriate columns or delete
      any subqueries or scalar fullselects. Remove any dereference
      operations, SQL functions, or SQL methods from the ON clause.

      If using full outer join ensure that all functions in the ON
      clause are deterministic and have no external action.

      sqlcode : -338

      sqlstate : 42972

        Comment

        • Sascha.Moellering@gmail.com
          #3
          Re: Left outer join with case

          Hi,

          thank you. Well, but I don't know how to correct the problem.

          Any ideas?

          Thank you in advance,
          Sascha


          On 27 Jun., 20:12, Lennart <erik.lennart.j ons...@gmail.co mwrote:
          Sascha.Moeller. ..@gmail.com wrote:
          Hi,
          >
          I receive the error code SQL0338N if I try to compile this statement
          (part of the statement):
          >
          .... left outer join lateral
          (SELECT * FROM LIZSYSABA.VWZL0 359TBS WHERE tbs_name = CASE WHEN
          MC.type_txt = 'ZAB' THEN 'BII' ELSE 'STD' END) AS TB1
          on CASE WHEN MC.fixed_date_d at IS NULL THEN cast('01.01.200 7' as date)
          + MC.rel_shift_NR DAY ELSE MC.fixed_date_d at END
          = TB1.TBS_START ...
          >
          Any hints?
          >
          Thanks in advance,
          Sascha
          >
          [lelle@53dbd181 lelle]$ db2 "? SQL0338N"
          >
          SQL0338N An ON clause associated with a JOIN operator or in a
          MERGE statement is not valid.
          >
          Explanation:
          >
          An ON clause associated with a JOIN operator or in a MERGE
          statement is not valid for one of the following reasons.
          >
          o The ON clause cannot include any subqueries.
          >
          o Column references in an ON clause must only reference columns
          of tables that are in the scope of the ON clause.
          >
          o Scalar fullselects are not allowed in the expressions of an
          ON clause.
          >
          o A function referenced in an ON clause of a full outer join
          must be deterministic and have no external action.
          >
          o A dereference operation (->) cannot be used.
          >
          o A SQL function or SQL method cannot be used.
          >
          o The ON clause cannot include an XMLQUERY or XMLEXISTS
          expression.
          >
          The statement cannot be processed.
          >
          User Response:
          >
          Correct the ON clause to reference appropriate columns or delete
          any subqueries or scalar fullselects. Remove any dereference
          operations, SQL functions, or SQL methods from the ON clause.
          >
          If using full outer join ensure that all functions in the ON
          clause are deterministic and have no external action.
          >
          sqlcode : -338
          >
          sqlstate : 42972

            Comment

            • Lennart
              #4
              Re: Left outer join with case

              Sascha.Moelleri ng@gmail.com wrote:
              Hi,
              >
              thank you. Well, but I don't know how to correct the problem.
              >
              Any ideas?
              >
              What is MC and how is it involved in the join?

              /Lennart


              Thank you in advance,
              Sascha
              >
              >
              On 27 Jun., 20:12, Lennart <erik.lennart.j ons...@gmail.co mwrote:
              >Sascha.Moeller ...@gmail.com wrote:
              Hi,
              >>
              I receive the error code SQL0338N if I try to compile this statement
              (part of the statement):
              >>
              .... left outer join lateral
              (SELECT * FROM LIZSYSABA.VWZL0 359TBS WHERE tbs_name = CASE WHEN
              MC.type_txt = 'ZAB' THEN 'BII' ELSE 'STD' END) AS TB1
              on CASE WHEN MC.fixed_date_d at IS NULL THEN cast('01.01.200 7' as date)
              + MC.rel_shift_NR DAY ELSE MC.fixed_date_d at END
              = TB1.TBS_START ...
              >>
              Any hints?
              >>
              Thanks in advance,
              Sascha
              >>
              >[lelle@53dbd181 lelle]$ db2 "? SQL0338N"
              >>
              >SQL0338N An ON clause associated with a JOIN operator or in a
              > MERGE statement is not valid.
              >>
              >Explanation:
              >>
              >An ON clause associated with a JOIN operator or in a MERGE
              >statement is not valid for one of the following reasons.
              >>
              >o The ON clause cannot include any subqueries.
              >>
              >o Column references in an ON clause must only reference columns
              > of tables that are in the scope of the ON clause.
              >>
              >o Scalar fullselects are not allowed in the expressions of an
              > ON clause.
              >>
              >o A function referenced in an ON clause of a full outer join
              > must be deterministic and have no external action.
              >>
              >o A dereference operation (->) cannot be used.
              >>
              >o A SQL function or SQL method cannot be used.
              >>
              >o The ON clause cannot include an XMLQUERY or XMLEXISTS
              > expression.
              >>
              > The statement cannot be processed.
              >>
              >User Response:
              >>
              >Correct the ON clause to reference appropriate columns or delete
              >any subqueries or scalar fullselects. Remove any dereference
              >operations, SQL functions, or SQL methods from the ON clause.
              >>
              > If using full outer join ensure that all functions in the ON
              >clause are deterministic and have no external action.
              >>
              > sqlcode : -338
              >>
              > sqlstate : 42972
              >
              >

                Comment

                • Serge Rielau
                  #5
                  Re: Left outer join with case

                  Sascha.Moelleri ng@gmail.com wrote:
                  Hi,
                  >
                  thank you. Well, but I don't know how to correct the problem.
                  Eliminate that SELECT * subquery. What is the use of it?
                  Why not LOJ on the base table?
                  BTW, which version platform of DB2 is this?

                  Cheers
                  Serge

                  --
                  Serge Rielau
                  DB2 Solutions Development
                  IBM Toronto Lab

                    Comment

                    • Tonkuma
                      #6
                      Re: Left outer join with case

                      On Jun 28, 2:33 am, "Sascha.Moeller ...@gmail.com"
                      <Sascha.Moeller ...@gmail.comwr ote:
                      ... left outer join lateral
                      (SELECT * FROM LIZSYSABA.VWZL0 359TBS WHERE tbs_name = CASE WHEN
                      MC.type_txt = 'ZAB' THEN 'BII' ELSE 'STD' END) AS TB1
                      on CASE WHEN MC.fixed_date_d at IS NULL THEN cast('01.01.200 7' as date)
                      + MC.rel_shift_NR DAY ELSE MC.fixed_date_d at END
                      = TB1.TBS_START ...
                      I think ON clause can be make simple by using COALESCE.
                      ON COALESCE(MC.fix ed_date_dat, cast('01.01.200 7' as date) +
                      MC.rel_shift_NR DAY)
                      = TB1.TBS_START ...

                        Comment

                        Previous template Next
                        Working...