SQL CE Syntax

[Peager]

I'm using a product sitting on top of SQL CE that allows me to run an SQL query. The following query runs on the same table in SQL Std just fine but throws an error (which is hidden by the product I am working with), under CE. Can someone point out what might not be supported under CE for the following script?

SELECT TOP 1 [Class], 
from ANSIPole 
where Species = 'DF' AND 
      Length = 70 AND 
      Butt_Circ <= 54 AND 
      Top_Circ <= 29
Order by Species, Length, Butt_Circ Desc, Top_Circ DESC

Much thanks in advance,

Paul

 

[r937]

i bet it's the dangling comma in front of the FROM keyword

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Peager]

No, that's my posting error.... I was selecting several columns and just 'trimmed' it up a bit to post for simplicities sake.

Paul

 

[SQLBill]

I'm suprised that would work on Standard Edition since the comma r937 pointed out would cause a syntax error. Did you remove column names from your original select?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[RiverGuy]

Try taking out TOP 1 just to test. I'm not sure if that is it, but last time I checked, SQL CE did not even support views, so I suspect anything other than the extremely basic has a very high chance of failing to work.

 

[markros]

My first quess was TOP (N) may not be supported. Try googling on "SQL Server CE unsupported commands"

PluralSight Learning Library

 

[markros]

Unfortunately I can not find the unsupported commands listed (I didn't try to search hard enough). Found instead

http://www.microsoft.com/Sqlserver/2005/en/us/compact-downloads.aspx

PluralSight Learning Library

 

[Peager]

Well, I found a downloadable books on line and TOP n is NOT explicitly supported. I rewrote the script as follows but it still won't run.... time to go to the vendor I guess. For those interested... this is how I re-wrote the script...
The data I'm returning is supposed to be ordered like this

Class Butt_Circ Top_Circ
4 41.5 21.0
3 45.0 23.0
2 48.0 25.0
1 51.0 27.0
H1 54.0 29.0
H2 57.0 31.0
H3 60.5 33.0
H4 63.5 35.0

So you have to fiddle with the MIN/MAX logic. Not pretty but it works.

SELECT  CASE 
	  WHEN MAX([Class]) LIKE 'H%' THEN MAX([CLASS])
	  ELSE MIN([CLASS])	
	END AS [CLASS]
FROM    ANSIPole
WHERE   Species = 'DF'
        AND Length = 70
        AND Butt_Circ <= 49
        AND Top_Circ <= 25

 

[RiverGuy]

SQL CE just doesn't have enough functionality IMO. SQLite or VistaDB would be better embedded choices.

 

[SQLBill]

For one thing square brackets are not allowed in CE. Here is a link to Microsoft's 'SQL Reference for SQL Server CE'.

http://msdn.microsoft.com/en-us/library/aa275621(SQL.80).aspx

I clicked on Overview and that's where I saw the comment about square brackets. There may be more, but that's the first one I saw that matched something in your script.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Peager]

Good catch SQLBill.... Still won't run though.... I have a call in to the vendor who's product I am running in... we'll see what thier engineers say.

Thanks for the tip though...