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 clause.


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(+)

On 25 Aug 2005 15:13:22 -0700, randy.***@gmail.com wrote:

Hi Randy,

Try if this works:

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

Note: The SELECT list in your original post didn't include any columns
from some of the tables used in the query. Increase perforamnce by
removing unused tables from the FROM clause!

Best, Hugo

Hi Hugo.

Thanks, it's working.

As for your last comment regarding my SELECT comment, I'm not sure what
you meant.  This is meant to be an INSERT INTO a fact table. I need to
validate all the keys with all the dimension tables.  So I need as many
joins as there are dimension keys right?

Regards,
Randy

On 26 Aug 2005 07:07:54 -0700, randy.***@gmail.com wrote:

Hi Randy,

I'm not sure how to explain this in a few words, so I'll use lots of
them.

If you INNER JOIN tableA to tableB, there are three possibilities fore
ach row in tableA:

* There is more than one match in tableB - the row will be duplicated as
often as there are matching rows in tableB and each duplicated row is
paired with one of the matching rows;
* There is one match in tableB - the row is retained once, and paired
with the matching row in tableB;
* There in no match in tableB - the row is eliminated from the result
set.

If you cahnge the INNER JOIN to an OUTER JOIN, the first two conditions
remain the same, but the third changes to:

* There is no match in tableB - the row is retained once, and paired
with a bunch of NULLs.

If you never use any of the values taken from tableB, it becomes
irrelevant what the rows from tableA are paired with, so we can simplify
the various options to:

* There is more than one match in tableB - the row will be duplicated as
often as there are matching rows in tableB;
* There is one match in tableB - the row is retained once;
* There is no match in tableB - the row is retained once.

Since you didn't post the full DDL, I don't know how your tables are
built - but my assumption was that the rows from the main table should
not match more than one row in the "unused" tables. And since the
"unused" tables are all OUTER JOINed, the effect of the join would be
that the row from the main table is either retained once, or retained
once - and if you remove the join, the row from the main table will also
be retained once.

That's why I advised you yo remove the unused tables from your query.

Best, Hugo

Return only the Third Row
Choosing one record over another
can't return zero when no rows exist using aggregate function sum
Table name as a variable
Count CHAR(11) in a string