2

How can I properly structure the below query to make it work? I would like to have the Ref_CD = MBR_ID_TYPE_ID to be my select statement on the beginning of it.

select MBR_ID_TYP_ID || '-' || (
    select ref_desc 
    from ref 
    where ref_nm='memberIDType' 
        and REF_CD = MBR_ID_TYP_ID) as "Member Type", id.* 
from mbr_id id 
where mbr_id = 0989879;
  • 2
    You should add sample data, and desired output to your question via the edit link. Also, please take the tour and check the help center for more details about how StackExchange works. – Max Vernon Apr 19 '18 at 19:59
2

I'm not too sure what you are doing there and that's not at all how I write queries. Either way, I would try it this way:

select 
    a.MBR_ID_TYP_ID || '-' || b.ref_desc as "Member Type" , 
    a.*
from mbr_id as a
    left outer join ref as b 
    on  b.ref_nm = 'memberIDType' 
    and a.REF_CD = b.MBR_ID_TYP_ID    -- or: b.REF_CD = a.MBR_ID_TYP_ID 
                                      -- not clear from the code
where 
    a.mbr_id = 6484532;

I don't join in where clauses because that is really hard to read. I generally put the columns in the select portion, then do the joins in the from section. The filtering goes on in the where clause. Finally if necessary the order by is last, if I need to sort.

|improve this answer|||||
  • Thnx. I fixed it so it's equivalent to OP's query – ypercubeᵀᴹ Apr 19 '18 at 20:11
  • I wouldn't put b.ref_nm='memberIDType' into join condition. Needs execution plan! – Dina Apr 19 '18 at 20:13
  • 2
    It needs to be there, if we want the query to be equivalent. If you move it to WHERE, you are converting the LEFT join to INNER join. – ypercubeᵀᴹ Apr 19 '18 at 20:14

Not the answer you're looking for? Browse other questions tagged or ask your own question.