SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Dynamic field name creation in loop without cursors
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 01/07/2005 :  08:58:43  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Xerxes writes "I have several fields with incremental names: "GRP1NAME, GRP1EffDt, GRP2NAME, GRP2EffDt, GRP3NAME, ...". I want to create and examine the content of each of these fields in a loop without cursors. When I reach a record that has nothing within a given field (say GRP7NAME) I want to report it."

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 01/07/2005 :  09:46:37  Show Profile  Visit spirit1's Homepage  Reply with Quote
maybe:
select GRP1NAME, GRP1EffDt, GRP2NAME, GRP2EffDt, GRP3NAME
from MyTable
where GRP1NAME is null or
GRP1EffDt is null or
GRP2NAME is null or
GRP2EffDt is null or
GRP3NAME is null


Go with the flow & have fun! Else fight the flow
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 01/07/2005 :  11:19:14  Show Profile  Reply with Quote
What I meant was that I wanted to loop through the fields creating the field name dynamically.....something like:

SET PFX = 'GRP'
SET SEQ = 1


then loop through each field using a variable like:

CASE
WHEN RTRIM(PFX+SEQ+'NAME') = 'whatever' THEN X = SEQ
END


~~~~~~~~~~~~~
Semper fi,
Xerxes
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 01/07/2005 :  12:33:28  Show Profile  Visit spirit1's Homepage  Reply with Quote
can't think of an elegant set based solution for this...
you could build the string dynamicaly and execute the recordset with

exec master..xp_execresultset @resultset, @database
see: http://www.rac4sql.net/xp_execresultset.asp

maybe that will give you some ideas.
however as xp_execresultset is undocumented it's not recommended to use it in production enviroment.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 01/07/2005 :  16:47:39  Show Profile  Reply with Quote
Thanks anyway....

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC
Go to Top of Page

wmelancon
Starting Member

9 Posts

Posted - 01/20/2005 :  17:08:58  Show Profile  Reply with Quote
Xerxes,

Have you gotten anywhere with this?

I want to accomplish something very similar.

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 01/20/2005 :  17:11:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:


in a loop without cursors




What's the difference? Both are bad. The alternative to cursors is using WHILE. But you won't get much of a performance improvement though.

Tara
Go to Top of Page

wmelancon
Starting Member

9 Posts

Posted - 01/20/2005 :  17:29:55  Show Profile  Reply with Quote
quote:
Originally posted by tduggan


What's the difference? Both are bad. The alternative to cursors is using WHILE. But you won't get much of a performance improvement though.

Tara




Sorry, I should have clarified. I don't care about the method. How do you reference a SQL field name using a variable and not have SQL try to resolve as a value.

I am trying to avoid 50 if then statements.
My fields are as follows: TTO1, TTO2... TTO50
I need to find the first 0 value field and plug it with a value.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 01/20/2005 :  17:33:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
You would use CASE for this. Why do you need to use a variable for a column name. Write them out explicitly for performance reasons.

Tara
Go to Top of Page

wmelancon
Starting Member

9 Posts

Posted - 01/20/2005 :  17:42:52  Show Profile  Reply with Quote
quote:
Originally posted by tduggan

You would use CASE for this. Why do you need to use a variable for a column name. Write them out explicitly for performance reasons.

Tara



So is it more efficient to just use the "if else" statements (see below) within my stored procedure than to try to create a loop to generate my answer. I was attempting to make this less of a maintenance nightmare.

if (select TTTO1 from qryTaxByUnit where WKUNAME = @Unit) = 0
SELECT @table2 = (SELECT Convert(varchar, TTBRKPT+1) + ' ,' + Convert(varchar,TTFROM2) .etc. from qryTaxByUnit where WKUNAME = @Unit)
else
if (select TTTO2 from qryTaxByUnit where WKUNAME = @Unit) = 0
SELECT @table2 = (SELECT Convert(varchar,TTFROM1) + ' ,' + Convert(varchar, TTBRKPT+1) .etc. from qryTaxByUnit where WKUNAME = @Unit)
else

And so on....

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 01/20/2005 :  17:44:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
Is this SQL Server or Access? If this is for Access, you'll want to post this in the Access forum as this is a SQL Server forum.

To answer your question, I would need to see your table structure, sample data, and expected result set using that sample data.

Tara
Go to Top of Page

wmelancon
Starting Member

9 Posts

Posted - 01/20/2005 :  17:50:18  Show Profile  Reply with Quote
quote:
Originally posted by tduggan

Is this SQL Server or Access? If this is for Access, you'll want to post this in the Access forum as this is a SQL Server forum.

To answer your question, I would need to see your table structure, sample data, and expected result set using that sample data.

Tara



SQL Server... as you can obviously tell given your question I don't write many stored procedures...

I will try to simplify the sample data to post it.

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/20/2005 :  17:59:05  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Advice -- read up on normalization your database. When you add data to your table, it should be stored in rows, not columns.

- Jeff
Go to Top of Page

wmelancon
Starting Member

9 Posts

Posted - 01/20/2005 :  18:01:27  Show Profile  Reply with Quote
quote:
Originally posted by jsmith8858

Advice -- read up on normalization your database. When you add data to your table, it should be stored in rows, not columns.

- Jeff



My source is not driven by me, nor created by me. Believe me I appreciate your advice. I just don't have any control over that at this point. I just have to deal with it as it is.
Thx
Go to Top of Page

wmelancon
Starting Member

9 Posts

Posted - 01/20/2005 :  18:22:46  Show Profile  Reply with Quote
quote:
Originally posted by tduggan

To answer your question, I would need to see your table structure, sample data, and expected result set using that sample data.

Tara


MAJOR SOURCE TABLE (Main one used in qryTaxByUnit)
CREATE TABLE [CRTTABLP] (
[TTABLE] [char] (15) NOT NULL ,
[TTMETHOD] [char] (1) NOT NULL ,
... removed for simplicity
[TTBRKPT] [decimal](3, 0) NOT NULL ,
[TTFROM1] [decimal](3, 0) NOT NULL ,
[TTFROM2] [decimal](3, 0) NOT NULL ,
... removed for simplicity
[TTFROM50] [decimal](3, 0) NOT NULL ,
[TTTO1] [decimal](3, 0) NOT NULL ,
[TTTO2] [decimal](3, 0) NOT NULL ,
...removed for simplicity
[TTTO50] [decimal](3, 0) NOT NULL ,
[TTAMT1] [decimal](2, 0) NOT NULL ,
[TTAMT2] [decimal](2, 0) NOT NULL ,
...
[TTAMT50] [decimal](2, 0) NOT NULL
)
SAMPLE DATA for above table (actual values are between the {})
WKUNAME {GFVDE}
TTABLE {T.07863}
TTMETHOD {T}
TTBRKPT {409}
TTOCCUR {25}
TTFROM1 {10}
TTFROM2 {17}

TTFROM50 {0}
TTTO1 {16}
TTTO2 {32}

TTTO50 {0}
TTAMT1 {1}
TTAMT2 {2}

TTAMT50 {0}


MY Mission is to use the data from the Source table above to create
the data for the [Table] field into the table below.
CREATE TABLE [Tax1] (
[FFID] [tinyint] NOT NULL ,
[TRID] [tinyint] NOT NULL ,
[IMID] [tinyint] NOT NULL ,
[Name] [char] (3) NULL ,
[Rate] [smallint] NULL ,
[CycleStart] [smallint] NULL ,
[CycleEnd] [smallint] NULL ,
[CycleTax] [smallint] NULL ,
[PercentStart] [smallint] NULL ,
[NoTaxBelow] [smallint] NULL ,
[Table] [varchar] (350) NULL , {Insert here}
[Unit] [char] (5) NULL
)

Sample Data for above table (actual values are between the {})

FFID {1}
TRID {1}
IMID {1}
Name {TAX}
Rate {625}
CycleStart {10}
CycleEnd {409}
CycleTax {25}
PercentStart {0}
NoTaxBelow {9}
Table {10 ,17 ,33 ,49 ,65 ,81 ,97 ,113 ,129 ,145 ,161 ,177 ,193 ,209 ,225 ,241 ,257 ,273 ,289 ,305 ,321 ,337 ,353 ,369 ,410}
Unit {GFVDE}


Edited by - wmelancon on 01/20/2005 18:33:41
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 01/26/2005 :  18:17:47  Show Profile  Reply with Quote
wmelancon,

Amazing that your problem was the same kind I had. ASnyone who has used other languages like, say, SAS, knows that you can create dynamic variables on the fly and create variables called by variables. If you've used SAS you'll know what I mean.

If I figure this out, I'll e-mail you the solution. Sorry I've gotten no further.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
328 Posts

Posted - 01/27/2005 :  17:10:35  Show Profile  Reply with Quote
CREATE TABLE #tblDemoBadDesign
(
pkey int
identity(1,1) PRIMARY KEY,
field1 int,
field2 int,
field3 int,
field4 int,
field5 int,
field6 int,
field7 int,
field8 int,
field9 int,
field10 int,
field11 int,
field12 int,
field13 int,
field14 int,
field15 int
);

INSERT #tblDemoBadDesign
(
field1,
field2,
field3,
field4,
field5,
field6,
field7,
field8,
field9,
field10,
field11,
field12,
field13,
field14,
field15
)
SELECT a,
b,
c,
d,
a+b,
a+c,
a+d,
a-b,
a-c,
a-d,
a*b,
a*c,
a*d,
a*b*c,
a*b*c*d
FROM (
SELECT 0 AS a
UNION ALL SELECT -1
UNION ALL SELECT 1
)
t1
CROSS JOIN (
SELECT 1 AS b
UNION ALL SELECT 3
UNION ALL SELECT 5
UNION ALL SELECT 7
UNION ALL SELECT 9
)
t2
CROSS JOIN (
SELECT 0 AS c
UNION ALL SELECT 10
UNION ALL SELECT 20
UNION ALL SELECT 30
UNION ALL SELECT 40
UNION ALL SELECT 50
UNION ALL SELECT 60
UNION ALL SELECT 70
UNION ALL SELECT 80
UNION ALL SELECT 90
)
t3
CROSS JOIN (
SELECT 0 AS d
UNION ALL SELECT 2
UNION ALL SELECT 4
UNION ALL SELECT 6
UNION ALL SELECT 8
)
t4;

DECLARE @x tinyint,
@SQL VARCHAR(1000);

SET @x = 1;
WHILE @x <= 15
BEGIN

SELECT @SQL = 'SELECT '
+ CAST(@x as varchar(3))
+ ' AS FieldNumber,'
+ ' COUNT(*) AS LessThanZeroCount'
+ ' FROM #tblDemoBadDesign'
+ ' WHERE field'
+ CAST(@x as varchar(3))
+ ' <= 0';

EXEC(@SQL);

SET @x=@x+1
END

DROP TABLE #tblDemoBadDesign;
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 01/27/2005 :  17:13:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:

#tblDemoBadDesign



...ain't that the truth.

Tara
Go to Top of Page

wmelancon
Starting Member

9 Posts

Posted - 01/27/2005 :  17:29:32  Show Profile  Reply with Quote

I realize that this is not the ideal situation and I appreciate everyone taking the time to post ideas and solutions.

Thanks!

Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 01/27/2005 :  18:33:58  Show Profile  Reply with Quote
quote:
Originally posted by wmelancon


I realize that this is not the ideal situation and I appreciate everyone taking the time to post ideas and solutions.

Thanks!





As the original poster on this, I realize that there IS NO way to DYNAMICALLY CREATE field names on the fly to access sequentially-named fields (e.g. FLD1, FLD2....). SQL has it's limitations since it is NOT IF-THEN-ELSE compatible. I still think there must be a way (outside of DECODE) of doing this rather than writing out each field.




~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 01/27/2005 :  18:36:06  Show Profile  Reply with Quote
Just want to see the fire burn on this one.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000