Hi,
I've a problem with the following query. I want to left join table sec (x5)
with x0, and x4. I try the following query :
select x0.coh_agent ,x0.bra_id ,x0.dpr_id ,x0.usr_id ,x0.csc_id ,x0.spp_id ,x0.csc_id_inv ,x0.coh_doc_inv
,x0.coh_d_inv, x0.coh_process ,x0.coh_doc_main ,x0.coh_status ,x0.coh_total_local ,x0.coh_basis_local
,x0.coh_cost_local,x0.coh_profit_local ,x0.coh_over_local ,x0.coh_com_earned ,x0.coh_com_adjust ,x0.coh_com_held
,x0.coh_com_cancel,x0.coh_com_topay ,x0.coh_d_paid ,x0.coh_matchname ,x0.coh_cscmatch ,x0.coh_com_paid ,x3.cur_dec_nb
,x3.cur_location ,x3.cur_negative ,x3.cur_dec_char ,x3.cur_group_char ,x5.sec_id ,x1.cpy_id ,x1.cpy_cr_tr
,x1.cpy_cr_tg,x1.cpy_cr_tb ,x2.bra_screen ,x2.bra_id ,x2.lng_id ,x4.tad_cpy_name from coh x0 , cpy x1 ,bra x2 ,
curx3 ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND
x5.usr_id=x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, dpr x6 where ((((((((((x0.cpy_id = x1.cpy_id
)AND (x0.bra_id = x2.bra_id ) ) AND (x0.cur_id = x3.cur_id ) ) AND (x0.dpr_id = x6.dpr_id ) ) AND (x2.tad_id =
x4.tad_id) ) AND (x5.thr_id = x4.thr_id ) ) AND (x2.bra_id = x6.bra_id ) ) AND (x5.usr_id = x0.usr_id ) ) AND
(x5.bra_id= x0.bra_id ) ) AND (x5.dpr_id = x0.dpr_id )
Unfortunatelly, postgres returns me the following error :Error: ERROR: Relation "x0" does not exist (State:S1000,
NativeCode: 7)
I tried to modify the FROM sentence by:FROM (coh x0 , cpy x1 ,bra x2 , cur x3 ,tad x4 ) LEFT OUTER JOIN sec x5 on
x5.thr_id=x4.thr_idAND x5.usr_id=x0.usr_id AND
x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id,
but I get a syntax error , may be due to parentheses.
Does anyone knows how to write this query correctly ?
Regards, Nicolas.
---------------------------------------------------------------
Nicolas JOUANIN - SA REGIE FRANCE
Village Informatique BP 3002
17030 La Rochelle CEDEX
Tel: 05 46 44 75 76
Fax: 05 46 45 34 17
email: n.jouanin@regie-france.com
Web : www.regie-france.com
---------------------------------------------------------------