OraFAQ Forum: SQL & PL/SQL » compare fields ignoring spaces & dashes & leading zero's

Home » SQL & PL/SQL » SQL & PL/SQL » compare fields ignoring spaces & dashes & leading zero's (Oracle SQL developer)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

compare fields ignoring spaces & dashes & leading zero's [message #648852]

Fri, 04 March 2016 15:01

slackermn
Messages: 3
Registered: March 2016
Location: MN

Junior Member

Trying to compare main database data to the form source data. but ignoring dashes & leading zeros & spaces. Need main data to match, to find unmatched data.

Sorry can't figure out the code pasting correctly, so I upload pdf

Need these vales to equal
Main Form
519200110 = 05-192-00110
DE-DKM 3401385 = DEDKM3401385
BE-LI-ULB1219 = BELIULB1219
DEULM19861 = DE ULM 19861

 
SELECT f2.key_seq_num                       AS seq_num, 
       'DQC47'                              AS check_id, 
       inf.unique_patient_id                AS CRID, 
       inf.infus_dte                        AS HST, 
       'CBU/DID mismatch t_infus and FORM ' AS summary, 
       dnr.non_nmdp_cbuid, 
       f2.donor_cbu_non_nmdp_cbu_id         AS FORM_NON_NMDP_CBU_ID, 
       f2.frm_track_id                      AS FORM_FRM_TRACK_ID, 
       f2.frm_rev                           AS FORM_FRM_REV 
FROM   iidb_stg.fn_infus_dnr dnr 
       JOIN iidb_stg.fn_infus inf 
         ON inf.infus_id = dnr.infus_id 
       JOIN iidb_stg.fn_frm_track ft 
         ON ft.nmdp_id = inf.unique_patient_id 
            AND ft.event_dte = inf.infus_dte 
       LEFT JOIN im_forms.frm_2005_uv f2 
              ON f2.key_cibmtr_rcip_id = inf.unique_patient_id 
                 AND f2.key_hsct_dte = inf.infus_dte 
                 AND f2.frm_track_id = ft.frm_track_id 
WHERE  Upper (f2.donor_cbu_non_nmdp_cbu_id) <> Upper (dnr.non_nmdp_cbuid)

--moderator edit: corrected tags

Attachment: code.pdf
(Size: 16.68KB, Downloaded 1315 times)

[Updated on: Fri, 04 March 2016 15:06] by Moderator

Report message to a moderator

Re: compare fields ignoring spaces & dashes & leading zero's [message #648853 is a reply to message #648852]

Fri, 04 March 2016 15:06

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I've corrected your use of tags: you need to close a code block with [/code], not jut another [code]
(I can't help with your question - not my area of expertise)

Report message to a moderator

Re: compare fields ignoring spaces & dashes & leading zero's [message #648854 is a reply to message #648853]

Fri, 04 March 2016 15:08

slackermn
Messages: 3
Registered: March 2016
Location: MN

Junior Member

Thanks John, hopefully my issue can be solved that easily. Cool

Report message to a moderator

Re: compare fields ignoring spaces & dashes & leading zero's [message #648855 is a reply to message #648854]

Fri, 04 March 2016 15:21

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

WHERE  UPPER(LTRIM(REPLACE(f2.donor_cbu_non_nmdp_cbu_id,' -'),'0')) <> UPPER(LTRIM(REPLACE(dnr.non_nmdp_cbuid,' -'),'0'))

SY.

Report message to a moderator

Re: compare fields ignoring spaces & dashes & leading zero's [message #649066 is a reply to message #648855]

Fri, 11 March 2016 08:49

slackermn
Messages: 3
Registered: March 2016
Location: MN

Junior Member

perfect, thanks. Just had to do some other minor editing, but this was piece missing.

Report message to a moderator

Previous Topic:	cursor logic
Next Topic:	package -error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 06:14:13 CDT 2024