Another n00b select statement problem...

When I try to use this select statement:

SELECT main.title, main.URL, main.description, main.city FROM main ORDER BY city WHERE (((main.active)=True));

I get a: Syntax error (missing operator) in query expression 'city WHERE (((main.active)=True))'

But, if I try this:

SELECT main.title, main.URL, main.description, main.city FROM main WHERE (((main.active)=True));

or this:

SELECT main.title, main.URL, main.description, main.city FROM main ORDER BY city;

They both work as expected. How do I combine them effectively?

Also, when I try the select statement below:

SELECT main.title, main.url, main.description, main.city
FROM main
WHERE (((main.category)="educ") AND ((main.category_2)="elem") AND ((main.active)=True));

I get a: Unterminated string constant Error (probably because of the double quotes?)

So how can this one be formatted to work without the quotes? (I have tried just removing them to no avail)

Thank You for your help. I am very happy to have found this resource (even though I couldn't find a thread with a solution to a similar problem)for your first problem, ORDER BY must follow WHERE

for your second problem the string delimiter is the single quote, not the double quote|||Originally posted by r937
for your first problem, ORDER BY must follow WHERE

for your second problem the string delimiter is the single quote, not the double quote

Thank you, first problem solved (Order of operations did the trick)

However, the second problem remains:

rs.Open "SELECT main.title, main.url, main.description, main.city
FROM main
WHERE (((main.category_2)='elem'));", conn%>

When you said the string delimiter is the single quote, I am assuming that means to bracket the elem by single quotes instead of double. The above select statement still gives me a Unterminated string constant error.|||have a look at this: Getting Your Quotes Right In SQL For ASP (http://www.webdevelopersjournal.com/articles/quotes_sql_asp.html)|||Could this be because you are using VBScript which requires end of line continuation markers something more like this? :-

rs.Open "SELECT main.title, main.url, main.description, main.city" _
& " FROM main" _
& " WHERE (((main.category_2)='elem'));", conn%>

another n00b question

Man am I ever being thick this week.

M'kay, I've been banging my head against the wall for the last few hours trying to do what conceptually seems quite easy.

Basically I've got an input variable to an SP (in SQL Express 2005) set as type xml. The XML being passed accross looks like this:

<i:items xmlns:i="http://www.mydn.co.uk/schema/boxcheck/list">

<i:item number="12345" />

<i:item number="12346" />

<i:item number="11223" />

</i:items>

I also have a table which has a field called Id which contains, for now at least, the same values as the @.number attribute in the above XML. All I wish to do is to extract the @.number value from the XML above and use that value to check the Id field in the table for any matches. If a match is found, return the xml field in the table.

I've looked at nodes() and query() and exist() and different structures of XQuery, and have tried other methods using normal SQL select statements along with XQuery, but I just cant get the damn thing to work. Can someone, anyone, for the love of all this is holy, please help me out here? what's the big obvious noisy annoying thing I'm missing, apart from a brain?

Cheers for any replies

See if this helps

declare @.x xml
set @.x='<i:items xmlns:i="http://www.mydn.co.uk/schema/boxcheck/list">
<i:item number="12345" />
<i:item number="12346" />
<i:item number="11223" />
</i:items>'

SELECT X.n.value('@.number','int') as [number]
FROM @.x.nodes('declare default element namespace
"http://www.mydn.co.uk/schema/boxcheck/list";
/items/item') AS X(n)

|||

Cheers for the reply

The problem I've had all along was using the value extracted from the input xml (I did it a different way to you as it happens) to get the associated row from the table with the same id value

any clues?

|||

I'm not totally clear what you're asking. If the code below
doesn't help, try posting some DDL and sample data.

declare @.x xml
set @.x='<i:items xmlns:i="http://www.mydn.co.uk/schema/boxcheck/list">
<i:item number="12345" />
<i:item number="12346" />
<i:item number="11223" />
</i:items>'

SELECT xmlField
FROM mytable
WHERE ID IN (
SELECT X.n.value('@.number','int') as [number]
FROM @.x.nodes('declare default element namespace
"http://www.mydn.co.uk/schema/boxcheck/list";
/items/item') AS X(n)
)

|||Man you a supastar! cheers dude