Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How do I use the TRIM function in my Query?

928521May 17 2012 — edited May 17 2012
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 14 2012
Added on May 17 2012
6 comments
356 views