Showing posts with label folks. Show all posts
Showing posts with label folks. Show all posts

Monday, February 20, 2012

query syntax help

Folks,
Hi, I have the following query:
SELECT cast([\\host\NetItfc(Intel[R] CardType\Bytes Sent/sec] AS float) AS
[BytesSentPerSec] FROM tblHostCapacity001
It returns the following syntax error:
Server: Msg 170, Level 15, State 1, Line 1. Line 1: Incorrect syntax near
'CardType'.
I understand the reasons for the error - i.e. I need to escape the square
brackets around the [R] in the fieldname...
BUT how do I accomplish this escaping of the [ and ] - normally these chars
delimit a space-contained fieldname. Haven't been able to find any solutions
on the web via google.
Appreciate any advice.
Cheers,
Neil Evans-Mudie
-. . .. .-.. / .--. ... -- -. .. -.-. .--. / . ...- .- -.
... -...- -- ..- -.. .. .
e: My@.myorg.com address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w: http://groups.msn.com/TheEvansMudie...new.msnw?&pps=kYou should really consider renaming the column to something sensible
following the rules of indentifiers. As for a short term workaround, use
double quotes (") instead of square brackets.
The actual alternative to escaping square brackets is to add another closing
square bracket after the existing one like:
CREATE TABLE tbl ( [\\host\NetItfc(Intel [R]] CardType\Bytes Sent/sec] INT )
Note the addition of ']' after [R] but not before it.
Anith|||See if this helps:
-- cast([\\host\NetItfc(Intel[R]] CardType\Bytes Sent/sec] AS float)
create table t1 (
[a[b]]c] int
)
go
select
cast([a[b]]c] as varchar) as c1
from
t1
go
drop table t1
go
AMB
"Neil Evans-Mudie" wrote:

> Folks,
> Hi, I have the following query:
> SELECT cast([\\host\NetItfc(Intel[R] CardType\Bytes Sent/sec] AS float) AS
> [BytesSentPerSec] FROM tblHostCapacity001
> It returns the following syntax error:
> Server: Msg 170, Level 15, State 1, Line 1. Line 1: Incorrect syntax near
> 'CardType'.
> I understand the reasons for the error - i.e. I need to escape the square
> brackets around the [R] in the fieldname...
> BUT how do I accomplish this escaping of the [ and ] - normally these char
s
> delimit a space-contained fieldname. Haven't been able to find any solutio
ns
> on the web via google.
> Appreciate any advice.
> Cheers,
> Neil Evans-Mudie
> -. . .. .-.. / .--. ... -- -. .. -.-. .--. / . ...- .- -.
> ... -...- -- ..- -.. .. .
> e: My@.myorg.com address is a spam sink
> If you wish to email me, try neilevans underscore mudie at hotmail dot com
> w: http://groups.msn.com/TheEvansMudie...new.msnw?&pps=k
>
>

Query String using Web Matrix

Hi folks,

I have two tables in one database.

One table has an automatic numbering primary key named ID and is named rfi.

The other table has a field named rfinumber and is named discussion.

Both ID and rfinumber have a datatype of number.

Upon using the querybuilder with Web Matrix, I issue a select command to get all records from the discussion table that have a rfinumber field equal to the ID field in the rfi table.

Problem is that I am getting the entire discussion table when I use the following query:

"SELECT [discussion].* FROM [discussion], [rfi] WHERE ([discussion].[rfinumber] = [rfi].[ID])"

For example

DISCUSSION TABLE
rfinumber
1
1
1
2

RFI TABLE
rfi
1
2

Given the query, I should get a discussion table only listing the rfinumber = 1.

When I run the query from my database package it runs fine??

Any clues?

thanks,
glenn

gmeadows:

Problem is that I am getting the entire discussion table when I use the following query:


That's exactly what I would expect. You are returning a row from discussion matched with each row from rfi where the rfinumber from discussion is the same as the ID from rfi. Why would you expect it only to return those rows where rfinumber = 1? You need to use a parameter of some sort to make that happen.