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

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

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

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

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

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

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