SQL join problems

[lhg1]

Hi

I building an SQL that retrives data from a lot of tables.

But one of the identifiers is not always there, giving me a lot of problem.

ex1 - this works as long as g.p_number_import_seq existe, witch is not all the time.

select c.campaign_id, d.birth_date, d.first_name,d.last_name, d.company_name, 
d.party_id , d.address_id, d.kob, d.cvr, e.sim_no, e.sim_order_type, f.phone_number
from ol_p_gsm_create a, ol_p_nd_create b, campaign c, contact_info d, p_sim e, p_number f, p_number_import f
where a.order_line_id = '3860089'
and a.order_line_id =B.ORDER_LINE_ID
and b.campaign_seq=c.campaign_seq
and b.contact_info_seq=d.contact_info_seq
and a.p_sim_seq = e.p_sim_seq
and b.p_number_seq=f.p_number_seq
and a.p_number_import_seq = g.p_number_import_seq

I've tried using left join, witch is quite new to me, and I can't really get it to work.
Something like this

select c.campaign_id, d.birth_date, d.first_name,d.last_name, d.company_name, 
d.party_id , d.address_id, d.kob, d.cvr, e.sim_no, e.sim_order_type, f.phone_number
from ol_p_gsm_create a, ol_p_nd_create b, campaign c, contact_info d, p_sim e, p_number f
left join p_number_import g on a.p_number_import_seq = g.p_number_import_seq
where a.order_line_id = '3866031'
and a.order_line_id =B.ORDER_LINE_ID
and b.campaign_seq=c.campaign_seq
and b.contact_info_seq=d.contact_info_seq
and a.p_sim_seq = e.p_sim_seq
and b.p_number_seq=f.p_number_seq
and a.p_number_import_seq = g.p_number_import_seq

All I want is to have an empty field if the field is empty.

Regards
Lars

 

[Dagon]

You seem to have the same condition in both the ON clause and the WHERE clause:

on a.p_number_import_seq = g.p_number_import_seq
...
and a.p_number_import_seq = g.p_number_import_seq

Remove it from the latter.

 

[lhg1]

That didn't help much

Error given is:
ORA-00904: "A"."P_NUMBER_IMPORT_SEQ": invalid identifier

 

[SantaMufasa]

First, LHG, when people take the time to respond to requests for help, our etiquette and protocol here is not to respond back with, "That didn't help much...". (Such will often result in a "red-flag" removal of your post, and additional recurrences result Tek-Tips membership restrictions.)

If you experience a problem with a posted solution, a more diplomatic, less offensive response would be:

Thanks, Dagon, for your response. I received the following error message, however, when I ran the code. Can you point out what went wrong?

Thanks,

LHG

Secondly, I believe the run-time error you are receiving, "ORA-00904: "A"."P_NUMBER_IMPORT_SEQ": invalid identifier", results not from any fault of Dagon's, but rather from your code referring to a column, "P_NUMBER_IMPORT_SEQ", which does not reside in the table, "ol_p_gsm_create".

Could you please post a "DESCRIBE" of the tables to which you refer in your code? That way, we can better assist you in resolving your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Dagon]

I struggle to see how removing a line of code could result in getting an "invalid identifier" error that wasn't there before. You must have made some other alterations to your SQL as well.

 

[lhg1]

Sorry for the bad conduct, I see how it could be interpreted as offensive. That was by no meens my ideer, I'm very glad for the help I get here.

And no, its was not removing the line that gave me that error, I've been getting that the hole time, again my mistake and probably due to a lot of trail and error, the line should by no meens be there.

I actually figured out the problem "with online dokumentation help "

In Oracle you can add a (+) to do an outer join, witch solved my problem

select c.campaign_id, d.birth_date, d.first_name,d.last_name, d.company_name,
d.party_id , d.address_id, d.kob, d.cvr, e.sim_no, e.sim_order_type, f.phone_number
from ol_p_gsm_create a, ol_p_nd_create b, campaign c, contact_info d, p_sim e, p_number f, p_number_import f
where a.order_line_id = '3860089'
and a.order_line_id =B.ORDER_LINE_ID
and b.campaign_seq=c.campaign_seq
and b.contact_info_seq=d.contact_info_seq
and a.p_sim_seq = e.p_sim_seq
and b.p_number_seq=f.p_number_seq
and a.p_number_import_seq = g.p_number_import_seqselect c.campaign_id, d.birth_date, d.first_name,d.last_name, d.company_name,
d.party_id , d.address_id, d.kob, d.cvr, e.sim_no, e.sim_order_type, f.phone_number
from ol_p_gsm_create a, ol_p_nd_create b, campaign c, contact_info d, p_sim e, p_number f, p_number_import g
where a.order_line_id = '3860089'
and a.order_line_id =B.ORDER_LINE_ID
and b.campaign_seq=c.campaign_seq
and b.contact_info_seq=d.contact_info_seq
and a.p_sim_seq = e.p_sim_seq
and b.p_number_seq=f.p_number_seq
and a.p_number_import_seq = g.p_number_import_seq (+)

That meens if g.p_number_import_seq does not exists the an empty field is just returned.

Thanks

 

[Dagon]

I'm glad you resolved the problem, but using (+) is effectively the same as "left outer join", so it should have been possible to do it without using this.