oracle case套case,，套用when case

求助，套用when case

原sql

SQL codeselect pgdwgms.isse_no, pgdwgms.dwg_no

from pgdwgms, pgissetr, pgappref

where (pgissetr.isse_no like '%06%' and pgappref.upd_who = '1001' and

pgappref.table_no = '1' and pgappref.record_type = '4' and

pgappref.seq_no = pgissetr.seq_no and

pgissetr.isse_no = pgdwgms.isse_no)

or (pgdwgms.dwg_no like '%SAA%' and pgappref.upd_who = '1001' and

pgappref.table_no = '1' and pgappref.record_type = '4' and

pgappref.seq_no = pgissetr.seq_no and

pgissetr.isse_no = pgdwgms.isse_no)

or (pgdwgms.dwg_name_j like '%支架%' and pgappref.upd_who = '1001' and

pgappref.table_no = '1' and pgappref.record_type = '4' and

pgappref.seq_no = pgissetr.seq_no and

pgissetr.isse_no = pgdwgms.isse_no)

or (pgdwgms.dwg_name_e like '%支架%' and pgappref.upd_who = '1001' and

pgappref.table_no = '1' and pgappref.record_type = '4' and

pgappref.seq_no = pgissetr.seq_no and

pgissetr.isse_no = pgdwgms.isse_no)

or (pgissetr.title_j like '%ML-GS%' and

pgappref.upd_who = '1001' and pgappref.table_no = '1' and

pgappref.record_type = '4' and pgappref.seq_no = pgissetr.seq_no and

pgissetr.isse_no = pgdwgms.isse_no)

or (pgissetr.title_j like '%ML-GS%' and

pgappref.upd_who = '1001' and pgappref.table_no = '1' and

pgappref.record_type = '4' and pgappref.seq_no = pgissetr.seq_no and

pgissetr.isse_no = pgdwgms.isse_no)

想达到类似于这句sql效果

SQL codeselect * from(

select Rt_Basic_Data.*,

(case when Parent_dwg_no like '%SA%' then 1 else 0 end) title_weight,

(case when parent_rev_no like '%1%' then 1 else 0 end) dept_weight,

(case when dwg_no like '%G%' then 1 else 0 end) dwg_weight

from Rt_Basic_Data

) where (title_weight+dept_weight)>0

order by (title_weight+dept_weight) desc

我改成如下格式，执行无效

SQL codeselect * from(

select pgdwgms.isse_no, pgdwgms.dwg_no,

(case when (pgissetr.isse_no like '%06%' and pgappref.upd_who = '1001' and

pgappref.table_no = '1' and pgappref.record_type = '4' and

pgappref.seq_no = pgissetr.seq_no and

pgissetr.isse_no = pgdwgms.isse_no) then 1 else 0 end) search_isse_no,

(case when (pgdwgms.dwg_no like '%SAA%' and pgappref.upd_who = '1001' and

pgappref.table_no = '1' and pgappref.record_type = '4' and

pgappref.seq_no = pgissetr.seq_no and

pgissetr.isse_no = pgdwgms.isse_no) then 1 else 0 end) search_dwg_no,

(case when (pgdwgms.dwg_name_j like '%支架%' and pgappref.upd_who = '1001' and

pgappref.table_no = '1' and pgappref.record_type = '4' and

pgappref.seq_no = pgissetr.seq_no and

pgissetr.isse_no = pgdwgms.isse_no) then 1 else 0 end) search_dwg_name_j,

(case when (pgdwgms.dwg_name_e like '%支架%' and pgappref.upd_who = '1001' and

pgappref.table_no = '1' and pgappref.record_type = '4' and

pgappref.seq_no = pgissetr.seq_no and

pgissetr.isse_no = pgdwgms.isse_no) then 1 else 0 end) search_dwg_name_e,

(case when (pgissetr.title_j like '%ML-GS%' and

pgappref.upd_who = '1001' and pgappref.table_no = '1' and

pgappref.record_type = '4' and pgappref.seq_no = pgissetr.seq_no and

pgissetr.isse_no = pgdwgms.isse_no) then 1 else 0 end) search_title_j,

(case when (pgissetr.title_e like '%ML-GS%' and

pgappref.upd_who = '1001' and pgappref.table_no = '1' and

pgappref.record_type = '4' and pgappref.seq_no = pgissetr.seq_no and

pgissetr.isse_no = pgdwgms.isse_no) then 1 else 0 end) search_title_e

from pgdwgms, pgissetr, pgappref

)where (search_isse_no+search_dwg_no+search_dwg_name_j+search_dwg_name_e+search_title_j+search_title_e)>0

order by (search_isse_no+search_dwg_no+search_dwg_name_j+search_dwg_name_e+search_title_j+search_title_e) desc