Hi ,
I have an application whose data is stored in some other db and I am
converting
to SQL. The existing app has a table, Contractors with a field ContractorCo
de
char(10) as an index to the table. For some reason, I am against using
recognizable data as an index an instead opt for GUID. I s having a char or
varchar
field as an index slower than a GUID field index?
ThanksOpa
I personally try to avoid creating an index on GUID . ( I try keep my index
as small as possible) Having index on VARCHAR/CHAR depends on your
specific requirements . See an execution plan of the query, is an optimizer
used the index and then make a decision?
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||There is a lot of debate on using GUIDs as primary keys. Here is an example:
http://www.sql-server-performance.c...erformance.asp. There is no
problem in using CHAR(10) as your index as long as the column helps you in
identifying the entity uniquely.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:584774
Index on GUID will take up more storage therefore bigger index therefore
more I/0. This would be pronounced on a clustered index.
The Contarctors table with Contractor code, what is the variety of data
within the col ? and what types of searches (if any) will be commited
on that column?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uS4EcQjMGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Opa
> I personally try to avoid creating an index on GUID . ( I try keep my
index
> as small as possible) Having index on VARCHAR/CHAR depends on your
> specific requirements . See an execution plan of the query, is an
optimizer
> used the index and then make a decision?
>
> "Opa" <Opa@.discussions.microsoft.com> wrote in message
> news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
char
>|||Opa (Opa@.discussions.microsoft.com) writes:
> I have an application whose data is stored in some other db and I am
> converting to SQL. The existing app has a table, Contractors with a
> field ContractorCode char(10) as an index to the table. For some
> reason, I am against using recognizable data as an index an instead opt
> for GUID. I s having a char or varchar field as an index slower than a
> GUID field index?
The last question is not really meaningful, because there are always a lot
of "it depends".
But generally, as a guid is 16 bytes, it's longer than the code, and the
longer the field, the less keys you get on a page, and the bigger the
index gets, and the more pages to read.
I would suspect, though, that the Contractors table is not of a size
where this is a much of an issue. Then again, the code may be used as an
FK in a larger table where it may matter.
Personally, I rather use the code as key, than GUID which is much more
difficult to manage. If you want artificial keys, integer is probably
better.
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|||Ok so if I have varchar(16) vs GUID will this index field always perform bet
ter
or equal to GUID is SELECTS, INSERTS, JOINS etc?
BTW, I disagree with your comment stating the column should help identify
the entity uniquely in a visual way.
"SriSamp" wrote:
> There is a lot of debate on using GUIDs as primary keys. Here is an exampl
e:
> http://www.sql-server-performance.c...erformance.asp. There is no
> problem in using CHAR(10) as your index as long as the column helps you in
> identifying the entity uniquely.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Opa" <Opa@.discussions.microsoft.com> wrote in message
> news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
>
>|||The performance aspect is something that you need to check for. Regarding my
other comment, what I meant was, if the CHAR(10) field is the "natural" key
for your table, you should go ahead and use it, rather than defining another
key.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:824B3723-E44D-410F-930F-58A4F31F5DB1@.microsoft.com...
> Ok so if I have varchar(16) vs GUID will this index field always perform
> better
> or equal to GUID is SELECTS, INSERTS, JOINS etc?
> BTW, I disagree with your comment stating the column should help identify
> the entity uniquely in a visual way.
>
> "SriSamp" wrote:
>|||Have a look at this excellent article from Kimberly L Tripp
http://www.sqlskills.com/blogs/kimb...
f-a290db645159
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||You, sir, are a moron. I usually don't say that about people, but I'll
make an exception in your case.
Stu|||When you use "index", I'm assuming you really mean to say "primary key". A
primary key is a type of index, but an index is not a primary key.
I don't believe there is any basic performance benefit to using integer
based keys over char based keys. As far as SQL Server is concerned, a key is
just a block of bytes, and the smaller the better. A GUID is typically
displayed as a string of text, but is stored internally as a 16 byte
integer. Consider instead an int (4 bytes) or smallint (2 bytes) identity.
The only use I can see for using GUID (globally unique) keys is if the
Contractor data is to be merged with Contractor data from another system and
you want to retain the same surrogate key values. However, this could be
better implemented by adding a SystemCode to the Contractors table and have
SystemCode + ContractorCode as the primary key.
Perhaps keeping ContractorCode as the primary key would be the best solution
unless there is a strong and compelling reason to implement a surrogate key.
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks
Showing posts with label table. Show all posts
Showing posts with label table. 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.
> 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
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
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 too long. Maximum length is 128. Error
I try to Update a field of a table using this statement
UPDATE Table SET field="Forget......(long text)" WHERE id=1
and I get this error
The identifier that starts with 'Forget your busexcursions. Marta Patiño takes a trip out of this world at LaLaguna's Science Museum.In April 2001, De' is too long. Maximum length is 128.
What is wrong?Looks like the "field" column in your table is defined to hold a maximum of 128 characters of data. Update your table definition to make the column bigger or reduce the size of your data.
Bill
|||Literal text is enclosed in single quotes. Identifiers (Like column names) may be enclosed in double quotes. Because you've put the text in double quotes, it is saying that your column name (That whole block of text) is too long.|||If you use a t-sql statement like
Update Models Set LocalDescription = "General Description" Where ModelId = 2
And if you have LocalDescription and "General Description" as the name of the fields you will update one field with orders value.
"" points to an identifier (a field name) is up to 128 characters.
If you are trying to set a field with a value more than it is expecting you will get "string or binary data would be truncated" error message.
Eralper
http://www.kodyaz.com
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. .
>
Is this T-SQL dangerous/not reliable
We are trying to create a string of column names for a
given table. (SQL 2000, SP3a--W2K Server, SP3)
The included code here works well so far, but I remember
reading in the past that the optimizer can sometimes break
this approach to creating strings.
If all column names are not null and the total len(string)
does not exceed varchar(8000), is this construct safe? If
not, why?
TIA, -- Brian
declare @.FldStr varchar(8000)
select @.FldStr = ''
select @.FldStr = @.FldStr + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @.TableName
order by ORDINAL_POSITIONThis behavior isn't documented and also isn't supported. I believe someone
posted an example not too long ago that showed this syntax breaking, but
can't seem to find it on a quick search of google.
Why not return the set of column names to your application, and have the
application assemble them into a string?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> We are trying to create a string of column names for a
> given table. (SQL 2000, SP3a--W2K Server, SP3)
> The included code here works well so far, but I remember
> reading in the past that the optimizer can sometimes break
> this approach to creating strings.
> If all column names are not null and the total len(string)
> does not exceed varchar(8000), is this construct safe? If
> not, why?
> TIA, -- Brian
> declare @.FldStr varchar(8000)
> select @.FldStr = ''
> select @.FldStr = @.FldStr + COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = @.TableName
> order by ORDINAL_POSITION
>
>
>
>|||Thanks, Aaron.
I'm taking it further and doing joins dynamically with
sp_executesql, thus would like to keep it on the backend.
Would sending the field names to a #temp table with an
identity field be a better to go, looping through 1 to n
records to build the string of field names?
I'll also try to find the post you mentioned. I'm very
interested in the behind the scenes stuff that would cause
this to break. If you find it at a later point, I'd
greatly appreciate it if you could forward it to me.
(brianglinebaugh@.yahoo.com)
Thanks very much for your time, -- Brian
>--Original Message--
>This behavior isn't documented and also isn't supported.
I believe someone
>posted an example not too long ago that showed this
syntax breaking, but
>can't seem to find it on a quick search of google.
>Why not return the set of column names to your
application, and have the
>application assemble them into a string?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Brian" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09c401c3b076$8d083260$a001280a@.phx.gbl...
>> We are trying to create a string of column names for a
>> given table. (SQL 2000, SP3a--W2K Server, SP3)
>> The included code here works well so far, but I remember
>> reading in the past that the optimizer can sometimes
break
>> this approach to creating strings.
>> If all column names are not null and the total len
(string)
>> does not exceed varchar(8000), is this construct safe?
If
>> not, why?
>> TIA, -- Brian
>> declare @.FldStr varchar(8000)
>> select @.FldStr = ''
>> select @.FldStr = @.FldStr + COLUMN_NAME
>> from INFORMATION_SCHEMA.COLUMNS
>> where TABLE_NAME = @.TableName
>> order by ORDINAL_POSITION
>>
>>
>>
>
>.
>|||"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> We are trying to create a string of column names for a
> given table. (SQL 2000, SP3a--W2K Server, SP3)
> The included code here works well so far, but I remember
> reading in the past that the optimizer can sometimes break
> this approach to creating strings.
> If all column names are not null and the total len(string)
> does not exceed varchar(8000), is this construct safe? If
> not, why?
> TIA, -- Brian
> declare @.FldStr varchar(8000)
> select @.FldStr = ''
> select @.FldStr = @.FldStr + COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = @.TableName
> order by ORDINAL_POSITION
>
How about
set @.FldStr = '*'
?
David|||I don't know of a KB that states this but it will fail most of the time in
anything other than a simple select with no order by, join etc. You do not
want to put that code into your production env. Create a cursor and di it
that way if you must have it in a particular order.
--
Andrew J. Kelly
SQL Server MVP
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:3a2c01c3b07f$eef7fab0$a601280a@.phx.gbl...
> Thanks, Aaron.
> I'm taking it further and doing joins dynamically with
> sp_executesql, thus would like to keep it on the backend.
> Would sending the field names to a #temp table with an
> identity field be a better to go, looping through 1 to n
> records to build the string of field names?
> I'll also try to find the post you mentioned. I'm very
> interested in the behind the scenes stuff that would cause
> this to break. If you find it at a later point, I'd
> greatly appreciate it if you could forward it to me.
> (brianglinebaugh@.yahoo.com)
> Thanks very much for your time, -- Brian
>
>
>
> >--Original Message--
> >This behavior isn't documented and also isn't supported.
> I believe someone
> >posted an example not too long ago that showed this
> syntax breaking, but
> >can't seem to find it on a quick search of google.
> >
> >Why not return the set of column names to your
> application, and have the
> >application assemble them into a string?
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >
> >
> >"Brian" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> >>
> >> We are trying to create a string of column names for a
> >> given table. (SQL 2000, SP3a--W2K Server, SP3)
> >>
> >> The included code here works well so far, but I remember
> >> reading in the past that the optimizer can sometimes
> break
> >> this approach to creating strings.
> >>
> >> If all column names are not null and the total len
> (string)
> >> does not exceed varchar(8000), is this construct safe?
> If
> >> not, why?
> >>
> >> TIA, -- Brian
> >>
> >> declare @.FldStr varchar(8000)
> >> select @.FldStr = ''
> >>
> >> select @.FldStr = @.FldStr + COLUMN_NAME
> >> from INFORMATION_SCHEMA.COLUMNS
> >> where TABLE_NAME = @.TableName
> >> order by ORDINAL_POSITION
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >.
> >|||In this case, it seems to work because for INFORMATION_SCHEMA.COLUMNS view
the optimizer behavior luckily generated a plan for that concatenated the
values in a way you intended. However, this is a risky proposition, since
this is undocumented, inconsistent and thus unreliable. Here are some trials
that can break your luck.
--#1 ( Add a TOP clause)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT TOP 100 PERCENT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#2 ( Add a DISTINCT)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT DISTINCT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#3 ( Add a CROSS JOIN)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT @.FldStr = @.FldStr + c1.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c1, (SELECT 1) D (n)
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#4 (Use another view)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE TABLE_NAME = 'test'
ORDER BY TABLE_NAME;
SELECT @.FldStr;
--
- Anith
( Please reply to newsgroups only )|||"Anith Sen" wrote
> In this case, it seems to work because for INFORMATION_SCHEMA.COLUMNS view
> the optimizer behavior luckily generated a plan for that concatenated the
> values in a way you intended..
Where I live everyone drives a SUV.I drive a Benz 560 SL.
They keep telling me 'this is a risky proposition, since
it's undocumented, inconsistent and thus unreliable' :~)
given table. (SQL 2000, SP3a--W2K Server, SP3)
The included code here works well so far, but I remember
reading in the past that the optimizer can sometimes break
this approach to creating strings.
If all column names are not null and the total len(string)
does not exceed varchar(8000), is this construct safe? If
not, why?
TIA, -- Brian
declare @.FldStr varchar(8000)
select @.FldStr = ''
select @.FldStr = @.FldStr + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @.TableName
order by ORDINAL_POSITIONThis behavior isn't documented and also isn't supported. I believe someone
posted an example not too long ago that showed this syntax breaking, but
can't seem to find it on a quick search of google.
Why not return the set of column names to your application, and have the
application assemble them into a string?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> We are trying to create a string of column names for a
> given table. (SQL 2000, SP3a--W2K Server, SP3)
> The included code here works well so far, but I remember
> reading in the past that the optimizer can sometimes break
> this approach to creating strings.
> If all column names are not null and the total len(string)
> does not exceed varchar(8000), is this construct safe? If
> not, why?
> TIA, -- Brian
> declare @.FldStr varchar(8000)
> select @.FldStr = ''
> select @.FldStr = @.FldStr + COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = @.TableName
> order by ORDINAL_POSITION
>
>
>
>|||Thanks, Aaron.
I'm taking it further and doing joins dynamically with
sp_executesql, thus would like to keep it on the backend.
Would sending the field names to a #temp table with an
identity field be a better to go, looping through 1 to n
records to build the string of field names?
I'll also try to find the post you mentioned. I'm very
interested in the behind the scenes stuff that would cause
this to break. If you find it at a later point, I'd
greatly appreciate it if you could forward it to me.
(brianglinebaugh@.yahoo.com)
Thanks very much for your time, -- Brian
>--Original Message--
>This behavior isn't documented and also isn't supported.
I believe someone
>posted an example not too long ago that showed this
syntax breaking, but
>can't seem to find it on a quick search of google.
>Why not return the set of column names to your
application, and have the
>application assemble them into a string?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Brian" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09c401c3b076$8d083260$a001280a@.phx.gbl...
>> We are trying to create a string of column names for a
>> given table. (SQL 2000, SP3a--W2K Server, SP3)
>> The included code here works well so far, but I remember
>> reading in the past that the optimizer can sometimes
break
>> this approach to creating strings.
>> If all column names are not null and the total len
(string)
>> does not exceed varchar(8000), is this construct safe?
If
>> not, why?
>> TIA, -- Brian
>> declare @.FldStr varchar(8000)
>> select @.FldStr = ''
>> select @.FldStr = @.FldStr + COLUMN_NAME
>> from INFORMATION_SCHEMA.COLUMNS
>> where TABLE_NAME = @.TableName
>> order by ORDINAL_POSITION
>>
>>
>>
>
>.
>|||"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> We are trying to create a string of column names for a
> given table. (SQL 2000, SP3a--W2K Server, SP3)
> The included code here works well so far, but I remember
> reading in the past that the optimizer can sometimes break
> this approach to creating strings.
> If all column names are not null and the total len(string)
> does not exceed varchar(8000), is this construct safe? If
> not, why?
> TIA, -- Brian
> declare @.FldStr varchar(8000)
> select @.FldStr = ''
> select @.FldStr = @.FldStr + COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = @.TableName
> order by ORDINAL_POSITION
>
How about
set @.FldStr = '*'
?
David|||I don't know of a KB that states this but it will fail most of the time in
anything other than a simple select with no order by, join etc. You do not
want to put that code into your production env. Create a cursor and di it
that way if you must have it in a particular order.
--
Andrew J. Kelly
SQL Server MVP
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:3a2c01c3b07f$eef7fab0$a601280a@.phx.gbl...
> Thanks, Aaron.
> I'm taking it further and doing joins dynamically with
> sp_executesql, thus would like to keep it on the backend.
> Would sending the field names to a #temp table with an
> identity field be a better to go, looping through 1 to n
> records to build the string of field names?
> I'll also try to find the post you mentioned. I'm very
> interested in the behind the scenes stuff that would cause
> this to break. If you find it at a later point, I'd
> greatly appreciate it if you could forward it to me.
> (brianglinebaugh@.yahoo.com)
> Thanks very much for your time, -- Brian
>
>
>
> >--Original Message--
> >This behavior isn't documented and also isn't supported.
> I believe someone
> >posted an example not too long ago that showed this
> syntax breaking, but
> >can't seem to find it on a quick search of google.
> >
> >Why not return the set of column names to your
> application, and have the
> >application assemble them into a string?
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >
> >
> >"Brian" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> >>
> >> We are trying to create a string of column names for a
> >> given table. (SQL 2000, SP3a--W2K Server, SP3)
> >>
> >> The included code here works well so far, but I remember
> >> reading in the past that the optimizer can sometimes
> break
> >> this approach to creating strings.
> >>
> >> If all column names are not null and the total len
> (string)
> >> does not exceed varchar(8000), is this construct safe?
> If
> >> not, why?
> >>
> >> TIA, -- Brian
> >>
> >> declare @.FldStr varchar(8000)
> >> select @.FldStr = ''
> >>
> >> select @.FldStr = @.FldStr + COLUMN_NAME
> >> from INFORMATION_SCHEMA.COLUMNS
> >> where TABLE_NAME = @.TableName
> >> order by ORDINAL_POSITION
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >.
> >|||In this case, it seems to work because for INFORMATION_SCHEMA.COLUMNS view
the optimizer behavior luckily generated a plan for that concatenated the
values in a way you intended. However, this is a risky proposition, since
this is undocumented, inconsistent and thus unreliable. Here are some trials
that can break your luck.
--#1 ( Add a TOP clause)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT TOP 100 PERCENT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#2 ( Add a DISTINCT)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT DISTINCT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#3 ( Add a CROSS JOIN)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT @.FldStr = @.FldStr + c1.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c1, (SELECT 1) D (n)
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#4 (Use another view)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE TABLE_NAME = 'test'
ORDER BY TABLE_NAME;
SELECT @.FldStr;
--
- Anith
( Please reply to newsgroups only )|||"Anith Sen" wrote
> In this case, it seems to work because for INFORMATION_SCHEMA.COLUMNS view
> the optimizer behavior luckily generated a plan for that concatenated the
> values in a way you intended..
Where I live everyone drives a SUV.I drive a Benz 560 SL.
They keep telling me 'this is a risky proposition, since
it's undocumented, inconsistent and thus unreliable' :~)
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 table design correct?
Hey Everyone,
Just had a quick question for you . I have these 3 tables that are related in some way - below are the structures. I know the structures are correct and they work fine. However, I am using Visio Enterprise Architect to design these tables and when I try to generate the code, I get an error saying that it does not like the table with 2 fields as the primary key. And I just wanted to get feedback to find out whether I am correct or Visio is.
Table: Orders
Columns:
OrderID int PK
Name varchar(100)
Phone varchar(20)
Table: Products
Columns:
ProductID int PK
Name varchar(100)
Table: OrderLineItems
Columns:
OrderID int PK
ProductID int PK
Price money
Thanks for any feedback.
Johnny DevA composite primary key is perfectly legitimate and is often useful if you have a link table, like in your case. The only thing that puzzles me is the Price column in the OrderLineItems table - shouldn't it be in the Products table?|||Hi Diplo,
Thanks for the response. This clears things up. Visio needs to be updated to support this.
The table structure that I posted in this message is not what I have. It was just a sample to demonstrate my case so I wont post my real tables (too complicated :). In fact, my tables are not even Order/Products related. But thanks anyway for catching that for others to see.sql
is this query right? (urgent)
Hi
I have a report which has a table and that table has 4 columns
I want to represent the Data like this.
Company Match or Profit Sharing or Safe Harbor Company Match or ProfitSharing or Safeharbor
Years of Service Vesting Years of service Vesting
1 40 1 50
I have 3 text boxes saying Company Match, Safe harbor and Profit Sharing, and the User normally can click 2 checkboxes
Suppose if the user clicks only company Match, i want the company Match to display on the left hand side if the users clicks on 2 things say company match and safe harbor.
I want the Company match to come on the left and safe harbor to be on the right. and my Expression is as follows:
for the Left hand side its :
IIf(Fields!CompanyMatch.Value =true,"Company Match",IIf(Fields!SafeHarbor.Value =true,"Safe Harbor","Profit Sharing"))
so how can i display the details in the above fashion.
any help is appreciated.
Regards,
Karen
Is that you are passign values from an asp page to report, in that case pass two paramaters to reports 1) companymatching 2) safeharbour as boolean values
In the report desgin mode put all the 4 columns and based on the input parameters you can toggle the view to visible or hide.
|||actually just showing the data from the Database
can u give me a example please.
Regards,
Karen
Is this Query right? (urgent)
Hi
I have a report which has a table and that table has 4 columns
I want to represent the Data like this.
Company Match or Profit Sharing or Safe Harbor Company Match or ProfitSharing or Safeharbor
Years of Service Vesting Years of service Vesting
1 40 1 50
I have 3 text boxes saying Company Match, Safe harbor and Profit Sharing, and the User normally can click 2 checkboxes
Suppose if the user clicks only company Match, i want the company Match to display on the left hand side if the users clicks on 2 things say company match and safe harbor.
I want the Company match to come on the left and safe harbor to be on the right. and my Expression is as follows:
for the Left hand side its :
IIf(Fields!CompanyMatch.Value = true,"Company Match",IIf(Fields!SafeHarbor.Value = true,"Safe Harbor","Profit Sharing"))
and the right hand side its:
IIf(Fields!ProfitSharing.Value = true," Profit Sharing","")
so how can i display the details in the above fashion.
any help is appreciated.
Regards,
Karen
Does your data need to be aggregated or is it a straight read? If so, are you looking to sum the data based on the selected criteria?
|||My data is straight read from the db, and I am not looking to sum the data.
Hope this helps,
Regards,
Karen
|||Replace your text values with field values (if this is what you are trying to do)
IIf(Fields!CompanyMatch.Value = true,CompanyMatchField.value,IIf(Fields!SafeHarbor.Value = true,SafeHarborField.value,ProfitSharingField.value))
and the right hand side its:
IIf(Fields!ProfitSharing.Value = true,ProfitSharingField.value,"")
Simone
sqlIs this query possible?
Hi folks.
So I have a table which stores the following information:
a recordID (PK)
a regionID
a month (in text; such as "January 2005")
and other fields which probrably aren't important to know for this question.
The table can have more than one record for each region and month in question. The problem is for the report I want to pull up ONLY the last record for each region for the month. I got around this by doing a seperate query for each region (5 total). Now I have to do the totals for all regions combined. I could do this math using the array but I was wondering if I was able to do a query to do it for me.
So, I want to query the database and retreive 1 record. That record would contain aggragate data from each of the regions (the last record for each region and the month in question).
I might not be asking the right question, which aludes that I might not understand the problem or capabilities of SQL (newbie).
Thanks,
Douglas.If I am following you correctly, I'd try something like this:
SELECT
myTable.regionID,
myTable.month
FROM
myTable
INNER JOIN
(
SELECT
regionID,
max(recordID) AS recordID
FROM
myTable
GROUP BY
regionID
) AS SQ ON myTable.recordID = SQ.recordID
Terri|||That was perfect. Thank you.
I forgot to say thanks. Actually, I didn't even know that was an option. I use this sort of stuff a ton now.
Douglas.
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? Defaulting of a Dimension Atrribute
I have a dimension with one visible attribute, let's call it "Status." This attribute is binded to one column from a forecast fact table that stores monthly snapshots. This column simply has a 'Yes' or 'No' value for every record in the fact table.
For a specific customer and product combination in this fact table, this "Status" column could have started off having a value of 'No' a few months ago, and now has 'Yes' for the current month. In other words, it can have both possible values.
For example, let's assume the following forecast data for the Customer A and Product B combination:
Month | Forecast $ | Status
-
November 2006 | $5 | 'No'
December 2006 | $10 | 'No'
January 2007 | $10 | 'Yes'
This "Status" attribute is meant to be used at the Page Field level in Excel, and users would like to be able to see customer forecast data based on the "Status" being filtered for either 'Yes' or 'No'.
Now, all of our measures in the cube have the following MDX ((TAIL(EXISTING [Date].[Month].MEMBERS).ITEM(0).ITEM(0), [Measure]). This defaults the measures to the current month in a report unless a time frame, whether year(s) and/or month(s), is explicityly specified or filtered at the Page Field level.
Assuming a timeframe is not explicitly specified at the Page Field level and a user is looking at the forecast data for Customer A for Product B, this customer will show up regardless of what the "Status" Page Field is filtered for. I believe this is the case because it historically has had a record associated with both 'No' and 'Yes'. What does differ hough, is whether or not Forecast $ will populate or not.
For example, Status = 'Yes', then Forecast $ will show $10. If Status = 'No', then Forecast $ will now be NULL. Ideally, if Status = 'No', this customer would not even show up for the current month.
So is this possible? Hopefully what I'm asking makes sense.
Thanks!From your description it looks to me that it already should behave the way you described, assuming the Status attribute is marked as IsAggregatable=false. I must note, though, that the MDX you use for your measures is not the optimal solution. It is much better to simply define all your measures as having LastChild semiadditive aggregation to get the same effect.|||
Mosha Pasumansky wrote:
From your description it looks to me that it already should behave the way you described, assuming the Status attribute is marked as IsAggregatable=false. I must note, though, that the MDX you use for your measures is not the optimal solution. It is much better to simply define all your measures as having LastChild semiadditive aggregation to get the same effect.
I did not have the IsAggregatble property set to false. However, after setting it to true, it's still not behaving the way I would like. Would it be easier if you took a look at my solution file to see what I may have missed? I've tried this numerous times with no luck.
Once I get this behavior resolved, I'll follow up with you with the MDX.
Thanks!|||
I did not have the IsAggregatble property set to false. However, after setting it to true, it's still not behaving the way I would like.
You actually need to set it to false, not to true. Did you follow my suggestion about LastChild semiadditive aggregation type ?
Sorry - but I won't have time to go over your solution file - perhaps somebody else in this forum will be able to do it.
|||I did not have the IsAggregatble property set to false. However, after setting it to true, it's still not behaving the way I would like.
You actually need to set it to false, not to true. Did you follow my suggestion about LastChild semiadditive aggregation type ?
Sorry - but I won't have time to go over your solution file - perhaps somebody else in this forum will be able to do it.
My fault. My mind must have been elsewhere when I posted. I did as you suggested with no luck. I set it to false from the default of true.
I did not follow your suggestion on the LastChild semiadditve aggregation type yet, as I wanted to focus on the above since I'm not too familiar with this LastChild thing. Now that I think about it, I assume this wouldn't be available to me in Standard edition? We're running Standard Edition.|||In fact, LastChild is the only semiadditive aggregation type available in Standard Edition - so you got lucky :)|||Anyone have any other thoughts? Is what I'm looking for even possible?
The IsAggregatable property when set to false, simply removed the 'All' member and didn't do what I am seeking.
is this possible? ...not an SQL master
Hi,
I am trying to figure out the best way to reformat the record entries in a database.
In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.
Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.
I have attached a copy of a screenshot with some notes.
Thanks in advance to anyone who sees the easy way to do this!
cheers,
RickAn example:
INSERT INTO newtable (county, route, px_back, backpm)
SELECT county, integer(route), CASE WHEN SUBSTR('098',LENGTH('098')) > 'A' THEN SUBSTR('098', 1, LENGTH('098') -1) ELSE null END, CASE WHEN SUBSTR('098R',LENGTH('098R')) > 'A' THEN SUBSTR('098R', LENGTH('098R')) ELSE null END FROM oldtable
Assumes that the character is always the last position and length of 1.
Originally posted by entangled
Hi,
I am trying to figure out the best way to reformat the record entries in a database.
In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.
Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.
I have attached a copy of a screenshot with some notes.
Thanks in advance to anyone who sees the easy way to do this!
cheers,
Rick|||HI,
try to use decode(substr(backPM,length(backPM)-1,1),'R',substr(backPM,1,length(backPM)-1,backPM)
and use the same formula for AheadPM column
Originally posted by entangled
Hi,
I am trying to figure out the best way to reformat the record entries in a database.
In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.
Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.
I have attached a copy of a screenshot with some notes.
Thanks in advance to anyone who sees the easy way to do this!
cheers,
Rick|||Thank you for the example. This one example pretty much addresses both issues. I will work with this and see how I can apply this approach.
many thanks,
Rick Sperling
Originally posted by dmmac
An example:
INSERT INTO newtable (county, route, px_back, backpm)
SELECT county, integer(route), CASE WHEN SUBSTR('098',LENGTH('098')) > 'A' THEN SUBSTR('098', 1, LENGTH('098') -1) ELSE null END, CASE WHEN SUBSTR('098R',LENGTH('098R')) > 'A' THEN SUBSTR('098R', LENGTH('098R')) ELSE null END FROM oldtable
Assumes that the character is always the last position and length of 1.
I am trying to figure out the best way to reformat the record entries in a database.
In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.
Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.
I have attached a copy of a screenshot with some notes.
Thanks in advance to anyone who sees the easy way to do this!
cheers,
RickAn example:
INSERT INTO newtable (county, route, px_back, backpm)
SELECT county, integer(route), CASE WHEN SUBSTR('098',LENGTH('098')) > 'A' THEN SUBSTR('098', 1, LENGTH('098') -1) ELSE null END, CASE WHEN SUBSTR('098R',LENGTH('098R')) > 'A' THEN SUBSTR('098R', LENGTH('098R')) ELSE null END FROM oldtable
Assumes that the character is always the last position and length of 1.
Originally posted by entangled
Hi,
I am trying to figure out the best way to reformat the record entries in a database.
In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.
Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.
I have attached a copy of a screenshot with some notes.
Thanks in advance to anyone who sees the easy way to do this!
cheers,
Rick|||HI,
try to use decode(substr(backPM,length(backPM)-1,1),'R',substr(backPM,1,length(backPM)-1,backPM)
and use the same formula for AheadPM column
Originally posted by entangled
Hi,
I am trying to figure out the best way to reformat the record entries in a database.
In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.
Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.
I have attached a copy of a screenshot with some notes.
Thanks in advance to anyone who sees the easy way to do this!
cheers,
Rick|||Thank you for the example. This one example pretty much addresses both issues. I will work with this and see how I can apply this approach.
many thanks,
Rick Sperling
Originally posted by dmmac
An example:
INSERT INTO newtable (county, route, px_back, backpm)
SELECT county, integer(route), CASE WHEN SUBSTR('098',LENGTH('098')) > 'A' THEN SUBSTR('098', 1, LENGTH('098') -1) ELSE null END, CASE WHEN SUBSTR('098R',LENGTH('098R')) > 'A' THEN SUBSTR('098R', LENGTH('098R')) ELSE null END FROM oldtable
Assumes that the character is always the last position and length of 1.
Is this possible?
I have a procedure that uses a cursor. I want to use this procedure to build a table based on the cursor. How can I dynamically generate a table the will contain the results. I plan on executing this procedure to populate this table every week.
Thanks...create procedure...
declare my_cursor cursor for
select ...
open my_cursor
fetch next ... into <variables list>
while @.@.fetch_status = 0 begin
insert <table_name> values (<variables list>)
fetch next ... into ...
end
close my_cursor
deallocate my_cursor
returnsql
Thanks...create procedure...
declare my_cursor cursor for
select ...
open my_cursor
fetch next ... into <variables list>
while @.@.fetch_status = 0 begin
insert <table_name> values (<variables list>)
fetch next ... into ...
end
close my_cursor
deallocate my_cursor
returnsql
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?
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 in SSIS?
I got a OLE DB source pointing to 1 table
and 1 flat file destination.
currently this is how i export data from 1 table to 1 flat file.
To make things easier, I was wondering whether i can have only 1 OLE DB source pointing to few tables pointing to few file destinations so I dun need to create 1 SIS project for each table data exporting.
anyone can help me?
Unfortunately not because the metadata i.e. the columns involved in the transform need to be the same. So you can't have 1 data flow in a loop that is reconfigured for different tables and destinations.
If the source is SQL you could use BCP to produce the flat files but this then really isn't SSIS. Although you could run the bcp from within SSIS.
|||i was wondering if there can be a conditional loop in between the OLE DB source and the flat file.....
like if the table name is A then go to File destination A
if table name is B then go to File destination B etc....
hope someone understands what i m saying.
|||You can direct rows to 1 of multiple destinations based on characteristics of the row. This is done usnig the Conditional Split transform. You should look into that and see whether it will do what you require.
I don't know why you used the word "loop". There is no notion of looping in a data-flow.
-Jamie
|||no tats not what i want....
I trying to backup many tables into many text files using 1 SSIS package project. Is that possible?
trying to reduce the no. of SIS packages file i need to maintain.
|||Why not have 1 package containing many data-flows?
-Jamie
|||I got a OLE DB source pointing to 1 table
and 1 flat file destination.
currently this is how i export data from 1 table to 1 flat file.
To make things easier, I was wondering whether i can have only 1 OLE DB source pointing to few tables pointing to few file destinations so I dun need to create 1 SIS project for each table data exporting.
anyone can help me by posting a screenshot of how this can be done in SSIS....the data flows diagram i m not very sure...cos i just started using SSIS in SQL Server 2005.
any guides to SSIS will also be appreciated. Thanks!
i tried using 1 ole db source + file A
and 2 ole db source + file B separated in the Diagram but when i execute it , it doesnt run :(
Ah ok. You can't do this, as Simon explained earlier!
-Jamie
|||ok Jamie, lets look at it the other manner
I got a table on database server -> export to text file -> import to my local database....
I m doing this task several times...
can this be run consecutively in a data flow diagram? i tried but its not working.....cos of concurrency issues i guess.
can some expert enlighten me?
|||
You can run them all in the same data-flow (in which case there will be as many source and destination adapters as there are tables you are moving data from) or concurrently in seperate data-flows.
-Jamie
|||Be aware that even if you have 20 sources and 20 destinations they may not all run at once. SSIS has a process that determines the threads to use and the amount of concurrency. If running on a 1 proc machine you will get very different results that running on a 4 way machine.|||hmm so simon...what do u recommend?|||Brohans,
In this scenario its really hard to make a recommendation. These are your options where you have N tables that you have to move data from:
1) Have 1 data-flow that contains N source adapters going to N destination adapters
2) Have N data-flows, 1 for each table. Run them all in the same package
3) Have N packages
Its generally accepted that option #1 will be quicker when N is fairly small (e.g. 4 or 5 tables. I wouldn't like to speculate as to what will be quicker when (e.g.) N>25, I would guess at option #2 but that's only a guess. Option #3 probably isn't a goer. The amount of hardware will come into play here whatever you do. Perhaps this will help: http://blogs.conchango.com/jamiethomson/archive/2005/10/02/2227.aspx
To be honest, the only person that can answer this is yourself. Test and measure,Test and measure, Test and measure...
And let us know how it goes cos this could be really interesting.
-Jamie
|||great, my boss says he wants the individual packages which i just make to be used in
creating 1 entire database......like program them in sequence so that the data gets into
just nicely into tables which has foreign keys constraints....
i just beginning to figure out SSIS....how do i configure the file path for the data in each of these packages and how do i link these packages together......
SSIS is really a pain ...arghhhhhhh
|||brohans wrote:
great, my boss says he wants the individual packages which i just make to be used in
creating 1 entire database......like program them in sequence so that the data gets into
just nicely into tables which has foreign keys constraints....
If you want to execute things in a defined order then put everything in seperate data-flows and make sure they execute in that defined order using precedence constaints. There is no need to have more than 1 package.
i just beginning to figure out SSIS....how do i configure the file path for the data in each of these packages and how do i link these packages together......
The file path can be made dynamic through the use of expressions. For example, put an expression on the connection string of the flat file connection manager.
SSIS is really a pain ...arghhhhhhh
Why is it a pain? So far you haven't got a requirement that cannot be achieved.
Is it SSIS that is a pain or the fact that you're still learning how to use it? Its a hugely powerful tool but because of that there is a learning curve - I am confident you'll like it when you know how to fully leverage it. Like any technology it takes time to learn it properly.
-Jamie
Is this possible in SQL Server 2000 and MySQL?
hi guys, is it possible that i can retrieve records from different database like SQL Server 2000 and MySQL?
example, assuming i have some table in SQL Server 2000 and i also have in MySQL. the table in SQL Server 2000 is for example Table1 and in MySQL is Table2.
if i create a query that will combine and retrieve the fields of the two database, is it possible?
if so anyone who have idea with that and how to do that?
I assume you mean retrieving records from different servers because from different databases on the same Server it is just as simple as mentioning database name and owner in front of your table name like this:
Databasename.dbo.tablename
First ask your DBA to link both servers. With linked server it is really easy to do. If you dont have DBA go to help then search for subject linked servers. When servers are linked you query tables like this
Servername.Databasename.dbo.tablename
Also you can try OPENDATASOURCE query. Search for it in SQL help. You still need correct drivers to be installed on SQL server side and use them as provider otherwise it will not work.
Good Luck.
example, assuming i have some table in SQL Server 2000 and i also have in MySQL. the table in SQL Server 2000 is for example Table1 and in MySQL is Table2.
if i create a query that will combine and retrieve the fields of the two database, is it possible?
if so anyone who have idea with that and how to do that?
Thank You.
Quote:
Originally Posted by klaydze
hi guys, is it possible that i can retrieve records from different database like SQL Server 2000 and MySQL?
example, assuming i have some table in SQL Server 2000 and i also have in MySQL. the table in SQL Server 2000 is for example Table1 and in MySQL is Table2.
if i create a query that will combine and retrieve the fields of the two database, is it possible?
if so anyone who have idea with that and how to do that?
Thank You.
I assume you mean retrieving records from different servers because from different databases on the same Server it is just as simple as mentioning database name and owner in front of your table name like this:
Databasename.dbo.tablename
First ask your DBA to link both servers. With linked server it is really easy to do. If you dont have DBA go to help then search for subject linked servers. When servers are linked you query tables like this
Servername.Databasename.dbo.tablename
Also you can try OPENDATASOURCE query. Search for it in SQL help. You still need correct drivers to be installed on SQL server side and use them as provider otherwise it will not work.
Good Luck.
Subscribe to:
Posts (Atom)