Link to home
Create AccountLog in
Avatar of arthurh88
arthurh88

asked on

Unsupported Data Type in My Select Query

I am puzzled.  This query will return several <Unsupported Data Type> for all the A.(records) and then in my SQL pane (using Management Studio), I erase the "AS MoreAddresses_1" and click the exclamation point (execute icon) and Mgt Studio will pop the AS MoreAddresses_1 back in, and it will work just fine.  Then I add just a meaningless space after something like "zip" and I get unsupported data type again.  Then erase the "AS MoreAddresses_1" (i leave the space I added though), and it goes back to working.  So this query flips back and forth from working to not working.

Something in this query is broken, like it isn't stable, but I'm not sure what.


SELECT     A.City, providers.Active, providers.FirstName, providers.LastName, A.Address, providers.MainPromo, A.Zip, A.Country, providers.PersonalWebsite, A.Phone, A.State,
                      providers.Designations, providers.Title, providers.E_Counselor, providers.E_Email, providers.E_Phone, providers.E_Webcam, providers.E_IM, providers.Fees,
                      providers.IsPHD, providers.OtherPopulations, providers.FollowLink, providers.Featured, providers.AwayMessage, providers.Notes,
                          (SELECT     COUNT(*) AS Expr1
                            FROM          MoreAddresses
                            WHERE      (UserID = ' 1044')) AS countAnotherAddress
FROM         providers INNER JOIN
                          (SELECT     UserID, City, State, Phone, Address, Country, Zip
                            FROM          MoreAddresses AS MoreAddresses_1
                            WHERE      (UserID = '1044') AND (AddressNumber = 1) AND (LEN(Address) > 1) OR
                                                   (UserID = '1044') AND (AddressNumber = 0)) AS A ON A.UserID = providers.UserID
WHERE     (providers.UserID = '1044')
Avatar of PortletPaul
PortletPaul
Flag of Australia image

I would suggest you try an alias in the scalar subquery within the selection list.

...
         , (
           SELECT COUNT(*) AS Expr1
           FROM   MoreAddresses AS MoreAdresses_A
           WHERE  (UserID = ' 1044'))
           AS countAnotherAddress
FROM       providers
INNER JOIN (
           SELECT UserID
                , City
                , State
                , Phone
                , Address
                , Country
                , Zip
           FROM   MoreAddresses
           WHERE  (UserID = '1044')
           AND    (AddressNumber = 1)
           AND    (LEN(Address) > 1)
           OR     /* is there a ( missing here ? */
                  (UserID = '1044')
           AND    (AddressNumber = 0)
                  /* is there a ) missing here ? */
           ) AS A ON A.UserID = providers.UserID
WHERE (providers.UserID = '1044')

Open in new window

and their may be parentheses missing with the OR condition?
and as you have an inner join, with the subquery restricted by userID, do you need the final where?
Avatar of arthurh88
arthurh88

ASKER

hi.  thanks for tackling my question.  The logic for the parenthesis is this:
WHERE     (UserID = 1044)    AND  
((Addressnumber =1 AND len(address) > 1) OR (Addressnumber = 0))

The thing is, that when I put this into SQL management studio, it will "rewrite" it all automatically and put things in different places.  

So I enter this into SQL Management Studio:
 WHERE     (UserID = 1044)    AND  
((Addressnumber =1 AND len(address) > 1) OR (Addressnumber = 0))) AS A ON A.UserID = providers.UserID

And it then automatically rewrites it to this:

WHERE      (UserID = 1044) AND (AddressNumber = 1) AND (LEN(Address) > 1) OR
                                                   (UserID = 1044) AND (AddressNumber = 0)) AS A ON A.UserID = providers.UserID
if len(address) > 1 then I want to select where userid=1044 and addressnumber = 1, otherwise just where userid=1044 and addressnumber = 0
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
<arthurh88>
did your code worked before? is it 1st time you see this problem- or you had this before?

what is your sql server edition\service pack? same for OS
are you using SSMS 2008 or 2005?

what is your DB compatibility level? Are you using system data types or user defined types in this DB?
----
you may need to change your DB compatibility level and patch your sql server \sql tools
my code worked perfectly until I changed the following:

I changed this:

 WHERE     (UserID = 1044)    AND (Addressnumber =1 )) AS A ON A.UserID = providers.UserID

to this:

 WHERE     (UserID = 1044)    AND  
((Addressnumber =1 AND len(address) > 1) OR (Addressnumber = 0))) AS A ON A.UserID = providers.UserID

that change was where i started seeing the unsupported data type....and you are right about designer.  maybe that is my problem.  i'll have a look
you know what?  you are right..its the DESIGNER WINDOW that mucks up my statements.  when i opened a query window, it executes flawlessly exactly as I originally posted, without any errors at all, time and time again.  thanks a lot.   the designer window is no good for advanced statements!
the designer window is no good for advanced statements!
I suspect the Designer was added to help MS Access developers get up to speed with T-SQL.  Unfortunately it is severely crippled and does not support anything much beyond basic SELECT statements.