This discussion is archived
6 Replies Latest reply: Apr 27, 2013 10:26 AM by 1003231 RSS

left join

1003231 Newbie
Currently Being Moderated
left join
Posted: Apr 27, 2013 8:08 AM
11g [Edit]
     
     Edit                    Reply
dear all,

in the below query, when i use left join with PTRNPF i am getting 5 recirds only. for the sub query ''t'' am getting lots of records.sub query t is working fine. but left joining with ptrnpf gives 4 or 5 records only. is there any mistake in my query? im using 11g. i need to get all records from the sub query t.. please let me know if more info required.

select t.CO_ID, t.POL_ID,t.AGNCY_ID,t.AGT_ID, npf.subdate
from (select distinct com.m1co as CO_ID,
fs.policy_number as POL_ID,
com.agency_cd as AGNCY_ID,
c.agent_code as AGT_ID,
p.address,
cas.date
FROM comagtm1 com left JOIN channel c ON (com.agentno = c.agent_code),
individual_agreement ia,
benefit cas,
CASCNTRM casc,
casmwagt casm,
person p ,
financial_services fs

WHERE fs.role_player_id = c.role_player_id
AND com.m1co = fs.company_code
and fs.policy_number = DECODE(ia.Business_Key_Contract_Number, NULL, ia.Business_Key_Policy_Number,
ia.Business_Key_Contract_Number)
and cas.fpolno = fs.policy_number
and cas.fbrcd = fs.base_rider_code
and casc.MCCNTR = fs.policy_number
and casc.MCCO = fs.company_code
and p.role_player_id = fs.role_player_id
and fs.policy_number = casm.mwpoln
and com.agency_cd = 'YAP') t
left join .PTRNPF npf on t.POL_ID = npf.chdrnum
where BATCDE = 'T600

Edited by: 1000228 on Apr 27, 2013 12:10 PM
  • 1. Re: left join
    sb92075 Guru
    Currently Being Moderated
    1000228 wrote:
    dear all,

    in the below query, when i use left join with PTRNPF i am getting 5 recirds only. for the sub query ''t'' am getting lots of records.sub query t is working fine. but left joining with ptrnpf gives 4 or 5 records only. is there any mistake in my query? im using 11g. i need to get all records from the sub query t.. please let me know if more info required.

    select t.CO_ID, t.POL_ID,t.AGNCY_ID,t.AGT_ID, npf.subdate
    from (select distinct com.m1co as CO_ID,
    fs.policy_number as POL_ID,
    com.agency_cd as AGNCY_ID,
    c.agent_code as AGT_ID,
    p.address,
    cas.date


    FROM comagtm1 com left JOIN channel c ON (com.agentno = c.agent_code),
    individual_agreement ia,
    benefit cas,
    CASCNTRM casc,
    casmwagt casm,
    person p ,
    financial_services fs

    WHERE fs.role_player_id = c.role_player_id
    AND com.m1co = fs.company_code
    and fs.policy_number = DECODE(ia.Business_Key_Contract_Number, NULL, ia.Business_Key_Policy_Number,
    ia.Business_Key_Contract_Number)
    and cas.fpolno = fs.policy_number
    and cas.fbrcd = fs.base_rider_code
    and casc.MCCNTR = fs.policy_number
    and casc.MCCO = fs.company_code
    and p.role_player_id = fs.role_player_id
    and fs.policy_number = casm.mwpoln

    and com.agency_cd = 'YAP') t
    left join .PTRNPF npf on t.POL_ID = npf.chdrnum

    where BATCDE = 'T600'

    Edited by: 1000228 on Apr 27, 2013 8:09 AM
    is COPY & PASTE broken for you?

    AFAIK, post SQL contains invalid syntax.
    left join .PTRNPF npf on t.POL_ID = npf.chdrnum
    what is the period character to the right of "left join" & to the left of "PTRNPF"?
  • 2. Re: left join
    Paulie Pro
    Currently Being Moderated
    >


    >


    Hi, and welcome to the group.


    Please read the two "fixed" posts at the top
    of this group and follow their instructions.
    (Marked with yellow asterisks)

    Please provide sample data in the form of
    working* (test before posting) statements.

    CREATE TABLE Blah(...

    INSERT INTO Blah VALUES(...

    Show desired results.


    HTH,


    Paul...
  • 3. Re: left join
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ


    Handle:     1000228
    Status Level:     Newbie
    Registered:     Apr 15, 2013
    Total Posts:     27
    Total Questions:     8 (7 unresolved)


    I extend to you my condolences since you rarely get your questions answered.
  • 4. Re: left join
    ranit B Expert
    Currently Being Moderated
    Always post formatted code :
    SELECT 
        t.co_id, 
        t.pol_id, 
        t.agncy_id, 
        t.agt_id, 
        npf.subdate
      FROM (
         SELECT DISTINCT com.m1co AS co_id, fs.policy_number AS pol_id,
                            com.agency_cd AS agncy_id, c.agent_code AS agt_id,
                            p.address, cas.DATE
                       FROM comagtm1 com LEFT JOIN channel c
                            ON (com.agentno = c.agent_code)
                            ,
                            individual_agreement ia,
                            benefit cas,
                            cascntrm casc,
                            casmwagt casm,
                            person p,
                            financial_services fs
                      WHERE fs.role_player_id = c.role_player_id
                        AND com.m1co = fs.company_code
                        AND fs.policy_number =
                               DECODE (ia.business_key_contract_number,
                                       NULL, ia.business_key_policy_number,
                                       ia.business_key_contract_number
                                      )
                        AND cas.fpolno = fs.policy_number
                        AND cas.fbrcd = fs.base_rider_code
                        AND casc.mccntr = fs.policy_number
                        AND casc.mcco = fs.company_code
                        AND p.role_player_id = fs.role_player_id
                        AND fs.policy_number = casm.mwpoln
                        AND com.agency_cd = 'YAP'
        ) t
       LEFT JOIN
          ptrnpf npf 
       ON t.pol_id = npf.chdrnum
     WHERE 
        batcde = 'T600';
    May be your final WHERE clause (batcode = 'T600') is filtering all the records... just a guess
    Check w/o the WHERE clause how many records your query returns.

    Is there any specific reason you using both ANSI and Oracle style of SQL?
    Also, please tell us how did you decide upon this joining condition - <tt>"ON (t.pol_id = npf.chdrnum)"</tt>
  • 5. Re: left join
    Etbin Guru
    Currently Being Moderated
    Maybe (not mixing ANSI and Oracle join syntax)
    select t.co_id,
           t.pol_id,
           t.agncy_id,
           t.agt_id,
           npf.subdate
      from (select distinct 
                   com.m1co as co_id,
                   fs.policy_number as pol_id,
                   com.agency_cd as agncy_id, 
                   c.agent_code as agt_id,
                   p.address,
                   cas.date
              from comagtm1 com,
                   channel c,
                   individual_agreement ia,
                   benefit cas, 
                   cascntrm casc,
                   casmwagt casm,
                   person p,
                   financial_services fs 
             where com.agentno = c.agent_code(+)
               and fs.role_player_id = c.role_player_id
               and com.m1co = fs.company_code 
               and fs.policy_number = nvl(ia.business_key_contract_number,ia.business_key_policy_number) 
               and cas.fpolno = fs.policy_number 
               and cas.fbrcd = fs.base_rider_code 
               and casc.mccntr = fs.policy_number
               and casc.mcco = fs.company_code 
               and p.role_player_id = fs.role_player_id 
               and fs.policy_number = casm.mwpoln
               and com.agency_cd = 'YAP'
           ) t,
           ptrnpf npf
     where t.pol_id = npf.chdrnum(+)
       and batcde = 'T600'
    Regards

    Etbin
  • 6. Re: left join
    1003231 Newbie
    Currently Being Moderated
    WHERE clause (batcode = 'T600') is filtering all the records
    now i got solution
    Thank you for your suggestion ranjit...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points