conditions, expressions

I have a table

CREATE TABLE [dbo].[CmnLanguage]
(
[Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY,
[EnglishName] [varchar](26) NOT NULL,
[NativeName] [nvarchar](26) NOT NULL,
[DirectionType] [smallint] NOT NULL,
[IsVisible] [bit] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(),
[ModifiedDateTime] [datetime] NULL
)

We will use these 3 queries

select * from CmnLanguage where IsVisible = 0
select * from CmnLanguage where IsVisible = 1
select * from CmnLanguage

I want to make a method which handles these queries.

But at the back end on Stored Procedures

We have to write 3 queries

Which I don't want to do.

I want to minimize the queries and conditions

and want to just write one for these 3

Can any one do it?

How about this:

SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOCREATE PROCEDURE dbo.sp_MyProcedure(@.IsVisibleAS BIT =NULL)ASBEGINSELECT*FROM[dbo].[CmnLanguage]WHERE[IsVisible] =CASEWHEN @.IsVisibleISNULLTHEN [IsVisible]ELSE @.IsVisibleENDENDGO

|||

Nice.

Very Useful.

Thanks.

Conditions on latest record

I have a table that has records layed out as so:

Table:
fd_Id INT IDENTITY (1, 1)
fd_User VARCHAR(30)
fd_Effective DATETIME

Data could be as follows:
1 | "user1" | 6/20/2001
2 | "user2" | 6/1/2002
3 | "user2" | 6/5/2002
4 | "user2" | 6/5/2002
5 | "user2" | 2/1/2002
6 | "user3" | 9/1/2003
7 | "user3" | 10/2/2002
8 | "user4" | 1/1/2005

What I need to retrieve from that table is the SINGLE LATEST item of
each fd_User.

Results:
1 | "user1" | 6/20/2001
3 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates are
the same but only 1 of them
6 | "user3" | 9/1/2003
8 | "user4" | 1/1/2005Untested

SELECT
MAX(FD_ID) AS 'FD_ID',
FD_USER,
MAX(FD_EFFECTIVE) AS 'FD_EFFECTIVE'
FROM F_TABLE
GROUP FD_USER|||select min(a.fd_Id) as fd_Id,
a.fd_User,
a.fd_Effective
from mytable a
inner join (select fd_User,max(fd_Effective) as fd_Effective
from mytable
group by fd_User) b on a.fd_User=b.fd_User and
a.fd_Effective=b.fd_Effective
group by a.fd_User,a.fd_Effective|||Verticon:: wrote:
> I have a table that has records layed out as so:
> Table:
> fd_Id INT IDENTITY (1, 1)
> fd_User VARCHAR(30)
> fd_Effective DATETIME
> Data could be as follows:
> 1 | "user1" | 6/20/2001
> 2 | "user2" | 6/1/2002
> 3 | "user2" | 6/5/2002
> 4 | "user2" | 6/5/2002
> 5 | "user2" | 2/1/2002
> 6 | "user3" | 9/1/2003
> 7 | "user3" | 10/2/2002
> 8 | "user4" | 1/1/2005
> What I need to retrieve from that table is the SINGLE LATEST item of
> each fd_User.
> Results:
> 1 | "user1" | 6/20/2001
> 3 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates are
> the same but only 1 of them
> 6 | "user3" | 9/1/2003
> 8 | "user4" | 1/1/2005

First add the constraint that you're apparently missing:

ALTER TABLE tbl
ADD CONSTRAINT ak1_tbl
UNIQUE (fd_User, fd_Effective);

Then:

SELECT fd_Id, fd_User, fd_Effective
FROM tbl
WHERE fd_Effective =
(SELECT MAX(fd_Effective)
FROM tbl AS t
WHERE t.fd_User = tbl.fd_User);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--sqlsql

Conditional Split query

Hi,

I have the following table in MsAccess

EmployeesA

empId integer,

empName varchar(60),

empAge integer,

empStatus char(1) - can be N,D or S - New, Deleted or Shifted

and the following in Sql2005

EmployeesB

Id smallint,

Name varchar(60),

Age int,

Status char(1) - Bydefault 'N'

I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.

If empStatus = N in EmployeesA, then insert a new record in EmployeesB

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.

How do I do it for each table each row in EmployeesA using a foreach file loop?

Thanks,

ron

If you are using a data flow inside your For Each, you can use the techniques shown in this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1) to determine whether the row should be inserted or updated. The thread is dicussing specifically whether the row already exists or not, so you may need to add a conditional split to your data flow.

|||

Hi,

thanks for the reply. I had already seen that link. I cannot do a look up as Employees B will already contain millions of rows.

I just want to know this step by step if you could explain. I am so new to this SSIS.

How will I specify conditions :

If empStatus = N in EmployeesA, then insert a new record in EmployeesB

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.

Which control to use. Where to specify etc.

thanks,

|||

Ok,

I have figured out most of it through a friend. Just tell me this:

What i am doing is :

For status D, I do a Lookup and if found, I have to use an OLE DB Command tranform to do the update.

What query do I fire in the look up over here. If that row exists, after that what to do in the OLEDB command. ?How to pass the current row?

thanks.

|||Do you mean how to update the row that was matched in the lookup? Why can you not use the same fields you used in the lookup for the match and put them into the WHERE clause of your update? Sometimes it is cleaner to return a key column or two from the lookup, and them as basis for the WHERE clause.|||

Can you state an example. What should be in the Lookup and what in the OledbCommand based on my table.

Thanks

|||

An example, do you mean for this problem-

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

I would be tempted to skip the lookup. Use a Conditional Split to get a feed of all EmployeesA rows where empStatus = "D", then use a command to do the update. Set your connection, and end the SQL statement -

UPDATE EmployeesB

SET empStatus = 'D'

WHERE empname = ?

AND age = ?

Map the two input columns empname and age to the two parameters, to complete the OLD-DB Command setup.

This avoids the costs of a lookup, which may be faster overall. If there is no match, then no update happens, which is the same overall outcome as if the lookup had failed to find anything and the command was not run.

It may be faster to use a Lookup to help filter out the non-matches, it really depends on row counts and ratios of lookup hits to misses. Test both if you are worried about performance, but it is often faster to attempt and "fail" than to prevent the "fail" in the first place in SSIS.

|||

Darren,

you know what..that worked like a charm i removed the look up and did as you said..I will try the rest and if everything works, i will close this thread. thankuuuuuu.

If you could have a look at this thread too, I will be much obliged.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2178311&SiteID=1

thanks.

Condensed Type vs Data Type

In the database diagram, it used to be that Data Type was int, varchar, char, etc. and Condensed Type was int, varchar(30), char(5), etc. Why was this changed in SQL 2005? Is this a bug?

My assumption is that it's because of the new data type "xml". The help says as follows:-

Condensed Data Type

Displays information about the field's data type, in the same format as the SQL CREATE TABLE statement. For example, a field containing a variable-length string with a maximum length of 20 characters would be represented as "varchar(20)". To change this property, type the value directly.

ym

Condensed Type vs Data Type

In the database diagram, it used to be that Data Type was int, varchar, char, etc. and Condensed Type was int, varchar(30), char(5), etc. Why was this changed in SQL 2005? Is this a bug?

My assumption is that it's because of the new data type "xml". The help says as follows:-

Condensed Data Type

Displays information about the field's data type, in the same format as the SQL CREATE TABLE statement. For example, a field containing a variable-length string with a maximum length of 20 characters would be represented as "varchar(20)". To change this property, type the value directly.

ym

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advance
perspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4
|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advanceperspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
>> this is my DDL:
>> CREATE TABLE [dbo].[Table1] (
>> [Code] [int] IDENTITY (1, 1) NOT NULL ,
>> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [ParentCode] [int] NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Table1] ADD
>> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>> (
>> [Code]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Table1] ADD
>> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
>> (
>> [ParentCode]
>> ) REFERENCES [dbo].[Table1] (
>> [Code]
>> )
>> I want to concat Column of Name:
>> Code Name ParentCode
>> 1 test NULL
>> 2 book NULL
>> 3 Cake 1
>> 4 Mouse 3
>> I want to concat column of Name for Code=4 and output will be: testCake
>> thanks in advance
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>|||On Apr 30, 9:13 am, "perspolis" <reza...@.hotmail.com> wrote:
> I want to do that for many levels as is not for 2 rows.
> "Ed Enstrom" <nos...@.invalid.net> wrote in message
> news:np8Zh.98$eH4.18@.newsfe12.lga...
>
> > perspolis wrote:
> >> this is my DDL:
> >> CREATE TABLE [dbo].[Table1] (
> >> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> >> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> >> [ParentCode] [int] NULL
> >> ) ON [PRIMARY]
> >> GO
> >> ALTER TABLE [dbo].[Table1] ADD
> >> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> >> (
> >> [Code]
> >> ) ON [PRIMARY]
> >> GO
> >> ALTER TABLE [dbo].[Table1] ADD
> >> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> >> (
> >> [ParentCode]
> >> ) REFERENCES [dbo].[Table1] (
> >> [Code]
> >> )
> >> I want to concat Column of Name:
> >> Code Name ParentCode
> >> 1 test NULL
> >> 2 book NULL
> >> 3 Cake 1
> >> 4 Mouse 3
> >> I want to concat column of Name for Code=4 and output will be: testCake
> >> thanks in advance
> > SELECT c.[Name] + b.[Name] AS ConcatName
> > FROM Table1 AS a
> > JOIN Table1 AS b ON a.ParentCode = b.Code
> > JOIN Table1 AS c ON b.ParentCode = c.Code
> > WHERE a.Code = 4- Hide quoted text -
> - Show quoted text -
If you are using SQL Server 2005 CTE with recursive query.
with temp as
(select convert(varchar(50),'') + convert(varchar(50),'')
name ,parentcode,code from table1 where code =4
union all
select convert(varchar(50),t1.name)+convert(varchar(50),t.name) as
name ,t1.parentcode,t1.code
from table1 t1 inner join temp t on t.parentcode = t1.code)
select name from temp where parentcode is null
Regards
Amish shah
http://shahamishm.tripod.com

Concatinating Variables

Hello,

How do I concatinate a variable. Here's the scenarios:

declare @.var1 varchar(20)
declare @.var2 varchar(20)
declare @.var3 varchar(20)
declare @.var4 varchar(20)
..
..
declare @.var32 varchar(20)

set @.var1 = 'Something 1'
set @.var2 = 'Something 2'
...
set @.var32 = 'Something 3'

/* I have to store the values of these individual variables. I wish to
have a "While" routine which iterates through the above variables. I
wish to have the variable name concatinated as that I do not have to
write numerous lines of code setting up individual 32 variables. How
could I use the '+' operator to join 'var' + @.count . Where count is
from 1 through 32. I am having some trouble with the syntax.*/

Regards,
VS[posted and mailed, please reply in news]

TinTin (lalalulu24@.yahoo.com) writes:
> How do I concatinate a variable. Here's the scenarios:
> declare @.var1 varchar(20)
> declare @.var2 varchar(20)
> declare @.var3 varchar(20)
> declare @.var4 varchar(20)
> .
> .
> declare @.var32 varchar(20)
> set @.var1 = 'Something 1'
> set @.var2 = 'Something 2'
> ...
> set @.var32 = 'Something 3'

SELECT @.concat = @.var1 + @.var2 + ... + @.var32

> /* I have to store the values of these individual variables. I wish to
> have a "While" routine which iterates through the above variables. I
> wish to have the variable name concatinated as that I do not have to
> write numerous lines of code setting up individual 32 variables. How
> could I use the '+' operator to join 'var' + @.count . Where count is
> from 1 through 32. I am having some trouble with the syntax.*/

Nah, you don't have a syntax problem, you have a mindset problem. When
you work in T-SQL, looping is something you don't do that often. This
is a very different language from VB or C++.

While T-SQL does have some looping constructs, normally you operate on
sets of data at a time through tables. While this may be more difficult
to grok initially, this is necessity to get performance with any size
of data volume.

Since I don't know why you have these 32 variables, and what your actual
business problem is, I cannot really tell what you should do instead.
But you should probably not concatenate 32 variables.

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

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

You will probably run into scope problems with this approach.

There seems to be a design issue here, but without knowing more about the
table strucures and what you are actually trying to do it is hard to suggest
a better solution other than hard coding each one or (as per your previous
post) look at using a temporary tables.

John

"TinTin" <lalalulu24@.yahoo.com> wrote in message
news:2d5425d1.0406151243.3bf0165a@.posting.google.c om...
> Hello,
> How do I concatinate a variable. Here's the scenarios:
> declare @.var1 varchar(20)
> declare @.var2 varchar(20)
> declare @.var3 varchar(20)
> declare @.var4 varchar(20)
> .
> .
> declare @.var32 varchar(20)
> set @.var1 = 'Something 1'
> set @.var2 = 'Something 2'
> ...
> set @.var32 = 'Something 3'
> /* I have to store the values of these individual variables. I wish to
> have a "While" routine which iterates through the above variables. I
> wish to have the variable name concatinated as that I do not have to
> write numerous lines of code setting up individual 32 variables. How
> could I use the '+' operator to join 'var' + @.count . Where count is
> from 1 through 32. I am having some trouble with the syntax.*/
> Regards,
> VS|||Hello John and Erland,

Thankyou for replying to my message.

As a matter or fact I have total of 52 variables.

These values are stored in an Excel spreadsheet. I use the DTS service
to import this spreadsheet into a temp table in SQL Server. Now I need
to catch these 53 different numeric values and parse them into
appropriate tabels in my Database. I use the Cursor to traverse
through the records in temp table and all these 53 values are Fetched
in 1 single record.

Hope this helps.

Regards!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<VbLzc.1167$hb6.9819346@.news-text.cableinet.net>...
> Hi
> You will probably run into scope problems with this approach.
> There seems to be a design issue here, but without knowing more about the
> table strucures and what you are actually trying to do it is hard to suggest
> a better solution other than hard coding each one or (as per your previous
> post) look at using a temporary tables.
> John
> "TinTin" <lalalulu24@.yahoo.com> wrote in message
> news:2d5425d1.0406151243.3bf0165a@.posting.google.c om...
> > Hello,
> > How do I concatinate a variable. Here's the scenarios:
> > declare @.var1 varchar(20)
> > declare @.var2 varchar(20)
> > declare @.var3 varchar(20)
> > declare @.var4 varchar(20)
> > .
> > .
> > declare @.var32 varchar(20)
> > set @.var1 = 'Something 1'
> > set @.var2 = 'Something 2'
> > ...
> > set @.var32 = 'Something 3'
> > /* I have to store the values of these individual variables. I wish to
> > have a "While" routine which iterates through the above variables. I
> > wish to have the variable name concatinated as that I do not have to
> > write numerous lines of code setting up individual 32 variables. How
> > could I use the '+' operator to join 'var' + @.count . Where count is
> > from 1 through 32. I am having some trouble with the syntax.*/
> > Regards,
> > VS|||Also:

53 values is the worse case scenario. Total number of variables could
be 3 to 53. Script needs to check how many total variables there are
and then likewise take action.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<VbLzc.1167$hb6.9819346@.news-text.cableinet.net>...
> Hi
> You will probably run into scope problems with this approach.
> There seems to be a design issue here, but without knowing more about the
> table strucures and what you are actually trying to do it is hard to suggest
> a better solution other than hard coding each one or (as per your previous
> post) look at using a temporary tables.
> John
> "TinTin" <lalalulu24@.yahoo.com> wrote in message
> news:2d5425d1.0406151243.3bf0165a@.posting.google.c om...
> > Hello,
> > How do I concatinate a variable. Here's the scenarios:
> > declare @.var1 varchar(20)
> > declare @.var2 varchar(20)
> > declare @.var3 varchar(20)
> > declare @.var4 varchar(20)
> > .
> > .
> > declare @.var32 varchar(20)
> > set @.var1 = 'Something 1'
> > set @.var2 = 'Something 2'
> > ...
> > set @.var32 = 'Something 3'
> > /* I have to store the values of these individual variables. I wish to
> > have a "While" routine which iterates through the above variables. I
> > wish to have the variable name concatinated as that I do not have to
> > write numerous lines of code setting up individual 32 variables. How
> > could I use the '+' operator to join 'var' + @.count . Where count is
> > from 1 through 32. I am having some trouble with the syntax.*/
> > Regards,
> > VS|||> through the records in temp table and all these 53 values are Fetched
> in 1 single record.

This sounds like a design problem too. Column values should be atomic values
not concatenated strings. On the other hand, 53 columns representing a list
of values seems unlikely to be the right design either: "Lists" are
analogous to *tables* not a set of values in a row.

Are you familiar with the concept of Normalization? Are you sure your
database is in at least Third Normal Form? If you don't have the correct
design to start with then you won't have the right foundation to build
concise, efficient and maintainable code and you'll have to struggle with
lots of cursors, loops and variables.

On the other hand, if you've already properly identified the entities and
attributes in your schema then post your CREATE TABLE statements so that we
can understand the problem and suggest how to tacke it.

Hope this helps.

--
David Portas
SQL Server MVP
--|||The table which I talk about is a temporary table which is created
within the procedure and would be deleted once I leave the stored
procedure. The sole purpose of the table would be to catch the values
from a seperate table which is extracted from Excel spreadsheet. It's
not dependent on any of the other tables in the database. Hence,
haven't looked upon Normalizing.

Here's the sample lines from the spreadsheet.I used the DTS to import
this into the database table.

Country Nigeria
Region African Continent
Gender Male Male Male Male Male Female
Female ...
Age Group 0-10 10-20 20-30 40-50 50-60 0-10
10-20 ...
Total 200 323 111 3232 333 555 333
..
..
..

Country Nicragua
Region African Continent
Gender Male Male Male Female Female Female
Age Group 0-4 15-20 20-30 0-4 15-20 20-30
Total 200 323 111 3232 112 441
..
..
..

Now... Age group can have several more or a lot fewer age categories.
I need to capture the individual values in the 'Age Group' and
'Total'. That is why I need to have 53 variables (Worst Case). I use a
Cursor to iterate through each of the above lines. Once I capture the
information, I need to send it to my database structure.

What's the best way to design a Stored Procedure. Messier way is to
have 53 varbs. (worst case) and have 2 tempory tables to store the
above 2 rows (Age Group, Total). If I follow this approach, I need to
iterate through the fields in the temporary variables to make sure I
do not encounter any NULLs in between. For example, the 2nd example
above ( Nicragua) will have nulls in all the fields from field 6
through 53.

Following is the structure of the temp table
create table temptable1(
var1 varchar (10),
var2 varchar (10),
..
..
..
var53 varchar (10)
)

Regards,
VS

lalalulu24@.yahoo.com (TinTin) wrote in message news:<2d5425d1.0406160522.73ecb03@.posting.google.com>...
> Also:
> 53 values is the worse case scenario. Total number of variables could
> be 3 to 53. Script needs to check how many total variables there are
> and then likewise take action.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<VbLzc.1167$hb6.9819346@.news-text.cableinet.net>...
> > Hi
> > You will probably run into scope problems with this approach.
> > There seems to be a design issue here, but without knowing more about the
> > table strucures and what you are actually trying to do it is hard to suggest
> > a better solution other than hard coding each one or (as per your previous
> > post) look at using a temporary tables.
> > John
> > "TinTin" <lalalulu24@.yahoo.com> wrote in message
> > news:2d5425d1.0406151243.3bf0165a@.posting.google.c om...
> > > Hello,
> > > > How do I concatinate a variable. Here's the scenarios:
> > > > declare @.var1 varchar(20)
> > > declare @.var2 varchar(20)
> > > declare @.var3 varchar(20)
> > > declare @.var4 varchar(20)
> > > .
> > > .
> > > declare @.var32 varchar(20)
> > > > set @.var1 = 'Something 1'
> > > set @.var2 = 'Something 2'
> > > ...
> > > set @.var32 = 'Something 3'
> > > > /* I have to store the values of these individual variables. I wish to
> > > have a "While" routine which iterates through the above variables. I
> > > wish to have the variable name concatinated as that I do not have to
> > > write numerous lines of code setting up individual 32 variables. How
> > > could I use the '+' operator to join 'var' + @.count . Where count is
> > > from 1 through 32. I am having some trouble with the syntax.*/
> > > > Regards,
> > > VS|||It seems that the purpose of your stored procedure is to transform the
spreadsheet data into a form that you can use, presumably in a table. It is
possible to do this kind of transformation in SQL, it just isn't pretty!
Here
goes.

First, assume you have your sample data loaded into your temp table. I've
added a row number to help us later. You would obviously do this bit in DTS:

CREATE TABLE TempTable1 (rowno INTEGER IDENTITY PRIMARY KEY, col1
VARCHAR(20) NULL, col2 VARCHAR(20) NULL, col3 VARCHAR(20) NULL, col4
VARCHAR(20) NULL, col5 VARCHAR(20) NULL, col6 VARCHAR(20) NULL, col7
VARCHAR(20) NULL, col8 VARCHAR(20) NULL)

INSERT INTO TempTable1 (col1,col2) VALUES ('Country','Nigeria')
INSERT INTO TempTable1 (col1,col2) VALUES ('Region','African Continent')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7,col8)
VALUES ('Gender','Male','Male','Male','Male','Male','Fema le','Female')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7,col8)
VALUES ('Age Group','0-10','10-20','20-30','40-50','50-60','0-10','10-20')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7,col8)
VALUES ('Total','200','323','111','3232','333','555','333 ')
INSERT INTO TempTable1 (col1,col2) VALUES ('Country','Nicaragua')
INSERT INTO TempTable1 (col1,col2) VALUES ('Region','African Continent')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7)
VALUES ('Gender','Male','Male','Male','Female','Female',' Female')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7)
VALUES ('Age Group','0-4','15-20','20-30','0-4','15-20','20-30')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7)
VALUES ('Total','200','323','111','3232','112','441')

You haven't told us what the target structure is that you want to put the
data into. Based on your sample I'll assume it looks something like this:

CREATE TABLE SomeStats (country VARCHAR(20) NOT NULL /* REFERENCES Countries
(country) */, min_age INTEGER NOT NULL, max_age INTEGER NOT NULL, CHECK
(min_age>=0 AND max_age>min_age AND max_age<=120), gender CHAR(1) NOT NULL
CHECK (gender IN ('M','F')), stat INTEGER NOT NULL, PRIMARY KEY
(country,gender,min_age))

In reality you would probably want to use codes rather than country names.
The Continent name obviously belongs in the Countries table rather than here
so I've left it out.

Create a view:

CREATE VIEW TransformStats
AS
SELECT rowno, 1 AS col, col1 AS stat
FROM TempTable1
UNION ALL
SELECT rowno, 2 AS col, col2
FROM TempTable1
UNION ALL
SELECT rowno, 3 AS col, col3
FROM TempTable1
UNION ALL
SELECT rowno, 4 AS col, col4
FROM TempTable1
UNION ALL
SELECT rowno, 5 AS col, col5
FROM TempTable1
UNION ALL
SELECT rowno, 6 AS col, col6
FROM TempTable1
UNION ALL
SELECT rowno, 7 AS col, col7
FROM TempTable1
UNION ALL
SELECT rowno, 8 AS col, col8
FROM TempTable1

Finally, insert your data:

INSERT INTO SomeStats (country, min_age, max_age, gender, stat)
SELECT country,
LEFT(age,CHARINDEX('-',age)-1),
SUBSTRING(age,CHARINDEX('-',age)+1,20),
gender, stat
FROM
(SELECT
(SELECT stat
FROM TransformStats
WHERE rowno=
(SELECT MAX(rowno)
FROM TransformStats
WHERE col = 1
AND stat = 'Country'
AND rowno <= T.rowno)
AND col=2) AS country,
(SELECT stat
FROM TransformStats
WHERE rowno=
(SELECT MAX(rowno)
FROM TransformStats
WHERE col = 1
AND stat = 'Age Group'
AND rowno <= T.rowno)
AND col=T.col) AS age,
(SELECT LEFT(stat,1)
FROM TransformStats
WHERE rowno=
(SELECT MAX(rowno)
FROM TransformStats
WHERE col = 1
AND stat = 'Gender'
AND rowno <= T.rowno)
AND col=T.col) AS gender,
stat
FROM
TransformStats AS T
WHERE ISNUMERIC(stat)=1) AS T

This will of course fail if your spreadsheets aren't in a fairly regular,
predictable format. This is an unavoidable problem with spreadsheet data and
there isn't an easy solution except to find a reliable, structured data
source. Your data looks somewhat suspect anyway. Last time I checked my
atlas Nicragua [sic] wasn't in Africa :)

Hope this helps.

--
David Portas
SQL Server MVP
--

Concatenation of Text

I have an instance where I need to concatenate some data that is stored in a text datatype. I can't cast it to a varchar/char because that may well truncate the data. I just read about UPDATETEXT, which I think I can use, but I need to use it for a bunch or rows and it looks like this works on one row at a time. Anyone have experience with this?Yes, UpdateText processes only one row per call.

The MDAC infrastructure was never meant to handle BLOBs, so trying to update a TEXT column in a million rows at once is really a bad idea. I'd either change the column datatype, or find a different way to acheive the same goal.

This just sounds like a problem sniffing eagerly at a new victim to me!

-PatP|||Thanks Pat. Unfortunately, I have to use a TEXT column because the data can exceed the 8k limit for VARCHAR and CHAR. I guess my choices are to use DTS and some sort of ActiveX script, write a script to concatenate before it hits the database, or do the unthinkable and write a cursor (although I don't really want to do that).

Dandy|||Nothing quite like being caught between the devil and the deep blue sea, is there? Given those choices, I'd opt for DTS.

Actually, cursors aren't logically bad, it is just that their performance is awful compared to set operations. Many databases like Oracle and Z-Series DB2 rely on cursors to do much of anything.

Be forewarned though, TEXT manipulation is slower than other datatypes. Just retrieving or storing a row with a TEXT or IMAGE column takes a lot longer than it does without those columns.

-PatP|||Of course it does, doh...First it needs to get the pointer (binary(16)), then retrieve 8K worth of data stored in a separate set of pages which results in at least 1 additional IO per row retrieved. BLOBs are wonderful (hehehe) when used for what they were invented, - not for set-based operations. And this is one of the few cases when a cursor may very well be applicable.

The MDAC infrastructure was never meant to handle BLOBs...Really? Who told you that?|||Really? Who told you that?It was either the guy who invented DTS, or the GPM for MS-SQL 7.0. They were both there, I just don't remember who actually said it and who just nodded sagely.

-PatP|||Oh, I see, and I'm an airplane :D|||I'm a teapot! I'm a teapot!

Concatenation getting truncated

Hello,

Using SQL SERVER 2000

I have 4 columns with varchar(80) each that I want to concatenate.
When I look at the result, it only gives me 256 characters. What am I
missing on my code?

Select Cust_Number, Info = convert(varchar(1000),rtrim(line1) +
char(13)+rtrim(Line2) + char(13)+ rtrim(line3) + char(13)+
rtrim(line4))
>From tableOne
Go

Thank you for your input.

EdgarEdgar (edgarjtan@.yahoo.com) writes:
> Using SQL SERVER 2000
> I have 4 columns with varchar(80) each that I want to concatenate.
> When I look at the result, it only gives me 256 characters. What am I
> missing on my code?
> Select Cust_Number, Info = convert(varchar(1000),rtrim(line1) +
> char(13)+rtrim(Line2) + char(13)+ rtrim(line3) + char(13)+
> rtrim(line4))
>>From tableOne
> Go

Probably nothing. If you are using Query Analyzer, look under
Tools->Options->Results->Maximum Characters per Column.

--
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|||On 23 May 2006 21:09:32 -0700, Edgar wrote:

>Hello,
>Using SQL SERVER 2000
>I have 4 columns with varchar(80) each that I want to concatenate.
>When I look at the result, it only gives me 256 characters. What am I
>missing on my code?
>Select Cust_Number, Info = convert(varchar(1000),rtrim(line1) +
>char(13)+rtrim(Line2) + char(13)+ rtrim(line3) + char(13)+
>rtrim(line4))
>>From tableOne
>Go
>Thank you for your input.
>Edgar

Hi Edgar,

Query Analyzer is displaying only part of the results.

Click "Tools" / "Options" and activate the "Results" tab. Increase the
"Maximum characters per column" to at least 320. Now rerun the query.

--
Hugo Kornelis, SQL Server MVP|||Thank you so much, Mr. Kornelis and Mr.Sommarskog, for your direction.

Now I can see all the data on my concatenated column.

Edgar J.

concatenation

could you give me a dig out with this concatenation, it's driving me nuts
declare @.ev_bat_desc varchar(50), @.text varchar(100)
select @.ev_bat_desc = 'CQA_cmdmgr_Marc.bat'
-- select 'c:\psexec \\10.2.27.230 -I ' + @.ev_bat_desc + ' ;'
-- emulate
Exec master..xp_cmdshell 'c:\psexec \\10.2.27.230 -I' + @.ev_bat_desc + ' ;'
-- works
Exec master..xp_cmdshell 'c:\psexec \\10.2.27.230 -I CQA_cmdmgr_Marc.bat' ;Check whether this works
Exec master..xp_cmdshell 'c:\psexec \\10.2.27.230 -I @.ev_bat_desc '
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"marcmc" wrote:
> could you give me a dig out with this concatenation, it's driving me nuts
> declare @.ev_bat_desc varchar(50), @.text varchar(100)
> select @.ev_bat_desc = 'CQA_cmdmgr_Marc.bat'
> -- select 'c:\psexec \\10.2.27.230 -I ' + @.ev_bat_desc + ' ;'
> -- emulate
> Exec master..xp_cmdshell 'c:\psexec \\10.2.27.230 -I' + @.ev_bat_desc + ' ;
'
> -- works
> Exec master..xp_cmdshell 'c:\psexec \\10.2.27.230 -I CQA_cmdmgr_Marc.bat' ;[/color
]|||Hi, no it just gives me the parameters/functions list for the psExec app.
If I build up the string into one variable eg
@.text = 'c:\psexec \\10.2.27.230 -I ' + @.ev_bat_desc + ' ;'
Exec master..xp_cmdshell @.text
it will work. It means a lot more arsing around re: parameterisation but it
seems a bit silly that its can't build its string in the Exec Stmt.

concatenating Varchar and Text

I am trying to write a select statement that concatenates a Varchar and Text
field into a Text.
So I basically want something like:
Select VarcharColumn + TextColumn from tablea
I know that I could convert the TextColumn to varchar(8000) and concatenate
that with the varchar column but there may be instances where the TextColumn
exceeds 8000 bytes. So I would need the datatype of this concatenated field
to be of type TEXT.
Any help would be appreciated.
ThanksYou will have to use UPDATETEXT to concatenate text columns. Check out the
details, syntax and examples of UPDATETEXT in SQL Server Books Online.
Anith

concatenating Varchar and Text

I am trying to write a select statement that concatenates a Varchar and Text
field into a Text.
So I basically want something like:
Select VarcharColumn + TextColumn from tablea
I know that I could convert the TextColumn to varchar(8000) and concatenate
that with the varchar column but there may be instances where the TextColumn
exceeds 8000 bytes. So I would need the datatype of this concatenated field
to be of type TEXT.
Any help would be appreciated.
ThanksYou will have to use UPDATETEXT to concatenate text columns. Check out the
details, syntax and examples of UPDATETEXT in SQL Server Books Online.
--
Anith

concatenating Varchar and Text

I am trying to write a select statement that concatenates a Varchar and Text
field into a Text.
So I basically want something like:
Select VarcharColumn + TextColumn from tablea
I know that I could convert the TextColumn to varchar(8000) and concatenate
that with the varchar column but there may be instances where the TextColumn
exceeds 8000 bytes. So I would need the datatype of this concatenated field
to be of type TEXT.
Any help would be appreciated.
Thanks
You will have to use UPDATETEXT to concatenate text columns. Check out the
details, syntax and examples of UPDATETEXT in SQL Server Books Online.
Anith

Concatenating two floats with a comma in the middle

i am using the following code to try and get the following results:
convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
to get:
(for example) "100.22,44.5"
But i get the following error: Error converting data type varchar to
float.
I assume sqlserver is trying to convert the comma to a float to do an
addition.
I thought that the fact that i converted the two floats to varchars
would have stopped this, but it doesn't.
Does anyone know why?
<arun.hallan@.gmail.com> wrote in message
news:1138889221.983004.20020@.g47g2000cwa.googlegro ups.com...
>i am using the following code to try and get the following results:
> convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
> to get:
> (for example) "100.22,44.5"
>
> But i get the following error: Error converting data type varchar to
> float.
> I assume sqlserver is trying to convert the comma to a float to do an
> addition.
> I thought that the fact that i converted the two floats to varchars
> would have stopped this, but it doesn't.
> Does anyone know why?
>
Your code snippet worked fine for me using the following DDL. Can you post
your DDL and maybe we can find the error?
CREATE TABLE #Foo (
LowerStrike float,
UpperStrike float
)
INSERT #Foo VALUES (100.22, 44.5)
INSERT #Foo VALUES (889.38, 4830.0)
SELECT *
FROM #Foo
SELECT CONVERT(varchar, r.LowerStrike) + ', ' + CONVERT(varchar,
r.UpperStrike) AS 'NewValue'
FROM #Foo r
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA
|||I'm not sure what my DDL is.
It's the sqk server at work - not sure where those things are kept.
|||<arun.hallan@.gmail.com> wrote in message
news:1138897900.963273.254320@.z14g2000cwz.googlegr oups.com...
> I'm not sure what my DDL is.
> It's the sqk server at work - not sure where those things are kept.
>
Check here for more info.
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

Concatenating two floats with a comma in the middle

i am using the following code to try and get the following results:
convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
to get:
(for example) "100.22,44.5"
But i get the following error: Error converting data type varchar to
float.
I assume sqlserver is trying to convert the comma to a float to do an
addition.
I thought that the fact that i converted the two floats to varchars
would have stopped this, but it doesn't.
Does anyone know why?<arun.hallan@.gmail.com> wrote in message
news:1138889221.983004.20020@.g47g2000cwa.googlegroups.com...
>i am using the following code to try and get the following results:
> convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
> to get:
> (for example) "100.22,44.5"
>
> But i get the following error: Error converting data type varchar to
> float.
> I assume sqlserver is trying to convert the comma to a float to do an
> addition.
> I thought that the fact that i converted the two floats to varchars
> would have stopped this, but it doesn't.
> Does anyone know why?
>
Your code snippet worked fine for me using the following DDL. Can you post
your DDL and maybe we can find the error?
CREATE TABLE #Foo (
LowerStrike float,
UpperStrike float
)
INSERT #Foo VALUES (100.22, 44.5)
INSERT #Foo VALUES (889.38, 4830.0)
SELECT *
FROM #Foo
SELECT CONVERT(varchar, r.LowerStrike) + ', ' + CONVERT(varchar,
r.UpperStrike) AS 'NewValue'
FROM #Foo r
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA|||I'm not sure what my DDL is.
It's the sqk server at work - not sure where those things are kept.|||<arun.hallan@.gmail.com> wrote in message
news:1138897900.963273.254320@.z14g2000cwz.googlegroups.com...
> I'm not sure what my DDL is.
> It's the sqk server at work - not sure where those things are kept.
>
Check here for more info.
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

Concatenating strings in a Select statment

I have this code:
declare @.var varchar(3000)
select @.var = @.var + column1 + ', '
from table1
select @.var
This statements give as a result all the values in column1 followed each by
a coma. My problem is that in a particular sever that doesn't work ok. If I
run the statements inside a proc it only returs the last value in the table,
but if I run it outside the proc (same query) it gives me the correct
results. Does anyone know why could that be?..
THanks>> Does anyone know why could that be?..
The SELECT statement you have is not a valid or supported in t-SQL. It is
simply a hack which seems to work in some cases, but breaks in a variety of
scenarios. Avoid such make-shift constructs due to its undocumented & risky
nature.
Anith

Concatenating a field while grouping records

All,

Given multiple records with identical values in all fields except a
single varchar field, is there an efficient query that will group the
records into a single record and concatenate the aforementioned
varchar field into a single field with each of the source records'
values separated by commas?

Example:
Record 1 'Doug' , '1'
Record 2 'Doug' , '2'

Output record 'Doug' , '1,2'

Thanks in advance,
DougSELECT col1,
MIN(CASE seq WHEN 1 THEN col2 END)+
COALESCE(', '+MIN(CASE seq WHEN 2 THEN col2 END),'')+
COALESCE(', '+MIN(CASE seq WHEN 3 THEN col2 END),'')+
COALESCE(', '+MIN(CASE seq WHEN 4 THEN col2 END),'')+
COALESCE(', '+MIN(CASE seq WHEN 5 THEN col2 END),'')
FROM
(SELECT S1.col1, S2.col2, COUNT(*) AS seq
FROM Sometable AS S1
JOIN Sometable AS S2
ON S1.col1 = S2.col1
AND S1.col2 <= S2.col2
GROUP BY S1.col1, S2.col2) AS X
GROUP BY col1

--
David Portas
----
Please reply only to the newsgroup
--|||This is trivial with the RAC utility for S2k.
No cursors,no complicated code and no hassles.

More info @.
http://www.rac4sql.net/onlinehelp.asp?topic=236

RAC v2.2 and QALite released.
www.rac4sql.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!