6 Replies Latest reply: Apr 5, 2013 12:39 PM by Frank Kulash RSS

    nvl query question

    1001349
      Hello,
      I have a request for a query to be written that checks for null values in the CHANGE_CODE, makes then 'none' if they are null, then takes the 'none' value and makes them 'open-well.' Does this need to be written in 2 separate calls to the database or can it be written as one? I wrote the query below, the 'none' value is being picked up, but its not changing the none to 'open-well.' i

      StringBuilder sbSQL = new StringBuilder();
      sbSQL.Append("SELECT distinct RO.OBJECT_ID, RE.CATEGORY_ID, RR.NAME, replace( nvl(A.CHANGE_CODE,'(none)'), '(none)','open_well') REV_CODE, 'Autobalance' REMARK");
      sbSQL.Append(" FROM PLX_SCRIPTED_LOAD_OBJECT_SET OBJ");
      sbSQL.Append(" INNER JOIN PLX_CASE_ATTRIBUTE A ON OBJ.OBJECT_ID = A.OBJECT_ID");
      sbSQL.Append(" INNER JOIN PLX_RECON_RESERVOIR RR ON A.UNIQUE_ID = RR.RESERVOIR_NAME");
      sbSQL.Append(" INNER JOIN PLX_RECON_OBJECT RO ON RR.RESERVOIR_ID = RO.RESERVOIR_ID");
      sbSQL.Append(" INNER JOIN PLX_RECON_RESERVOIR_ENTRY RE ON RO.RESERVOIR_DATA_OBJECT_ID = RE.RESERVOIR_DATA_OBJECT_ID");
      sbSQL.Append(" INNER JOIN PLX_RECON_RESERVE_CATEGORY RRC ON RE.RESERVE_CATEGORY_ID = RRC.CATEGORY_ID AND A.RESERVE_CATEGORY = RRC.CATEGORY_NAME");

      If I run this query below in sql developer i get the results i want:
      SELECT replace( nvl(a.CHANGE_CODE,'(none)'), '(none)',open_well'), reservoir_entity, object_id, rr.RESERVOIR_NAME FROM xxx_plx_case_attribute A, PLX_RECON_RESERVOIR RR
      where A.UNIQUE_ID = RR.RESERVOIR_NAME


      any suggestions?
      thank you
        • 1. Re: nvl query question
          Ramin Hashimzadeh
          i dont see any logic in this query
          SELECT replace( nvl(a.CHANGE_CODE,'(none)'), '(none)',open_well'), reservoir_entity, object_id, >rr.RESERVOIR_NAME
          FROM xxx_plx_case_attribute A, PLX_RECON_RESERVOIR RR
          where A.UNIQUE_ID = RR.RESERVOIR_NAME
          why you not use this?
          SELECT nvl(a.CHANGE_CODE,'open_well'), reservoir_entity, object_id, rr.RESERVOIR_NAME
          FROM xxx_plx_case_attribute A, PLX_RECON_RESERVOIR RR
          where A.UNIQUE_ID = RR.RESERVOIR_NAME
          • 2. Re: nvl query question
            1001349
            Hi Ramin,
            Thanks for your solution. There are records that are none as well, i was trying to change those records as well. Pretty much taking all null values and making them none then all none values and making them open_well.
            I am writing this as part of a data loader.

            Edited by: 998346 on Apr 5, 2013 10:24 AM
            • 3. Re: nvl query question
              sb92075
              UPDATE XXX_PLX_CASE_ATTRIBUE SET CHANGE_CODE='OPEN_WELL'
              WHERE CHANGE_CODE IS NULL OR CHANGE_CODE = 'NONE';
              • 4. Re: nvl query question
                1001349
                my only question now is can i put this in my string:
                Hello,
                I have a request for a query to be written that checks for null values in the CHANGE_CODE, makes then 'none' if they are null, then takes the 'none' value and makes them 'open-well.' Does this need to be written in 2 separate calls to the database or can it be written as one? I wrote the query below, the 'none' value is being picked up, but its not changing the none to 'open-well.' i

                ("SELECT distinct RO.OBJECT_ID, RE.CATEGORY_ID, RR.NAME, replace( nvl(A.CHANGE_CODE,'(none)'), '(none)','open_well') REV_CODE, 'Autobalance' REMARK");


                any suggestions?
                thank you
                • 5. Re: nvl query question
                  Ramin Hashimzadeh
                  select decode(nvl(a.CHANGE_CODE,'(none)'),'(none)','open_well',a.CHANGE_CODE)....
                  Edited by: Ramin Hashimzadeh on Apr 5, 2013 10:36 PM
                  • 6. Re: nvl query question
                    Frank Kulash
                    Hi,

                    Or, if you want to use NVL:
                    UPDATE  xxx_plx_case_attribute 
                    SET    change_code  ='open_well'
                    WHERE  NVL (   change_code
                                   ,   '(none)'
                            ) = '(none)'
                    ; 
                    There's no advantage (or disadvantage) to using NVL rather than OR.