6 Replies Latest reply: May 17, 2012 10:36 AM by Solomon Yakobson RSS

    How do I use the TRIM function in my Query?

    928521
      I have a field that has called AP.service_user_defined_1 that houses a Code the code is like Alpha Num ie "AA01" then it has about 30 to 40 whites spaces and then the description of what the code means. I need to have the description to show in my report I don't necessarily need the code but I do need the description I tried putting the TRIM Function in as below and it will not work what do I need to do to correct this?? I need help ASAP this is due today! Thanks for any help!

      SELECT DISTINCT
                A1.AUTH_NUMBER,
      PM.LAST_NAME,
                A1.AUTH_TYPE,
                A1.PLACE_OF_SERVICE,
                A1.OVERALL_STATUS,
                A1.CLOSED_REASON,
                A1.DENIED_REASON,
      Case
      when A1.DENIED_REASON in ('HS001','HS002') then 'MED NECESS'
      when A1.DENIED_REASON in ('HS004','HS005','HS006','HS007','HS008','HS013','HS014','HS016','HS017','HS019','HS020') then 'Admin Denial'
      when A1.DENIED_REASON is NUll and ap.advisor_decision = 'AAPPR' then 'Approved'
      else 'Unknown'
      end DENIED,
      A1.admit_primary_date,
      A1.service_admit_type AS "IP ADMIT TYPE",
      A1.service_user_defined_1 AS "OP Serv Type",
      Trim (leading ('AA01                 ',)from dual,
      ap.contact_date,
      AP.decision_date,
      AP.ADVISOR_DECISION,
      PM.PROVIDER_ID,
           A1.INSERT_DATETIME,
      TO_CHAR (A1.insert_datetime,'MONTH-YY')AS "Month",
      A1.admit_primary_date,
                A1.ACTIVE_PHYSICIAN_ADVISOR,
                MV.LINE_OF_BUSINESS

      FROM Windsoradm.auth_master a1
      INNER JOIN Windsoradm.auth_phys_advisor ap
      ON a1.auth_number=ap.auth_number
      INNER JOIN windsoradm.prov_master pm
      ON ap.seq_prov_id=pm.seq_prov_id
      LEFT JOIN windsoradm.note_master nm
      ON nm.seq_memb_id=a1.seq_memb_id
      INNER JOIN windsoradm.member_mv mv
      ON mv.seq_memb_id=a1.seq_memb_id

      Where mv.Line_of_Business <>'SFS'
      /*AND A1.PLACE_OF_SERVICE IN ('11','21','22','24')*/
      /*AND a1.active_physician_advisor = 'Y'*/
      /*AND (a1.closed_reason ='A06' OR a1.closed_reason is Null)*/
      AND a1.insert_datetime Between To_Date ('04/01/2012', 'MM/DD/YYYY') and To_Date ('04/30/2012','MM/DD/YYYY')


      ORDER BY 1
        • 1. Re: How do I use the TRIM function in my Query?
          BluShadow
          regexp_replace(column, '^[^ ]+ +')
          • 2. Re: How do I use the TRIM function in my Query?
            928521
            This still didn't work it shows up in the report with the Code just like before Here is an example of how it looks in the application


            OpServType: AA04 Behavioural Health

            When I try to get this in my report the only thing showing is the Code. Any other suggestions? I really just need the description. I notice when I post this the blank space doesn't show between my code and the description but in my application the white space between the code and the description is about 25 to 45 spaces.

            Edited by: 925518 on May 17, 2012 7:52 AM
            • 3. Re: How do I use the TRIM function in my Query?
              John Spencer
              Assuming no spaces before the code, then something like:
              SQL> select trim(substr(str, instr(str, ' '))) descr
                2  from (select 'AA04 Behavioural Health' str from dual);
              
              DESCR
              ------------------
              Behavioural Health
              If there could be spaces before the code, then just trim the value in the substr function.

              John
              • 4. Re: How do I use the TRIM function in my Query?
                Solomon Yakobson
                regexp_replace(OpServType,'^[^ ]* +')
                For example:
                SQL> select regexp_replace('AA04 Behavioural Health','^[^ ]* +') from dual
                  2  /
                
                REGEXP_REPLACE('AA
                ------------------
                Behavioural Health
                
                SQL> 
                SY.
                • 5. Re: How do I use the TRIM function in my Query?
                  928521
                  I don't know how to use this within my query I put it in as you suggest and get an error: I'm new to these functions can you show me how within my query I should code it? The field Name is A1.service_user_defined_1 There are about 50 Different Codes in a Drop down list within the application that have a different Code and Different Description it appears that the Codes are all 4 character alpha numeric then about 25 to 45 spaces and then the description. Please show me in my code how to get this I wish there was a table I could link to that housed both code and description but who ever created this did not make life easy. Thanks (I also tried the TRIM but could not get it to work ) I know it is because I do not know how to put it within my code to make it work.


                  SELECT DISTINCT
                            A1.AUTH_NUMBER,
                  PM.LAST_NAME,
                            A1.AUTH_TYPE,
                            A1.PLACE_OF_SERVICE,
                            A1.OVERALL_STATUS,
                            A1.CLOSED_REASON,
                            A1.DENIED_REASON,
                  Case
                  when A1.DENIED_REASON in ('HS001','HS002') then 'MED NECESS'
                  when A1.DENIED_REASON in ('HS004','HS005','HS006','HS007','HS008','HS013','HS014','HS016','HS017','HS019','HS020') then 'Admin Denial'
                  when A1.DENIED_REASON is NUll and ap.advisor_decision = 'AAPPR' then 'Approved'
                  else 'Unknown'
                  end DENIED,
                  A1.admit_primary_date,
                  A1.service_admit_type AS "IP ADMIT TYPE",
                  A1.service_user_defined_1 AS "OP Serv Type",
                  regexp_replace('AA01 Behavioral/Mental Health Service', '^[^ ]+ +')from dual,
                  ap.contact_date,
                       AP.decision_date,
                       AP.ADVISOR_DECISION,
                            PM.PROVIDER_ID,
                       A1.INSERT_DATETIME,
                  TO_CHAR (A1.insert_datetime,'MONTH-YY')AS "Month",
                  A1.admit_primary_date,
                            A1.ACTIVE_PHYSICIAN_ADVISOR,
                            MV.LINE_OF_BUSINESS

                  FROM Windsoradm.auth_master a1
                  INNER JOIN Windsoradm.auth_phys_advisor ap
                  ON a1.auth_number=ap.auth_number
                  INNER JOIN windsoradm.prov_master pm
                  ON ap.seq_prov_id=pm.seq_prov_id
                  LEFT JOIN windsoradm.note_master nm
                  ON nm.seq_memb_id=a1.seq_memb_id
                  INNER JOIN windsoradm.member_mv mv
                  ON mv.seq_memb_id=a1.seq_memb_id

                  Where mv.Line_of_Business <>'SFS'
                  /*AND A1.PLACE_OF_SERVICE IN ('11','21','22','24')*/
                  /*AND a1.active_physician_advisor = 'Y'*/
                  /*AND (a1.closed_reason ='A06' OR a1.closed_reason is Null)*/
                  AND a1.insert_datetime Between To_Date ('04/01/2012', 'MM/DD/YYYY') and To_Date ('04/30/2012','MM/DD/YYYY')


                  ORDER BY 1
                  • 6. Re: How do I use the TRIM function in my Query?
                    Solomon Yakobson
                    925518 wrote:
                    I don't know how to use this within my query . The field Name is A1.service_user_defined_1
                    SELECT DISTINCT
                    A1.AUTH_NUMBER,
                    PM.LAST_NAME,
                    A1.AUTH_TYPE,
                    A1.PLACE_OF_SERVICE,
                    A1.OVERALL_STATUS,
                    A1.CLOSED_REASON,
                    A1.DENIED_REASON,
                    Case
                    when A1.DENIED_REASON in ('HS001','HS002') then 'MED NECESS'
                    when A1.DENIED_REASON in ('HS004','HS005','HS006','HS007','HS008','HS013','HS014','HS016','HS017','HS019','HS020') then 'Admin Denial'
                    when A1.DENIED_REASON is NUll and ap.advisor_decision = 'AAPPR' then 'Approved'
                    else 'Unknown'
                    end DENIED,
                    A1.admit_primary_date,
                    A1.service_admit_type AS "IP ADMIT TYPE",
                    --A1.service_user_defined_1 AS "OP Serv Type",
                    regexp_replace(A1.service_user_defined_1, '^[^ ]+ +')  "OP Serv Type",
                    ap.contact_date,
                    AP.decision_date,
                    AP.ADVISOR_DECISION,
                    PM.PROVIDER_ID,
                    A1.INSERT_DATETIME,
                    TO_CHAR (A1.insert_datetime,'MONTH-YY')AS "Month", 
                    A1.admit_primary_date,
                    A1.ACTIVE_PHYSICIAN_ADVISOR,
                    MV.LINE_OF_BUSINESS
                    
                    FROM Windsoradm.auth_master a1
                    INNER JOIN Windsoradm.auth_phys_advisor ap
                    ON a1.auth_number=ap.auth_number
                    INNER JOIN windsoradm.prov_master pm
                    ON ap.seq_prov_id=pm.seq_prov_id
                    LEFT JOIN windsoradm.note_master nm
                    ON nm.seq_memb_id=a1.seq_memb_id
                    INNER JOIN windsoradm.member_mv mv
                    ON mv.seq_memb_id=a1.seq_memb_id
                    
                    Where mv.Line_of_Business 'SFS'
                    /*AND A1.PLACE_OF_SERVICE IN ('11','21','22','24')*/
                    /*AND a1.active_physician_advisor = 'Y'*/
                    /*AND (a1.closed_reason ='A06' OR a1.closed_reason is Null)*/
                    AND a1.insert_datetime Between To_Date ('04/01/2012', 'MM/DD/YYYY') and To_Date ('04/30/2012','MM/DD/YYYY')
                    ORDER BY 1
                    SY.