I just had a situation where I was creating a temp table in a stored proc. I
create the temp table and then do a select into it from a couple of other
tables. Problem is, we had changed the size of one of the field in the table
from varchar(250) to varchar(500). But the temp table didn't reflect that so
data was being truncated.
So the easiest way around this would be to make the field varchar(max)
instead of 500 so if and when that field grows later, I don't get an error
in the stored proc.
Question is - does using varchar(max) have any repurcusions? Could I just
use it everywhere and then not have to worry about this problem?
TIA - Jeff.
> So the easiest way around this would be to make the field varchar(max)
> instead of 500 so if and when that field grows later, I don't get an error
> in the stored proc.
Or maybe you can avoid using a temp table for this data?
Or maybe if you change the data type of the base column (which shouldn't
happen very often) you also change the other places it is referenced (e.g.
SP params, variable declarations, etc.)?
> Question is - does using varchar(max) have any repurcusions? Could I just
> use it everywhere and then not have to worry about this problem?
This is like trading in a sub-compact for a minivan because you don't like
the way the tennis racket fits on the seat.
I would strongly recommend only using MAX when you absolutely need to have >
4000 or 8000 characters. In this case, if you changed from 250 to 500 now,
you will probably change it again. I would say envision the largest # of
characters that column will ever need to hold, then double it, and fix it
everywhere once. This drastically reduces the likelihood you will have to
worry about it again.
|||> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
That's a possible solution, and I don't know the op's requirements, but I
often opt for CREATE TABLE and then INSERT INTO, in case I need to have
additional columns, or in case I want to define indexes/keys/constraints
etc. BEFORE all of the data is in the destination table...
A
|||I would create the table that way but I actually am doing a couple of
different selects putting data in the table so I need to do insert intos.
<jhofmeyr@.googlemail.com> wrote in message
news:861f7e9e-1970-4538-99c8-e599c1847a0b@.f10g2000hsf.googlegroups.com...
> Hi Jeff,
> I would avoid using varchar(max) unless you actually need to store
> very long values (> 8000 chars)
> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
> You can do this very easily by creating the table like this:
> SELECT <col list>
> INTO #tmp_table
> FROM <table list>
> WHERE 1 = 0
> Good luck!
> J
|||>I would create the table that way but I actually am doing a couple of
>different selects putting data in the table so I need to do insert intos.
But the very first one could be a select into, no? I assume that the source
table that drives that column would have the same data type as the column
that fills that data if you have source data from other tables, so all the
tables should be updated if you increase the size again...
A
sql
Showing posts with label select. Show all posts
Showing posts with label select. Show all posts
Friday, March 30, 2012
Is using Max in varchar a bad thing?
I just had a situation where I was creating a temp table in a stored proc. I
create the temp table and then do a select into it from a couple of other
tables. Problem is, we had changed the size of one of the field in the table
from varchar(250) to varchar(500). But the temp table didn't reflect that so
data was being truncated.
So the easiest way around this would be to make the field varchar(max)
instead of 500 so if and when that field grows later, I don't get an error
in the stored proc.
Question is - does using varchar(max) have any repurcusions? Could I just
use it everywhere and then not have to worry about this problem?
TIA - Jeff.Hi Jeff,
I would avoid using varchar(max) unless you actually need to store
very long values (> 8000 chars)
As an alternative, why not create your temp table from the underlying
source columns - that way your temp table columns will always match
the source columns.
You can do this very easily by creating the table like this:
SELECT <col list>
INTO #tmp_table
FROM <table list>
WHERE 1 = 0
Good luck!
J|||> So the easiest way around this would be to make the field varchar(max)
> instead of 500 so if and when that field grows later, I don't get an error
> in the stored proc.
Or maybe you can avoid using a temp table for this data?
Or maybe if you change the data type of the base column (which shouldn't
happen very often) you also change the other places it is referenced (e.g.
SP params, variable declarations, etc.)?
> Question is - does using varchar(max) have any repurcusions? Could I just
> use it everywhere and then not have to worry about this problem?
This is like trading in a sub-compact for a minivan because you don't like
the way the tennis racket fits on the seat.
I would strongly recommend only using MAX when you absolutely need to have >
4000 or 8000 characters. In this case, if you changed from 250 to 500 now,
you will probably change it again. I would say envision the largest # of
characters that column will ever need to hold, then double it, and fix it
everywhere once. This drastically reduces the likelihood you will have to
worry about it again.|||> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
That's a possible solution, and I don't know the op's requirements, but I
often opt for CREATE TABLE and then INSERT INTO, in case I need to have
additional columns, or in case I want to define indexes/keys/constraints
etc. BEFORE all of the data is in the destination table...
A|||I would create the table that way but I actually am doing a couple of
different selects putting data in the table so I need to do insert intos.
<jhofmeyr@.googlemail.com> wrote in message
news:861f7e9e-1970-4538-99c8-e599c1847a0b@.f10g2000hsf.googlegroups.com...
> Hi Jeff,
> I would avoid using varchar(max) unless you actually need to store
> very long values (> 8000 chars)
> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
> You can do this very easily by creating the table like this:
> SELECT <col list>
> INTO #tmp_table
> FROM <table list>
> WHERE 1 = 0
> Good luck!
> J|||>I would create the table that way but I actually am doing a couple of
>different selects putting data in the table so I need to do insert intos.
But the very first one could be a select into, no? I assume that the source
table that drives that column would have the same data type as the column
that fills that data if you have source data from other tables, so all the
tables should be updated if you increase the size again...
A
create the temp table and then do a select into it from a couple of other
tables. Problem is, we had changed the size of one of the field in the table
from varchar(250) to varchar(500). But the temp table didn't reflect that so
data was being truncated.
So the easiest way around this would be to make the field varchar(max)
instead of 500 so if and when that field grows later, I don't get an error
in the stored proc.
Question is - does using varchar(max) have any repurcusions? Could I just
use it everywhere and then not have to worry about this problem?
TIA - Jeff.Hi Jeff,
I would avoid using varchar(max) unless you actually need to store
very long values (> 8000 chars)
As an alternative, why not create your temp table from the underlying
source columns - that way your temp table columns will always match
the source columns.
You can do this very easily by creating the table like this:
SELECT <col list>
INTO #tmp_table
FROM <table list>
WHERE 1 = 0
Good luck!
J|||> So the easiest way around this would be to make the field varchar(max)
> instead of 500 so if and when that field grows later, I don't get an error
> in the stored proc.
Or maybe you can avoid using a temp table for this data?
Or maybe if you change the data type of the base column (which shouldn't
happen very often) you also change the other places it is referenced (e.g.
SP params, variable declarations, etc.)?
> Question is - does using varchar(max) have any repurcusions? Could I just
> use it everywhere and then not have to worry about this problem?
This is like trading in a sub-compact for a minivan because you don't like
the way the tennis racket fits on the seat.
I would strongly recommend only using MAX when you absolutely need to have >
4000 or 8000 characters. In this case, if you changed from 250 to 500 now,
you will probably change it again. I would say envision the largest # of
characters that column will ever need to hold, then double it, and fix it
everywhere once. This drastically reduces the likelihood you will have to
worry about it again.|||> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
That's a possible solution, and I don't know the op's requirements, but I
often opt for CREATE TABLE and then INSERT INTO, in case I need to have
additional columns, or in case I want to define indexes/keys/constraints
etc. BEFORE all of the data is in the destination table...
A|||I would create the table that way but I actually am doing a couple of
different selects putting data in the table so I need to do insert intos.
<jhofmeyr@.googlemail.com> wrote in message
news:861f7e9e-1970-4538-99c8-e599c1847a0b@.f10g2000hsf.googlegroups.com...
> Hi Jeff,
> I would avoid using varchar(max) unless you actually need to store
> very long values (> 8000 chars)
> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
> You can do this very easily by creating the table like this:
> SELECT <col list>
> INTO #tmp_table
> FROM <table list>
> WHERE 1 = 0
> Good luck!
> J|||>I would create the table that way but I actually am doing a couple of
>different selects putting data in the table so I need to do insert intos.
But the very first one could be a select into, no? I assume that the source
table that drives that column would have the same data type as the column
that fills that data if you have source data from other tables, so all the
tables should be updated if you increase the size again...
A
is this wrong ?
declare @.name1 varchar(100)
select @.name = 'table1'
Truncate table @.name
I get an error at the truncate table statement
Whats the correct way of writing this ?
ThanksWhy do it with a variable? You just need to say:
truncate table table1;
(See TRUNCATE TABLE
<http://msdn.microsoft.com/library/e..._ta-tz_2hk5.asp> in BOL.)
Is this part of something larger that's causing you issues? If you need
to do this in a repeating loop for many tables then you'll have to use
dynamic sql (see sp_executesql
<http://msdn.microsoft.com/library/e..._ea-ez_2h7w.asp>
in BOL). Something like:
exec sp_executesql
N'TRUNCATE TABLE @.tablename',
N'@.tablename sysname',
@.tablename = N'table1';
with a looping wrapper (ie. cursor) around it.
*mike hodgson*
http://sqlnerd.blogspot.com
Hassan wrote:
>declare @.name1 varchar(100)
>select @.name = 'table1'
>Truncate table @.name
>I get an error at the truncate table statement
>Whats the correct way of writing this ?
>Thanks
>
>|||Mike Hodgson (e1minst3r@.gmail.com) writes:
> exec sp_executesql
> N'TRUNCATE TABLE @.tablename',
> N'@.tablename sysname',
> @.tablename = N'table1';
This has the same problem as the original post. You cannot use a variable
to hold the name of a table.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hassan wrote:
> declare @.name1 varchar(100)
> select @.name = 'table1'
> Truncate table @.name
> I get an error at the truncate table statement
> Whats the correct way of writing this ?
> Thanks
Use dynamic SQL:
Declare @.sql nvarchar(255)
Set @.sql = N'Truncate Table [' + @.name + N']'
EXEC (@.sql)
David Gugick - SQL Server MVP
Quest Software|||Yeah (oops). I discovered that just after I'd posted this reply (when I
was building up a reply to the next post regarding dropping all foreign
keys in a database) - same with an ALTER TABLE.
*mike hodgson*
http://sqlnerd.blogspot.com
Erland Sommarskog wrote:
>Mike Hodgson (e1minst3r@.gmail.com) writes:
>
>This has the same problem as the original post. You cannot use a variable
>to hold the name of a table.
>
>|||SQL is a compiled programming language. Do you have any idea what a
compiler is? Please, please do not try to write SQL; you have no idea
what you are doing and need at least a year of intense education and
not just in SQL. .|||How is the book coming along?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1140477991.507316.78270@.f14g2000cwb.googlegroups.com...
> SQL is a compiled programming language. Do you have any idea what a
> compiler is? Please, please do not try to write SQL; you have no idea
> what you are doing and need at least a year of intense education and
> not just in SQL. .
>
select @.name = 'table1'
Truncate table @.name
I get an error at the truncate table statement
Whats the correct way of writing this ?
ThanksWhy do it with a variable? You just need to say:
truncate table table1;
(See TRUNCATE TABLE
<http://msdn.microsoft.com/library/e..._ta-tz_2hk5.asp> in BOL.)
Is this part of something larger that's causing you issues? If you need
to do this in a repeating loop for many tables then you'll have to use
dynamic sql (see sp_executesql
<http://msdn.microsoft.com/library/e..._ea-ez_2h7w.asp>
in BOL). Something like:
exec sp_executesql
N'TRUNCATE TABLE @.tablename',
N'@.tablename sysname',
@.tablename = N'table1';
with a looping wrapper (ie. cursor) around it.
*mike hodgson*
http://sqlnerd.blogspot.com
Hassan wrote:
>declare @.name1 varchar(100)
>select @.name = 'table1'
>Truncate table @.name
>I get an error at the truncate table statement
>Whats the correct way of writing this ?
>Thanks
>
>|||Mike Hodgson (e1minst3r@.gmail.com) writes:
> exec sp_executesql
> N'TRUNCATE TABLE @.tablename',
> N'@.tablename sysname',
> @.tablename = N'table1';
This has the same problem as the original post. You cannot use a variable
to hold the name of a table.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hassan wrote:
> declare @.name1 varchar(100)
> select @.name = 'table1'
> Truncate table @.name
> I get an error at the truncate table statement
> Whats the correct way of writing this ?
> Thanks
Use dynamic SQL:
Declare @.sql nvarchar(255)
Set @.sql = N'Truncate Table [' + @.name + N']'
EXEC (@.sql)
David Gugick - SQL Server MVP
Quest Software|||Yeah (oops). I discovered that just after I'd posted this reply (when I
was building up a reply to the next post regarding dropping all foreign
keys in a database) - same with an ALTER TABLE.
*mike hodgson*
http://sqlnerd.blogspot.com
Erland Sommarskog wrote:
>Mike Hodgson (e1minst3r@.gmail.com) writes:
>
>This has the same problem as the original post. You cannot use a variable
>to hold the name of a table.
>
>|||SQL is a compiled programming language. Do you have any idea what a
compiler is? Please, please do not try to write SQL; you have no idea
what you are doing and need at least a year of intense education and
not just in SQL. .|||How is the book coming along?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1140477991.507316.78270@.f14g2000cwb.googlegroups.com...
> SQL is a compiled programming language. Do you have any idea what a
> compiler is? Please, please do not try to write SQL; you have no idea
> what you are doing and need at least a year of intense education and
> not just in SQL. .
>
Wednesday, March 28, 2012
Is This The Best Solution For Select?
HI , I HAVE A TABLE LIKE THIS
DUP_CODIGO AND DUP_VLDUPLICATA
123 123,66
123 12,88
...
...
...
49 19,99
49 23,99
..
..
51
51
ETC
I want to get the MAX VALUE FROM VLDUPLICATA AND HIS CODIGO
THIS SELECT WORKS FINE. BUT I WOULD LIKE TO
KNOW IF THERE ARE BEST SOLUTIONs.
BY THE way THE RESULT FOR THIS SELECT WILL BE
DUP_CODIGO MAXIMO
49 23,99
SELECT DISTINCT dup_codigo,
(SELECT MAX(DUP_VLDUPLICATA)
FROM DUPLICAT
WHERE DUP_CODIGO = 49) AS MAXIMO
FROM Duplicat
WHERE (dup_codigo = 49)
TKS
Carlos Lagesif you can find the answer to that I would like to know also.
I have a table similiar to that and am using select statement like urs too.|||SELECT dup_codigo,MAX(DUP_VLDUPLICATA)
FROM DUPLICAT
group by dup_codigo|||hmm yeah y din i think of that.. neway that won't solve the problem that I'm having
what if you need to find the max like this
employeeid, dateeffective,effectivesequence,value
000001,1/1/2003,0,123
000001,1/2/2003,0,456
000001,1/2/2003,1,789
max of the dateeffective and effectivesequence...
i'm doing the sql similar to the one given by Carlos. Any suggestions??|||select employeeid
, dateeffective
, effectivesequence
, value
from yourtable X
where dateeffective =
( select max(dateeffective)
from yourtable
where employeeid = X.employeeid )
and effectivesequence =
( select max(effectivesequence)
from yourtable
where employeeid = X.emplyeeid
and dateeffective =
( select max(dateeffective)
from yourtable
where employeeid = X.emplyeeid )
)
rudy
http://r937.com/|||that's the code that I'm having now... which is not really that efficient =(|||well, there are other ways to do it (e.g. joins to derived tables)
but perhaps you might want simply just to select the table, order by dateeffective and effectivesequence, and use a cursor or bring the entire result set into your scripting language and do it there
i'd be interested in hearing about the EXPLAIN plans for whatever alternatives you come up with
rudy
DUP_CODIGO AND DUP_VLDUPLICATA
123 123,66
123 12,88
...
...
...
49 19,99
49 23,99
..
..
51
51
ETC
I want to get the MAX VALUE FROM VLDUPLICATA AND HIS CODIGO
THIS SELECT WORKS FINE. BUT I WOULD LIKE TO
KNOW IF THERE ARE BEST SOLUTIONs.
BY THE way THE RESULT FOR THIS SELECT WILL BE
DUP_CODIGO MAXIMO
49 23,99
SELECT DISTINCT dup_codigo,
(SELECT MAX(DUP_VLDUPLICATA)
FROM DUPLICAT
WHERE DUP_CODIGO = 49) AS MAXIMO
FROM Duplicat
WHERE (dup_codigo = 49)
TKS
Carlos Lagesif you can find the answer to that I would like to know also.
I have a table similiar to that and am using select statement like urs too.|||SELECT dup_codigo,MAX(DUP_VLDUPLICATA)
FROM DUPLICAT
group by dup_codigo|||hmm yeah y din i think of that.. neway that won't solve the problem that I'm having
what if you need to find the max like this
employeeid, dateeffective,effectivesequence,value
000001,1/1/2003,0,123
000001,1/2/2003,0,456
000001,1/2/2003,1,789
max of the dateeffective and effectivesequence...
i'm doing the sql similar to the one given by Carlos. Any suggestions??|||select employeeid
, dateeffective
, effectivesequence
, value
from yourtable X
where dateeffective =
( select max(dateeffective)
from yourtable
where employeeid = X.employeeid )
and effectivesequence =
( select max(effectivesequence)
from yourtable
where employeeid = X.emplyeeid
and dateeffective =
( select max(dateeffective)
from yourtable
where employeeid = X.emplyeeid )
)
rudy
http://r937.com/|||that's the code that I'm having now... which is not really that efficient =(|||well, there are other ways to do it (e.g. joins to derived tables)
but perhaps you might want simply just to select the table, order by dateeffective and effectivesequence, and use a cursor or bring the entire result set into your scripting language and do it there
i'd be interested in hearing about the EXPLAIN plans for whatever alternatives you come up with
rudy
Is this query possible in Sql Server ?
I use this query in Oracle, is such query possible in Sql Server, if so,
what is the sql server syntax.
Select * from Contract
where (cn,rev) in
(select cn,rev from Invoice)
Best Regards,
Luqmanthe possibility to use the query in SQL server is
Select * from Contract
where cn in (select cn from Invoice),rev) and rev in (select rev from
Invoice)
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:OLC$eypCFHA.1292@.TK2MSFTNGP10.phx.gbl...
> I use this query in Oracle, is such query possible in Sql Server, if so,
> what is the sql server syntax.
> Select * from Contract
> where (cn,rev) in
> (select cn,rev from Invoice)
> Best Regards,
> Luqman
>
>
>|||the query is
Select * from Contract
where cn in (select cn from Invoice) and rev in (select rev from Invoice)
"Devinder Singh" <devinder79@.hotmail.com> wrote in message
news:eddin1pCFHA.2540@.TK2MSFTNGP09.phx.gbl...
> the possibility to use the query in SQL server is
> Select * from Contract
> where cn in (select cn from Invoice),rev) and rev in (select rev from
> Invoice)
>
> "Luqman" <pearlsoft@.cyber.net.pk> wrote in message
> news:OLC$eypCFHA.1292@.TK2MSFTNGP10.phx.gbl...
>|||the query is
Select * from Contract where cn in (select cn from Invoice) and rev in
(select rev from Invoice)|||Select * from Contract C
where EXISTS
(select 1 from Invoice I
WHERE I.cn = C.Cn AND I.rev = C.rev)
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:OLC$eypCFHA.1292@.TK2MSFTNGP10.phx.gbl...
>I use this query in Oracle, is such query possible in Sql Server, if so,
> what is the sql server syntax.
> Select * from Contract
> where (cn,rev) in
> (select cn,rev from Invoice)
> Best Regards,
> Luqman
>
>
>|||a simple inner join would probably do here.
dean
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:OLC$eypCFHA.1292@.TK2MSFTNGP10.phx.gbl...
> I use this query in Oracle, is such query possible in Sql Server, if so,
> what is the sql server syntax.
> Select * from Contract
> where (cn,rev) in
> (select cn,rev from Invoice)
> Best Regards,
> Luqman
>
>
>|||On Fri, 4 Feb 2005 15:15:49 +0530, Devinder Singh wrote:
>the query is
>
>Select * from Contract where cn in (select cn from Invoice) and rev in
>(select rev from Invoice)
Hi Devinder,
This won't produce the same results as the original query posted by
Luqman. Luqman wants to know if there is one row in Invoice with matching
cn and rev values - you are testing if there is a row with matching cn and
a row (not necessarily the same) with matching rev.
Roji's solution is correct.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 4 Feb 2005 11:04:03 +0100, Dean wrote:
>a simple inner join would probably do here.
Hi Dean,
Only if the combination of cn + rev is unique in the Invoice table. If it
isn't, you'll get duplicates and you'll have to use a derived table
(SELECT DISTINCT cn, rev FROM Invoice) AS D in your join.
I suggest using Roji's solution, which will work regardless of duplicates
in Invoice, without the need for an expensive DISTINCT operator.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I checked out and I confirm Roji's query meets my requirement.
Thanks to all of you.
Best Regards,
Luqman
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:lhq601hjmukcmbtbedkh8euf6q337ijva3@.
4ax.com...
> On Fri, 4 Feb 2005 11:04:03 +0100, Dean wrote:
>
> Hi Dean,
> Only if the combination of cn + rev is unique in the Invoice table. If it
> isn't, you'll get duplicates and you'll have to use a derived table
> (SELECT DISTINCT cn, rev FROM Invoice) AS D in your join.
> I suggest using Roji's solution, which will work regardless of duplicates
> in Invoice, without the need for an expensive DISTINCT operator.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sql
what is the sql server syntax.
Select * from Contract
where (cn,rev) in
(select cn,rev from Invoice)
Best Regards,
Luqmanthe possibility to use the query in SQL server is
Select * from Contract
where cn in (select cn from Invoice),rev) and rev in (select rev from
Invoice)
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:OLC$eypCFHA.1292@.TK2MSFTNGP10.phx.gbl...
> I use this query in Oracle, is such query possible in Sql Server, if so,
> what is the sql server syntax.
> Select * from Contract
> where (cn,rev) in
> (select cn,rev from Invoice)
> Best Regards,
> Luqman
>
>
>|||the query is
Select * from Contract
where cn in (select cn from Invoice) and rev in (select rev from Invoice)
"Devinder Singh" <devinder79@.hotmail.com> wrote in message
news:eddin1pCFHA.2540@.TK2MSFTNGP09.phx.gbl...
> the possibility to use the query in SQL server is
> Select * from Contract
> where cn in (select cn from Invoice),rev) and rev in (select rev from
> Invoice)
>
> "Luqman" <pearlsoft@.cyber.net.pk> wrote in message
> news:OLC$eypCFHA.1292@.TK2MSFTNGP10.phx.gbl...
>|||the query is
Select * from Contract where cn in (select cn from Invoice) and rev in
(select rev from Invoice)|||Select * from Contract C
where EXISTS
(select 1 from Invoice I
WHERE I.cn = C.Cn AND I.rev = C.rev)
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:OLC$eypCFHA.1292@.TK2MSFTNGP10.phx.gbl...
>I use this query in Oracle, is such query possible in Sql Server, if so,
> what is the sql server syntax.
> Select * from Contract
> where (cn,rev) in
> (select cn,rev from Invoice)
> Best Regards,
> Luqman
>
>
>|||a simple inner join would probably do here.
dean
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:OLC$eypCFHA.1292@.TK2MSFTNGP10.phx.gbl...
> I use this query in Oracle, is such query possible in Sql Server, if so,
> what is the sql server syntax.
> Select * from Contract
> where (cn,rev) in
> (select cn,rev from Invoice)
> Best Regards,
> Luqman
>
>
>|||On Fri, 4 Feb 2005 15:15:49 +0530, Devinder Singh wrote:
>the query is
>
>Select * from Contract where cn in (select cn from Invoice) and rev in
>(select rev from Invoice)
Hi Devinder,
This won't produce the same results as the original query posted by
Luqman. Luqman wants to know if there is one row in Invoice with matching
cn and rev values - you are testing if there is a row with matching cn and
a row (not necessarily the same) with matching rev.
Roji's solution is correct.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 4 Feb 2005 11:04:03 +0100, Dean wrote:
>a simple inner join would probably do here.
Hi Dean,
Only if the combination of cn + rev is unique in the Invoice table. If it
isn't, you'll get duplicates and you'll have to use a derived table
(SELECT DISTINCT cn, rev FROM Invoice) AS D in your join.
I suggest using Roji's solution, which will work regardless of duplicates
in Invoice, without the need for an expensive DISTINCT operator.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I checked out and I confirm Roji's query meets my requirement.
Thanks to all of you.
Best Regards,
Luqman
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:lhq601hjmukcmbtbedkh8euf6q337ijva3@.
4ax.com...
> On Fri, 4 Feb 2005 11:04:03 +0100, Dean wrote:
>
> Hi Dean,
> Only if the combination of cn + rev is unique in the Invoice table. If it
> isn't, you'll get duplicates and you'll have to use a derived table
> (SELECT DISTINCT cn, rev FROM Invoice) AS D in your join.
> I suggest using Roji's solution, which will work regardless of duplicates
> in Invoice, without the need for an expensive DISTINCT operator.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sql
Is this query correct
I have two tables cd_customer and cd_customer_parent_hierarchy, I want to select all those customers from the parent table that are in the child table where the seed_flag for the child key is yes but the seed_flag for the parent is no
SELECT ccph.parent_customer_key
FROM cd_customer cc,
cd_customer_parent_hierarchy ccph,
cd_customer cc_p
WHERE
ccph.customer_key = cc.customer_key AND
ccph.parent_customer_key = cc_p.customer_key AND
CCPH.HIERARCHY_TYPE = '-' AND
CC.SEED_FLAG = 'Y' AND
CC_P.SEED_FLAG = 'N'
also what would be the difference in queries if I were to add a group by ccph.parent_customer_key
Thanksquery looks okay, given the fact that we can't see your table layouts nor sample data
if you add that GROUP BY, two things happen -- the query slows down, and only unique values will be returned
SELECT ccph.parent_customer_key
FROM cd_customer cc,
cd_customer_parent_hierarchy ccph,
cd_customer cc_p
WHERE
ccph.customer_key = cc.customer_key AND
ccph.parent_customer_key = cc_p.customer_key AND
CCPH.HIERARCHY_TYPE = '-' AND
CC.SEED_FLAG = 'Y' AND
CC_P.SEED_FLAG = 'N'
also what would be the difference in queries if I were to add a group by ccph.parent_customer_key
Thanksquery looks okay, given the fact that we can't see your table layouts nor sample data
if you add that GROUP BY, two things happen -- the query slows down, and only unique values will be returned
Monday, March 26, 2012
Is this possible?
I can write a stored proc to create a temp table and with "while" loops achi
eve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and ClientCit
ies and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in Client
Cities and four rows in ClientProducts, so this is a means of listing Cities
and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6 ro
ws with products only in the first four rows.
Hope this makes sense.
Thanks.GOT DDL?
Sounds like what you might need is one of the JOINs, but it's hard to give y
ou the proper syntax without your DDL. For instance, can you sell Pears and
Apples in LA? If so, how do you want that listed? If you can post your DD
L and sample data in addition to your expected output you can probably get a
proper answer pretty quickly.
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message news:eJzV629ZFH
A.1412@.TK2MSFTNGP12.phx.gbl...
I can write a stored proc to create a temp table and with "while" loops achi
eve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and ClientCit
ies and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in Client
Cities and four rows in ClientProducts, so this is a means of listing Cities
and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6 ro
ws with products only in the first four rows.
Hope this makes sense.
Thanks.|||Hi
u can do this using an outer join like
As an example, if I have 3 tables, Clients (with ID and name), and
ClientCities and ClientProducts.
select clientid , cityname , productname
from clients , clientcities , clientproducts
where clients.clientid *= clientcities.clientid
and clients.clientid *= clientproducts.clientid
renjith
"Chris Botha" wrote:
> I can write a stored proc to create a temp table and with "while" loops ac
hieve this, but was wondering if there is a select/something simpler.
> As an example, if I have 3 tables, Clients (with ID and name), and ClientC
ities and ClientProducts. Then I want to list them something like:
> ClientId City Product
> -- -- --
> 1 LA Apples
> 1 NY Pears
> 1 Oranges
> 1 Bananas
> Note that in the example above for ClientId = 1 there are two rows in Clie
ntCities and four rows in ClientProducts, so this is a means of listing Citi
es and Products on as few lines as possible.
> If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
> Hope this makes sense.
> Thanks|||Hi,
I think there is a key missing here.
If clientId is the only key, you are going to form a many-to-many
relationship after you use JOIN to combine the data.
So, you may need to add another between city and product.
Else, the output that you are showing is not a relational result. This is
not RDBMS meant to be.
Leo Leong
"Chris Botha" wrote:
> I can write a stored proc to create a temp table and with "while" loops ac
hieve this, but was wondering if there is a select/something simpler.
> As an example, if I have 3 tables, Clients (with ID and name), and ClientC
ities and ClientProducts. Then I want to list them something like:
> ClientId City Product
> -- -- --
> 1 LA Apples
> 1 NY Pears
> 1 Oranges
> 1 Bananas
> Note that in the example above for ClientId = 1 there are two rows in Clie
ntCities and four rows in ClientProducts, so this is a means of listing Citi
es and Products on as few lines as possible.
> If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
> Hope this makes sense.
> Thanks|||> For instance, can you sell Pears and Apples in LA?
Hi Michael, all products are sold in all cities, I just want the shortest
list listing Cities and Products, so if the Cities table had WA in as well,
in my example table below it should appear on the row having Oranges.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:wKOne.38973$NZ1.12558@.fe09.lga...
GOT DDL?
Sounds like what you might need is one of the JOINs, but it's hard to give
you the proper syntax without your DDL. For instance, can you sell Pears
and Apples in LA? If so, how do you want that listed? If you can post your
DDL and sample data in addition to your expected output you can probably get
a proper answer pretty quickly.
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message
news:eJzV629ZFHA.1412@.TK2MSFTNGP12.phx.gbl...
I can write a stored proc to create a temp table and with "while" loops
achieve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and
ClientCities and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
Hope this makes sense.
Thanks.|||Thanks Renjith, problem with this is it will repeat every product for every
city, so in my example table below it will show 8 lines, LA repeated with
every product, and NY repeated with every product. Adding a 3rd city will
show 12 rows, while it should appear on the row with the Oranges.
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:25524AF2-4771-4864-BE75-F2BEB42588BB@.microsoft.com...
> Hi
> u can do this using an outer join like
> As an example, if I have 3 tables, Clients (with ID and name), and
> ClientCities and ClientProducts.
> select clientid , cityname , productname
> from clients , clientcities , clientproducts
> where clients.clientid *= clientcities.clientid
> and clients.clientid *= clientproducts.clientid
> renjith
>
> "Chris Botha" wrote:
>
achieve this, but was wondering if there is a select/something simpler.
ClientCities and ClientProducts. Then I want to list them something like:
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
6 rows with products only in the first four rows.|||Hi Leo, sorry, I guess my example is not that good, all of the tables have
Client_ID as a column.
And you are right when you say "the output that you are showing is not a
relational result", in this case it is not, it is taking all cities and all
products for this client and showing them in the shortest list.
"Leo Leong" <LeoLeong@.discussions.microsoft.com> wrote in message
news:B6AAFCA0-4E47-4690-89A3-3E547E6E51F3@.microsoft.com...
> Hi,
> I think there is a key missing here.
> If clientId is the only key, you are going to form a many-to-many
> relationship after you use JOIN to combine the data.
> So, you may need to add another between city and product.
> Else, the output that you are showing is not a relational result. This is
> not RDBMS meant to be.
> Leo Leong
> "Chris Botha" wrote:
>
achieve this, but was wondering if there is a select/something simpler.
ClientCities and ClientProducts. Then I want to list them something like:
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
6 rows with products only in the first four rows.|||Here's what it looks like you want to do:
SELECT 1 AS ClientID, s1.Cityname, s2.FruitName
FROM
(
SELECT TOP 100 PERCENT CityRank=COUNT(*), c1.Cityname
FROM CITIES c1, CITIES c2
WHERE c1.CityName >= c2.CityName
GROUP BY c1.CityName
ORDER BY CityRank
) s1
FULL OUTER JOIN
(
SELECT TOP 100 PERCENT FruitRank=COUNT(*), f1.Fruitname
FROM FRUITS f1, FRUITS f2
WHERE f1.FruitName >= f2.FruitName
GROUP BY f1.FruitName
ORDER BY FruitRank
) s2
ON s1.CityRank = s2.FruitRank
Which results in the following output on my schema:
1, LA, Apples
1, NY, Bananas
1, NULL, Oranges
1, NULL, Pears
Of course you'll have to modify it to match your schema and to join on your
Clients table.
Enjoy.
of Uniqe items, but all side-by-side
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message
news:efaOo5DaFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Hi Leo, sorry, I guess my example is not that good, all of the tables have
> Client_ID as a column.
> And you are right when you say "the output that you are showing is not a
> relational result", in this case it is not, it is taking all cities and
> all
> products for this client and showing them in the shortest list.
>
> "Leo Leong" <LeoLeong@.discussions.microsoft.com> wrote in message
> news:B6AAFCA0-4E47-4690-89A3-3E547E6E51F3@.microsoft.com...
> achieve this, but was wondering if there is a select/something simpler.
> ClientCities and ClientProducts. Then I want to list them something like:
> ClientCities and four rows in ClientProducts, so this is a means of
> listing
> Cities and Products on as few lines as possible.
> 6 rows with products only in the first four rows.
>|||On Thu, 2 Jun 2005 23:52:05 -0700, Renjith wrote:
>Hi
>u can do this using an outer join like
>As an example, if I have 3 tables, Clients (with ID and name), and
>ClientCities and ClientProducts.
>select clientid , cityname , productname
>from clients , clientcities , clientproducts
>where clients.clientid *= clientcities.clientid
>and clients.clientid *= clientproducts.clientid
Hi renjith,
Not only will that produce more rows than the OP asked for, it also uses
a depracated outer join construction.
Please don't create any new code with the =* and *= operators. Please
use the infixed outer join syntax instead. AFAIK, the =* and *= will
already stop working in SQL Server 2005, unless you lower the
compatibility level!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Hope this makes sense. <<
Actually it does not. A table is a collection of facts with one fact
per row. You want to destroy data and create falsehoods. Put this in
a VIEW or simplely remember any combination of prtoduct and place is
valid. next, this violates the rule that you do display in the front
end and not the database.
eve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and ClientCit
ies and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in Client
Cities and four rows in ClientProducts, so this is a means of listing Cities
and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6 ro
ws with products only in the first four rows.
Hope this makes sense.
Thanks.GOT DDL?
Sounds like what you might need is one of the JOINs, but it's hard to give y
ou the proper syntax without your DDL. For instance, can you sell Pears and
Apples in LA? If so, how do you want that listed? If you can post your DD
L and sample data in addition to your expected output you can probably get a
proper answer pretty quickly.
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message news:eJzV629ZFH
A.1412@.TK2MSFTNGP12.phx.gbl...
I can write a stored proc to create a temp table and with "while" loops achi
eve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and ClientCit
ies and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in Client
Cities and four rows in ClientProducts, so this is a means of listing Cities
and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6 ro
ws with products only in the first four rows.
Hope this makes sense.
Thanks.|||Hi
u can do this using an outer join like
As an example, if I have 3 tables, Clients (with ID and name), and
ClientCities and ClientProducts.
select clientid , cityname , productname
from clients , clientcities , clientproducts
where clients.clientid *= clientcities.clientid
and clients.clientid *= clientproducts.clientid
renjith
"Chris Botha" wrote:
> I can write a stored proc to create a temp table and with "while" loops ac
hieve this, but was wondering if there is a select/something simpler.
> As an example, if I have 3 tables, Clients (with ID and name), and ClientC
ities and ClientProducts. Then I want to list them something like:
> ClientId City Product
> -- -- --
> 1 LA Apples
> 1 NY Pears
> 1 Oranges
> 1 Bananas
> Note that in the example above for ClientId = 1 there are two rows in Clie
ntCities and four rows in ClientProducts, so this is a means of listing Citi
es and Products on as few lines as possible.
> If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
> Hope this makes sense.
> Thanks|||Hi,
I think there is a key missing here.
If clientId is the only key, you are going to form a many-to-many
relationship after you use JOIN to combine the data.
So, you may need to add another between city and product.
Else, the output that you are showing is not a relational result. This is
not RDBMS meant to be.
Leo Leong
"Chris Botha" wrote:
> I can write a stored proc to create a temp table and with "while" loops ac
hieve this, but was wondering if there is a select/something simpler.
> As an example, if I have 3 tables, Clients (with ID and name), and ClientC
ities and ClientProducts. Then I want to list them something like:
> ClientId City Product
> -- -- --
> 1 LA Apples
> 1 NY Pears
> 1 Oranges
> 1 Bananas
> Note that in the example above for ClientId = 1 there are two rows in Clie
ntCities and four rows in ClientProducts, so this is a means of listing Citi
es and Products on as few lines as possible.
> If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
> Hope this makes sense.
> Thanks|||> For instance, can you sell Pears and Apples in LA?
Hi Michael, all products are sold in all cities, I just want the shortest
list listing Cities and Products, so if the Cities table had WA in as well,
in my example table below it should appear on the row having Oranges.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:wKOne.38973$NZ1.12558@.fe09.lga...
GOT DDL?
Sounds like what you might need is one of the JOINs, but it's hard to give
you the proper syntax without your DDL. For instance, can you sell Pears
and Apples in LA? If so, how do you want that listed? If you can post your
DDL and sample data in addition to your expected output you can probably get
a proper answer pretty quickly.
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message
news:eJzV629ZFHA.1412@.TK2MSFTNGP12.phx.gbl...
I can write a stored proc to create a temp table and with "while" loops
achieve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and
ClientCities and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
Hope this makes sense.
Thanks.|||Thanks Renjith, problem with this is it will repeat every product for every
city, so in my example table below it will show 8 lines, LA repeated with
every product, and NY repeated with every product. Adding a 3rd city will
show 12 rows, while it should appear on the row with the Oranges.
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:25524AF2-4771-4864-BE75-F2BEB42588BB@.microsoft.com...
> Hi
> u can do this using an outer join like
> As an example, if I have 3 tables, Clients (with ID and name), and
> ClientCities and ClientProducts.
> select clientid , cityname , productname
> from clients , clientcities , clientproducts
> where clients.clientid *= clientcities.clientid
> and clients.clientid *= clientproducts.clientid
> renjith
>
> "Chris Botha" wrote:
>
achieve this, but was wondering if there is a select/something simpler.
ClientCities and ClientProducts. Then I want to list them something like:
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
6 rows with products only in the first four rows.|||Hi Leo, sorry, I guess my example is not that good, all of the tables have
Client_ID as a column.
And you are right when you say "the output that you are showing is not a
relational result", in this case it is not, it is taking all cities and all
products for this client and showing them in the shortest list.
"Leo Leong" <LeoLeong@.discussions.microsoft.com> wrote in message
news:B6AAFCA0-4E47-4690-89A3-3E547E6E51F3@.microsoft.com...
> Hi,
> I think there is a key missing here.
> If clientId is the only key, you are going to form a many-to-many
> relationship after you use JOIN to combine the data.
> So, you may need to add another between city and product.
> Else, the output that you are showing is not a relational result. This is
> not RDBMS meant to be.
> Leo Leong
> "Chris Botha" wrote:
>
achieve this, but was wondering if there is a select/something simpler.
ClientCities and ClientProducts. Then I want to list them something like:
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
6 rows with products only in the first four rows.|||Here's what it looks like you want to do:
SELECT 1 AS ClientID, s1.Cityname, s2.FruitName
FROM
(
SELECT TOP 100 PERCENT CityRank=COUNT(*), c1.Cityname
FROM CITIES c1, CITIES c2
WHERE c1.CityName >= c2.CityName
GROUP BY c1.CityName
ORDER BY CityRank
) s1
FULL OUTER JOIN
(
SELECT TOP 100 PERCENT FruitRank=COUNT(*), f1.Fruitname
FROM FRUITS f1, FRUITS f2
WHERE f1.FruitName >= f2.FruitName
GROUP BY f1.FruitName
ORDER BY FruitRank
) s2
ON s1.CityRank = s2.FruitRank
Which results in the following output on my schema:
1, LA, Apples
1, NY, Bananas
1, NULL, Oranges
1, NULL, Pears
Of course you'll have to modify it to match your schema and to join on your
Clients table.
Enjoy.
of Uniqe items, but all side-by-side
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message
news:efaOo5DaFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Hi Leo, sorry, I guess my example is not that good, all of the tables have
> Client_ID as a column.
> And you are right when you say "the output that you are showing is not a
> relational result", in this case it is not, it is taking all cities and
> all
> products for this client and showing them in the shortest list.
>
> "Leo Leong" <LeoLeong@.discussions.microsoft.com> wrote in message
> news:B6AAFCA0-4E47-4690-89A3-3E547E6E51F3@.microsoft.com...
> achieve this, but was wondering if there is a select/something simpler.
> ClientCities and ClientProducts. Then I want to list them something like:
> ClientCities and four rows in ClientProducts, so this is a means of
> listing
> Cities and Products on as few lines as possible.
> 6 rows with products only in the first four rows.
>|||On Thu, 2 Jun 2005 23:52:05 -0700, Renjith wrote:
>Hi
>u can do this using an outer join like
>As an example, if I have 3 tables, Clients (with ID and name), and
>ClientCities and ClientProducts.
>select clientid , cityname , productname
>from clients , clientcities , clientproducts
>where clients.clientid *= clientcities.clientid
>and clients.clientid *= clientproducts.clientid
Hi renjith,
Not only will that produce more rows than the OP asked for, it also uses
a depracated outer join construction.
Please don't create any new code with the =* and *= operators. Please
use the infixed outer join syntax instead. AFAIK, the =* and *= will
already stop working in SQL Server 2005, unless you lower the
compatibility level!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Hope this makes sense. <<
Actually it does not. A table is a collection of facts with one fact
per row. You want to destroy data and create falsehoods. Put this in
a VIEW or simplely remember any combination of prtoduct and place is
valid. next, this violates the rule that you do display in the front
end and not the database.
Is this possible?
Hello,
My question relates to the following select statement:
Select Report_description from Report where Report_name = (grab this value from the item selected from a listbox)
I wonder whether it would be possible to make the above statement a stored procedure but instead of filling in the last value in the bracket, I would like to grab that value from else where, for example from an item from a listbox which has been selected by the user.
Hi is Dude
we use this n number of time. The thing you need to do is, just create the comma separated value of the selected item list in the front end.
As an Example
list selected values as
'i','am','a',boy' (you need to do this in the front end itself)
in query do like this
Select Report_description from Report where Report_name in('i','am','a',boy')
you need to use the IN operator to select the selected values for the Report table
Regards,
Thanks.
Gurpreet S. Gill
|||
Hi Gill,
Its great to know that this can be done. Unfortunately I am a newbie to all this. Could you please elaborate? For example if I was using ASP.NET, and I suppose alll this code would go into the code behind file of the list box control? So what would the code actually look like? And would I just leave the last value in the stored procedure as a blank space?
Thank you so much
|||I cant say much about the ASP.NET, but this code works for me.
here the ListBox1 is the List box from where you want to collect the values, CSV is string variable, used in IN clause of SQL
Try this
Dim CSV As String, SQL As String, i As Integer
CSV = ""
'Loop to all the Items in the ListBox1
For i = 0 To ListBox1.Items.Count - 1
'Check if selected or not
If ListBox1.Items(i).Selected Then
' if selected, make the comma separated value single Quote around it
CSV = CSV & "'" & ListBox1.Items(i).Text & "' , "
End If
Next
' Ignore the last extra comma
CSV = Left(CSV, Len(CSV) - 3)
' Create the SQL command
SQL = "Select Report_description from Report where Report_name IN( " & CSV & " )"
' Your codes goes here
' Use the SQL variable to execute the query
'
Kiind Regards,
Gurpreet S. Gill
|||ohhhh, PLEASE IGNORE THIS post twice same
Dim CSV As String, SQL As String, i As Integer
CSV = ""
'Loop to all the Items in the ListBox1
For i = 0 To ListBox1.Items.Count - 1
'Check if selected or not
If ListBox1.Items(i).Selected Then
' if selected, make the comma separated value single Quote around it
CSV = CSV & "'" & ListBox1.Items(i).Text & "' , "
End If
Next
' Ignore the last extra comma
CSV = Left(CSV, Len(CSV) - 3)
' Create the SQL command
SQL = "Select Report_description from Report where Report_name IN( " & CSV & " )"
' Your codes goes here
' Use the SQL variable to execute the query
'
Kind Regards,
Gurpreet S. GIll
|||thank you very much gill!!!
Is this possible?
I have a stored procedure and I want to do something like:
INSERT INTO @.Table
(Field1, Field2, Field3, ...FieldN)
SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
FROM @.Table2
WHERE n is null
SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
FROM @.Table2
WHERE x < 50
SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
FROM @.Table2
WHERE x >= 50
Can I somehow do this? Since I couldn't return the result sets from my
3 selects, I combined them into one result set.
Right now I have a giant UPDATE statement, but it seems really
unwieldy...it's something like:
UPDATE @.Table
SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
There has to be a better way. (Can you tell I only half know what I'm
doing?)
Thank you!union your selects together
Confused wrote:
> I have a stored procedure and I want to do something like:
> INSERT INTO @.Table
> (Field1, Field2, Field3, ...FieldN)
> SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
> FROM @.Table2
> WHERE n is null
> SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
> FROM @.Table2
> WHERE x < 50
> SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
> FROM @.Table2
> WHERE x >= 50
> Can I somehow do this? Since I couldn't return the result sets from my
> 3 selects, I combined them into one result set.
> Right now I have a giant UPDATE statement, but it seems really
> unwieldy...it's something like:
> UPDATE @.Table
> SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
> There has to be a better way. (Can you tell I only half know what I'm
> doing?)
> Thank you!
>|||Do:
SELECT SUM( CASE WHEN n IS NULL THEN a END ) AS "Field1",
SUM( CASE WHEN n IS NULL THEN b END ) AS "Field2",
SUM( CASE WHEN n IS NULL THEN c END ) AS "Field3",
SUM( CASE WHEN x < 50 THEN a END ) AS "Field4",
SUM( CASE WHEN x < 50 THEN b END ) AS "Field5",
SUM( CASE WHEN x < 50 THEN c END ) AS "Field6",
SUM( CASE WHEN x >= 50 THEN a END ) AS "Field7",
SUM( CASE WHEN x >= 50 THEN b END ) AS "Field8",
SUM( CASE WHEN x >= 50 THEN c END ) AS "Field9"
FROM Table2 ;
Anith|||"Confused" <cschanz@.gmail.com> wrote in message
news:1135287913.453858.252290@.g49g2000cwa.googlegroups.com...
>I have a stored procedure and I want to do something like:
> INSERT INTO @.Table
> (Field1, Field2, Field3, ...FieldN)
> SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
> FROM @.Table2
> WHERE n is null
> SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
> FROM @.Table2
> WHERE x < 50
> SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
> FROM @.Table2
> WHERE x >= 50
> Can I somehow do this? Since I couldn't return the result sets from my
> 3 selects, I combined them into one result set.
> Right now I have a giant UPDATE statement, but it seems really
> unwieldy...it's something like:
> UPDATE @.Table
> SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
> There has to be a better way. (Can you tell I only half know what I'm
> doing?)
> Thank you!
>
See the following example. Of course you would also replace the word "field"
with "column" because if you even half knew what you were doing then you'd
know that a column isn't a field. :-)
INSERT INTO @.Table
(field1, field2, field3, field4, field5, field6, field7, field8, field9)
SELECT
SUM(CASE WHEN n IS NULL THEN a END) AS field1,
SUM(CASE WHEN n IS NULL THEN b END) AS field2,
SUM(CASE WHEN n IS NULL THEN c END) AS field3,
SUM(CASE WHEN x < 50 THEN a END) AS field4,
SUM(CASE WHEN x < 50 THEN b END) AS field5,
SUM(CASE WHEN x < 50 THEN c END) AS field6,
SUM(CASE WHEN x >= 50 THEN a END) AS field7,
SUM(CASE WHEN x >= 50 THEN b END) AS field8,
SUM(CASE WHEN x >= 50 THEN c END) AS field9
FROM @.Table2 ;
David Portas
SQL Server MVP
--|||Use a case statement to populate Table2.
select <Other Columns>, sum(case when n is null then a else 0 end) as
Field1,
sum(case when n is null then b else 0 end) as Field2,
sum(case when n is null then c else 0 end) as Field3,
sum(case when x < 50 then a else 0 end) as Field4,
sum(case when x < 50 then b else 0 end) as Field5,
sum(case when x < 50 then c else 0 end) as Field6,
sum(case when x >= 50 then a else 0 end) as Field7,
sum(case when x >= 50 then b else 0 end) as Field8,
sum(case when x >= 50 then c else 0 end) as Field9
from Table2
group by <Other Columns>|||ok - i'm not even waiting for my other post to get out there - ignore it
- incomplete
insert into @.table (Field1, ..., Field9)
select
sum(case when n is null then a end) as Field1,
sum(case when n is null then b end) as Field2,
sum(case when n is null then c end) as Field3,
sum(case when x<50 then a end) as Field4,
sum(case when x<50 then b end) as Field5,
sum(case when x<50 then c end) as Field6,
sum(case when x>=50 then a end) as Field7,
sum(case when x>=50 then b end) as Field8,
sum(case when x>=50 then c end) as Field9
from @.Table2
another possibility is to change your table1 to have a criteria
indicator and fewer columns, and union the queries together, e.g.
insert into @.Table (criteria, Field1, Field2, Field3)
select 'null n', sum(a), sum(b), sum(c)
from @.table2
where n is null
union all
select 'x>50', sum(a), sum(b), sum(c)
from @.table2
where x>50
union all
select 'x<=50', sum(a), sum(b), sum(c)
from @.table2
where x<=50
Confused wrote:
> I have a stored procedure and I want to do something like:
> INSERT INTO @.Table
> (Field1, Field2, Field3, ...FieldN)
> SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
> FROM @.Table2
> WHERE n is null
> SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
> FROM @.Table2
> WHERE x < 50
> SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
> FROM @.Table2
> WHERE x >= 50
> Can I somehow do this? Since I couldn't return the result sets from my
> 3 selects, I combined them into one result set.
> Right now I have a giant UPDATE statement, but it seems really
> unwieldy...it's something like:
> UPDATE @.Table
> SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
> There has to be a better way. (Can you tell I only half know what I'm
> doing?)
> Thank you!
>|||Oops...I forgot to put that...I did try that. And when I do that I get
the following error:
The select list for the INSERT statement contains fewer items than the
insert list. The number of SELECT values must match the number of
INSERT columns.|||Thank you everyone for the input and help! It is much appreciated!
INSERT INTO @.Table
(Field1, Field2, Field3, ...FieldN)
SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
FROM @.Table2
WHERE n is null
SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
FROM @.Table2
WHERE x < 50
SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
FROM @.Table2
WHERE x >= 50
Can I somehow do this? Since I couldn't return the result sets from my
3 selects, I combined them into one result set.
Right now I have a giant UPDATE statement, but it seems really
unwieldy...it's something like:
UPDATE @.Table
SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
There has to be a better way. (Can you tell I only half know what I'm
doing?)
Thank you!union your selects together
Confused wrote:
> I have a stored procedure and I want to do something like:
> INSERT INTO @.Table
> (Field1, Field2, Field3, ...FieldN)
> SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
> FROM @.Table2
> WHERE n is null
> SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
> FROM @.Table2
> WHERE x < 50
> SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
> FROM @.Table2
> WHERE x >= 50
> Can I somehow do this? Since I couldn't return the result sets from my
> 3 selects, I combined them into one result set.
> Right now I have a giant UPDATE statement, but it seems really
> unwieldy...it's something like:
> UPDATE @.Table
> SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
> There has to be a better way. (Can you tell I only half know what I'm
> doing?)
> Thank you!
>|||Do:
SELECT SUM( CASE WHEN n IS NULL THEN a END ) AS "Field1",
SUM( CASE WHEN n IS NULL THEN b END ) AS "Field2",
SUM( CASE WHEN n IS NULL THEN c END ) AS "Field3",
SUM( CASE WHEN x < 50 THEN a END ) AS "Field4",
SUM( CASE WHEN x < 50 THEN b END ) AS "Field5",
SUM( CASE WHEN x < 50 THEN c END ) AS "Field6",
SUM( CASE WHEN x >= 50 THEN a END ) AS "Field7",
SUM( CASE WHEN x >= 50 THEN b END ) AS "Field8",
SUM( CASE WHEN x >= 50 THEN c END ) AS "Field9"
FROM Table2 ;
Anith|||"Confused" <cschanz@.gmail.com> wrote in message
news:1135287913.453858.252290@.g49g2000cwa.googlegroups.com...
>I have a stored procedure and I want to do something like:
> INSERT INTO @.Table
> (Field1, Field2, Field3, ...FieldN)
> SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
> FROM @.Table2
> WHERE n is null
> SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
> FROM @.Table2
> WHERE x < 50
> SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
> FROM @.Table2
> WHERE x >= 50
> Can I somehow do this? Since I couldn't return the result sets from my
> 3 selects, I combined them into one result set.
> Right now I have a giant UPDATE statement, but it seems really
> unwieldy...it's something like:
> UPDATE @.Table
> SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
> There has to be a better way. (Can you tell I only half know what I'm
> doing?)
> Thank you!
>
See the following example. Of course you would also replace the word "field"
with "column" because if you even half knew what you were doing then you'd
know that a column isn't a field. :-)
INSERT INTO @.Table
(field1, field2, field3, field4, field5, field6, field7, field8, field9)
SELECT
SUM(CASE WHEN n IS NULL THEN a END) AS field1,
SUM(CASE WHEN n IS NULL THEN b END) AS field2,
SUM(CASE WHEN n IS NULL THEN c END) AS field3,
SUM(CASE WHEN x < 50 THEN a END) AS field4,
SUM(CASE WHEN x < 50 THEN b END) AS field5,
SUM(CASE WHEN x < 50 THEN c END) AS field6,
SUM(CASE WHEN x >= 50 THEN a END) AS field7,
SUM(CASE WHEN x >= 50 THEN b END) AS field8,
SUM(CASE WHEN x >= 50 THEN c END) AS field9
FROM @.Table2 ;
David Portas
SQL Server MVP
--|||Use a case statement to populate Table2.
select <Other Columns>, sum(case when n is null then a else 0 end) as
Field1,
sum(case when n is null then b else 0 end) as Field2,
sum(case when n is null then c else 0 end) as Field3,
sum(case when x < 50 then a else 0 end) as Field4,
sum(case when x < 50 then b else 0 end) as Field5,
sum(case when x < 50 then c else 0 end) as Field6,
sum(case when x >= 50 then a else 0 end) as Field7,
sum(case when x >= 50 then b else 0 end) as Field8,
sum(case when x >= 50 then c else 0 end) as Field9
from Table2
group by <Other Columns>|||ok - i'm not even waiting for my other post to get out there - ignore it
- incomplete
insert into @.table (Field1, ..., Field9)
select
sum(case when n is null then a end) as Field1,
sum(case when n is null then b end) as Field2,
sum(case when n is null then c end) as Field3,
sum(case when x<50 then a end) as Field4,
sum(case when x<50 then b end) as Field5,
sum(case when x<50 then c end) as Field6,
sum(case when x>=50 then a end) as Field7,
sum(case when x>=50 then b end) as Field8,
sum(case when x>=50 then c end) as Field9
from @.Table2
another possibility is to change your table1 to have a criteria
indicator and fewer columns, and union the queries together, e.g.
insert into @.Table (criteria, Field1, Field2, Field3)
select 'null n', sum(a), sum(b), sum(c)
from @.table2
where n is null
union all
select 'x>50', sum(a), sum(b), sum(c)
from @.table2
where x>50
union all
select 'x<=50', sum(a), sum(b), sum(c)
from @.table2
where x<=50
Confused wrote:
> I have a stored procedure and I want to do something like:
> INSERT INTO @.Table
> (Field1, Field2, Field3, ...FieldN)
> SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
> FROM @.Table2
> WHERE n is null
> SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
> FROM @.Table2
> WHERE x < 50
> SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
> FROM @.Table2
> WHERE x >= 50
> Can I somehow do this? Since I couldn't return the result sets from my
> 3 selects, I combined them into one result set.
> Right now I have a giant UPDATE statement, but it seems really
> unwieldy...it's something like:
> UPDATE @.Table
> SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
> There has to be a better way. (Can you tell I only half know what I'm
> doing?)
> Thank you!
>|||Oops...I forgot to put that...I did try that. And when I do that I get
the following error:
The select list for the INSERT statement contains fewer items than the
insert list. The number of SELECT values must match the number of
INSERT columns.|||Thank you everyone for the input and help! It is much appreciated!
Is this possible......??
Say I have a table with one field and there are 26 records in it... A, B, C, D, E, ... etc.
If I do a select statement on that table I get 26 records, one for each field. (select fieldName from tblName)
Is it possible to set a variable equal to a single string off of the select statement and delimit it with a chosen delimiter (ie "A,B,C,D,E,F......")
Thank You for the help!!!With which DBMS? There is no standard SQL answer to this.|||Using MS SQL 7|||Looking for something like
set @.stringName = (select fieldName & ',' from tblName)
So that @.stringName is set to a string 'A,B,C,D,E,....'|||What about this?
drop table test
create table test(id int identity,code varchar(10))
go
insert test(code) values('a')
insert test(code) values('b')
insert test(code) values('c')
insert test(code) values('d')
insert test(code) values('e')
go
declare @.str varchar(8000)
set @.str=''
select @.str=@.str+code from test
select @.str|||Originally posted by snail
What about this?
drop table test
create table test(id int identity,code varchar(10))
go
insert test(code) values('a')
insert test(code) values('b')
insert test(code) values('c')
insert test(code) values('d')
insert test(code) values('e')
go
declare @.str varchar(8000)
set @.str=''
select @.str=@.str+code from test
select @.str That's pretty much what I'm looking for but I don't understand how your '+ code from test' is going to work. That piece should be my recordset
set @.str = @.str + (select fieldName from tblName)
something like that where my recordset can be turned into a string.|||Originally posted by gman_gsxr750
That's pretty much what I'm looking for but I don't understand how your '+ code from test' is going to work. That piece should be my recordset
set @.str = @.str + (select fieldName from tblName)
something like that where my recordset can be turned into a string.
Just try and you'll see...|||Originally posted by snail
Just try and you'll see... Holy moley!!! I've never seen that before!!!
Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!
One last question (only because I've never used the code in that way before...
can I put a conditional on it
set @.str = @.str + code from table (where id < 100)
or something like that?
And did I mention..... Thank you!|||Originally posted by gman_gsxr750
Holy moley!!! I've never seen that before!!!
Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!
One last question (only because I've never used the code in that way before...
can I put a conditional on it
set @.str = @.str + code from table (where id < 100)
or something like that?
And did I mention..... Thank you!
Why not?|||Originally posted by snail
Why not? Ever get that rush when something finally goes your way and things work out?
Thank you soooooooooo much! I got the conditional to work as well. I just need to play with this a little to figure out the nuances.
Do you know what that kind of query is called so I can reference?|||Originally posted by gman_gsxr750
Ever get that rush when something finally goes your way and things work out?
Thank you soooooooooo much! I got the conditional to work as well. I just need to play with this a little to figure out the nuances.
Do you know what that kind of query is called so I can reference?
I have no idea...|||Originally posted by snail
I have no idea... OK, last question, hopefully you can help me with this.
Here's my code:
declare @.idpeople int
set @.idpeople = 200002
declare @.str varchar(8000)
set @.str = ''
select @.str = @.str + ',' + ideventcode from tblPeopleEvents where idpeople = @.idpeople
let's say this returns the following ',CXL,AS' (two codes CXL and AS)
this works fine, but if I add an order by clause to it (so it returns AS,CXL instead), I only get one of the two values (CXL)
any ideas on how far I can take the select portion (where, order by, group by, etc)?|||it's called a magic query
it works, and it produces the result by magic
hey snail, where's the comma between values?
;)|||OK, here's my final code. I used a subquery to get the result set the way I needed it. Much thanks to Snail for the help. And to r937 for the sarcasm ;).
create procedure spGetEventString
@.idpeople int,
@.eventString varchar(255) OUTPUT
as
set @.eventString = ''
-- Create string of Event codes
-- use sub query to order result set
select @.eventString = @.eventString + rTPE.ideventcode
from
(
select top 100 idEventCode + ',' as idEventCode
from tblPeopleEvents
where idpeople = @.idpeople
order by idEventCode
) as rTPE
--Remove Trailing Comma
set @.eventString = left(@.eventString,len(@.eventString)-1)
return|||Originally posted by r937
it's called a magic query
it works, and it produces the result by magic
hey snail, where's the comma between values?
;)
I am not a magician I am only learning... ;)|||I think it should be called the Loophole query, because it doesn't look like it should work, but it does.|||Originally posted by blindman
I think it should be called the Loophole query, because it doesn't look like it should work, but it does.
That is a bit harsh ...
http://www.dbforums.com/showthread.php?threadid=979593|||Originally posted by Enigma
That is a bit harsh ...
http://www.dbforums.com/showthread.php?threadid=979593 Yep, that's it. Works like a charm too!|||This type of query is the coolest thing I've learned from DBForums, but I haven't seen any Microsoft Documentation that talks about it. That's why it seems like a loophole to me.
Does anybody know of any BOL or MS Support references regarding this self-referential query?|||Thats what it should be called -->"A self-referential query"|||and it even does what Enigma was asking in the referenced post:
select @.str=@.str+case @.str when '' then '' else ',' end + code from test
If I do a select statement on that table I get 26 records, one for each field. (select fieldName from tblName)
Is it possible to set a variable equal to a single string off of the select statement and delimit it with a chosen delimiter (ie "A,B,C,D,E,F......")
Thank You for the help!!!With which DBMS? There is no standard SQL answer to this.|||Using MS SQL 7|||Looking for something like
set @.stringName = (select fieldName & ',' from tblName)
So that @.stringName is set to a string 'A,B,C,D,E,....'|||What about this?
drop table test
create table test(id int identity,code varchar(10))
go
insert test(code) values('a')
insert test(code) values('b')
insert test(code) values('c')
insert test(code) values('d')
insert test(code) values('e')
go
declare @.str varchar(8000)
set @.str=''
select @.str=@.str+code from test
select @.str|||Originally posted by snail
What about this?
drop table test
create table test(id int identity,code varchar(10))
go
insert test(code) values('a')
insert test(code) values('b')
insert test(code) values('c')
insert test(code) values('d')
insert test(code) values('e')
go
declare @.str varchar(8000)
set @.str=''
select @.str=@.str+code from test
select @.str That's pretty much what I'm looking for but I don't understand how your '+ code from test' is going to work. That piece should be my recordset
set @.str = @.str + (select fieldName from tblName)
something like that where my recordset can be turned into a string.|||Originally posted by gman_gsxr750
That's pretty much what I'm looking for but I don't understand how your '+ code from test' is going to work. That piece should be my recordset
set @.str = @.str + (select fieldName from tblName)
something like that where my recordset can be turned into a string.
Just try and you'll see...|||Originally posted by snail
Just try and you'll see... Holy moley!!! I've never seen that before!!!
Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!
One last question (only because I've never used the code in that way before...
can I put a conditional on it
set @.str = @.str + code from table (where id < 100)
or something like that?
And did I mention..... Thank you!|||Originally posted by gman_gsxr750
Holy moley!!! I've never seen that before!!!
Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!
One last question (only because I've never used the code in that way before...
can I put a conditional on it
set @.str = @.str + code from table (where id < 100)
or something like that?
And did I mention..... Thank you!
Why not?|||Originally posted by snail
Why not? Ever get that rush when something finally goes your way and things work out?
Thank you soooooooooo much! I got the conditional to work as well. I just need to play with this a little to figure out the nuances.
Do you know what that kind of query is called so I can reference?|||Originally posted by gman_gsxr750
Ever get that rush when something finally goes your way and things work out?
Thank you soooooooooo much! I got the conditional to work as well. I just need to play with this a little to figure out the nuances.
Do you know what that kind of query is called so I can reference?
I have no idea...|||Originally posted by snail
I have no idea... OK, last question, hopefully you can help me with this.
Here's my code:
declare @.idpeople int
set @.idpeople = 200002
declare @.str varchar(8000)
set @.str = ''
select @.str = @.str + ',' + ideventcode from tblPeopleEvents where idpeople = @.idpeople
let's say this returns the following ',CXL,AS' (two codes CXL and AS)
this works fine, but if I add an order by clause to it (so it returns AS,CXL instead), I only get one of the two values (CXL)
any ideas on how far I can take the select portion (where, order by, group by, etc)?|||it's called a magic query
it works, and it produces the result by magic
hey snail, where's the comma between values?
;)|||OK, here's my final code. I used a subquery to get the result set the way I needed it. Much thanks to Snail for the help. And to r937 for the sarcasm ;).
create procedure spGetEventString
@.idpeople int,
@.eventString varchar(255) OUTPUT
as
set @.eventString = ''
-- Create string of Event codes
-- use sub query to order result set
select @.eventString = @.eventString + rTPE.ideventcode
from
(
select top 100 idEventCode + ',' as idEventCode
from tblPeopleEvents
where idpeople = @.idpeople
order by idEventCode
) as rTPE
--Remove Trailing Comma
set @.eventString = left(@.eventString,len(@.eventString)-1)
return|||Originally posted by r937
it's called a magic query
it works, and it produces the result by magic
hey snail, where's the comma between values?
;)
I am not a magician I am only learning... ;)|||I think it should be called the Loophole query, because it doesn't look like it should work, but it does.|||Originally posted by blindman
I think it should be called the Loophole query, because it doesn't look like it should work, but it does.
That is a bit harsh ...
http://www.dbforums.com/showthread.php?threadid=979593|||Originally posted by Enigma
That is a bit harsh ...
http://www.dbforums.com/showthread.php?threadid=979593 Yep, that's it. Works like a charm too!|||This type of query is the coolest thing I've learned from DBForums, but I haven't seen any Microsoft Documentation that talks about it. That's why it seems like a loophole to me.
Does anybody know of any BOL or MS Support references regarding this self-referential query?|||Thats what it should be called -->"A self-referential query"|||and it even does what Enigma was asking in the referenced post:
select @.str=@.str+case @.str when '' then '' else ',' end + code from test
Is this possible ?
is it possible to have a select statement from a stored proceedure ? example ...
CREATE PROCEDURE spA
SELECT * FROM ( exec spB '1','1' )
go
the reason for doing this is becos spB does some data massaging and is used across many stored procedures, spB will be passing back a table if it is possible.
Thanks.I'd recommend that you rewrite spB as a User-defined table function. Then you can reference it in stored procedures, views, triggers, etc, just like any other table or subquery:
CREATE PROCEDURE spA
SELECT * FROM dbo.spB ('1','1')
blindman|||Or it that's something you would like to avoid, you could use
Insert Into #TmpSpB Exec SpB '1','1'
where #TmpSpB is a temporary table which has the structure of the result return by the stored procedure. The only problem with this, is that can not be nested, and I am not sure if it's working "below" SQL2000.
Best regards!|||thanks for the replies ... you've been a great help ...
CREATE PROCEDURE spA
SELECT * FROM ( exec spB '1','1' )
go
the reason for doing this is becos spB does some data massaging and is used across many stored procedures, spB will be passing back a table if it is possible.
Thanks.I'd recommend that you rewrite spB as a User-defined table function. Then you can reference it in stored procedures, views, triggers, etc, just like any other table or subquery:
CREATE PROCEDURE spA
SELECT * FROM dbo.spB ('1','1')
blindman|||Or it that's something you would like to avoid, you could use
Insert Into #TmpSpB Exec SpB '1','1'
where #TmpSpB is a temporary table which has the structure of the result return by the stored procedure. The only problem with this, is that can not be nested, and I am not sure if it's working "below" SQL2000.
Best regards!|||thanks for the replies ... you've been a great help ...
Friday, March 23, 2012
is this possible
hey all,
i have a 2 table join select statement that i would like to turn into an
update statement? is this possible?
thanks,
rodcharPlease post DDL and DML statements in order to understand better you request
.
--
Current location: Alicante (ES)
"rodchar" wrote:
> hey all,
> i have a 2 table join select statement that i would like to turn into an
> update statement? is this possible?
> thanks,
> rodchar|||I normally do it using aliases, like so:
-- SELECT version
SELECT *
FROM TableA a
INNER JOIN TableB b ON a.key_field = b.key_field
-- UPDATE version
UPDATE a
SET a.update_field = b.update_field
FROM TableA a
INNER JOIN TableB b ON a.key_field = b.key_field
Let me know how you get on.
Damien
"rodchar" wrote:
> hey all,
> i have a 2 table join select statement that i would like to turn into an
> update statement? is this possible?
> thanks,
> rodchar|||thank you everyone. this helped.
rodchar
"rodchar" wrote:
> hey all,
> i have a 2 table join select statement that i would like to turn into an
> update statement? is this possible?
> thanks,
> rodchar
i have a 2 table join select statement that i would like to turn into an
update statement? is this possible?
thanks,
rodcharPlease post DDL and DML statements in order to understand better you request
.
--
Current location: Alicante (ES)
"rodchar" wrote:
> hey all,
> i have a 2 table join select statement that i would like to turn into an
> update statement? is this possible?
> thanks,
> rodchar|||I normally do it using aliases, like so:
-- SELECT version
SELECT *
FROM TableA a
INNER JOIN TableB b ON a.key_field = b.key_field
-- UPDATE version
UPDATE a
SET a.update_field = b.update_field
FROM TableA a
INNER JOIN TableB b ON a.key_field = b.key_field
Let me know how you get on.
Damien
"rodchar" wrote:
> hey all,
> i have a 2 table join select statement that i would like to turn into an
> update statement? is this possible?
> thanks,
> rodchar|||thank you everyone. this helped.
rodchar
"rodchar" wrote:
> hey all,
> i have a 2 table join select statement that i would like to turn into an
> update statement? is this possible?
> thanks,
> rodchar
is this Possible
I want to pass a table name to a query
Declare @.Tablename '
Set @.TableName = 'MyTable'
Select * from @.TableName
how can i get this to work, if at all possibleResist the urge!
http://www.sommarskog.se/dynamic_sql.html
Keith
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:C461CC2C-7B1C-4606-A212-992D61F62B0A@.microsoft.com...
> I want to pass a table name to a query
> Declare @.Tablename '
> Set @.TableName = 'MyTable'
> Select * from @.TableName
> how can i get this to work, if at all possible|||You can use either a table variable or dynamic sql to work with dynamic tabl
e
names, a lot of experts will have tons of bad things to say about "dynamicly
"
creating tables but sometime you just can't help which I hope this is the
case otherwise you shouldn't use it :)
There are limitations to table variables which you can search for on the web
.
There are also limitations and nasties when you use dynamic SQL especially
in webased evironments where people can inject code into yours if they know
their way...
You can also have a look at local and global temp tables for the use of
adhoc tables .
Something that I can think of quickly
Table variable VS temp tables (# tables)
Table variables don't have schema, so you can't use indexing so massive
tables will be slow for querying, temp tables got a schema thus not a proble
m.
There are plenty of differences , pro's and cons for both.
What would be helpful to you is if you tell us what you are trying to
accomplish, then maybe we can be even more helpful.
Some code examples of "normal" table query using dynamic sql and table
variable.
HTH
declare @.mytable table (col1 int)
insert into @.mytable select '1'
select * from @.mytable
/**************/
create table mytable(col1 int)
insert into mytable select '1'
declare @.tablename nvarchar(22)
declare @.sql nvarchar(999)
set @.tablename = 'mytable'
set @.sql = 'select * from ' + @.tablename
exec sp_executesql @.sql
"Peter Newman" wrote:
> I want to pass a table name to a query
> Declare @.Tablename '
> Set @.TableName = 'MyTable'
> Select * from @.TableName
> how can i get this to work, if at all possible
Declare @.Tablename '
Set @.TableName = 'MyTable'
Select * from @.TableName
how can i get this to work, if at all possibleResist the urge!
http://www.sommarskog.se/dynamic_sql.html
Keith
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:C461CC2C-7B1C-4606-A212-992D61F62B0A@.microsoft.com...
> I want to pass a table name to a query
> Declare @.Tablename '
> Set @.TableName = 'MyTable'
> Select * from @.TableName
> how can i get this to work, if at all possible|||You can use either a table variable or dynamic sql to work with dynamic tabl
e
names, a lot of experts will have tons of bad things to say about "dynamicly
"
creating tables but sometime you just can't help which I hope this is the
case otherwise you shouldn't use it :)
There are limitations to table variables which you can search for on the web
.
There are also limitations and nasties when you use dynamic SQL especially
in webased evironments where people can inject code into yours if they know
their way...
You can also have a look at local and global temp tables for the use of
adhoc tables .
Something that I can think of quickly
Table variable VS temp tables (# tables)
Table variables don't have schema, so you can't use indexing so massive
tables will be slow for querying, temp tables got a schema thus not a proble
m.
There are plenty of differences , pro's and cons for both.
What would be helpful to you is if you tell us what you are trying to
accomplish, then maybe we can be even more helpful.
Some code examples of "normal" table query using dynamic sql and table
variable.
HTH
declare @.mytable table (col1 int)
insert into @.mytable select '1'
select * from @.mytable
/**************/
create table mytable(col1 int)
insert into mytable select '1'
declare @.tablename nvarchar(22)
declare @.sql nvarchar(999)
set @.tablename = 'mytable'
set @.sql = 'select * from ' + @.tablename
exec sp_executesql @.sql
"Peter Newman" wrote:
> I want to pass a table name to a query
> Declare @.Tablename '
> Set @.TableName = 'MyTable'
> Select * from @.TableName
> how can i get this to work, if at all possible
Is this guaranteed: SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
Will the statement below, always return the first record of the same
stand-alone SELECT statement as below:
SELECT * FROM ... ORDER BY Field1, Field2, Field3
Thanks,
JayYes, assuming you use the ORDER BY clause and the data remains constant.
Insert a new row, and it may be the new top result.
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>|||Didn't you see the contrary example that Razvan posted?
http://groups.google.com/group/micr...3752b9548322706
David Portas
SQL Server MVP
--|||In SQL Server 2005, we are a bit more consistent with TOP + ORDER BY
semantics than perhaps some previous releases.
Here are the basic rules:
1. ORDER BY determines the presentation order for the _output_ of a query.
2. Within the same select block, an ORDER BY implies that TOP returns the
TOP N rows (not necessarily in a specific order).
3. ORDER BY in subselects or views does *not* guarantee the output of a
containing query.
So, for TOP N... ORDER BY ... with no containing select block, both the set
and the order are guaranted.
Within a subquery, TOP N ... ORDER BY guarantees the set but not the output
order (you need a top-level ORDER BY to guarantee output order).
Conor Cunningham
SQL Server Query Optimization Team
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>
Will the statement below, always return the first record of the same
stand-alone SELECT statement as below:
SELECT * FROM ... ORDER BY Field1, Field2, Field3
Thanks,
JayYes, assuming you use the ORDER BY clause and the data remains constant.
Insert a new row, and it may be the new top result.
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>|||Didn't you see the contrary example that Razvan posted?
http://groups.google.com/group/micr...3752b9548322706
David Portas
SQL Server MVP
--|||In SQL Server 2005, we are a bit more consistent with TOP + ORDER BY
semantics than perhaps some previous releases.
Here are the basic rules:
1. ORDER BY determines the presentation order for the _output_ of a query.
2. Within the same select block, an ORDER BY implies that TOP returns the
TOP N rows (not necessarily in a specific order).
3. ORDER BY in subselects or views does *not* guarantee the output of a
containing query.
So, for TOP N... ORDER BY ... with no containing select block, both the set
and the order are guaranted.
Within a subquery, TOP N ... ORDER BY guarantees the set but not the output
order (you need a top-level ORDER BY to guarantee output order).
Conor Cunningham
SQL Server Query Optimization Team
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>
Is this guaranteed: SELECT TOP 1 FROM ... ORDER BY Field1, Fie
Many thanks David and Razvan, I've rated your posts as helpful.
--
Adam J Warne, MCDBA
"David Portas" wrote:
> Not necessarily. We don't know what other columns are involved or what
> the keys are. You can only guarantee that you'll get the same
> particular row from the top of the second query if the three ORDER BY
> clolumns are UNIQUE. Use TOP 1 WITH TIES if the ORDER BY criteria isn't
> unique.
> --
> David Portas
> SQL Server MVP
> --
>>The WITH TIES clause is new to me should be
There is even a web log named toponewithties :)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131539036.434912.103710@.g44g2000cwa.googlegroups.com...
> Thanks for the replies. The WITH TIES clause is new to me should be
> very helpful. Thanks.
>
--
Adam J Warne, MCDBA
"David Portas" wrote:
> Not necessarily. We don't know what other columns are involved or what
> the keys are. You can only guarantee that you'll get the same
> particular row from the top of the second query if the three ORDER BY
> clolumns are UNIQUE. Use TOP 1 WITH TIES if the ORDER BY criteria isn't
> unique.
> --
> David Portas
> SQL Server MVP
> --
>>The WITH TIES clause is new to me should be
There is even a web log named toponewithties :)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131539036.434912.103710@.g44g2000cwa.googlegroups.com...
> Thanks for the replies. The WITH TIES clause is new to me should be
> very helpful. Thanks.
>
Is this expected behavior?
I posted this at the asp.net forums but somone suggested I post it here. So:
Try this in sql server:
select COALESCE(a1, char(254)) as c1 from
(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1
group by a1
with rollup order by c1
select COALESCE(a1, char(255)) as c1 from
(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1
group by a1
with rollup order by c1
The only difference is that the first one uses 254 and the second one uses 255. The first sorts like this:
W
Y
Ya
Z
t
The second one sorts like this:
W
Y
?
Ya
Z
Is this expected behavior?
It is because the sort order is based on the character set you are using, not where the characters show up in the ascii or unicode charts. If you want it to sort based on position like that, you need to use a binary sort order.
select COALESCE(a1, char(254)) COLLATE Latin1_General_BIN as c1
from (select 'Z' as a1
union select 'Ya' as a1
union select 'Y' as a1
union select 'W' as a1) as b1
group by a1
with rollup order by c1
select COALESCE(a1, char(255)) COLLATE Latin1_General_BIN as c1
from (select 'Z' as a1
union select 'Ya' as a1
union select 'Y' as a1
union select 'W' as a1) as b1
group by a1
with rollup order by c1
In this case both characters that are NULL will sort to the end of the results.
is this even possible in SQL?
Hi i have a sql statement as follows:
SELECT
seq = (select rts.seq from sj_rts rts where lhm.oper = rts.oper and lhm.route=rts.route),
lhm.date_time,
lhm.route,
lhm.oper,
x3o.operName,
(lhm.date_time - (SELECT max(lhm1.date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm1, x3oprs x3o1 WHERE lhm1.lot
= 'S6D0IQ002A' AND lhm1.oper = x3o1.oper and lhm1.date_time < lhm.date_time)) as ActualTime,
theoreticalTime = (subquery that returns theoretical time for particular row depending on value of oper and route)
FROM
brettb.pdash2.dbo.lothistorymoves lhm,
x3oprs x3o
WHERE
lhm.lot ='S6D0IQ002A' AND
lhm.oper = x3o.oper
UNION
SELECT
rts.seq,
PROJECTEDTIME = '' -- (Contains the estimated projected time by adding the theoretical time to the date_time value in
the row above it)
rts.route,
rts.oper,
rts.name AS operName,
ActualTime = '', -- Blank since this is the future
theoreticalTime = ( subquery that returns theoretical time for particular row depending on value of oper and route)
FROM
Routes_X3 rts
where
rts.route=(subquery)
and rts.seq > ( subquery the returns the seq from the history)
Order By
rts.seq asc
The first sql statement is the history for a particular item and gets its data from a history table. the second query
is the next steps that item needs to go through and gets its data from another table that just lists all the steps
according to its seq number. Each row in the whole UNION has a related theoretical time that is specific to the route
and the oper values.
What I am trying to do is create a column called projectedTime in the second query that will take the LAST date_time
from the first query (i can do this with a max(date_time) ) and add the theoretical time to produce the projected
date_time for the current row in the second query. THen for the next row, i want to add its theoretical time to the
projectedtime of the row above to get the next projected time. i want to do this for all the rows in the next steps
query (the second query). Essentially what i would get is a report detailing the steps already completed and the
projected completion dates for the next steps.
The issue is that I have to add the theoretical time for the second query's row to something. For the first row of
query2 its easy, just add the theoretical time to the last row of the first query (i can use a subquery to get the
date) to create the projected time. However, then for the next row, adding the theoretical time to the last row of
the first query won't give me the projected time, instead i need to add the theoretical time to the projected time of
the first row and then continue doing this to get the rest of the projected times.
I'm not sure how i can accomplish this in SQL or if its even possible. If i could somehow either create another
column on the second query that adds together the theoretical times of that row and that of the rows above it and add
the sume to the last date_time from the first query, i could get what i need. Or if i could somehow use a CURSOR to
bring back the date_time from the row above and add the theoretical time to that datetime and stick it in the column,
it could work. However, I read somewhere that you cant use cursors with more than one select statement, such as my
UNION.
I am trying to decide if i should do this in SQL or just do it programmatically.
Thanks for any help into my problem.Stop! You are giving me a headache.
The answer to your question is "Yes".
Yes you should to it using SQL. Yes, you should do it programmatically.
Create an SQL Stored procedure to generate your recordset, and use declared variables, temporary tables, etc, liberally in order to break your task down into smaller components.sql
SELECT
seq = (select rts.seq from sj_rts rts where lhm.oper = rts.oper and lhm.route=rts.route),
lhm.date_time,
lhm.route,
lhm.oper,
x3o.operName,
(lhm.date_time - (SELECT max(lhm1.date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm1, x3oprs x3o1 WHERE lhm1.lot
= 'S6D0IQ002A' AND lhm1.oper = x3o1.oper and lhm1.date_time < lhm.date_time)) as ActualTime,
theoreticalTime = (subquery that returns theoretical time for particular row depending on value of oper and route)
FROM
brettb.pdash2.dbo.lothistorymoves lhm,
x3oprs x3o
WHERE
lhm.lot ='S6D0IQ002A' AND
lhm.oper = x3o.oper
UNION
SELECT
rts.seq,
PROJECTEDTIME = '' -- (Contains the estimated projected time by adding the theoretical time to the date_time value in
the row above it)
rts.route,
rts.oper,
rts.name AS operName,
ActualTime = '', -- Blank since this is the future
theoreticalTime = ( subquery that returns theoretical time for particular row depending on value of oper and route)
FROM
Routes_X3 rts
where
rts.route=(subquery)
and rts.seq > ( subquery the returns the seq from the history)
Order By
rts.seq asc
The first sql statement is the history for a particular item and gets its data from a history table. the second query
is the next steps that item needs to go through and gets its data from another table that just lists all the steps
according to its seq number. Each row in the whole UNION has a related theoretical time that is specific to the route
and the oper values.
What I am trying to do is create a column called projectedTime in the second query that will take the LAST date_time
from the first query (i can do this with a max(date_time) ) and add the theoretical time to produce the projected
date_time for the current row in the second query. THen for the next row, i want to add its theoretical time to the
projectedtime of the row above to get the next projected time. i want to do this for all the rows in the next steps
query (the second query). Essentially what i would get is a report detailing the steps already completed and the
projected completion dates for the next steps.
The issue is that I have to add the theoretical time for the second query's row to something. For the first row of
query2 its easy, just add the theoretical time to the last row of the first query (i can use a subquery to get the
date) to create the projected time. However, then for the next row, adding the theoretical time to the last row of
the first query won't give me the projected time, instead i need to add the theoretical time to the projected time of
the first row and then continue doing this to get the rest of the projected times.
I'm not sure how i can accomplish this in SQL or if its even possible. If i could somehow either create another
column on the second query that adds together the theoretical times of that row and that of the rows above it and add
the sume to the last date_time from the first query, i could get what i need. Or if i could somehow use a CURSOR to
bring back the date_time from the row above and add the theoretical time to that datetime and stick it in the column,
it could work. However, I read somewhere that you cant use cursors with more than one select statement, such as my
UNION.
I am trying to decide if i should do this in SQL or just do it programmatically.
Thanks for any help into my problem.Stop! You are giving me a headache.
The answer to your question is "Yes".
Yes you should to it using SQL. Yes, you should do it programmatically.
Create an SQL Stored procedure to generate your recordset, and use declared variables, temporary tables, etc, liberally in order to break your task down into smaller components.sql
Wednesday, March 21, 2012
Is this correct?
Are these two statements equivalent? If not, how can I rephrase the 2nd
one?
UPDATE Contact
SET DoNotCallTypeKey = 20
WHERE EXISTS (
SELECT *
FROM SSA
INNER JOIN SSP
ON SSP.SampleSourceArchiveKey = SSA.SampleSourceArchiveKey
WHERE ssp.ContactKey = Contact.ContactKey
AND ssa.SampleSourceKey = @.sampleSourceKey
AND ssa.SurveyFlag = 'DNS'
)
vs
UPDATE Contact
SET DoNotCallTypeKey = 20
FROM Contact
INNER JOIN SSP
ON ssp.ContactKey = ssp.ContactKey
INNER JOIN SSA
ON ssa.SampleSourceArchiveKey = ssp.SampleSourceArchiveKey
WHERE ssa.SampleSourceKey = @.sampleSourceKey
AND ssa.SurveyFlag = 'DNS'
They both look like they return the same results, but when it operates on
1.5 million records, I really don't want to have to scroll trhough both
result sets to prove it. I want to rephrase it because I hate all these
dumb "where exists" things that are upside down with the criteria and joins
all stuffed into sub-selects.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneMike,
They look the same with the exception:
ON ssp.ContactKey = ssp.ContactKey
should be
ON ssp.ContactKey = Contact.ContactKey
HTH
Jerry
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:Ou%23dxAvuFHA.908@.tk2msftngp13.phx.gbl...
> Are these two statements equivalent? If not, how can I rephrase the 2nd
> one?
> UPDATE Contact
> SET DoNotCallTypeKey = 20
> WHERE EXISTS (
> SELECT *
> FROM SSA
> INNER JOIN SSP
> ON SSP.SampleSourceArchiveKey = SSA.SampleSourceArchiveKey
> WHERE ssp.ContactKey = Contact.ContactKey
> AND ssa.SampleSourceKey = @.sampleSourceKey
> AND ssa.SurveyFlag = 'DNS'
> )
> vs
> UPDATE Contact
> SET DoNotCallTypeKey = 20
> FROM Contact
> INNER JOIN SSP
> ON ssp.ContactKey = ssp.ContactKey
> INNER JOIN SSA
> ON ssa.SampleSourceArchiveKey = ssp.SampleSourceArchiveKey
> WHERE ssa.SampleSourceKey = @.sampleSourceKey
> AND ssa.SurveyFlag = 'DNS'
>
> They both look like they return the same results, but when it operates on
> 1.5 million records, I really don't want to have to scroll trhough both
> result sets to prove it. I want to rephrase it because I hate all these
> dumb "where exists" things that are upside down with the criteria and
> joins all stuffed into sub-selects.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> They look the same with the exception:
> ON ssp.ContactKey = ssp.ContactKey
> should be
> ON ssp.ContactKey = Contact.ContactKey
Sorry, that was just a typo.
I'm just looking for confirmation that they're the same. Thanks.
These people around here come from a MS Access background and they don't
know how to use joins right. So they just write all these upside down
things with WHERE [NOT] EXISTS and a long list of sub-selects, and it's
almost indecipherable sometimes.
Thank You!
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OcA08JvuFHA.3864@.TK2MSFTNGP12.phx.gbl...
> Mike,
>
> HTH
> Jerry
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:Ou%23dxAvuFHA.908@.tk2msftngp13.phx.gbl...
>|||The first is correct SQL, while the seconnd is proprietary and
problematic.
It makes no sense in terms of the SQL language model. A FROM clause is
always suppose effectively materialize a working table that disappeare
at the end of the statement. Likewise, an alias is supposed to act as
it materializes a new working table with the data from the original
table expression in it. To be consistent, this syntax says that you
have done nothing to the base table.
Sybase and some other vendors had the same syntax but with different
semantics. Worst of both worlds!
And on top of that, it is unpredictable. This is a simple example from
Adam Machanic
CREATE TABLE Foo
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Foo VALUES ('A', 0);
INSERT INTO Foo VALUES ('B', 0);
INSERT INTO Foo VALUES ('C', 0);
CREATE TABLE Bar
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Bar VALUES ('A', 1);
INSERT INTO Bar VALUES ('A', 2);
INSERT INTO Bar VALUES ('B', 1);
INSERT INTO Bar VALUES ('C', 1);
You run this proprietary UPDATE with a FROM clause:
UPDATE Foo
SET Foo.col_b = Bar.col_b
FROM Foo INNER JOIN Bar
ON Foo.col_a = Bar.col_a;
The result of the update cannot be determined. The value of the column
will depend upon either order of insertion, (if there are no clustered
indexes present), or on order of clustering (but only if the cluster
isn't fragmented).
The join mechanism hides cardinality violations, turns your data into
garbage.|||On Fri, 16 Sep 2005 14:55:46 -0400, Mike Labosh wrote:
>Are these two statements equivalent? If not, how can I rephrase the 2nd
>one?
Hi Mike,
Apart from the typo, they might be. But it's also possible that the
second version sets the DoNotCallTypeKey for a row to 20 hundreds of
times during the execution. I'd have to know the table structure to be
sure.
But there are some important other issues that you should think about.
First: The first statement is ANSI standard SQL, that will eaasily port
to other database platforms. The second is proprietary syntax that runs
fine on SQL Server, but can't be ported to other databases. Even MS' own
"other" database product (Access) won't run this code - it has a similar
non-ANSI syntax for UPDATE and DELETE, but it's not the same as in SQL
Server!
Second: There are definitely situations where I would choose to use the
non-standard UPDATE ... FROM syntax. But this is not one of them. I
would consider using the proprietary syntax if the new value for the
DoNotCallTypeKey had to be taken from one of the tables in the subquery
(as SQL Server isn't very clever about optimizing statements that have
the same subquery twice).
Third: Even on SQL Server, the second syntax will sometimes fail. If
Contact is not a base table, but a view, AND you have an INSTEAD OF
UPDATE trigger defined on Contact, you'll get an error if you try the
second syntax. SQL Server somehow doesn't know how to handle this
situation.
Fourth:
> I want to rephrase it because I hate all these
>dumb "where exists" things that are upside down with the criteria and joins
>all stuffed into sub-selects.
I consider this to be a very bad reason. Personal bias should always
come second to professional impartiality.
Instead of trying to get your Access developers to learn a syntax that
won't work on Acces, you'd be better off getting yourself acquainted
with the syntax that will work on all SQL-92 compliant databases. You'll
also find that this syntax grows on you when you use it more often (as I
found out when I had to rewrite dozens of UPDATE ... FROM statements
because the view they updated had to be equipped with an ISNTEAD OF
trigger).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
one?
UPDATE Contact
SET DoNotCallTypeKey = 20
WHERE EXISTS (
SELECT *
FROM SSA
INNER JOIN SSP
ON SSP.SampleSourceArchiveKey = SSA.SampleSourceArchiveKey
WHERE ssp.ContactKey = Contact.ContactKey
AND ssa.SampleSourceKey = @.sampleSourceKey
AND ssa.SurveyFlag = 'DNS'
)
vs
UPDATE Contact
SET DoNotCallTypeKey = 20
FROM Contact
INNER JOIN SSP
ON ssp.ContactKey = ssp.ContactKey
INNER JOIN SSA
ON ssa.SampleSourceArchiveKey = ssp.SampleSourceArchiveKey
WHERE ssa.SampleSourceKey = @.sampleSourceKey
AND ssa.SurveyFlag = 'DNS'
They both look like they return the same results, but when it operates on
1.5 million records, I really don't want to have to scroll trhough both
result sets to prove it. I want to rephrase it because I hate all these
dumb "where exists" things that are upside down with the criteria and joins
all stuffed into sub-selects.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneMike,
They look the same with the exception:
ON ssp.ContactKey = ssp.ContactKey
should be
ON ssp.ContactKey = Contact.ContactKey
HTH
Jerry
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:Ou%23dxAvuFHA.908@.tk2msftngp13.phx.gbl...
> Are these two statements equivalent? If not, how can I rephrase the 2nd
> one?
> UPDATE Contact
> SET DoNotCallTypeKey = 20
> WHERE EXISTS (
> SELECT *
> FROM SSA
> INNER JOIN SSP
> ON SSP.SampleSourceArchiveKey = SSA.SampleSourceArchiveKey
> WHERE ssp.ContactKey = Contact.ContactKey
> AND ssa.SampleSourceKey = @.sampleSourceKey
> AND ssa.SurveyFlag = 'DNS'
> )
> vs
> UPDATE Contact
> SET DoNotCallTypeKey = 20
> FROM Contact
> INNER JOIN SSP
> ON ssp.ContactKey = ssp.ContactKey
> INNER JOIN SSA
> ON ssa.SampleSourceArchiveKey = ssp.SampleSourceArchiveKey
> WHERE ssa.SampleSourceKey = @.sampleSourceKey
> AND ssa.SurveyFlag = 'DNS'
>
> They both look like they return the same results, but when it operates on
> 1.5 million records, I really don't want to have to scroll trhough both
> result sets to prove it. I want to rephrase it because I hate all these
> dumb "where exists" things that are upside down with the criteria and
> joins all stuffed into sub-selects.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> They look the same with the exception:
> ON ssp.ContactKey = ssp.ContactKey
> should be
> ON ssp.ContactKey = Contact.ContactKey
Sorry, that was just a typo.
I'm just looking for confirmation that they're the same. Thanks.
These people around here come from a MS Access background and they don't
know how to use joins right. So they just write all these upside down
things with WHERE [NOT] EXISTS and a long list of sub-selects, and it's
almost indecipherable sometimes.
Thank You!
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OcA08JvuFHA.3864@.TK2MSFTNGP12.phx.gbl...
> Mike,
>
> HTH
> Jerry
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:Ou%23dxAvuFHA.908@.tk2msftngp13.phx.gbl...
>|||The first is correct SQL, while the seconnd is proprietary and
problematic.
It makes no sense in terms of the SQL language model. A FROM clause is
always suppose effectively materialize a working table that disappeare
at the end of the statement. Likewise, an alias is supposed to act as
it materializes a new working table with the data from the original
table expression in it. To be consistent, this syntax says that you
have done nothing to the base table.
Sybase and some other vendors had the same syntax but with different
semantics. Worst of both worlds!
And on top of that, it is unpredictable. This is a simple example from
Adam Machanic
CREATE TABLE Foo
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Foo VALUES ('A', 0);
INSERT INTO Foo VALUES ('B', 0);
INSERT INTO Foo VALUES ('C', 0);
CREATE TABLE Bar
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Bar VALUES ('A', 1);
INSERT INTO Bar VALUES ('A', 2);
INSERT INTO Bar VALUES ('B', 1);
INSERT INTO Bar VALUES ('C', 1);
You run this proprietary UPDATE with a FROM clause:
UPDATE Foo
SET Foo.col_b = Bar.col_b
FROM Foo INNER JOIN Bar
ON Foo.col_a = Bar.col_a;
The result of the update cannot be determined. The value of the column
will depend upon either order of insertion, (if there are no clustered
indexes present), or on order of clustering (but only if the cluster
isn't fragmented).
The join mechanism hides cardinality violations, turns your data into
garbage.|||On Fri, 16 Sep 2005 14:55:46 -0400, Mike Labosh wrote:
>Are these two statements equivalent? If not, how can I rephrase the 2nd
>one?
Hi Mike,
Apart from the typo, they might be. But it's also possible that the
second version sets the DoNotCallTypeKey for a row to 20 hundreds of
times during the execution. I'd have to know the table structure to be
sure.
But there are some important other issues that you should think about.
First: The first statement is ANSI standard SQL, that will eaasily port
to other database platforms. The second is proprietary syntax that runs
fine on SQL Server, but can't be ported to other databases. Even MS' own
"other" database product (Access) won't run this code - it has a similar
non-ANSI syntax for UPDATE and DELETE, but it's not the same as in SQL
Server!
Second: There are definitely situations where I would choose to use the
non-standard UPDATE ... FROM syntax. But this is not one of them. I
would consider using the proprietary syntax if the new value for the
DoNotCallTypeKey had to be taken from one of the tables in the subquery
(as SQL Server isn't very clever about optimizing statements that have
the same subquery twice).
Third: Even on SQL Server, the second syntax will sometimes fail. If
Contact is not a base table, but a view, AND you have an INSTEAD OF
UPDATE trigger defined on Contact, you'll get an error if you try the
second syntax. SQL Server somehow doesn't know how to handle this
situation.
Fourth:
> I want to rephrase it because I hate all these
>dumb "where exists" things that are upside down with the criteria and joins
>all stuffed into sub-selects.
I consider this to be a very bad reason. Personal bias should always
come second to professional impartiality.
Instead of trying to get your Access developers to learn a syntax that
won't work on Acces, you'd be better off getting yourself acquainted
with the syntax that will work on all SQL-92 compliant databases. You'll
also find that this syntax grows on you when you use it more often (as I
found out when I had to rewrite dozens of UPDATE ... FROM statements
because the view they updated had to be equipped with an ISNTEAD OF
trigger).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
is this call inner join as well ?
hi, if i have query like following
select * from o_customer,o_address
where
o_customer.name = o_address.custname
is it same as
select * from o_customer
inner join o_address on o_customer.name = o_address.custname
if there are same, which one is prefer in term of performance ..
thank you for guidanceHi
Former is old school, latter is current ANSI compliant.
I would imagine the optimiser would be able to equate the two but if it didn't then number one is likely to be a dog compared to the ANSI join
HTH|||BTW - I would use neither:
SELECT Col1, Col2, Col3
FROM o_customer
INNER JOIN o_address ON o_customer.name = o_address.custname
;)
select * from o_customer,o_address
where
o_customer.name = o_address.custname
is it same as
select * from o_customer
inner join o_address on o_customer.name = o_address.custname
if there are same, which one is prefer in term of performance ..
thank you for guidanceHi
Former is old school, latter is current ANSI compliant.
I would imagine the optimiser would be able to equate the two but if it didn't then number one is likely to be a dog compared to the ANSI join
HTH|||BTW - I would use neither:
SELECT Col1, Col2, Col3
FROM o_customer
INNER JOIN o_address ON o_customer.name = o_address.custname
;)
Is this an SQL bug (SQL 2000)?
Hi....
Can anyone explain the following results:
CREATE TABLE A
(
A varchar(256) NOT NULL
)
go
INSERT INTO A VALUES('test')
go
SELECT * FROM A WHERE A LIKE 'test'
go
=> Returns 1 row
DECLARE @.mytest varchar
SET @.mytest = 'test'
SELECT * FROM A WHERE A LIKE @.mytest
go
=> Returns nothing! Why?
thanks,
Neil"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172825344.594113.234760@.j27g2000cwj.googlegroups.com...
> Hi....
> Can anyone explain the following results:
> CREATE TABLE A
> (
> A varchar(256) NOT NULL
> )
> go
> INSERT INTO A VALUES('test')
> go
> SELECT * FROM A WHERE A LIKE 'test'
> go
> => Returns 1 row
> DECLARE @.mytest varchar
> SET @.mytest = 'test'
> SELECT * FROM A WHERE A LIKE @.mytest
> go
> => Returns nothing! Why?
>
Not a bug.
"DECLARE @.mytest varchar" is equivalent to "DECLARE @.mytest varchar(1)".
So your second SELECT statement is equivalent to "SELECT * FROM A WHERE A
LIKE 't'".
Always specify the size for VARCHAR/NVARCHAR.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Can anyone explain the following results:
CREATE TABLE A
(
A varchar(256) NOT NULL
)
go
INSERT INTO A VALUES('test')
go
SELECT * FROM A WHERE A LIKE 'test'
go
=> Returns 1 row
DECLARE @.mytest varchar
SET @.mytest = 'test'
SELECT * FROM A WHERE A LIKE @.mytest
go
=> Returns nothing! Why?
thanks,
Neil"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172825344.594113.234760@.j27g2000cwj.googlegroups.com...
> Hi....
> Can anyone explain the following results:
> CREATE TABLE A
> (
> A varchar(256) NOT NULL
> )
> go
> INSERT INTO A VALUES('test')
> go
> SELECT * FROM A WHERE A LIKE 'test'
> go
> => Returns 1 row
> DECLARE @.mytest varchar
> SET @.mytest = 'test'
> SELECT * FROM A WHERE A LIKE @.mytest
> go
> => Returns nothing! Why?
>
Not a bug.
"DECLARE @.mytest varchar" is equivalent to "DECLARE @.mytest varchar(1)".
So your second SELECT statement is equivalent to "SELECT * FROM A WHERE A
LIKE 't'".
Always specify the size for VARCHAR/NVARCHAR.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Subscribe to:
Posts (Atom)