| Author |
 Topic  |
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 01/07/2005 : 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
Slovenia
11741 Posts |
Posted - 01/07/2005 : 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
USA
665 Posts |
Posted - 01/07/2005 : 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
Slovenia
11741 Posts |
Posted - 01/07/2005 : 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
USA
665 Posts |
Posted - 01/07/2005 : 16:47:39
|
Thanks anyway....
~~~~~~~~~~~~~
Semper fi,
Xerxes, USMC |
|
|
|
wmelancon
Starting Member
9 Posts |
Posted - 01/20/2005 : 17:08:58
|
Xerxes,
Have you gotten anywhere with this?
I want to accomplish something very similar.
Thanks |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 01/20/2005 : 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 - 01/20/2005 : 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
USA
35017 Posts |
Posted - 01/20/2005 : 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 - 01/20/2005 : 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
USA
35017 Posts |
Posted - 01/20/2005 : 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 - 01/20/2005 : 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
USA
7423 Posts |
Posted - 01/20/2005 : 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 - 01/20/2005 : 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 - 01/20/2005 : 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}
|
Edited by - wmelancon on 01/20/2005 18:33:41 |
|
|
|
Xerxes
Aged Yak Warrior
USA
665 Posts |
Posted - 01/26/2005 : 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
USA
278 Posts |
Posted - 01/27/2005 : 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
USA
35017 Posts |
Posted - 01/27/2005 : 17:13:42
|
quote:
#tblDemoBadDesign
 ...ain't that the truth.
Tara |
|
|
|
wmelancon
Starting Member
9 Posts |
Posted - 01/27/2005 : 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
USA
665 Posts |
Posted - 01/27/2005 : 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
USA
665 Posts |
Posted - 01/27/2005 : 18:36:06
|
Just want to see the fire burn on this one.
~~~~~~~~~~~~~
Semper fi,
Xerxes, USMC |
|
|
|
Topic |
|
|
|
Dynamic field name creation in loop without cursors
