Dynamic field name creation in loop without cursors

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic field name creation in loop without cursors

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-07 : 08:58:43

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

11752 Posts

Posted - 2005-01-07 : 09:46:37

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

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-07 : 11:19:14

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-07 : 12:33:28

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

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-07 : 16:47:39

Thanks anyway....

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

Xerxes, USMC

wmelancon
Starting Member

9 Posts

Posted - 2005-01-20 : 17:08:58

Xerxes,

Have you gotten anywhere with this?

I want to accomplish something very similar.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-20 : 17:11:05

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

wmelancon
Starting Member

9 Posts

Posted - 2005-01-20 : 17:29:55

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-20 : 17:33:36

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

wmelancon
Starting Member

9 Posts

Posted - 2005-01-20 : 17:42:52

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....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-20 : 17:44:59

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

wmelancon
Starting Member

9 Posts

Posted - 2005-01-20 : 17:50:18

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.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-20 : 17:59:05

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

- Jeff

wmelancon
Starting Member

9 Posts

Posted - 2005-01-20 : 18:01:27

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

wmelancon
Starting Member

9 Posts

Posted - 2005-01-20 : 18:22:46

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}

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-26 : 18:17:47

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

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2005-01-27 : 17:10:35

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;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-27 : 17:13:42

quote:

#tblDemoBadDesign

...ain't that the truth.

Tara

wmelancon
Starting Member

9 Posts

Posted - 2005-01-27 : 17:29:32

I realize that this is not the ideal situation

and I appreciate everyone taking the time to post ideas and solutions.

Thanks!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 18:33:58

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

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 18:36:06

Just want to see the fire burn on this one.

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

Xerxes, USMC

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources