1 Reply Latest reply: Apr 5, 2013 11:52 AM by HamidHelal RSS

    nvl query help

    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 help
          HamidHelal
          998346 wrote:
          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
          welcome to OTN
          Post in wrong forum. Post at {forum:id=75}. Before posting there close this thread marking as answered.

          If someone's response is helpful or correct, please mark it accordingly.*