sybase SQL -> SQL Server

[kingz2000]

hi guys,

I'm having problems with translating a sybase SQL with a slightly complicated outer join binding into SQL server form. can someone tell me where I can find adedquate help to solve this problem.Or maybe directly help with this SQL?!.

The SQL , simplified, is looking like this:

select a,b,c,d,e,f
FROM
DBA.ZNNADF,
DBA.ZNNUDF left outer join DBA.ZNAQDF on (NUADCD = AQADCD),
DBA.ZNNUDF left outer join DBA.ZNN8DF on (NUNACD = N8NACD AND NUNUCD = N8NUCD),
DBA.ZNAQDF left outer join DBA.ZNADDF on (AQADCD = ADADCD),
DBA.ZNNCDF,
DBA.ZNNUDF left outer join DBA.ZIIHDF on (NUCOCD = IACOCD AND NULECD = IALECD AND NUACID = IAACID AND NUSBCD = IASBCD),
DBA.ZIIHDF left outer join DBA.ZABLDF on (IHMPNB = BLMPNB),
DBA.ZNAQDF left outer join DBA.BO_REFTEXT on (AQA1CD = CODE AND GID='Country')

the error message I get reads:
'Tables or functions 'DBA.ZNNUDF' and 'DBA.ZNNUDF'have the same exposed names.Use correlation names to distinguish them

.I know SQL-Server doesn't like the use of the same tables saveal times after the 'FROM' syntax..but how do I solve it exactly??


I thank you in advance for any help offered.
Cheers,

kingsley

 

[gmmastros]

I would suggest that you use table aliases. Like this...

select a,b,c,d,e,f
FROM
DBA.ZNNADF,
DBA.ZNNUDF [!]As SomeAlias1[/!] left outer join DBA.ZNAQDF on (NUADCD = [!]SomeAlias1.[/!]AQADCD),
DBA.ZNNUDF [!]As SomeAlias2[/!] left outer join DBA.ZNN8DF on (NUNACD = [!]SomeAlias2.[/!]N8NACD AND NUNUCD = [!]SomeAlias2.[/!]N8NUCD),
DBA.ZNAQDF left outer join DBA.ZNADDF on (AQADCD = ADADCD),
DBA.ZNNCDF,
DBA.ZNNUDF left outer join DBA.ZIIHDF on (NUCOCD = IACOCD AND NULECD = IALECD AND NUACID = IAACID AND NUSBCD = IASBCD),
DBA.ZIIHDF left outer join DBA.ZABLDF on (IHMPNB = BLMPNB),
DBA.ZNAQDF left outer join DBA.BO_REFTEXT on (AQA1CD = CODE AND GID='Country')

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kingz2000]

ah..Thanks!!

Does that mean, it doesn't matter which alias I use in the select?? ie.

select Somealias1.a,b,c,d,e,f .....

would give the same answer as??:

select Somealias2.a,b,c,d,e,f ....

after all, I'm getting a message:

Ambiguous column name 'b'

Secondly, if I have restrictions in the the 'WHERE' clause, I presume I have to mention all the aliases right?!.


Thanx in advance again.

Kingsley

 

[gmmastros]

>> Does that mean, it doesn't matter which alias I use in the select??

Not necessarily. If the 'a' column contains the same data in both tables, then it doesn't really matter. You will need to prefix the column with an alias from one of the tables though, so yes... you need to choose which one.

>> Secondly, if I have restrictions in the the 'WHERE' clause, I presume I have to mention all the aliases right?!.

When you use a table alias, you must use that alias everywhere in your query, including the where clause.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kingz2000]

I did as you said...

To obtain my result I'm waiting very long, and the result is not the same.
Basically, I am getting a lot of datasets repeated, due to the simple fact ofhaving 2 same tables with different names.

Is there maybe a way of changing the syntax so that I am only mentioning the table once, ie. not needing to use alias....Or am I missing something else????

 

[gmmastros]

>> Basically, I am getting a lot of datasets repeated, due to the simple fact of having 2 same tables with different names.

Not necessarily. I don't doubt that you are getting repeated data, what I am challenging is the reason why.

Let's take a closer look at your from clause. I see stuff like this...

[tt][blue]
FROM
DBA.ZNNADF,
DBA.ZNNUDF As SomeAlias1 left outer join DBA.ZNAQDF on (NUADCD = SomeAlias1.AQADCD)
[/blue][/tt]

When you list tables separated by a comma in your from clause, you will effectively get a cross join (unless you have where conditions limiting the rows returned).

For example, suppose you have these 2 tables.

[tt]
People
PersonId Name FavoriteColorId
-------- ------ ---------------
1 George 1
2 Paul 1
3 Ted 2

Color
ColorId Description
------- -----------
1 Red
2 Blue
[/tt]


Now, Suppose you have this query:

Select *
From People, Color

The output would be...

[tt][blue]
PersonId Name FavoriteColorId ColorId Description
-------- ------ --------------- ------- --------------
1 George 1 1 Red
2 Paul 1 1 Red
3 Ted 2 1 Red
1 George 1 2 Blue
2 Paul 1 2 Blue
3 Ted 2 2 Blue

(6 row(s) affected)
[/blue][/tt]

This cross join results in every combination of available data. In this case, the first table has 3 rows and the second table has 2, the output has 2 * 3 = 6 rows.

Now, if you specify the join condition, you get better results.

[tt]
Select *
From People
Inner Join C
On People.FavoriteColorId = Color.ColorId
[/tt]

The output is now...

[tt][blue]
PersonId Name FavoriteColorId ColorId Description
-------- ------ --------------- ------- -----------
1 George 1 1 Red
2 Paul 1 1 Red
3 Ted 2 2 Blue

(3 row(s) affected)
[/blue][/tt]

Does this make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kingz2000]

Yes, I understand what you're saying, but I'm not sure it applies in my case, since I AM using joins.

I have now found a syntactically apt way to convert the SQL from sybase to SQL-Server..My current version reads:

FROM
DBO.ZNNADF,
DBO.ZNNUDF left outer join DBO.ZNAQDF on (NUADCD = AQADCD)
left outer join DBO.ZNADDF on (AQADCD = ADADCD)
left outer join DBO.BO_REFTEXT on (AQA1CD = CODE AND GID='Country')
left outer join DBO.ZNN8DF on ( NUNACD = N8NACD AND NUNUCD =N8NUCD)
left outer join DBO.ZIIHDF on (NUCOCD = IACOCD AND NULECD = IALECD AND NUACID = IAACID AND NUSBCD = IASBCD)
left outer join DBO.ZABLDF on (IHMPNB = BLMPNB),
DBO.ZNNCDF
WHERE
NANACD = NUNACD AND
NANACD = NCNACD AND
NANAFG = '1' AND
NCNQSQ = '001' AND
NCEDSQ = 1
ORDER BY NANACD, NUNUCD

Now that works, but the only problem is that I get less datasets..in comparison to the 1st SQL right at the top..ie the result isn't quite the same. Now can anyone tell me why???