question

ArthurDeguzman-2049 avatar image
0 Votes"
ArthurDeguzman-2049 asked ArthurDeguzman-2049 commented

SQL Table aliases

Hello all,

I create my aliases for tables in the "FROM" section.

I got a complex code from a Crystal report I would like to modify and use. Some tables do not appear in any FROM section.

Is there a way where I can create aliases for these tables before the SELECT command?

If it matters, I will be dealing with read-only tables. I can only retrieve data from them.

Thanks!


sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

I got a complex code from a Crystal report I would like to modify and use. Some tables do not appear in any FROM section.

They do. You only need to find them. A table name can only be introduced in the FROM clause. JOIN is part of the FROM clause.

But a query may include subqueries, derived tables, common table expressions, so there can be more than one FROM in a query.

No, you cannot create alias beforehand for a query.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ArthurDeguzman-2049 avatar image
0 Votes"
ArthurDeguzman-2049 answered ArthurDeguzman-2049 commented

Hello Erland,

I have for example have the following:

LEFT OUTER JOIN billing_guar_table billing_guar_table

I don't yet understand why Crystal reports the table name yet, but can I create the alias here like this?

LEFT OUTER JOIN billing_guar_table AS b billing_guar_table

and in fact can I then write this as:

LEFT OUTER JOIN billing_guar_table AS a a


Thanks!




· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LEFT OUTER JOIN billing_guar_table billing_guar_table

is the same as

LEFT OUTER JOIN billing_guar_table AS billing_guar_table

That is, the AS is not mandatory.

As for why Crystal uses the table as alias, I don't know, but, hey, it's a computer program. You can't expect too much from these guys.

Your suggestion:

LEFT OUTER JOIN billing_guar_table AS b billing_guar_table

Is syntactically invalid.

0 Votes 0 ·

Thank you!

Deguza

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @ArthurDeguzman-2049,

Welcome to Microsoft Q&A!

In TSQL, you could update the example like below:

 LEFT OUTER JOIN billing_guar_table AS b

OR

 LEFT OUTER JOIN billing_guar_table  b

If above is not working, please provide the whole query and we could check further.

Thank you for understanding!

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.