This content has been marked as final. Show 6 replies
1000228 wrote:is COPY & PASTE broken for you?
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,
FROM comagtm1 com left JOIN channel c ON (com.agentno = c.agent_code),
person p ,
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,
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
AFAIK, post SQL contains invalid syntax.
left join .PTRNPF npf on t.POL_ID = npf.chdrnumwhat is the period character to the right of "left join" & to the left of "PTRNPF"?
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.
How do I ask a question on the forums?
SQL and PL/SQL FAQ
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.
Always post formatted code :
May be your final WHERE clause (batcode = 'T600') is filtering all the records... just a guess
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';
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>
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'