sql - CASE Statement inside a subquery -

i able create following query after post below

select * duppri t exists (   select 1   duppri   symbolup = t.symbolup   , date = t.date   , price <> t.price)   order date 

sql check when pairs don't match

i have realized need add case statement indicate when above criteria fits, type value equal between duppri , t.duppri. occurs because of case sensitivity. query attempt clean portfolio accounting system unfortunately allowed numerous duplicates because didn't have strong referential integrity or constraints.

i case statement produce column 'ismatch'

date      |type|symbol   |symbolup |concatt      |price |ismatch 6/30/1995 |gaus|313586u72|313586u72|gaus313586u72|109.25|different 6/30/1995 |gbus|313586u72|313586u72|gbus313586u72|108.94|different 6/30/1995 |agus|srr      |srr      |agussrr      |10.25 |different 6/30/1995 |lcus|srr      |srr      |lcussrr      |0.45  |different 11/27/1996|lcus|lly      |lly      |lcuslly      |76.37 |matched 11/27/1996|lcus|lly      |lly      |lcuslly      |76    |matched 11/28/1996|lcus|lly      |lly      |lcuslly      |76.37 |matched 11/28/1996|lcus|lly      |lly      |lcuslly      |76    |matched 

i tried following case statement creating errors

select * duppri t exists (   select 1,   case ismatch when [type] = [t.type] 'matched' else 'different' end   duppri   symbolup = t.symbolup   , date = t.date   , price <> t.price)     order date 

you use window functions, if understand correctly:

select d.*,        (case when mint = maxt              'matched' else 'different'         end) (select d.*,               min(type) on (partition symbolup, date) mint,              max(type) on (partition symbolup, date) maxt,              min(price) on (partition symbolup, date) minp,              max(price) on (partition symbolup, date) maxp       duppri d      ) d minp <> maxp order date;