Left Outer Join

[Diya]

Hey,

Im using a Dynamic RDBMS stage to fetch data from MS SQL Server.
I want to do a left outer join in the DRS stage.
It is possible using "User Defined SQL" .

But I want to know if it is possible to do it using "Generated SQL Query"

Can anyone help me on this?

Thanks,
Diya

[narasimha]

It is possible.

Select multiple tables into the "SQL FROM clause" (You could give an alias if you want)

In the Selection tab, where clause write your required condition like

Code: Select all

coulmn1=column2(+)

[chulett]

That's old style Oracle syntax - you sure that works in Sql Server?

[Diya]

I tried it and it doesn't work in SQL server.

Any other ideas?

[narasimha]

Oops, that was oracle's old syntax
Let me give you an old SQL Server Syntax

Code: Select all

 *= 

I dont know if the newer versions of SQL Server support it.
Give it a shot.

[chulett]

It's perfectly feasible. Any of the methods in the stage should support it. I don't use SQL Server - are you not aware of the proper 'left outer join' syntax you'd need? Check with your DBA if not.

[Diya]

Thats working.... :D

Thanks Narasimha

[jdmiceli]

Hi all,

If I remember right the '*=' notation is not supported per se in SQL Server 2000. It still functions only for backwards compatibility purposes, but I believe I read (somewhere) that notation is not supported in SQL Server 2005. Many things that existed for backwards compatibility have been dropped from 2005 as they were considered limiting factors.

If you want the official 'left join' syntax it would be as follows:

SELECT <whatever>
FROM <table1> a LEFT JOIN
<table2> b on a.column = b.column
and a.col = b.col ....
WHERE ...

Hope that offers some additional assistance.

Bestest!

[ray.wurlod]

Try to use generated SQL wherever possible. Everything in the table name field becomes the FROM clause. So what you put in the table name field is all of:

Code: Select all

table1 LEFT OUTER JOIN table2 ON table1.key = table2.key

(Adjust as required. Make sure any ambiguous column names are fully qualified in the Derivation field of the Columns grid.)