ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Confusion with brackets

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Confusion with brackets

    UPDATE AFILE A SET OPTCOD =
    IFNULL((SELECT MIN(B.NOPTCOD) FROM BFILE B
    WHERE A.OPTCOD = B.IOPTCOD),' ')

    to the above code I have to include two more filter conditions

    AND WHERE A.CLIENT = B.ICLIENT),' ')
    AND WHERE A.FUTCOD = B.IFUTCOD),' ')

    But some how a lot of confusion on brackets & Im unable to get correct sql.
    can some one help me ?

  • #2
    Re: Confusion with brackets

    I think you want


    UPDATE AFILE A SET OPTCOD =
    IFNULL((SELECT MIN(B.NOPTCOD) FROM BFILE B
    WHERE A.OPTCOD = B.IOPTCOD AND WHERE A.CLIENT = B.ICLIENT AND WHERE A.FUTCOD = B.IFUTCOD),' ')

    Comment


    • #3
      Re: Confusion with brackets

      I usually use Coalesce


      UPDATE AFILE A SET OPTCOD =
      COALESCE(SELECT MIN(B.NOPTCOD) FROM BFILE B
      WHERE A.OPTCOD = B.IOPTCOD AND WHERE A.CLIENT = B.ICLIENT AND WHERE A.FUTCOD = B.IFUTCOD,' ')

      Coalesce checks each element in the list until it finds a non-null entry.

      Comment


      • #4
        Re: Confusion with brackets

        UPDATE AFILE A SET OPTCOD =
        IFNULL((SELECT MIN(B.NOPTCOD) FROM BFILE B
        WHERE A.OPTCOD = B.IOPTCOD AND WHERE A.CLIENT = B.ICLIENT AND WHERE A.FUTCOD = B.IFUTCOD),' ')


        I think I ve to remove where &make it some thing like

        UPDATE AFILE A SET OPTCOD =
        IFNULL((SELECT MIN(B.NOPTCOD) FROM BFILE B
        WHERE A.OPTCOD = B.IOPTCOD AND A.CLIENT = B.ICLIENT AND A.FUTCOD = B.IFUTCOD),' ')


        Its working butr not sure whether im correct?
        but the above suggested is throwing error because of where clause i think.

        Comment


        • #5
          Re: Confusion with brackets

          Originally posted by arrow483 View Post
          I usually use Coalesce


          UPDATE AFILE A SET OPTCOD =
          COALESCE(SELECT MIN(B.NOPTCOD) FROM BFILE B
          WHERE A.OPTCOD = B.IOPTCOD AND WHERE A.CLIENT = B.ICLIENT AND WHERE A.FUTCOD = B.IFUTCOD,' ')

          Coalesce checks each element in the list until it finds a non-null entry.


          Hey Iam getting this error
          Token MIN was not valid. Valid tokens: ) ,.

          Comment


          • #6
            Re: Confusion with brackets

            You should only have one where clause.

            PHP Code:
            UPDATE AFILE A SET OPTCOD 
            COALESCE(SELECT MIN(B.NOPTCODFROM BFILE B 
            WHERE A
            .OPTCOD B.IOPTCOD AND A.CLIENT B.ICLIENT AND A.FUTCOD B.IFUTCOD,' '
            Predictions are usually difficult, especially about the future. ~Yogi Berra

            Vertical Software Systems
            VSS.biz

            Comment

            Working...
            X