Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
Alias in where clause |
Fri, Oct 2 2009 9:18 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Someone please refresh my mind as to why this
SELECT _ID, _Name, _Status, _fkOrgType, FALSE AS _Zap, _Links, (SELECT LIST(CAST(_ID AS VARCHAR)) FROM NLH.Contacts X1 WHERE _Links IS NOT NULL AND EXISTS(SELECT _ID FROM NLH.Career WHERE _fkcontacts = X1._ID AND _fkCompanies IN (SELECT _ID FROM NLH.Companies WHERE _Status = 'Erase')) ) AS _ContactList, (SELECT LIST(_Links,'') FROM NLH.Contacts X2 WHERE _Links IS NOT NULL AND EXISTS(SELECT _ID FROM NLH.Career WHERE _fkcontacts = X2._ID AND _fkCompanies IN (SELECT _ID FROM NLH.Companies WHERE _Status = 'Erase')) ) AS _ContactDocs FROM NLH.Companies WHERE _Status = 'Erase' is legal and this SELECT _ID, _Name, _Status, _fkOrgType, FALSE AS _Zap, _Links, (SELECT LIST(CAST(_ID AS VARCHAR)) FROM NLH.Contacts WHERE _Links IS NOT NULL AND EXISTS(SELECT _ID FROM NLH.Career WHERE _fkcontacts = NLH.Contacts._ID AND _fkCompanies IN (SELECT _ID FROM NLH.Companies WHERE _Status = 'Erase')) ) AS _ContactList, (SELECT LIST(_Links,'') FROM NLH.Contacts WHERE _Links IS NOT NULL AND EXISTS(SELECT _ID FROM NLH.Career WHERE _fkcontacts = NLH.Contacts._ID AND _fkCompanies IN (SELECT _ID FROM NLH.Companies WHERE _Status = 'Erase')) ) AS _ContactDocs FROM NLH.Companies WHERE _Status = 'Erase' isn't. To make it a bit easier to spot I have to use an alias X1 & X2 for NLH.Contacts in the WHERE clause Roy Lambert |
Fri, Oct 2 2009 12:04 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< isn't. To make it a bit easier to spot I have to use an alias X1 & X2 for NLH.Contacts in the WHERE clause >> It's because EDB does not support qualifying a column reference with both a database name and a table name. Database name qualifiers are only used for table names, not column references. -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Oct 3 2009 6:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>It's because EDB does not support qualifying a column reference with both a >database name and a table name. Database name qualifiers are only used for >table names, not column references. Was there a reason for that? Roy Lambert |
Sat, Oct 3 2009 11:18 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Was there a reason for that? >> Yes, simplicity. Resolving 4-part qualifiers (database, schema, table, and column) can be very hard to do manually for a developer, and it's easier to just resolve the database and schema at the table reference level. -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Oct 3 2009 12:05 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>Yes, simplicity. Resolving 4-part qualifiers (database, schema, table, and >column) can be very hard to do manually for a developer, and it's easier to >just resolve the database and schema at the table reference level. Fair enough as long as you mean the engine developer (eg Tim) and not an application developer (eg Roy). Otherwise I think you're totally wrong. Expecting me to remember X1 means NLH.Contacts is definitely not on Roy Lambert |
Tue, Oct 6 2009 12:40 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Fair enough as long as you mean the engine developer (eg Tim) and not an application developer (eg Roy). Otherwise I think you're totally wrong. Expecting me to remember X1 means NLH.Contacts is definitely not on >> If you have trouble with X1, then you can always name them something better like NLH_Contacts. C'mon Roy, you're talking about naming a table with a correlation name and then referring to it two or three lines away. I think you can handle that level of detail. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Oct 6 2009 1:45 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>If you have trouble with X1, then you can always name them something better >like NLH_Contacts. C'mon Roy, you're talking about naming a table with a >correlation name and then referring to it two or three lines away. I think >you can handle that level of detail. You want to try living in my brain at the moment <vbg> Roy Lambert |
This web page was last updated on Saturday, June 22, 2024 at 05:51 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |