Query help

CREATE TABLE [dbo].[stuff] (
[c1] [char] (10)
)
insert into stuff values ('a')
insert into stuff values ('b')
Is there an easy way to get this output ?
a 1
a 2
a 3
b 1
b 2
b 3
Thank you in advance for your helpYep:
select
s.c1
, x.a
from
stuff
cross join
(
select 1 union all
select 2 union all
select 3
) as x (a)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Jack" <jack@.jack.net> wrote in message
news:9uaje.13324$4d6.11003@.trndny04...
CREATE TABLE [dbo].[stuff] (
[c1] [char] (10)
)
insert into stuff values ('a')
insert into stuff values ('b')
Is there an easy way to get this output ?
a 1
a 2
a 3
b 1
b 2
b 3
Thank you in advance for your help|||> select
> s.c1
> , x.a
> from
> stuff
> cross join
> (
> select 1 union all
> select 2 union all
> select 3
> ) as x (a)
Wow, that's really badass. I as the VB.NET guy would have wasted precious
seconds slurping it into a DataTable object and then doing a whole bunch of
monkey business.
RESPECT!
This is the stuff that articles are made of!!
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare|||Well,
I did write one or two that involved a cross join. Here's one that
un-pivots a table:
create table Budgets
(
Contract int not null
, Nominal int not null
, Budget_01 int null
, Budget_02 int null
, Budget_03 int null
, primary key (Contract, Nominal)
)
go
insert Budgets values (1, 123, 1000, 1000, 2000)
insert Budgets values (1, 234, 500, 500, 1000)
insert Budgets values (2, 456, 3000, 4500, 3000)
insert Budgets values (2, 567, 800, 800, 800)
insert Budgets values (3, 789, 500, 500, 500)
insert Budgets values (3, 987, 5000, 500, NULL)
go
select
*
from
(
select
b.Contract
, b.Nominal
, x.Period
, case x.Period
when 1 then b.Budget_01
when 2 then b.Budget_02
when 3 then b.Budget_03
end as Budget
from
Budgets as b
cross join
(
select 1 as Period
union all
select 2
union all
select 3
) as x
) as y
where
Budget is not null
order by
Contract
, Nominal
, Period
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23l4YUHOXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> select
> s.c1
> , x.a
> from
> stuff
> cross join
> (
> select 1 union all
> select 2 union all
> select 3
> ) as x (a)
Wow, that's really badass. I as the VB.NET guy would have wasted precious
seconds slurping it into a DataTable object and then doing a whole bunch of
monkey business.
RESPECT!
This is the stuff that articles are made of!!
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare

Query for Row count

I am trying to create a header file that lists the
table_name, row_count and date. The count for tables are
from the tables themselves (Basic row count)except 2
tables that have join statement. How can I include the 2
with the join statements with the rest of them ?
SELECT A.name AS name, B.rows AS [row count],
LEFT(GETDATE(), 12) AS date
FROM sysobjects A
JOIN sysindexes B ON A.id = B.id
WHERE A.type = 'U'AND A.name in
('table1','table2','table3','table4','table5','tab le6','tab
le7','table8')
AND B.indid < 2
ORDER BY A.name
--One of the table with the join
SELECT Count(*)
fromT9 table9
left join T10 table10
on table9.customer_id = table10.oid
T.I.A
If it's a left join, why not just use the count for table9 ?
http://www.aspfaq.com/
(Reverse address to reply.)
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:926601c496a5$921c8790$a301280a@.phx.gbl...
> I am trying to create a header file that lists the
> table_name, row_count and date. The count for tables are
> from the tables themselves (Basic row count)except 2
> tables that have join statement. How can I include the 2
> with the join statements with the rest of them ?
>
> SELECT A.name AS name, B.rows AS [row count],
> LEFT(GETDATE(), 12) AS date
> FROM sysobjects A
> JOIN sysindexes B ON A.id = B.id
> WHERE A.type = 'U'AND A.name in
> ('table1','table2','table3','table4','table5','tab le6','tab
> le7','table8')
> AND B.indid < 2
> ORDER BY A.name
>
> --One of the table with the join
> SELECT Count(*)
> from T9 table9
> left join T10 table10
> on table9.customer_id = table10.oid
> T.I.A
|||Because I am getting a different count 1034 in one and
1029 in the other one. Can the cause be something else ?
Thanks.

>--Original Message--
>If it's a left join, why not just use the count for
table9 ?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:926601c496a5$921c8790$a301280a@.phx.gbl...
('table1','table2','table3','table4','table5','tab le6','tab
>
>.
>
|||Well, the count from your left join is not going to be stored in sysobjects,
because your left join is not a table and doesn't have any indexes!
What you could do is create a view, and then do something like:
SELECT 'SELECT t = '''+TABLE_NAME+''', c = COUNT(*) FROM '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN ('table1', ... , 'table8', 'view_table9_LJ_table10')
Run that, then copy the script from lower pane to top pane, and run it.
http://www.aspfaq.com/
(Reverse address to reply.)
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:91e101c496aa$06e898d0$a501280a@.phx.gbl...
> Because I am getting a different count 1034 in one and
> 1029 in the other one. Can the cause be something else ?

Query for Row count

I am trying to create a header file that lists the
table_name, row_count and date. The count for tables are
from the tables themselves (Basic row count)except 2
tables that have join statement. How can I include the 2
with the join statements with the rest of them '
SELECT A.name AS name, B.rows AS [row count],
LEFT(GETDATE(), 12) AS date
FROM sysobjects A
JOIN sysindexes B ON A.id = B.id
WHERE A.type = 'U'AND A.name in
('table1','table2','table3','table4','table5','table6','tab
le7','table8')
AND B.indid < 2
ORDER BY A.name
--One of the table with the join
SELECT Count(*)
from T9 table9
left join T10 table10
on table9.customer_id = table10.oid
T.I.AIf it's a left join, why not just use the count for table9 ?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:926601c496a5$921c8790$a301280a@.phx.gbl...
> I am trying to create a header file that lists the
> table_name, row_count and date. The count for tables are
> from the tables themselves (Basic row count)except 2
> tables that have join statement. How can I include the 2
> with the join statements with the rest of them '
>
> SELECT A.name AS name, B.rows AS [row count],
> LEFT(GETDATE(), 12) AS date
> FROM sysobjects A
> JOIN sysindexes B ON A.id = B.id
> WHERE A.type = 'U'AND A.name in
> ('table1','table2','table3','table4','table5','table6','tab
> le7','table8')
> AND B.indid < 2
> ORDER BY A.name
>
> --One of the table with the join
> SELECT Count(*)
> from T9 table9
> left join T10 table10
> on table9.customer_id = table10.oid
> T.I.A|||Because I am getting a different count 1034 in one and
1029 in the other one. Can the cause be something else '
Thanks.
>--Original Message--
>If it's a left join, why not just use the count for
table9 ?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:926601c496a5$921c8790$a301280a@.phx.gbl...
>> I am trying to create a header file that lists the
>> table_name, row_count and date. The count for tables are
>> from the tables themselves (Basic row count)except 2
>> tables that have join statement. How can I include the 2
>> with the join statements with the rest of them '
>>
>> SELECT A.name AS name, B.rows AS [row count],
>> LEFT(GETDATE(), 12) AS date
>> FROM sysobjects A
>> JOIN sysindexes B ON A.id = B.id
>> WHERE A.type = 'U'AND A.name in
('table1','table2','table3','table4','table5','table6','tab
>> le7','table8')
>> AND B.indid < 2
>> ORDER BY A.name
>>
>> --One of the table with the join
>> SELECT Count(*)
>> from T9 table9
>> left join T10 table10
>> on table9.customer_id = table10.oid
>> T.I.A
>
>.
>|||Well, the count from your left join is not going to be stored in sysobjects,
because your left join is not a table and doesn't have any indexes!
What you could do is create a view, and then do something like:
SELECT 'SELECT t = '''+TABLE_NAME+''', c = COUNT(*) FROM '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN ('table1', ... , 'table8', 'view_table9_LJ_table10')
Run that, then copy the script from lower pane to top pane, and run it.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:91e101c496aa$06e898d0$a501280a@.phx.gbl...
> Because I am getting a different count 1034 in one and
> 1029 in the other one. Can the cause be something else '

Query for multiple values

I was able to create a query that returns data based on a single order
number. Now I need to create a query that returns data based on
multiple order numbers. Obviously, when I run this query, it prompts me
for an order number. How do I change this so I can enter multiple order
numbers?
SELECT ORDNUMBE, CUSTNAME
FROM SOP10100
WHERE (ORDNUMBE = @.ordnumbe)
Thanks!http://www.sommarskog.se/arrays-in-sql.html
<2retread@.gmail.com> wrote in message
news:1126021459.621452.89010@.z14g2000cwz.googlegroups.com...
>I was able to create a query that returns data based on a single order
> number. Now I need to create a query that returns data based on
> multiple order numbers. Obviously, when I run this query, it prompts me
> for an order number. How do I change this so I can enter multiple order
> numbers?
> SELECT ORDNUMBE, CUSTNAME
> FROM SOP10100
> WHERE (ORDNUMBE = @.ordnumbe)
> Thanks!
>|||See if this helps:
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"2retread@.gmail.com" wrote:

> I was able to create a query that returns data based on a single order
> number. Now I need to create a query that returns data based on
> multiple order numbers. Obviously, when I run this query, it prompts me
> for an order number. How do I change this so I can enter multiple order
> numbers?
> SELECT ORDNUMBE, CUSTNAME
> FROM SOP10100
> WHERE (ORDNUMBE = @.ordnumbe)
> Thanks!
>|||CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm
FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
WHERE parm IS NOT NULL
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
In SQL Server, the VALUES() table constructor has to be faked with
(SELECT p1 UNION SELECT p2.. SELECT pn) AS Parmlist (p).|||On 6 Sep 2005 20:19:28 -0700, --CELKO-- wrote:

>CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
>INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
>BEGIN
>SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm
> FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
> WHERE parm IS NOT NULL
> AND <other conditions> )
> AND <more predicates>;
><more code>;
>END;
>In SQL Server, the VALUES() table constructor has to be faked with
>(SELECT p1 UNION SELECT p2.. SELECT pn) AS Parmlist (p).
Hi Joe,
And how exactly is your query better than
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (p1, p2, .., pN)
AND <more predicates>;
?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I usually wind up adding some CAST(), UPPER() and other procedures to
the SELECTs or in theVALUES() list.|||On 7 Sep 2005 12:49:10 -0700, --CELKO-- wrote:

>I usually wind up adding some CAST(), UPPER() and other procedures to
>the SELECTs or in theVALUES() list.
Hi Joe,
Assuming that you meant "functions", not "procedures", there's still no
reason to make it so complicated:
WHERE my_col IN (UPPER(p1), CAST(p2 AS verchar(10), ..., LOWER(pN))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Assuming that you meant "functions", not "procedures", there's still no r
eason to make it so complicated: <<
Opps! I grant that you can do a lot of edit work with CASE epxrssions,
too:
CASE WHEN @.p1 NOT BETWEEN 1 AND 10 THEN NULL ELSE @.p1 END
But it is hard to do things that involve another table, such as
translating codes in the parameters to the target table format::
(SELECT shoesize_eur
FROM ShoeSizes.
WHERE UPPER (shoesize_usa) = @.p1)
or multiple-parameter look-ups:
(SELECT address_nbr
FROM StandardAddresseNumbers AS A
WHERE CAST (x_ord AS INTEGER) = @.p1
AND CAST (y_ord AS INTEGER) = @.p2)|||On 8 Sep 2005 07:57:01 -0700, --CELKO-- wrote:

>Opps! I grant that you can do a lot of edit work with CASE epxrssions,
>too:
>CASE WHEN @.p1 NOT BETWEEN 1 AND 10 THEN NULL ELSE @.p1 END
>But it is hard to do things that involve another table, such as
>translating codes in the parameters to the target table format::
>(SELECT shoesize_eur
> FROM ShoeSizes.
>WHERE UPPER (shoesize_usa) = @.p1)
>or multiple-parameter look-ups:
>(SELECT address_nbr
> FROM StandardAddresseNumbers AS A
> WHERE CAST (x_ord AS INTEGER) = @.p1
> AND CAST (y_ord AS INTEGER) = @.p2)
Hi Joe,
Assuming that we're still discussing what can and can't be placed in an
IN (expression, expression, ...) string, I still fail to see how CASE
expressions and subqueries like the examples you posted would introduce
the need to move from IN with a list of expressions to IN with a
subquery, like the one you posted at the start of this thread.
I don't have the full SQL-92 standard at my disposal, but Books Online
says this:
Syntax
test_expression [ NOT ] IN
(
subquery
| expression [ ,...n ]
)
Arguments
(snip)
expression [,...n]
Is a list of expressions to test for a match. All expressions must be of
the same type as test_expression.
There are no further requirements for the expressions in the list of
expressions. So you should bne able to just slam the subqueries in
there.
Of course, the end result would be quite complicated - but adding an
extra layer of complexity by using a subquery with SELECT .. FROM VALUES
(...) would make it even more complicated, not less so!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query for create table

Hai All,
I have table t1 in db1.
I want to create a table t1 in DB2 with the same structure as t1 of
db1.
Can anyone tell me the query?
Urgent... Looking forward for the response...Well you use QA, right click on the table t1 in db1 and choose option "scrip
t
object to new window.. and then change the database to execute the script.
But if you want a query, then try this.
use db2
select * into t1 from db1.dbo.t1
--replace the * with the column list
But in this case you wo't get the indexes and constraints. Just the skeleton
.
Hope this helps.|||There's a difference between these two techniques. The first (script db)
will give you just the structure, as the OP asked for, but the second will
copy all the DATA too. If that is what is wanted, great, but if not, you
might end up with a process that takes a long time, and a lot of logging
going on in DB2 and a lot of space being wasted.
To copy a table without copying the data, you need a select statement that
is guaranteed to return no rows, something like this:
use db2
select * into t1 from db1.dbo.t1
where 1 = 0
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:78E120AB-5BC0-428C-9AC9-AC802A590532@.microsoft.com...
> Well you use QA, right click on the table t1 in db1 and choose option
> "script
> object to new window.. and then change the database to execute the script.
> But if you want a query, then try this.
> use db2
> select * into t1 from db1.dbo.t1
> --replace the * with the column list
> But in this case you wo't get the indexes and constraints. Just the
> skeleton.
> Hope this helps.
>|||Oops. I did intend to give the where clause 1=0. Dunno How I missed/messed i
t
:)
thanks for pointing it out.
--
"Kalen Delaney" wrote:

> There's a difference between these two techniques. The first (script db)
> will give you just the structure, as the OP asked for, but the second will
> copy all the DATA too. If that is what is wanted, great, but if not, you
> might end up with a process that takes a long time, and a lot of logging
> going on in DB2 and a lot of space being wasted.
> To copy a table without copying the data, you need a select statement that
> is guaranteed to return no rows, something like this:
> use db2
> select * into t1 from db1.dbo.t1
> where 1 = 0
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:78E120AB-5BC0-428C-9AC9-AC802A590532@.microsoft.com...
>
>|||Kalen
> use db2
> select * into t1 from db1.dbo.t1
> where 1 = 0
I think if the OP asked for table's structure the above technique is not
perfect because it does not move a Primary Keys as well as CONSTRAINS
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23%23Qyo6pdGHA.3364@.TK2MSFTNGP05.phx.gbl...
> There's a difference between these two techniques. The first (script db)
> will give you just the structure, as the OP asked for, but the second will
> copy all the DATA too. If that is what is wanted, great, but if not, you
> might end up with a process that takes a long time, and a lot of logging
> going on in DB2 and a lot of space being wasted.
> To copy a table without copying the data, you need a select statement that
> is guaranteed to return no rows, something like this:
> use db2
> select * into t1 from db1.dbo.t1
> where 1 = 0
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:78E120AB-5BC0-428C-9AC9-AC802A590532@.microsoft.com...
>|||Yes, I meant to just extend to what Omnibuzz said. He mentioned that this
solution wouldn't give you indexes and constraints, but I was only pointing
out that it WOULD give you the data, if you don't include the where clause
that is always false.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23HuxIZxdGHA.4532@.TK2MSFTNGP02.phx.gbl...
> Kalen
> I think if the OP asked for table's structure the above technique is not
> perfect because it does not move a Primary Keys as well as CONSTRAINS
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23%23Qyo6pdGHA.3364@.TK2MSFTNGP05.phx.gbl...
>

Query duplicates

I would like to create a query that will return all of the rows in a table
where the data in a certain column occurs more than once. I don't want to
return records with specific values(e.g. SELECT * from mytable WHERE age=14),
just records where any value occurs more than once. Any suggestions?
Many thanks
Homer
Homer
Look at Itzik Ben-Gan's example
--Modify it for your needs
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:CABBD9CA-DD66-4974-8B37-96216FF153C9@.microsoft.com...
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE
age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer
|||Homer,
Here's my test script for your issue:
create table Homerdupes (
i int not null primary key identity
, j int not null
, v varchar(50))
insert Homerdupes (j,v) values (100,'Hello')
insert Homerdupes (j,v) values (200,'Goodbye')
insert Homerdupes (j,v) values (200,'Goodbye - DUPE!!')
-- show me the data
select * from Homerdupes
-- show me the dupes
select a.* from Homerdupes a join
(select j,count(*) as counter from Homerdupes
group by j
having count(*) > 1) as d
on a.j = d.j
drop table Homerdupes
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Homer wrote:
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer

Query duplicates

I would like to create a query that will return all of the rows in a table
where the data in a certain column occurs more than once. I don't want to
return records with specific values(e.g. SELECT * from mytable WHERE age=14)
,
just records where any value occurs more than once. Any suggestions?
Many thanks
HomerHomer
Look at Itzik Ben-Gan's example
--Modify it for your needs
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:CABBD9CA-DD66-4974-8B37-96216FF153C9@.microsoft.com...
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE
age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer|||Homer,
Here's my test script for your issue:
create table Homerdupes (
i int not null primary key identity
, j int not null
, v varchar(50))
insert Homerdupes (j,v) values (100,'Hello')
insert Homerdupes (j,v) values (200,'Goodbye')
insert Homerdupes (j,v) values (200,'Goodbye - DUPE!!')
-- show me the data
select * from Homerdupes
-- show me the dupes
select a.* from Homerdupes a join
(select j,count(*) as counter from Homerdupes
group by j
having count(*) > 1) as d
on a.j = d.j
drop table Homerdupes
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Homer wrote:
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE age=1
4),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer

Query duplicates

I would like to create a query that will return all of the rows in a table
where the data in a certain column occurs more than once. I don't want to
return records with specific values(e.g. SELECT * from mytable WHERE age=14),
just records where any value occurs more than once. Any suggestions?
Many thanks
HomerHomer
Look at Itzik Ben-Gan's example
--Modify it for your needs
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:CABBD9CA-DD66-4974-8B37-96216FF153C9@.microsoft.com...
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE
age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer|||Homer,
Here's my test script for your issue:
create table Homerdupes (
i int not null primary key identity
, j int not null
, v varchar(50))
insert Homerdupes (j,v) values (100,'Hello')
insert Homerdupes (j,v) values (200,'Goodbye')
insert Homerdupes (j,v) values (200,'Goodbye - DUPE!!')
-- show me the data
select * from Homerdupes
-- show me the dupes
select a.* from Homerdupes a join
(select j,count(*) as counter from Homerdupes
group by j
having count(*) > 1) as d
on a.j = d.j
drop table Homerdupes
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Homer wrote:
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer

Query designer toggle button not present

When I create a Report Server Project Using Visual Studio 2005 with SQL Server 2005 I can create a data source with no problem and the test shows it is good good.

When I next create a report and go to the query builder using that same data source and click on the query builder button I see the Query Builder screen, but there is no toggle button in the top left of the screen so I cannot go into the graphical mode to see the tables.

I have uninstalled and reinstalled both Visual Studio and SQL Server but I still have the same problem. What should I do to get the button visible on the screen?

Can anyone help?

Terry,

Are you setting this report up from a shared data source or from making a new datasource. You may want to set up a shared datasource first then make your report from the shared datasource and see if that works. It works that way for me.

|||

It makes no difference either way, I can have new data source or a shared data source.

I have a server and my computer both of which appear to have a nearly identical setup of Studio and SQL Server but I can perform identical steps on both and on the server I get the toggle button but on my computer I get nothing. This is what makes be think it may be a bad installation but I have reinstalled both Studio and SQL Server on my machine and I still get the same thing!

Thanks for responding though.

Terry

|||

Terry,

You may have already figured it out, but you may just want to use stored procedures. When we create a new report we just go through the wizard, pick our datasource, and then tell it what stored proc we want it to use. I believe the syntax is

exec rptsp_MyStoredProcedure @.MyParameter1,@.MyParameter2

Do you have a lot of experience with SQL queries and stored procs? If not maybe I can help.

Query Designer for AS2000

Hello,
I've installed new CTP June of SQL2005. And I'd like to create new report in
reporting services with connection to Analysis Services 2000.
So, I've created datasource, looks fine, test connection succeeded. When I'm
starting to create report, after selecting datasource, when I click on
"Query Designer"
I get following message:
"The query designer could not be loaded.
Verify your connection string and query string or enter a valid query to
continue."
Where is the problem?
Best regards
Piotr TrêbaczMake sure you add ConnectTo=8.0 to the connection string
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Piotr Trêbacz" <ptrebacz@.kruk-inkaso.com.pl> wrote in message
news:uJ8n5WvdFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I've installed new CTP June of SQL2005. And I'd like to create new report
> in
> reporting services with connection to Analysis Services 2000.
> So, I've created datasource, looks fine, test connection succeeded. When
> I'm
> starting to create report, after selecting datasource, when I click on
> "Query Designer"
> I get following message:
> "The query designer could not be loaded.
> Verify your connection string and query string or enter a valid query to
> continue."
> Where is the problem?
> Best regards
> Piotr Trêbacz
>|||> Make sure you add ConnectTo=8.0 to the connection string
It works, great, big thanks. I wonder why MS people didn't mention this when
I ask few times:)
Thanks again
Reagards
Piotr

Query data from a different database

Greetings,

Is it possible to create a view that selects data from a different database, SQL 2005?

i.e. Server A.Database 1.Table 1, Can Server B.Database 2.View1 be based on Server A.Database 1.Table 1

I thought I browsed by a page a few days ago that gave an example. Unfotunately I did not bookmark it.

Thank you,

Charles

I think you can do this directly with views, but if not you should be able to use synonyms as a go between to get it done. Here is the view example that I think works in SQL Server 2005.

sp_addlinkedserver 'otherserver', <other stuff specific to the server I set up>

go

-- using a view directly

create view v as select * from otherserver.<thedatabase>.<owner>.<table>

go

select * from v

go

-- I think that works, but if it does not work directly, then you could do this:

create synonym a for otherserver.<thedatabase>.<owner>.<table>

go

create view v as select * from a

go

The advantage of the synonym is that it is dynamically bound, so you could switch what it points to dynamically, the disadvantage of the synonym is that API metadata queries such as ODBC and OLE DB catalog metadata queries don't work well with them. They provide a lot of flexibility on the server side, but not as much on the client side. If you combine them with views, however, and use a view as the client access point instead of directly using the synonym, you shouldn't run into issues with catalog metadata since the client will be querying a view, not a synonym.

Hope that helps!

John

|||View over a synonym is not late bound, it is bound at the compile time.
So if you change your underlying table definition your view will stop working, with something like:
The OLE DB provider "SQLNCLI" for linked server "server2" reported a change in schema version between compile time ("168946857210586") and run time ("168946857240160") for table ""tempdb"."dbo"."t1"".

Query calculated OLAP members

I'm trying to create a report using an OLAP cube as my data source, but I can
retrieve only the dimensions and measures, not the calculated members.
My query is:
select * from MasterCube
Is there any way to extract them?Below is a sample MDX query (used in the sample Foodmart reporting services
report) which includes a calculated measure, Store Cost.
SELECT { [Measures].[Store Sales], [Measures].[Store Cost] } ON COLUMNS,
{ Descendants([Product].[All Products], [Product].[Brand Name], LEAVES) }
ON ROWS,
{ Time.[1997].[Q1],Time.[1997].[Q2],Time.[1997].[Q3],Time.[1997].[Q4] } ON
PAGES
FROM Sales
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cesar Meza" <CesarMeza@.discussions.microsoft.com> wrote in message
news:0A29D2FD-F3CF-4772-A063-E585C6B1DDA3@.microsoft.com...
> I'm trying to create a report using an OLAP cube as my data source, but I
> can
> retrieve only the dimensions and measures, not the calculated members.
> My query is:
> select * from MasterCube
> Is there any way to extract them?|||The real issue this: I have a cube that is too big for Excel's Pivot table to
handle it. So I'm trying to export it as PDF report using RS.
So I have several formulas that rely on the Analysis Services functionalliy
(aggregates, ranking, etc.) that are already in the cube.
I know I can write the MDX statement to recreate them, but this will mean to
re-create all the calculated members once more in the statement.
Any suggestions?.
Thanks a lot for your response.

Query Builder Tool

Hello -- does anyone know of any good tools to create queries for SQL
Server? I'm currently using Query Analzyer, but would like something a bit
more QUI oriented similar to what Access has -- where I can insert tables
and drag columns, etc.
Any help would be appreciated.
Hello DavidM.
You can use Enterprise Manager for an interface very similar to Access.
Click the tables node to get a listing of all the tables in the right pane.
Select a table and right click. Select open table from the pop up menu.
Select Query. You should now see an interface like Access that you can use to
design queries.
regards,
Mark Baekdal
http://www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"DavidM" wrote:

> Hello -- does anyone know of any good tools to create queries for SQL
> Server? I'm currently using Query Analzyer, but would like something a bit
> more QUI oriented similar to what Access has -- where I can insert tables
> and drag columns, etc.
> Any help would be appreciated.
>
>
|||Probably the best (and easiest) one I've seen is Crystal Reports.
Regards,
Hank Arnold
"DavidM" <spam@.spam.net> wrote in message
news:OoefkbD$EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hello -- does anyone know of any good tools to create queries for SQL
> Server? I'm currently using Query Analzyer, but would like something a
> bit more QUI oriented similar to what Access has -- where I can insert
> tables and drag columns, etc.
> Any help would be appreciated.
>
>
|||This one seems nice --
http://www.sqlmanager.net/products/mssql/query
"Hank Arnold" <rasilon@.aol.com> wrote in message
news:351eeqF4fnrjtU1@.individual.net...
> Probably the best (and easiest) one I've seen is Crystal Reports.
> --
> Regards,
> Hank Arnold
> "DavidM" <spam@.spam.net> wrote in message
> news:OoefkbD$EHA.3908@.TK2MSFTNGP12.phx.gbl...
>

Query Builder Tool

Hello -- does anyone know of any good tools to create queries for SQL
Server? I'm currently using Query Analzyer, but would like something a bit
more QUI oriented similar to what Access has -- where I can insert tables
and drag columns, etc.
Any help would be appreciated.Hello DavidM.
You can use Enterprise Manager for an interface very similar to Access.
Click the tables node to get a listing of all the tables in the right pane.
Select a table and right click. Select open table from the pop up menu.
Select Query. You should now see an interface like Access that you can use t
o
design queries.
regards,
Mark Baekdal
http://www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"DavidM" wrote:

> Hello -- does anyone know of any good tools to create queries for SQL
> Server? I'm currently using Query Analzyer, but would like something a bi
t
> more QUI oriented similar to what Access has -- where I can insert tables
> and drag columns, etc.
> Any help would be appreciated.
>
>|||Probably the best (and easiest) one I've seen is Crystal Reports.
Regards,
Hank Arnold
"DavidM" <spam@.spam.net> wrote in message
news:OoefkbD$EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hello -- does anyone know of any good tools to create queries for SQL
> Server? I'm currently using Query Analzyer, but would like something a
> bit more QUI oriented similar to what Access has -- where I can insert
> tables and drag columns, etc.
> Any help would be appreciated.
>
>|||This one seems nice --
http://www.sqlmanager.net/products/mssql/query
"Hank Arnold" <rasilon@.aol.com> wrote in message
news:351eeqF4fnrjtU1@.individual.net...
> Probably the best (and easiest) one I've seen is Crystal Reports.
> --
> Regards,
> Hank Arnold
> "DavidM" <spam@.spam.net> wrote in message
> news:OoefkbD$EHA.3908@.TK2MSFTNGP12.phx.gbl...
>

Query Builder Tool

Hello -- does anyone know of any good tools to create queries for SQL
Server? I'm currently using Query Analzyer, but would like something a bit
more QUI oriented similar to what Access has -- where I can insert tables
and drag columns, etc.
Any help would be appreciated.Hello DavidM.
You can use Enterprise Manager for an interface very similar to Access.
Click the tables node to get a listing of all the tables in the right pane.
Select a table and right click. Select open table from the pop up menu.
Select Query. You should now see an interface like Access that you can use to
design queries.
regards,
Mark Baekdal
http://www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"DavidM" wrote:
> Hello -- does anyone know of any good tools to create queries for SQL
> Server? I'm currently using Query Analzyer, but would like something a bit
> more QUI oriented similar to what Access has -- where I can insert tables
> and drag columns, etc.
> Any help would be appreciated.
>
>|||Probably the best (and easiest) one I've seen is Crystal Reports.
--
Regards,
Hank Arnold
"DavidM" <spam@.spam.net> wrote in message
news:OoefkbD$EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hello -- does anyone know of any good tools to create queries for SQL
> Server? I'm currently using Query Analzyer, but would like something a
> bit more QUI oriented similar to what Access has -- where I can insert
> tables and drag columns, etc.
> Any help would be appreciated.
>
>|||This one seems nice --
http://www.sqlmanager.net/products/mssql/query
"Hank Arnold" <rasilon@.aol.com> wrote in message
news:351eeqF4fnrjtU1@.individual.net...
> Probably the best (and easiest) one I've seen is Crystal Reports.
> --
> Regards,
> Hank Arnold
> "DavidM" <spam@.spam.net> wrote in message
> news:OoefkbD$EHA.3908@.TK2MSFTNGP12.phx.gbl...
>> Hello -- does anyone know of any good tools to create queries for SQL
>> Server? I'm currently using Query Analzyer, but would like something a
>> bit more QUI oriented similar to what Access has -- where I can insert
>> tables and drag columns, etc.
>> Any help would be appreciated.
>>
>

Query Builder Tool

Hello -- does anyone know of any good tools to create queries for SQL
Server? I'm currently using Query Analzyer, but would like something a bit
more QUI oriented similar to what Access has -- where I can insert tables
and drag columns, etc.
Any help would be appreciated.
Hello DavidM.
You can use Enterprise Manager for an interface very similar to Access.
Click the tables node to get a listing of all the tables in the right pane.
Select a table and right click. Select open table from the pop up menu.
Select Query. You should now see an interface like Access that you can use to
design queries.
regards,
Mark Baekdal
http://www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"DavidM" wrote:

> Hello -- does anyone know of any good tools to create queries for SQL
> Server? I'm currently using Query Analzyer, but would like something a bit
> more QUI oriented similar to what Access has -- where I can insert tables
> and drag columns, etc.
> Any help would be appreciated.
>
>
|||Probably the best (and easiest) one I've seen is Crystal Reports.
Regards,
Hank Arnold
"DavidM" <spam@.spam.net> wrote in message
news:OoefkbD$EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hello -- does anyone know of any good tools to create queries for SQL
> Server? I'm currently using Query Analzyer, but would like something a
> bit more QUI oriented similar to what Access has -- where I can insert
> tables and drag columns, etc.
> Any help would be appreciated.
>
>
|||This one seems nice --
http://www.sqlmanager.net/products/mssql/query
"Hank Arnold" <rasilon@.aol.com> wrote in message
news:351eeqF4fnrjtU1@.individual.net...
> Probably the best (and easiest) one I've seen is Crystal Reports.
> --
> Regards,
> Hank Arnold
> "DavidM" <spam@.spam.net> wrote in message
> news:OoefkbD$EHA.3908@.TK2MSFTNGP12.phx.gbl...
>

query between two tables

Hi,
I create a xml template with a sql query that select data from two tables.
I need to create a xsl file to view the results. Do you know some link for
understanding this? Sample?
Thank's
andrea
"Andrea Racca" <nsi@.libero.it> wrote in message
news:eblAwRhHEHA.548@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I create a xml template with a sql query that select data from two tables.
> I need to create a xsl file to view the results. Do you know some link for
> understanding this? Sample?
Here is an example that shows how to do this:
http://sqlxml.org/faqs.aspx?faq=99
Bryant
|||Thank's, but I like to put results in a html table.. any other help?
Andrea
"Bryant Likes" <bryant@.suespammers.org> ha scritto nel messaggio
news:OTTNkYkHEHA.320@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> "Andrea Racca" <nsi@.libero.it> wrote in message
> news:eblAwRhHEHA.548@.TK2MSFTNGP09.phx.gbl...
tables.[vbcol=seagreen]
for
> Here is an example that shows how to do this:
> http://sqlxml.org/faqs.aspx?faq=99
> --
> Bryant
>

Query behaviour - (subquery results)

Hi there,
I'm experiencing some very strange effects when I trying to include a
subquery. I shall demonstrate with a simple example...
create table test
(ind int,
seq int,
message varchar(255))
insert into test (ind,seq, message) values
(1,1,'date=01/06/2006')
insert into test (ind,seq, message) values
(2,1,'date=1/12/2005')
insert into test (ind,seq, message) values
(2,2,'test')
insert into test (ind,seq, message) values
(2,3,'date=2/12/2005')
The column IND is theoretically a foreign key, the SEQ is a primary key. A
quick explanation is that this is a comment table from a main table (main
table being an 'order' table and this being a 'order comment' table.. the
relationship being (order) 1:m (comment) But for this example this doesn't
really matter.
So here are 2 queries.
select * from test t1
where t1.ind =1
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
This one simple extracts the date from the text string. It works OK. I've
had to include the IND =1 to avoid the date conversion error. It just shows
that the conversion works.
results
IND SEQ Message
1 1 date=01/06/2006
The second query...
select max(seq) from test t2
where t2.message like 'date=%'
group by ind
This is extrating the highest 'SEQ' for each 'IND'. ie the last comment
(that has got a date component) for each order
results
SEQ
1
3
So thats OK.
Now the fun starts when I try to combine the two...
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)
where convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
This causes a
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'where'.
So, its implying the date format is incorrect. If I remove the convert :-
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)
then the results are OK -
IND SEQ Message
1 1 date=01/06/2006
2 3 date=2/12/2005
Any help please?
thanks
Simon(...)
--Replace the where with and AND -->
AND convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
HTH, jens Suessmeyer.|||Sorry my bad typo... I meant AND... The query should have been
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
this results in the error. This isn't just a syntax error.
Thanks though
"Jens" wrote:

> (...)
> --Replace the where with and AND -->
> AND convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
> 10)),103) > getdate()
> HTH, jens Suessmeyer.
>|||A flaw in the substring function:
...(SUBSTRING(Message, CHARINDEX('=',Message) + 1, 10)),103) > getdate()
Maybe that's it.
ML
ML
http://milambda.blogspot.com/|||I don't think this is it...
If I do a
select ind , seq, SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)
from test
I get the results..
IND SEQ converted date
1 1 01/06/2006
2 1 1/12/2005
2 2 test
2 3 2/12/2005
So this shows that 3 of the rows can be converted into datetime (103 style).
I think that the problem is that the convert is being done on the whole data
set before approriate rows are excluded.
If I turn this into an inline view then I get the same error.
select * from (
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)) test
where convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
<bte this is where my original typo came from ;-) >
help !
"ML" wrote:

> A flaw in the substring function:
> ...(SUBSTRING(Message, CHARINDEX('=',Message) + 1, 10)),103) > getdate()
> Maybe that's it.
>
> ML
>
> ML
> --
> http://milambda.blogspot.com/|||I've had a quick look at this and get the same odd result.
The Where clause is being applied to all the contents of the input table.
You can see this in the showplan and can confirm it by removing the row
without a date.
Regards,
Craig
"s_clarke" wrote:
> I don't think this is it...
> If I do a
> select ind , seq, SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)
> from test
> I get the results..
> IND SEQ converted date
> 1 1 01/06/2006
> 2 1 1/12/2005
> 2 2 test
> 2 3 2/12/2005
>
> So this shows that 3 of the rows can be converted into datetime (103 style
).
> I think that the problem is that the convert is being done on the whole da
ta
> set before approriate rows are excluded.
> If I turn this into an inline view then I get the same error.
> select * from (
> select * from test t1
> where t1.seq in (select max(seq) from test t2
> where t2.ind = t1.ind
> and t2.message like 'date=%'
> group by t2.ind)) test
> where convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
> 10)),103) > getdate()
> <bte this is where my original typo came from ;-) >
> help !
>
> "ML" wrote:
>

Query and GROUP BY

Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <= @.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
NeilAssuming that if two sales occur at exactly the same, you want just one of
them:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT TOP 1
PRODUCT_CODE, PRODUCT_QTY, SALE_TIMESTAMP
FROM
HISTORY
WHERE
(PRODUCT_CODE = @.product_code)
AND (SALE_TIMESTAMP <= @.sale_timestamp)
ORDER BY
SALE_TIMESTAMP DESC
END
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172787732.437425.198610@.n33g2000cwc.googlegroups.com...
Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
Neil

Query Analyzer: Keyword color schema.

Hello,
Are there any information about Whick keywords is colored by Query Analyzer
?
I want to create keyword schema file for IMHO Instant blogger's Code Snippet
Editor.
Books Online's reserved keywords is not enouph.
For example: @.@.error, any sysobjects, etc.
Regards,
Yoshihiro Kawabata
MVP for SQL Server
PASSJ board-leader.
Yoshihiro Kawabata wrote:
> Hello,
> Are there any information about Whick keywords is colored by Query
> Analyzer ?
> I want to create keyword schema file for IMHO Instant blogger's Code
> Snippet Editor.
> Books Online's reserved keywords is not enouph.
> For example: @.@.error, any sysobjects, etc.
> Regards,
> Yoshihiro Kawabata
> MVP for SQL Server
> PASSJ board-leader.
Have a look at the SQL Server 2000 Syntax File for TextPad here:
http://www.textpad.com/add-ons/synn2t.html
Scroll to the item called:
SQL (Transact) TextPad syntax definitions for Transact SQL.
Complete list of keywords for MSSQL2000 SP3a.
Contributed by Alexander Mitelman, 17 August 2004.
You don't need TextPad to view the file. It may give you a good start.
David Gugick
Imceda Software
www.imceda.com
|||Hello, David.
Thank you for quick response.
It's a very nice.
I will feed back to IMHO team.
Best regards,
Yoshihiro Kawabata
MVP for SQL Server.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OAZog83KFHA.3076@.tk2msftngp13.phx.gbl...
> Yoshihiro Kawabata wrote:
> Have a look at the SQL Server 2000 Syntax File for TextPad here:
> http://www.textpad.com/add-ons/synn2t.html
> Scroll to the item called:
> SQL (Transact) TextPad syntax definitions for Transact SQL. Complete
> list of keywords for MSSQL2000 SP3a.
> Contributed by Alexander Mitelman, 17 August 2004.
>
> You don't need TextPad to view the file. It may give you a good start.
> --
> David Gugick
> Imceda Software
> www.imceda.com