Query with single quotes using OPENROWSET

I'm trying to pass through a SQL statement to an Oracle database using
OPENROWSET. My problem is that I'm not sure of the exact syntax I need
to use when the SQL statement itself contains single quotes.
Unfortunately, OPENROWSET doesn't allow me to use parameters so I can't
get around the problem by assigning the SQL statement to a parameter of
type varchar or nvarchar as in

SELECT *
FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword',
@.chvSQL)

I tried doubling the single quotes as in

SELECT *
FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword',
'SELECT *
FROM A
WHERE DateCol > To_Date(''2002-12-01'', ''yyyy-mm-dd'')'
)

But that didn't work. Is there a way out of this?

Thanks,

Bill E.
Hollywood, FLI take it back--doubling the single quotes is working fine now. I
don't know why I had trouble before.

Bill

Query to another DB

Hi,
Is anyway to Query to another DB?
I wrote the SQL Syntax on DB A and want to Query the data from DB B.
How can I implement this function?
Thanks for help!
Angiangi
SELECT <columns> FROM DataBase.dbo.Tablename WHERE.......
"angi" <angi@.news.microsoft.com> wrote in message
news:ueozMgIXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Is anyway to Query to another DB?
> I wrote the SQL Syntax on DB A and want to Query the data from DB B.
> How can I implement this function?
> Thanks for help!
> Angi
>|||Thanks, Uri!
"Uri Dimant" <urid@.iscar.co.il> glsD:uGv$wvIXGHA.4620@.TK2MSFTNGP04.phx.gbl...[vbco
l=seagreen]
> angi
> SELECT <columns> FROM DataBase.dbo.Tablename WHERE.......
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:ueozMgIXGHA.1204@.TK2MSFTNGP04.phx.gbl...
>[/vbcol]

Query to another DB

Hi,
Is anyway to Query to another DB?
I wrote the SQL Syntax on DB A and want to Query the data from DB B.
How can I implement this function?
Thanks for help!
Angiangi
SELECT <columns> FROM DataBase.dbo.Tablename WHERE.......
"angi" <angi@.news.microsoft.com> wrote in message
news:ueozMgIXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Is anyway to Query to another DB?
> I wrote the SQL Syntax on DB A and want to Query the data from DB B.
> How can I implement this function?
> Thanks for help!
> Angi
>|||Thanks, Uri!
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:uGv$wvIXGHA.4620@.TK2MSFTNGP04.phx.gbl...
> angi
> SELECT <columns> FROM DataBase.dbo.Tablename WHERE.......
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:ueozMgIXGHA.1204@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Is anyway to Query to another DB?
>> I wrote the SQL Syntax on DB A and want to Query the data from DB B.
>> How can I implement this function?
>> Thanks for help!
>> Angi
>

Query syntax: select parent record, including number of children?

OK, I'm totally blanking on how to get my query to do the right thing. I have two tables, "SurveyQuestion" and "SurveyAnswer" with a one-to-many relationship: a Question has many Answers, and they're hooked up by including a field in Answer that specifies the Question ID.

What I want to select is: all the columns of SurveyQuestion, and a count of how many SurveyAnswer records are associated with the SurveyQuestion. What's the right way to do this?? Should I make a stored proc or something to run the row count? Is there an easier way? I think the complication stems from the fact that I want to return a GROUP column alongside regular records, but I'm not really sure. I'm using MySQL 4.x if that affects your answer.4.x is not specific enough, you must be on at least 4.1 to run this --select Q.foo
, Q.bar
, Q.qux
, ( select count(*)
from SurveyAnswer
where questionID = Q.ID ) as answers
from SurveyQuestion as Q

query syntax problems

I am working in vb6 and am new to database access. I can't seem to figure out the syntax for the following:

rs1.Open "select * from orders where category = '" & button & " and invoice = " & invoice & "' order by guest", db, adOpenStatic, adLockOptimistic

The above statement doesn't work. What am I doing wrong?

JerrybYour embedded quotes were misplaced. The SQL being run is like:

select * from orders where category = 'xxx and invoice = yyy ' order by guest

But you probably meant this:

select * from orders where category = 'xxx' and invoice = 'yyy' order by guest

Try this:

rs1.Open "select * from orders where category = '" & button & "' and invoice = '" & invoice & "' order by guest", db, adOpenStatic, adLockOptimistic

(This is assuming that both button and invoice are strings rather than numbers - for numbers, you don't need the quotes.)

Hint: it's a good idea to print the SQL that is failing to see if it makes sense - e.g.:

Response.Write "select * from orders where category = '" & button & " and invoice = " & invoice & "' order by guest"

query syntax issue

What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?
UNICODE
"Mark" <mark_kurten@.acordia.com> wrote in message news:O7voP$dIGHA.3492@.TK2MSFTNGP09.phx.gbl...
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?
|||From BOL
Unicode strings
Unicode strings have a format similar to character strings but are preceded by an N identifier (N stands for National Language in the SQL-92 standard). The N prefix must be uppercase. For example, 'Michl' is a character constant while N'Michl' is a Unicode constant. Unicode constants are interpreted as Unicode data, and are not evaluated using a code page. Unicode constants do have a collation, which primarily controls comparisons and case sensitivity. Unicode constants are assigned the default collation of the current database, unless the COLLATE clause is used to specify a collation. Unicode data is stored using two bytes per character, as opposed to one byte per character for character data. For more information, see Using Unicode Data.
Unicode string constants support enhanced collations.
"Mark" <mark_kurten@.acordia.com> wrote in message news:O7voP$dIGHA.3492@.TK2MSFTNGP09.phx.gbl...
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?

query syntax issue

What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?UNICODE
"Mark" <mark_kurten@.acordia.com> wrote in message news:O7voP$dIGHA.3492@.TK2M
SFTNGP09.phx.gbl...
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?|||From BOL
Unicode strings
Unicode strings have a format similar to character strings but are preceded
by an N identifier (N stands for National Language in the SQL-92 standard).
The N prefix must be uppercase. For example, 'Michl' is a character constan
t while N'Michl' is a Unicode constant. Unicode constants are interpreted a
s Unicode data, and are not evaluated using a code page. Unicode constants d
o have a collation, which primarily controls comparisons and case sensitivit
y. Unicode constants are assigned the default collation of the current datab
ase, unless the COLLATE clause is used to specify a collation. Unicode data
is stored using two bytes per character, as opposed to one byte per characte
r for character data. For more information, see Using Unicode Data.
Unicode string constants support enhanced collations.
"Mark" <mark_kurten@.acordia.com> wrote in message news:O7voP$dIGHA.3492@.TK2M
SFTNGP09.phx.gbl...
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?

query syntax issue

This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C621B5.E56A3D60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_0008_01C621B5.E56A3D60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
What is the difference between
use [aes50] DBCC SHRINKFILE = (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', = TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_0008_01C621B5.E56A3D60--This is a multi-part message in MIME format.
--=_NextPart_000_000B_01C621B6.39640AE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
UNICODE
"Mark" <mark_kurten@.acordia.com> wrote in message =news:O7voP$dIGHA.3492@.TK2MSFTNGP09.phx.gbl...
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_000B_01C621B6.39640AE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

UNICODE
"Mark" =wrote in message news:O7voP$dIGHA.3492=@.TK2MSFTNGP09.phx.gbl...
What is the difference =between
use [aes50] DBCC SHRINKFILE =(N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE =('aes50_Log', TRUNCATEONLY)
What does the N =represent?

---

--=_NextPart_000_000B_01C621B6.39640AE0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0015_01C621B6.BB0ACED0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
From BOL
Unicode strings
Unicode strings have a format similar to character strings but are =preceded by an N identifier (N stands for National Language in the =SQL-92 standard). The N prefix must be uppercase. For example, ='Mich=E9l' is a character constant while N'Mich=E9l' is a Unicode =constant. Unicode constants are interpreted as Unicode data, and are not =evaluated using a code page. Unicode constants do have a collation, =which primarily controls comparisons and case sensitivity. Unicode =constants are assigned the default collation of the current database, =unless the COLLATE clause is used to specify a collation. Unicode data =is stored using two bytes per character, as opposed to one byte per =character for character data. For more information, see Using Unicode =Data.
Unicode string constants support enhanced collations.
"Mark" <mark_kurten@.acordia.com> wrote in message =news:O7voP$dIGHA.3492@.TK2MSFTNGP09.phx.gbl...
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_0015_01C621B6.BB0ACED0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

From BOL
Unicode strings
Unicode strings have a format similar to character =strings but are preceded by an N identifier (N stands for National Language in the =SQL-92 standard). The N prefix must be uppercase. For example, 'Mich=E9l' is a =character constant while N'Mich=E9l' is a Unicode constant. Unicode constants are interpreted as Unicode data, and are not evaluated using a code page. =Unicode constants do have a collation, which primarily controls comparisons and =case sensitivity. Unicode constants are assigned the default collation of the =current database, unless the COLLATE clause is used to specify a collation. =Unicode data is stored using two bytes per character, as opposed to one byte per =character for character data. For more information, see Using Unicode Data.
Unicode string constants support enhanced =collations.
"Mark" =wrote in message news:O7voP$dIGHA.3492=@.TK2MSFTNGP09.phx.gbl...
What is the difference =between
use [aes50] DBCC SHRINKFILE =(N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE =('aes50_Log', TRUNCATEONLY)
What does the N =represent?

---

--=_NextPart_000_0015_01C621B6.BB0ACED0--

Query Syntax Help

I have a query that I am running out of sql server 2000 that is pulling duplicate records. I can probably figure it out but I am wondering if someone could look at it and point out errors in my syntax and/or structure.

Thanks!

code:------------------------
SELECT p.ParticipantID, pr.RaceID, p.FirstName, p.LastName, pr.Bib, p.Gender, pr.Age,
pr.AgeGrp, p.DOB, p.Address, p.City, p.St, p.Zip, pr.Clyde, pr.WhlChr, pr.RcWlk,
p.Phone, p.Email, reg.ShrtSize, reg.ShrtStyle, reg.WhereReg, reg.DateReg, reg.AmtPd
FROM Participant p INNER JOIN PartReg reg ON p.ParticipantID = reg.ParticipantID JOIN PartRace pr
ON pr.ParticipantID = p.ParticipantID JOIN RaceData rd ON pr.RaceID = rd.RaceID
WHERE (rd.EventID = 45 AND pr.RaceID = reg.RaceID) ORDER BY p.LastName
------------------------what is the error you are getting?|||you have 4 tables, but what are the relationships and their cardinalities?

for example, Participant--PartReg is probably 1--m, but what about the others?

somewhere along the line you have two unrelated relationships giving you a cross join effect|||I am not getting an error, it is just pulling duplicate fields.

In the Participant table, ParticipantID is a Primary Key and in PartRace and PartReg tables it is a Foreign Key. In the RaceData table, RaceID is a Primary Key and in PartRace and PartReg it is a Foreign Key.

Hope this helps~|||Assuming your data is clean, I believe your selection list is misleading.
You are not pulling any colums of "rd" (RaceData) table.
Perhaps there is a 1-n realtion (participants has entered in more than one races ?). Selelect the PK of RaceData along, this may shed some light.


SELECT
p.ParticipantID,
pr.RaceID,
p.FirstName,
p.LastName,
pr.Bib,
p.Gender,
pr.Age,
pr.AgeGrp,
p.DOB,
p.Address,
p.City,
p.St,
p.Zip,
pr.Clyde,
pr.WhlChr,
pr.RcWlk,
p.Phone,
p.Email,
reg.ShrtSize,
reg.ShrtStyle,
reg.WhereReg,
reg.DateReg,
reg.AmtPd,
rd.RaceID
FROM Participant p
INNER JOIN
PartReg reg
ON p.ParticipantID = reg.ParticipantID
JOIN
PartRace pr
ON pr.ParticipantID = p.ParticipantID
JOIN
RaceData rd
ON pr.RaceID = rd.RaceID
WHERE
(
rd.EventID = 45
AND pr.RaceID = reg.RaceID
)
ORDER BY p.LastName|||Addendum: what is the relation/structure concerning "Eventid" / "RaceId" in RaceData ? does one RACE cover multiple EVENTS ?

You are joining via "EventId", but say PK is "RaceID" ?

...not seeing clear yet...|||Each event has one or more races. The EventID in Events is a primary key and in RaceData is a foreign key. The problem is that some people could have registered for multiple races hence they appear more than once in the PartRace and PartReg tables but only once in the Participant table. I am trying to identify all participants for all of the races in a specific event.|||I found the problem. It was an error in my code that was over-writing the raceid field with the most current one. The query was doing exactly what it was supposed to be doing. The data was corrupted but I have resolved it.

THANKS A TON FOR ALL OF YOUR HELP!

Query Syntax help

Hello All,

I have the following table:

CREATE TABLE [dbo].[TBL_NAME] (
[NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STANDARD_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

With values:

insert into tbl_name
values('DAN', 'DANIEL')
insert into tbl_name
values('DANNY', 'DANIEL')
insert into tbl_name
values('DANYY', 'DANIEL')

Question is:

I need want to construct a query which returns all names for a standard
name plus the standard name itself.
e.g.
if name = 'DAN' then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'
ff name = 'DANIEL', then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'

i have the following sql:

declare @.name varchar(50)
select @.name = 'DANIEL'
select standard_name from tbl_name where name = @.name
union
select name from tbl_name where standard_name = (select standard_name
from tbl_name where name = @.name)
union
select name from tbl_name where standard_name = @.name
union
select standard_name from tbl_name where standard_name = @.name

--

declare @.name varchar(50)
select @.name = 'DANNY'
select standard_name from tbl_name where name = @.name
union
select name from tbl_name where standard_name = (select standard_name
from tbl_name where name = @.name)
union
select name from tbl_name where standard_name = @.name
union
select standard_name from tbl_name where standard_name = @.name

--

Both appear to work fine..can anyone see a fault or suggest a cleaner
way to achieve the above ?

Suggestions/pointers appreciated
Thanks in advance1) How many people do you know or have ever heard of that have a name
that need to have CHAR(50)? The USPS allows CHAR(35)

2) Why did you violate common sense and ISO-11179 Standards with the
"tbI-" prefix?

3) Why don't you have a key? Why did you prevent having a key with
NULL_able? Why are you smarter than Dr. Codd?

4) If you knew SQL would this look like this:

CREATE TABLE FirstNames
(first_name VARCHAR (35) NOT NULL
CHECK (first_name = RTRIM(LTRIM(first_name))),
alternate_first_name VARCHAR (35) NOT NULL
CHECK (alternate_first_name = RTRIM(LTRIM(alternate_first_name))),
PRIMARY KEY (first_name, alternate_first_name)
);

>> I need want to construct a query which returns all names for a standard name plus the standard name itself. <<

SELECT first_name, alternate_first_name
FROM FirstNames
WHERE first_name = @.my_guy;|||hharry (paulquigley@.nyc.com) writes:
> CREATE TABLE [dbo].[TBL_NAME] (
> [NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [STANDARD_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> With values:
> insert into tbl_name
> values('DAN', 'DANIEL')
> insert into tbl_name
> values('DANNY', 'DANIEL')
> insert into tbl_name
> values('DANYY', 'DANIEL')
> Question is:
> I need want to construct a query which returns all names for a standard
> name plus the standard name itself.
> e.g.
> if name = 'DAN' then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'
> ff name = 'DANIEL', then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'
>...

If you add a row with (DANIEL, DANIEL), you can write a much simpler query:

insert into tbl_name
values('DAN', 'DANIEL')
insert into tbl_name
values('DANNY', 'DANIEL')
insert into tbl_name
values('DANYY', 'DANIEL')
insert into tbl_name
values('DANIEL', 'DANIEL')
go
DECLARE @.name varchar(50)
SELECT @.name = 'DANIEL'
SELECT name
FROM tbl_name t1
WHERE EXISTS (SELECT name, standard_name
FROM tbl_name t2
WHERE t2.standard_name = t1.standard_name
AND t2.name = @.name)
go

If this change is not feasible or possible, you could write:

SELECT t1.name
FROM (SELECT name, standard_name
FROM tbl_name
UNION
SELECT standard_name, standard_name
FROM tbl_name) t1
WHERE EXISTS (SELECT name, standard_name
FROM (SELECT name, standard_name
FROM tbl_name
UNION
SELECT standard_name, standard_name
FROM tbl_name) t2
WHERE t2.standard_name = t1.standard_name
AND t2.name = @.name)

But that's certainly a little more complex, and whether it's cleaner
your current query is a matter of taste.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||--CELKO-- (jcelko212@.earthlink.net) writes:
> 1) How many people do you know or have ever heard of that have a name
> that need to have CHAR(50)? The USPS allows CHAR(35)
> 2) Why did you violate common sense and ISO-11179 Standards with the
> "tbI-" prefix?
> 3) Why don't you have a key? Why did you prevent having a key with
> NULL_able? Why are you smarter than Dr. Codd?
> 4) If you knew SQL would this look like this:
> CREATE TABLE FirstNames
> (first_name VARCHAR (35) NOT NULL
> CHECK (first_name = RTRIM(LTRIM(first_name))),
> alternate_first_name VARCHAR (35) NOT NULL
> CHECK (alternate_first_name = RTRIM(LTRIM(alternate_first_name))),
> PRIMARY KEY (first_name, alternate_first_name)
> );
>>> I need want to construct a query which returns all names for a standard
name plus the standard name itself. <<
> SELECT first_name, alternate_first_name
> FROM FirstNames
> WHERE first_name = @.my_guy;

I don't know don't if "hharry" is smarter than Codd, but he is
obviously smarter than you. After all, he was able to write a query
that solved his problem - you weren't. (Since hharry supplied tables
and insert statements, you could have tested.)

As for your points 1-3, they are completely irrelevant and not the least
helpful. Just impolite and unfriendly. My guess is that hharry's real
business problem is different, and the table he posted he just a
throwaway table to demonstrate the SQL problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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 syntax help

I am trying to write a query that returns all suppliers within a given range
that either do not have any insurance (appear only in Suppliers table) or
Suppliers where the insurance has expired from a given date
Eg 4 Suppliers
Supplier1 - no insurance
Supplier2 - insurance expired
Supplier3 - insurance current
Supplier4 - not in range
The Supplier range is 'where AccRef like '^SC%'
The Expiry Date is less than or equal to '20071130'
The result set would include Supplier1 because it is not in the
InsuranceDetails table at all and Supplier2 because the insurance has
expired.
How can I do this in one query?
I have included some SQL for your info
Thanks
A
CREATE TABLE [dbo].[Suppliers](
[AccRef] [nvarchar](8) NOT NULL,
[AccName] [nvarchar](30) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[InsuranceDetails](
[AccRef] [nvarchar](8) NOT NULL,
[DateExpire] [datetime] NOT NULL
) ON [PRIMARY]
INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '^SC100' As Expr1, 'Supplier1' as Expr2
INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '^SC200' AS Expr1, 'Supplier2' as Expr2
INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '^SC300' AS Expr1, 'Supplier3' as Expr2
INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '10000' AS Expr1, 'Supplier4' as Expr2
INSERT INTO dbo.InsuranceDetails ([AccRef], [DateExpire])SELECT '^SC300' AS
Expr1, '20080331' as Expr2
INSERT INTO dbo.InsuranceDetails ([AccRef], [DateExpire])SELECT '^SC200' AS
Expr1, '20071101' as Expr2
There are a variety of ways you can do this. Here is one:
SELECT s1.accref, s1.AccName
FROM suppliers s1
WHERE s1.accref LIKE '^SC%'
AND COALESCE(
( SELECT i1.dateexpire
FROM InsuranceDetails i1
WHERE i1.accref = s1.accref ), '19000101' )
<= '20071130' ;
Anith

Query syntax for count comparison

I have 2 tables, with 2 columns each: Seq# and Client.
I need the syntax of a query to compare count(Client) by Seq#, between Table1 and Table2.
Something like this (although this doesn't work):
Select Table1.Seq#
from Table1 inner join Table2 on Table1.Seq# = Table2.Seq#
where count (table1.Client) = count (table2.Client)
Is this possible? If so, please provide correct syntax.
Thank you!
It would be easier for us to understand your requirement, if you posted some
sample data to work with, and the expected resultset.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:2DFCF9B8-D4C6-4330-932A-678B0687FC86@.microsoft.com...
I have 2 tables, with 2 columns each: Seq# and Client.
I need the syntax of a query to compare count(Client) by Seq#, between
Table1 and Table2.
Something like this (although this doesn't work):
Select Table1.Seq#
from Table1 inner join Table2 on Table1.Seq# = Table2.Seq#
where count (table1.Client) = count (table2.Client)
Is this possible? If so, please provide correct syntax.
Thank you!
|||Take a look at FULL OUTER JOIN in SQL Server Books Online. In simple cases
similar to yours, one can use it to compare data across different tables.
Anith

Query Syntax Error

This thing is giving me 'Incorrect syntax near the keyword declare'. What's the correct form?

declare @.Query varchar(8000)

set @.Query = 'insert into PortfolioStock (PortfolioID, StockSymbol) select ' + cast(@.Portfolio as varchar) + ', StockSymbol from PortfolioStock where StockSymbol in (''' + replace(ltrim(rtrim(@.Textbox)), ' ', ''', ''') + ''')'

exec @.Queryexec(@.Query)

Query Syntax - Right

I have a column that has dollar amounts with the dollar sign present (i.e. $500), due to bulk insert.
I need to convert the values in that column to Float, but I get error converting varchar to float, due to the $.
I can't do something like right(amount, 3), because the amounts aren't consistent ($500, $5000, $500000) . . .
Is there a way to select right minus one? or left 2 and on?
Help appreciated.
Thanks!
Ysandre,
You need to convert it to MONEY first, then to FLOAT:
SELECT CONVERT(FLOAT, CONVERT(MONEY, '$5000.00'))
That said, why are you using FLOAT? I recommend you use DECIMAL instead;
FLOAT is an inexact type and you could end up with rounding errors. That is
not the case with DECIMAL.
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:164A2AA4-B9EA-4ABA-8821-0E6994ADE5CC@.microsoft.com...
> I have a column that has dollar amounts with the dollar sign present (i.e.
$500), due to bulk insert.
> I need to convert the values in that column to Float, but I get error
converting varchar to float, due to the $.
> I can't do something like right(amount, 3), because the amounts aren't
consistent ($500, $5000, $500000) . . .
> Is there a way to select right minus one? or left 2 and on?
> Help appreciated.
> Thanks!
>
|||Thank you adam! that worked!
Our developer is the one who made everything float, I don't know why I just work with it
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBA
"Adam Machanic" wrote:

> Ysandre,
> You need to convert it to MONEY first, then to FLOAT:
> SELECT CONVERT(FLOAT, CONVERT(MONEY, '$5000.00'))
> That said, why are you using FLOAT? I recommend you use DECIMAL instead;
> FLOAT is an inexact type and you could end up with rounding errors. That is
> not the case with DECIMAL.
>
> "Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
> news:164A2AA4-B9EA-4ABA-8821-0E6994ADE5CC@.microsoft.com...
> $500), due to bulk insert.
> converting varchar to float, due to the $.
> consistent ($500, $5000, $500000) . . .
>
>

Query syntax

Hi all! I am new here and I'm also new to SQL.. I hope somebody could help me regarding my problem.

I don't know if this is possible but I would like to have a code that

can integrate data from 3 tables. The names of my tables are Savings,

Loans and Insurances. Their common field is the MemberID. One member

could have zero or more Savings Accounts. At the same time, a member

could also have one or more accounts on Loans or Insurances.

How can I get the data that would appear like this:

MemberID - Savings Account - Loan Account - Insurance Account

0001

-

S0001

- L0002

- I0001

-

S0003

-

L0005

-

0002

-

S0012

-

-

0003

-

S0004

-

- I0002

-

-

- I0003
I'm using MS SQL Server 2005.

Hope you guys could help me with this. Thanks a lot, in advance!

what are the blank spaces for?

|||Thanks for replying...

I just would like to appear it that way, all accounts will be grouped

by members. I cannot use a subquery to return multiple values, right?

So when I implement it using UNION, it will somehow appear this way:

MemberID - Savings Account - Loan Account - Insurance Account

0001

-

S0001

-

-

0001

-

S0003

-

-

0001

-

-

L0002

-

0001

-

-

L0005

-

0001

-

-

- I0001

0002

-

S0012

-

-

0003

-

S0004

-

-

0003 -

-

- I0002

0003

-

-

- I0003
Or when I code it using the simplest Select query, all the records of

each account will be returned for each account of the member (or the

records will repeat for every account of the member).

Is there a tool or means in the 2005 version of MS SQL to do a query just like the one on the top?

Thanks a lot!|||

If there is a table that has all of the memberIds, it is a lot easier:

select memberId, savingAccount.number, loanAccount.number, insuranceAccount.number
from member
left outer join savingAccount
on member.memberId = savingAccount.memberId
left outer join loanAccount
on member.memberId = loanAccount.memberId
left outer join insuranceAccount
on member.memberId = insuranceAccount.memberId

If not (and there really really should be :) you can do something like:

select coalesce(savingAccount.memberId, loanAccount.memberId, insuranceAccount.memberId) as memberId,
savingAccount.number, loanAccount.number, insuranceAccount.number
from savingAccount
full outer join loanAccount
on savingAccount.memberId = loanAccount.memberId
full outer join insuranceAccount
on loanAccount.memberId = insuranceAccount.memberId

And this should do it too.

|||Yes, there is another table called Member but if I use the code above

with left join operator, the result set will be like the following:

(I will use only two tables for simplicity, Savings and Loans with memberId as their common column)

(and, suppose, one member has 2 savings number and 3 loans number)

Member.MemberID - Savings.No - Loans.No

M001

-

S001

- L001

M001

-

S001 -

L002

M001

- S001

-

L003

M001

-

S002

- L001

M001

- S002

- L002

M001

- S002

-

L003
If I desire it to look this way:

Member.MemberID - Savings.No - Loans.No

M001

- S001

-

L001

M001

- S002

-

L002

M001

- NULL

- L003

would it be possible?

If the code above with full outer join is used, the result set is still repeating values of each savings and loans.

I really appreciate your efforts. I hope I am not bothering you much.

Thanks!|||

Hi EDeric, you might have that requirement. but yor desired output is giving another relationship between Savings and Loans. Then what is the relationship between Savings and Loans?. is there any relationship between them?

|||Hi Uma!

It's only through MemberId column that the Savings and Loans have a relationship. Each table has that column.

Query Syntax

I am looking to find records that share the same Policy_Desc but have
different Policy_ids. I know there are records that fit this criteria but fo
r
some reason this query returns zero rows.
SELECT Policy_Desc, Policy_id
FROM Policy_Table AS Outside
WHERE EXISTS
(
SELECT 1 FROM Policy_Table AS Inside
WHERE Inside.Policy_Desc = Outside.Policy_Desc
AND Inside.Policy_id <> Outside.Policy_id
)Maybe this will do it
SELECT t1.Policy_Desc, t1.Policy_id
FROM Policy_Table as t1 INNER JOIN Policy_Table as t2
ON t1.Policy_Desc = t2.Policy_Desc AND t1.Policy_ID=t2.Policy_id <>
t1.Policy_id
Adi|||A few things:
Check if the database collation is case sensitive. If so, then convert the
desc to upper case in both sides and check.
If there are leading blank spaces, you may want to use LTRIM.
If none of the above works, then we can break our heads.|||Also post ddl and sample data.|||Can Policy_Desc be NULL? If so try this
SELECT Policy_Desc, Policy_id
FROM Policy_Table AS Outside
WHERE EXISTS
(
SELECT 1 FROM Policy_Table AS Inside
WHERE (Inside.Policy_Desc = Outside.Policy_Desc
OR (Inside.Policy_Desc is null and Outside.Policy_Desc is
null))
AND Inside.Policy_id <> Outside.Policy_id
)
ORDER BY Policy_Desc, Policy_id|||And try if you are getting any result for this.
SELECT Policy_Desc, count(*)
FROM Policy_Table
group by policy_desc
having count(*) > 1|||> I know there are records that fit this criteria but for
> some reason this query returns zero rows
There is nothing wrong with your query, barring the NULLs and case
differences mentioned by the others. I would expect the query below to
return data if you actually have duplicates.
SELECT Policy_Desc, COUNT(*)
FROM Policy_Table
GROUP BY Policy_Desc
HAVING COUNT(*) > 1
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:B0A11403-3790-44FD-9C7F-976F72846942@.microsoft.com...
>I am looking to find records that share the same Policy_Desc but have
> different Policy_ids. I know there are records that fit this criteria but
> for
> some reason this query returns zero rows.
> SELECT Policy_Desc, Policy_id
> FROM Policy_Table AS Outside
> WHERE EXISTS
> (
> SELECT 1 FROM Policy_Table AS Inside
> WHERE Inside.Policy_Desc = Outside.Policy_Desc
> AND Inside.Policy_id <> Outside.Policy_id
> )

Query Syntax

I like some help on the syntax for a query: I have a table that has about 50
columns. I need to have 30 of the columns in a query using a select
statement. Since many of the columns are prefixed 'cc', I'd like to use a
wildcard, instead of typing out each column name. Is this possible? For
example, select cc* from table, or select cc% from table.
Thanks
steve.
No. Either select * from , or specify column names. If this is a repeated
work, create a view with the 30 columns and you can select * from the view.
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:utISCBGkEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I like some help on the syntax for a query: I have a table that has about
50
> columns. I need to have 30 of the columns in a query using a select
> statement. Since many of the columns are prefixed 'cc', I'd like to use a
> wildcard, instead of typing out each column name. Is this possible? For
> example, select cc* from table, or select cc% from table.
> Thanks
> steve.
>
|||> No. Either select * from
Ugh, in a production query? IMHO, this is not good advice at all, not only
because of the SELECT * alone, but also because he's going to retrieve 20
columns of data he has no interest in.
How about:
SELECT COLUMN_NAME + ', '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='table name'
AND COLUMN_NAME LIKE 'cc%'
Then you can take that result from the results pane, and paste it into your
query (removing the trailing comma).
http://www.aspfaq.com/
(Reverse address to reply.)
|||molsonexpert wrote:
> I like some help on the syntax for a query: I have a table that has
> about 50 columns. I need to have 30 of the columns in a query using a
> select statement. Since many of the columns are prefixed 'cc', I'd
> like to use a wildcard, instead of typing out each column name. Is
> this possible? For example, select cc* from table, or select cc% from
> table.
> Thanks
> steve.
Or use Query Analyzer. Right click on the table and pick the option to
script a SELECT to a new window and trim those results.
David G.
|||Perfect. Thanks, Aaron. By the way, can anyone suggest a good book for SQL,
particularly SQL scripting? SQL help and BOL aren't bad, but I'd like a
reference manual as well. Thanks again.
steve.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OmqxikGkEHA.632@.TK2MSFTNGP12.phx.gbl...
> Ugh, in a production query? IMHO, this is not good advice at all, not
only
> because of the SELECT * alone, but also because he's going to retrieve 20
> columns of data he has no interest in.
> How about:
> SELECT COLUMN_NAME + ', '
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME='table name'
> AND COLUMN_NAME LIKE 'cc%'
> Then you can take that result from the results pane, and paste it into
your
> query (removing the trailing comma).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
|||http://www.aspfaq.com/2423
http://www.aspfaq.com/
(Reverse address to reply.)
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:eBp8JVPkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Perfect. Thanks, Aaron. By the way, can anyone suggest a good book for
SQL,[vbcol=seagreen]
> particularly SQL scripting? SQL help and BOL aren't bad, but I'd like a
> reference manual as well. Thanks again.
> steve.
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OmqxikGkEHA.632@.TK2MSFTNGP12.phx.gbl...
> only
20
> your
>

Query Syntax

I like some help on the syntax for a query: I have a table that has about 50
columns. I need to have 30 of the columns in a query using a select
statement. Since many of the columns are prefixed 'cc', I'd like to use a
wildcard, instead of typing out each column name. Is this possible? For
example, select cc* from table, or select cc% from table.
Thanks
steve.No. Either select * from , or specify column names. If this is a repeated
work, create a view with the 30 columns and you can select * from the view.
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:utISCBGkEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I like some help on the syntax for a query: I have a table that has about
50
> columns. I need to have 30 of the columns in a query using a select
> statement. Since many of the columns are prefixed 'cc', I'd like to use a
> wildcard, instead of typing out each column name. Is this possible? For
> example, select cc* from table, or select cc% from table.
> Thanks
> steve.
>|||> No. Either select * from
Ugh, in a production query? IMHO, this is not good advice at all, not only
because of the SELECT * alone, but also because he's going to retrieve 20
columns of data he has no interest in.
How about:
SELECT COLUMN_NAME + ', '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='table name'
AND COLUMN_NAME LIKE 'cc%'
Then you can take that result from the results pane, and paste it into your
query (removing the trailing comma).
--
http://www.aspfaq.com/
(Reverse address to reply.)|||molsonexpert wrote:
> I like some help on the syntax for a query: I have a table that has
> about 50 columns. I need to have 30 of the columns in a query using a
> select statement. Since many of the columns are prefixed 'cc', I'd
> like to use a wildcard, instead of typing out each column name. Is
> this possible? For example, select cc* from table, or select cc% from
> table.
> Thanks
> steve.
Or use Query Analyzer. Right click on the table and pick the option to
script a SELECT to a new window and trim those results.
--
David G.|||Perfect. Thanks, Aaron. By the way, can anyone suggest a good book for SQL,
particularly SQL scripting? SQL help and BOL aren't bad, but I'd like a
reference manual as well. Thanks again.
steve.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OmqxikGkEHA.632@.TK2MSFTNGP12.phx.gbl...
> > No. Either select * from
> Ugh, in a production query? IMHO, this is not good advice at all, not
only
> because of the SELECT * alone, but also because he's going to retrieve 20
> columns of data he has no interest in.
> How about:
> SELECT COLUMN_NAME + ', '
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME='table name'
> AND COLUMN_NAME LIKE 'cc%'
> Then you can take that result from the results pane, and paste it into
your
> query (removing the trailing comma).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||http://www.aspfaq.com/2423
--
http://www.aspfaq.com/
(Reverse address to reply.)
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:eBp8JVPkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Perfect. Thanks, Aaron. By the way, can anyone suggest a good book for
SQL,
> particularly SQL scripting? SQL help and BOL aren't bad, but I'd like a
> reference manual as well. Thanks again.
> steve.
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OmqxikGkEHA.632@.TK2MSFTNGP12.phx.gbl...
> > > No. Either select * from
> >
> > Ugh, in a production query? IMHO, this is not good advice at all, not
> only
> > because of the SELECT * alone, but also because he's going to retrieve
20
> > columns of data he has no interest in.
> >
> > How about:
> >
> > SELECT COLUMN_NAME + ', '
> > FROM INFORMATION_SCHEMA.COLUMNS
> > WHERE TABLE_NAME='table name'
> > AND COLUMN_NAME LIKE 'cc%'
> >
> > Then you can take that result from the results pane, and paste it into
> your
> > query (removing the trailing comma).
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
>

Query Syntax

I like some help on the syntax for a query: I have a table that has about 50
columns. I need to have 30 of the columns in a query using a select
statement. Since many of the columns are prefixed 'cc', I'd like to use a
wildcard, instead of typing out each column name. Is this possible? For
example, select cc* from table, or select cc% from table.
Thanks
steve.No. Either select * from , or specify column names. If this is a repeated
work, create a view with the 30 columns and you can select * from the view.
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:utISCBGkEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I like some help on the syntax for a query: I have a table that has about
50
> columns. I need to have 30 of the columns in a query using a select
> statement. Since many of the columns are prefixed 'cc', I'd like to use a
> wildcard, instead of typing out each column name. Is this possible? For
> example, select cc* from table, or select cc% from table.
> Thanks
> steve.
>|||> No. Either select * from
Ugh, in a production query? IMHO, this is not good advice at all, not only
because of the SELECT * alone, but also because he's going to retrieve 20
columns of data he has no interest in.
How about :
SELECT COLUMN_NAME + ', '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='table name'
AND COLUMN_NAME LIKE 'cc%'
Then you can take that result from the results pane, and paste it into your
query (removing the trailing comma).
http://www.aspfaq.com/
(Reverse address to reply.)|||molsonexpert wrote:
> I like some help on the syntax for a query: I have a table that has
> about 50 columns. I need to have 30 of the columns in a query using a
> select statement. Since many of the columns are prefixed 'cc', I'd
> like to use a wildcard, instead of typing out each column name. Is
> this possible? For example, select cc* from table, or select cc% from
> table.
> Thanks
> steve.
Or use Query Analyzer. Right click on the table and pick the option to
script a SELECT to a new window and trim those results.
David G.|||Perfect. Thanks, Aaron. By the way, can anyone suggest a good book for SQL,
particularly SQL scripting? SQL help and BOL aren't bad, but I'd like a
reference manual as well. Thanks again.
steve.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OmqxikGkEHA.632@.TK2MSFTNGP12.phx.gbl...
> Ugh, in a production query? IMHO, this is not good advice at all, not
only
> because of the SELECT * alone, but also because he's going to retrieve 20
> columns of data he has no interest in.
> How about :
> SELECT COLUMN_NAME + ', '
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME='table name'
> AND COLUMN_NAME LIKE 'cc%'
> Then you can take that result from the results pane, and paste it into
your
> query (removing the trailing comma).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||http://www.aspfaq.com/2423
http://www.aspfaq.com/
(Reverse address to reply.)
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:eBp8JVPkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Perfect. Thanks, Aaron. By the way, can anyone suggest a good book for
SQL,
> particularly SQL scripting? SQL help and BOL aren't bad, but I'd like a
> reference manual as well. Thanks again.
> steve.
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OmqxikGkEHA.632@.TK2MSFTNGP12.phx.gbl...
> only
20[vbcol=seagreen]
> your
>