Query Woes!

Query Help - Please
I have a table:
CREATE TABLE [dbo].[tblStudentOffers] (
[stud_no] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[crse_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[subj_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[adms_offer_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[adms_elmnt_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[crse_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[subj_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[start_study_date] [datetime] NULL ,
[comp_study_date] [datetime] NULL
) ON [PRIMARY]
GO
With Data:
stud_no,crse_offer_code,subj_offer_code,
adms_offer_code,adms_elmnt_code,crse
_code,subj_code,start_study_date,comp_st
udy_date
1,10,40,40,ELEMENT1,COURSE1,ELEMENT1,200
4-10-01,2004-12-31
1,10,41,41,ELEMENT2,COURSE1,ELEMENT2,200
4-10-01,2004-12-31
1,10,42,42,ELEMENT3,COURSE1,ELEMENT3,200
5-02-01,2005-03-31
1,30,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
1,30,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
1,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
2,40,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
2,40,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
2,40,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
3,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-02-01,2005-03-31
I need to provide a list of new course enrolments for the month of March? If
providing new student date range of 2005-03-01 to 2005-03-31 the query would
need to return the following:
stud_no,crse_offer_code,crse_code,start_
study_date
1,30,COURSE3,2005-03-01
2,40,COURSE3,2005-03-01
Have the following query which eliminates student 1 and only return student
2. Am now really stumped how to achieve this. HEEEEEEELPP
select distinct stud_no,crse_offer_code,crse_code,start_
study_date
from tblStudentOffers
where start_study_date BETWEEN CONVERT(DATETIME, '2005-03-01', 102) AND
CONVERT(DATETIME, '2005-03-31', 102)
AND stud_no not in
(SELECT stud_no FROM tblStudentOffers
WHERE start_study_date < CONVERT(DATETIME, '2005-03-01', 102))
ORDER BY crse_code, stud_family_name
Thanks
RosscoMy best guess is this:
select stud_no, crse_offer_code, crse_code, start_study_date
from tblStudentOffers as SO1
where not exists (
select * from tblStudentOffers as SO2
where SO2.stud_no = SO1.stud_no
and SO2.start_study_date <= SO1.start_study_date
and SO2.crse_offer_code = SO1.crse_offer_code
and SO2.adms_elmnt_code < SO1.adms_elmnt_code
)
where start_study_date >= '20050301'
and start_study_date <= '20050401'
The parts of the criteria that get the specific single row you want
for a particular student probably have to be adjusted.
Steve Kass
Drew University
Rossco wrote:

>Query Help - Please
>I have a table:
>CREATE TABLE [dbo].[tblStudentOffers] (
> [stud_no] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [crse_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
> [subj_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
> [adms_offer_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [adms_elmnt_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [crse_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [subj_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [start_study_date] [datetime] NULL ,
> [comp_study_date] [datetime] NULL
> ) ON [PRIMARY]
>GO
>
>With Data:
> stud_no,crse_offer_code,subj_offer_code,
adms_offer_code,adms_elmnt_code,crs
e_code,subj_code,start_study_date,comp_s
tudy_date
> 1,10,40,40,ELEMENT1,COURSE1,ELEMENT1,200
4-10-01,2004-12-31
> 1,10,41,41,ELEMENT2,COURSE1,ELEMENT2,200
4-10-01,2004-12-31
> 1,10,42,42,ELEMENT3,COURSE1,ELEMENT3,200
5-02-01,2005-03-31
> 1,30,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
> 1,30,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
> 1,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
> 2,40,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
> 2,40,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
> 2,40,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
> 3,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-02-01,2005-03-31
>I need to provide a list of new course enrolments for the month of March? I
f
>providing new student date range of 2005-03-01 to 2005-03-31 the query woul
d
>need to return the following:
> stud_no,crse_offer_code,crse_code,start_
study_date
>1,30,COURSE3,2005-03-01
>2,40,COURSE3,2005-03-01
>Have the following query which eliminates student 1 and only return student
>2. Am now really stumped how to achieve this. HEEEEEEELPP
>select distinct stud_no,crse_offer_code,crse_code,start_
study_date
>from tblStudentOffers
>where start_study_date BETWEEN CONVERT(DATETIME, '2005-03-01', 102) AND
>CONVERT(DATETIME, '2005-03-31', 102)
>AND stud_no not in
>(SELECT stud_no FROM tblStudentOffers
>WHERE start_study_date < CONVERT(DATETIME, '2005-03-01', 102))
>ORDER BY crse_code, stud_family_name
>Thanks
>Rossco
>|||Your blood is worth bottling! Thank you it works perfectly!
Cheers
"Steve Kass" wrote:

> My best guess is this:
> select stud_no, crse_offer_code, crse_code, start_study_date
> from tblStudentOffers as SO1
> where not exists (
> select * from tblStudentOffers as SO2
> where SO2.stud_no = SO1.stud_no
> and SO2.start_study_date <= SO1.start_study_date
> and SO2.crse_offer_code = SO1.crse_offer_code
> and SO2.adms_elmnt_code < SO1.adms_elmnt_code
> )
> where start_study_date >= '20050301'
> and start_study_date <= '20050401'
> The parts of the criteria that get the specific single row you want
> for a particular student probably have to be adjusted.
> Steve Kass
> Drew University
> Rossco wrote:
>
>|||Rossco wrote:
> Your blood is worth bottling! Thank you it works perfectly!
>
I think Steve is realy smart. Maybe even groovy. But I don't want his
blood. Angelina and Billy Bob ruined the whole bottled blood thing for
me.
David Gugick
Imceda Software
www.imceda.com|||Black pudding is quite nice.
http://www.g4cio.demon.co.uk/bpudding/pudding.htm
"David Gugick" wrote:

> Rossco wrote:
> I think Steve is realy smart. Maybe even groovy. But I don't want his
> blood. Angelina and Billy Bob ruined the whole bottled blood thing for
> me.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||In my neck of the woods it's called "boudin noir".
Although I find this sounds better than "blood pouding",
I still wouldn't touch it with... well, with anything.
"Peter 'Not Peter The Spate' Nolan"
<PeterNotPeterTheSpateNolan@.discussions.microsoft.com> wrote in message
news:2CC7355D-0E84-46A5-854E-6A5D34499C9B@.microsoft.com...
> Black pudding is quite nice.
> http://www.g4cio.demon.co.uk/bpudding/pudding.htm
>
> "David Gugick" wrote:
>sql

Query with CASE and NULL values

Hi, please, take a look to this query:

declare @.IDCliente int
declare @.Cliente varchar(50)
declare @.IDUsuario int
declare @.IDUsuarioAlta int

set @.IDcliente = 0
set @.Cliente = ''
set @.IDUsuario = 0
set @.IDUsuarioAlta = 0

select * from cliente
where
(IDUsuario = CASE @.IDUsuario WHEN 0 THEN IDUsuario ELSE @.IDUsuario END or idusuario is null)
AND (IDUsuarioAlta = CASE @.IDUsuarioAlta WHEN 0 THEN IDUsuarioAlta ELSE @.IDUsuarioAlta END or idusuarioalta is null)
AND idCliente = CASE @.idCliente WHEN 0 THEN idCliente ELSE @.idCliente END
AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

Cliente
IDCliente Cliente IDUsusario IDUsuarioAlta
1 Esteban 1 2
2 Jose 3 1
3 Mario 2 NULL
4 Pedro NULL 2
5 NULL 1 2

Its work fine, except for the NULL values. What can I do to fix it ?

thanks

Here you go:

Code Snippet

select IDCliente, Cliente,

case when IDUsusario is null then '' --or whatever you want in place of null

else IDUsusario

end as IDUsusario,

case when IDUsuarioAlta is null then '' -- same thing here

else IDUsuarioAlta

end as IDUsuarioAlta

from cliente

where

(IDUsuario = CASE @.IDUsuario WHEN 0 THEN IDUsuario ELSE @.IDUsuario END or idusuario is null)

AND (IDUsuarioAlta = CASE @.IDUsuarioAlta WHEN 0 THEN IDUsuarioAlta ELSE @.IDUsuarioAlta END or idusuarioalta is null)

AND idCliente = CASE @.idCliente WHEN 0 THEN idCliente ELSE @.idCliente END

AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

|||

I'm sorry, I think I didn't explain my self.

The problem is in the WHERE part, No in the SELECT.

When @.IDUsuario has a value, then the query return the record with the NULL value, and that is not correct. If I take off or idusuario is null

then the record with de NULL value is never return.

thanks and sorry my english !.

|||

AH, gotcha.

How about this then:

Code Snippet

select *

from cliente

where

(IDUsuario = CASE @.IDUsuario WHEN 0 THEN IDUsuario

ELSE @.IDUsuario

END

or (idusuario is null and @.IDUsuario = 0) )

AND (IDUsuarioAlta = CASE @.IDUsuarioAlta WHEN 0 THEN IDUsuarioAlta

ELSE @.IDUsuarioAlta

END

or (idusuarioalta is null and @.IDUsuarioAlta = 0) )

AND (idCliente = CASE @.idCliente WHEN 0 THEN idCliente ELSE @.idCliente END

or (idCliente is null and @.idCliente = 0) )

AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

|||

This might be a little cleaner:

Code Snippet

select *

from cliente

where

((@.IDUsuario <> 0 and idusuario = @.IDUsuario ) or

(@.IDUsuario = 0))

AND ((@.IDUsuarioAlta <> 0 and IDUsuarioAlta = @.IDUsuarioAlta ) or

(@.IDUsuarioAlta = 0))

AND ((@.idCliente <> 0 and idCliente = @.idCliente ) or

(@.idCliente = 0))

AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

|||*** !, it work perfect, i think you know this !! like we say in Argentina: "Sos Groso !!"..... is like say You are Big !... I think so..sql

Query Tuning

Hi,
I have a query that selects 4 fields. One is of type Varchar(500). When I
execute the query, the response is about 9 seconds (very slow). When I
comment out the varchar field, it returns in less than 1 second.
It took me a while to figure out that it's not a missing index, i can't
figure this one out.
please advise.
rafaelHow many records are being returned?
"Rafael Chemtob" wrote:

> Hi,
> I have a query that selects 4 fields. One is of type Varchar(500). When
I
> execute the query, the response is about 9 seconds (very slow). When I
> comment out the varchar field, it returns in less than 1 second.
> It took me a while to figure out that it's not a missing index, i can't
> figure this one out.
> please advise.
> rafael
>
>|||10
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
> How many records are being returned?
> "Rafael Chemtob" wrote:
>
When I|||> How many records are being returned?
..And what is the average length of the data in those rows?
Thomas
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
> How many records are being returned?
> "Rafael Chemtob" wrote:
>|||ok, sorry for not being very detailed.
4 fields
id_rating INT
summary VARCHAR(500)
dt_rating smalldatetime
id_user INT
these are the 4 fields. The record count that's returned is 11 rows.
Hope that helps
thanks
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> ..And what is the average length of the data in those rows?
>
> Thomas
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
When I
>|||Is the performance discrepancy consistent? i.e., have you tested this a
number of times? How many records are in the table?,
and finally, is there an index on the table that contains all the other
three columns from the table, but not the varchar(500) column?
Also, please post the DDL for the tables, and the actual Query.
Charly
"Rafael Chemtob" wrote:

> ok, sorry for not being very detailed.
> 4 fields
> id_rating INT
> summary VARCHAR(500)
> dt_rating smalldatetime
> id_user INT
> these are the 4 fields. The record count that's returned is 11 rows.
> Hope that helps
> thanks
>
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> When I
>
>|||I mean, is there an index on the table which includes columns
(id_rating, dt_rating, id_user), but not Column summary ?
"Rafael Chemtob" wrote:

> ok, sorry for not being very detailed.
> 4 fields
> id_rating INT
> summary VARCHAR(500)
> dt_rating smalldatetime
> id_user INT
> these are the 4 fields. The record count that's returned is 11 rows.
> Hope that helps
> thanks
>
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> When I
>
>|||I query the table using id_rating (which is the PK).
and this is consistent. I comment out the varchar field and i get the
results MUCH quicker.
rafael
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:456CEB9F-6A05-464C-9EE5-93F4FF0B7DB3@.microsoft.com...
> I mean, is there an index on the table which includes columns
> (id_rating, dt_rating, id_user), but not Column summary ?
>
> "Rafael Chemtob" wrote:
>
When I
can't|||As I asked above, one possible reason for this is if there's an index that
includes the columns (id_rating, dt_rating, id_user), but NOT the summary
column. If that were the case, the query processor could use the index alon
e
for the query without Summary, but would be forced to do a table scan when
you include summary.. Is there such an index?
"Rafael Chemtob" wrote:

> I query the table using id_rating (which is the PK).
> and this is consistent. I comment out the varchar field and i get the
> results MUCH quicker.
> rafael
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:456CEB9F-6A05-464C-9EE5-93F4FF0B7DB3@.microsoft.com...
> When I
> can't
>
>|||Hi Rafael,
For performance questions like these, it is very important to post all
relevant DDL (so including indexes, constraints, etc.) and the exact
query.
So just a wild guess for now: make sure you have a clustered index on
the table. If the table does not have a clustered index, and you delete
many rows, then querying the table can become very slow.
HTH,
Gert-Jan
Rafael Chemtob wrote:
> Hi,
> I have a query that selects 4 fields. One is of type Varchar(500). When
I
> execute the query, the response is about 9 seconds (very slow). When I
> comment out the varchar field, it returns in less than 1 second.
> It took me a while to figure out that it's not a missing index, i can't
> figure this one out.
> please advise.
> rafael

query to show duplicates

mytable fld1 int primkey
fld2 varchar(20),
fld3 varchar(20)
From the definition of the above table, how do i do i modify my below query to only select the rows with duplicates in fld2. I know a groupby with a having count will display the duplicates for a given field, but i want my query to see all the fields and rows that are duplicates.
select fld1, fld2, fld3 from mytable
SELECT fld2,COUNT(*) FROM myTable GROUP BY Flt2 HAVING COUNT(*)>1

Query to return duplicate records

I have a table with a column varchar(50), say colA.
How can I create a sql query that returns all records with duplicate colA ?
For example:
colA colB
1 A
2 B
2 B
3 C
2 is the duplicate records for colA. How can I return those records ?
Thanks.SELECT ColA, count(*) FROM TableName
GROUP BY ColA
HAVING COUNT(*) > 1
HTH. Ryan
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:BF9FCDE6-61C4-4CD0-AEA7-98DE6049CE59@.microsoft.com...
>I have a table with a column varchar(50), say colA.
> How can I create a sql query that returns all records with duplicate colA
> ?
> For example:
> colA colB
> 1 A
> 2 B
> 2 B
> 3 C
> 2 is the duplicate records for colA. How can I return those records ?
> Thanks.
>|||"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:BF9FCDE6-61C4-4CD0-AEA7-98DE6049CE59@.microsoft.com...
>I have a table with a column varchar(50), say colA.
> How can I create a sql query that returns all records with duplicate colA
> ?
> For example:
> colA colB
> 1 A
> 2 B
> 2 B
> 3 C
> 2 is the duplicate records for colA. How can I return those records ?
> Thanks.
>
SELECT T.cola, T.colb
FROM your_table AS T
JOIN
(SELECT cola
FROM your_table
GROUP BY cola
HAVING COUNT(*)>1) AS D
ON T.cola = D.cola ;
David Portas
SQL Server MVP
--|||Select colA,ColB
>From Sometable
Where colA in
(
Select colA
From SomeTable
Group by colA
Having count(*) >1
)
HTH, jens Suessmeyer.

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 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)