LEFT joins multiple table syntax problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

LEFT joins multiple table syntax problem

Author

Topic

hkh99
Starting Member

3 Posts

Posted - 2005-08-25 : 18:08:58

Hi,

1)I need help for converting many ORACLE sql outer joins (+) to T-SQL format.

Let's say I have a star schema. I need to check if the foreign codes exist in the dimension table before loading them to the fact table. That's why I need a lot of left joins. I will insert a default value for any code that is not referenced in the dimension tables.

I specifically need help with the where clause and the from.

Thanks.
Randy

Here is the query,

SELECT
od.order_detail_code AS order_detail_code,
CAST(CONVERT(varchar,oh.order_date,112) AS int) AS time_code,
oh.order_date AS order_date,
oh.order_number AS order_number,
ISNULL (od.product_number,'0') AS product_number,
ISNULL (oh.order_method_code,'0') AS order_method_code,
ISNULL (oh.sales_staff_code,'0') AS sales_staff_code,
ISNULL (oh.retailer_site_code,'0') AS retailer_site_code,
od.unit_price AS unit_price,
od.unit_sale_price AS unit_sale_price,
od.unit_cost AS unit_cost,
od.quantity AS quantity,
od.unit_cost*od.quantity AS extended_cost,
od.unit_sale_price*od.quantity AS revenue,
od.quantity*(od.unit_sale_price-od.unit_cost) AS margin

FROM gosl..order_header oh,gosl..order_details od,order_method_dim om,salesappdm..product_dim pd,
salesappdm..retailer_site_dim rts,salesappdm..sales_staff_dim ssd

WHERE oh.order_number=od.order_details.order_number AND
oh.retailer_site_code=rts.retailer_site_code(+) AND
oh.sales_staff_code=ssd.sales_staff_code(+) AND
oh.order_method_code=om.order_method_code(+) AND
od.product_number=pd.product_number(+)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-25 : 18:17:00

Use the ANSI join syntax:

...
from
	order_header oh
	left outer join
	order_details od
	on oh.order_number = od.order_number
	... and so on ...

CODO ERGO SUM

hkh99
Starting Member

3 Posts

Posted - 2005-08-25 : 18:29:48

I think I've found how. Could help me confirm the syntax please?

SELECT
od.order_detail_code AS order_detail_code,
CAST(CONVERT(varchar,oh.order_date,112) AS int) AS time_code,
oh.order_date AS order_date,
oh.order_number AS order_number,
ISNULL (od.product_number,'0') AS product_number,
ISNULL (oh.order_method_code,'0') AS order_method_code,
ISNULL (oh.sales_staff_code,'0') AS sales_staff_code,
ISNULL (oh.retailer_site_code,'0') AS retailer_site_code,
od.unit_price AS unit_price,
od.unit_sale_price AS unit_sale_price,
od.unit_cost AS unit_cost,
od.quantity AS quantity,
od.unit_cost*od.quantity AS extended_cost,
od.unit_sale_price*od.quantity AS revenue,
od.quantity*(od.unit_sale_price-od.unit_cost) AS margin

FROM (gosl..order_header oh INNER JOIN gosl..order_details od ON oh.order_number=od.order_number
LEFT JOIN salesappdm..order_method_dim om ON oh.order_method_code=om.order_method_code
LEFT JOIN salesappdm..product_dim pd ON od.product_number=pd.product_number
LEFT JOIN salesappdm..retailer_site_dim rts ON oh.retailer_site_code=rts.retailer_site_code
LEFT JOIN salesappdm..sales_staff_dim ssd ON oh.sales_staff_code=ssd.sales_staff_code)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-25 : 18:53:23

The syntax looks pretty good. The parentheses in the FROM clause are not necessary.

Give it a run and let us know if any errors pop up.

---------------------------
EmeraldCityDomains.com

hkh99
Starting Member

3 Posts

Posted - 2005-08-26 : 10:01:40

Seems to work so far. Need to do further testing. Thanks to all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources