alias/source properties in QUERY

[Guest]

can someone give me an example pls of when you would use the alias/source
properties of a query?

 

[Nick 'The database Guy']

What is the problem that you are having?

 

[Guest]

Alias....

You might want to "pretty up" your results. For example, if your column
name is CUST_ID you might want the results column heading to appear as
"Customer Number" instead of "CUST_ID"

Select CUST_ID AS [Customer Number] FROM MyTable;


Someties, I just use a table alias to shorten the text of my queries.

select mybiglongtablename.customer_id from mytable;

or w/ alias it looks like this...

select a.customer_id from mytable as a;



Another example of using an alias is when using subqueries. Pardon the
lengthy example, ... just notice that table aliases "a" and "b" are used in
the sub query, specificially in the WHERE clause on customer_id and id_field.


As far as I know, you need to use aliases if you want to reference between
query and sub query.

INSERT INTO TMP_XLPDA_AdHoc ( customer_id, AdhocDate, AdhocCode, AdhocInfo,
A_AdhocInfo, MySequence )
SELECT a.customer_id, a.AdhocDate, a.AdhocCode, a.AdhocInfo, a.A_AdhocInfo,
(select count(*) as cnt from tmp_xlpda_adhoc as B where (a.customer_id
=b.customer_id) and b.id_field <= a.id_field) AS NewSequence
FROM TMP_XLPDA_AdHoc AS a
ORDER BY a.customer_id;


HTH
David

 

[Guest]

Sorry, I just realized a typo in my second example. Should be ...

select mybiglongtablename.customer_id from mybiglongtablename;

Alias....

You might want to "pretty up" your results. For example, if your column
name is CUST_ID you might want the results column heading to appear as
"Customer Number" instead of "CUST_ID"

Select CUST_ID AS [Customer Number] FROM MyTable;


Someties, I just use a table alias to shorten the text of my queries.

select mybiglongtablename.customer_id from mytable;

or w/ alias it looks like this...

select a.customer_id from mytable as a;



Another example of using an alias is when using subqueries. Pardon the
lengthy example, ... just notice that table aliases "a" and "b" are used in
the sub query, specificially in the WHERE clause on customer_id and id_field.


As far as I know, you need to use aliases if you want to reference between
query and sub query.

INSERT INTO TMP_XLPDA_AdHoc ( customer_id, AdhocDate, AdhocCode, AdhocInfo,
A_AdhocInfo, MySequence )
SELECT a.customer_id, a.AdhocDate, a.AdhocCode, a.AdhocInfo, a.A_AdhocInfo,
(select count(*) as cnt from tmp_xlpda_adhoc as B where (a.customer_id
=b.customer_id) and b.id_field <= a.id_field) AS NewSequence
FROM TMP_XLPDA_AdHoc AS a
ORDER BY a.customer_id;


HTH
David

can someone give me an example pls of when you would use the alias/source
properties of a query?

 

[Nick 'The database Guy']

Hi David,

I see what you are saying, and I don't think that I would be going out
on to much of a limb if I said that using aliases to shorten the length
of a query is generally frowned upon, it is exacebated further if you
begin calling tables nice descriptive names like 'a' and stuff like
that. It's ok (if you have a good memory) while you are working on the
database, but as soon as it's passed in to anothers hands then it will
confuse them and take a while to 'unpick'. So the general message that
I'm trying to send is, 'DON'T DO IT'. After all what advantage does it
give you?

Nick

Sorry, I just realized a typo in my second example. Should be ...

select mybiglongtablename.customer_id from mybiglongtablename;

Alias....

You might want to "pretty up" your results. For example, if your column
name is CUST_ID you might want the results column heading to appear as
"Customer Number" instead of "CUST_ID"

Select CUST_ID AS [Customer Number] FROM MyTable;


Someties, I just use a table alias to shorten the text of my queries.

select mybiglongtablename.customer_id from mytable;

or w/ alias it looks like this...

select a.customer_id from mytable as a;



Another example of using an alias is when using subqueries. Pardon the
lengthy example, ... just notice that table aliases "a" and "b" are used in
the sub query, specificially in the WHERE clause on customer_id and id_field.


As far as I know, you need to use aliases if you want to reference between
query and sub query.

INSERT INTO TMP_XLPDA_AdHoc ( customer_id, AdhocDate, AdhocCode, AdhocInfo,
A_AdhocInfo, MySequence )
SELECT a.customer_id, a.AdhocDate, a.AdhocCode, a.AdhocInfo, a.A_AdhocInfo,
(select count(*) as cnt from tmp_xlpda_adhoc as B where (a.customer_id
=b.customer_id) and b.id_field <= a.id_field) AS NewSequence
FROM TMP_XLPDA_AdHoc AS a
ORDER BY a.customer_id;


HTH
David

can someone give me an example pls of when you would use the alias/source
properties of a query?

 

[Nick 'The database Guy']

exacerbated*

Hi David,

I see what you are saying, and I don't think that I would be going out
on to much of a limb if I said that using aliases to shorten the length
of a query is generally frowned upon, it is exacebated further if you
begin calling tables nice descriptive names like 'a' and stuff like
that. It's ok (if you have a good memory) while you are working on the
database, but as soon as it's passed in to anothers hands then it will
confuse them and take a while to 'unpick'. So the general message that
I'm trying to send is, 'DON'T DO IT'. After all what advantage does it
give you?

Nick

Sorry, I just realized a typo in my second example. Should be ...

select mybiglongtablename.customer_id from mybiglongtablename;

Alias....

You might want to "pretty up" your results. For example, if your column
name is CUST_ID you might want the results column heading to appear as
"Customer Number" instead of "CUST_ID"

Select CUST_ID AS [Customer Number] FROM MyTable;


Someties, I just use a table alias to shorten the text of my queries.

select mybiglongtablename.customer_id from mytable;

or w/ alias it looks like this...

select a.customer_id from mytable as a;



Another example of using an alias is when using subqueries. Pardon the
lengthy example, ... just notice that table aliases "a" and "b" are used in
the sub query, specificially in the WHERE clause on customer_id and id_field.


As far as I know, you need to use aliases if you want to reference between
query and sub query.

INSERT INTO TMP_XLPDA_AdHoc ( customer_id, AdhocDate, AdhocCode, AdhocInfo,
A_AdhocInfo, MySequence )
SELECT a.customer_id, a.AdhocDate, a.AdhocCode, a.AdhocInfo, a.A_AdhocInfo,
(select count(*) as cnt from tmp_xlpda_adhoc as B where (a.customer_id
=b.customer_id) and b.id_field <= a.id_field) AS NewSequence
FROM TMP_XLPDA_AdHoc AS a
ORDER BY a.customer_id;


HTH
David


:

can someone give me an example pls of when you would use the alias/source
properties of a query?

 

[John Spencer]

Each to his own.

I personally find very long table names to be confusing and hard to read.
Aliases when carefully chosen can make the SQL a lot more readable for me.
I do try to pick aliases that do make some sense - maybe a one-word summary
of the table, subquery, or query I am aliasing. And there are times when
you have no choice. For instance when using a subquery.

That does not mean that I use aliases in every query or for every table, it
does mean that they are a good tool when used appropriately.

exacerbated*

Hi David,

I see what you are saying, and I don't think that I would be going out
on to much of a limb if I said that using aliases to shorten the length
of a query is generally frowned upon, it is exacebated further if you
begin calling tables nice descriptive names like 'a' and stuff like
that. It's ok (if you have a good memory) while you are working on the
database, but as soon as it's passed in to anothers hands then it will
confuse them and take a while to 'unpick'. So the general message that
I'm trying to send is, 'DON'T DO IT'. After all what advantage does it
give you?

Nick

Sorry, I just realized a typo in my second example. Should be ...

select mybiglongtablename.customer_id from mybiglongtablename;


:

Alias....

You might want to "pretty up" your results. For example, if your
column
name is CUST_ID you might want the results column heading to appear
as
"Customer Number" instead of "CUST_ID"

Select CUST_ID AS [Customer Number] FROM MyTable;


Someties, I just use a table alias to shorten the text of my queries.

select mybiglongtablename.customer_id from mytable;

or w/ alias it looks like this...

select a.customer_id from mytable as a;



Another example of using an alias is when using subqueries. Pardon
the
lengthy example, ... just notice that table aliases "a" and "b" are
used in
the sub query, specificially in the WHERE clause on customer_id and
id_field.


As far as I know, you need to use aliases if you want to reference
between
query and sub query.

INSERT INTO TMP_XLPDA_AdHoc ( customer_id, AdhocDate, AdhocCode,
AdhocInfo,
A_AdhocInfo, MySequence )
SELECT a.customer_id, a.AdhocDate, a.AdhocCode, a.AdhocInfo,
a.A_AdhocInfo,
(select count(*) as cnt from tmp_xlpda_adhoc as B where
(a.customer_id
=b.customer_id) and b.id_field <= a.id_field) AS NewSequence
FROM TMP_XLPDA_AdHoc AS a
ORDER BY a.customer_id;


HTH
David


:

can someone give me an example pls of when you would use the
alias/source
properties of a query?