select distinct cast

Question

select distinct cast

Iain Wilson

Say Hey Kid

Points: 666
More actions
August 26, 2005 at 3:48 am

#102744
Hi guys and gals,
I'm tearing my hair out (or rather, what little's left, being follically challenged and all) over the following piece of TSQL. Apparently there's an error at line 11 but for the life of me I can't figure out what.
If any of you could shed any light upon it I'd add you to my "heroes and heronines of the Internet" list...
```
select distinct cast (propertyid as int)as propertyid
```
```
from syc_weblog 
```
```
where (CAST(CAST(property_id AS INT) AS varchar(50)) not in 
```
```
(select (CAST(address_agency_details_id as int)) as propertyid 
```
```
from residentialsalescache
```
```
WHERE CAST (CAST(Address_Agency_Details_Id AS INT) AS varchar(50))
```

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

wz700 SSCertifiable Points: 7133 More actions · Answer 1

try

select distinct
 cast (propertyid as int) as propertyid
 from syc_weblog
 where CAST(CAST(property_id AS INT) AS varchar(50)) not in
 (select (CAST(address_agency_details_id as int)) as propertyid
 from residentialsalescache
 WHERE CAST(CAST(Address_Agency_Details_Id AS INT) AS varchar(50)))

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

I'm quite willing to be a heroine if you would at least tell me what it is that you are trying to accomplish with all those casts...splly. where you're trying to cast the int as varchar and then search for it in your residentialsalescache table...?!?!?!?!

would you be actually looking for something like this mayhap...?!?!

select distinct(cast(propertyid as int)) as propertyid

from syc_weblog 

where cast(propertyid as int) not in 

(select CAST(address_agency_details_id as int) as propertyid 

from residentialsalescache)

**ASCII stupid question, get a stupid ANSI !!!**

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 3

An earlier post pointed out the missing end-parens.

Also, it looks to me like the WHERE clause in the nested "IN (SELECT" etc. is missing criteria. I.e., what is this supposed to evaluate to?

WHERE CAST (CAST(Address_Agency_Details_Id AS INT) AS varchar(50))

You might try uncluttering the code a bit; I know that helps me see things more clearly. The following, for example:

WHERE (CAST (CAST (property_id AS INT) AS VARCHAR (50))
NOT IN (SELECT (CAST (address_agency_details_id AS INT)) AS propertyid

You convert the 'property_id' to INT and then to a VARCHAR, in order to compare it to an INT. No need for the convert to VARCHAR, in this case.

Also:

NOT IN (SELECT (CAST (address_agency_details_id AS INT)) AS propertyid

You don't really need that 'AS propertyid', at this point.