Column Query with a Variable

I have 30 fields in a table called ADP_Pay_Detail that I need to query with
the exact same query. Each field is named DED_CD_1, DED_CD_2, etc. up to
DED_CD_30. I have coded a counter with a WHILE statement to increment the
last number of this string, and assign the entire string to a varchar
variable named @.column_amt. All of the type conversions I need to do are
correct. I then try to write a query using this variable as a field name.
An example would be
SELECT *
FROM ADP_Pay_Detail
where @.column_amt = 30
This isn't working at all. Can anyone give me a suggestion about how to
accomplish what I want to do? The query I have to write is much more
complicated than my example, and I'd hate to have to write it 30 times to
query each column!
Thanks!
Keith
Keith>I have 30 fields in a table called ADP_Pay_Detail that I need to query with
> the exact same query. Each field is named DED_CD_1, DED_CD_2, etc. up to
> DED_CD_30.
Just curious if you have considered a more sensible and relational design?
Are you going to add DED_CD_31 -> DED_CD_n at some point?

> SELECT *
> FROM ADP_Pay_Detail
> where @.column_amt = 30
> This isn't working at all.
That is right, because T-SQL is not a language that allows you to construct
queries like this, you must tell it about the metadata and not let it decide
on its own. My first suggestion would be to pass all 30 columns back, and
let the app display only the column(s) it needs. This will be more flexible
as you can later decide to show 2 or 3 or all columns and you won't have
much change to do...
My more intuitive response would be to store the data and its value as data,
instead of mixing data and metadata. An example might be:
CREATE TABLE dbo.ADP_Pay_Detail
(
pdID INT PRIMARY KEY
-- , other columns
)
CREATE TABLE dbo.ADP_Pay_Detail_DED_CD
(
pdID INT FOREIGN KEY REFERENCES dbo.ADP_Pay_Detail(pdID),
column_amt TINYINT, -- CHECK CONSTRAINT perhaps?
value INT
)
Now you can say
SELECT * FROM ADP_Pay_Detail d
INNER JOIN ADP_Pay_Detail_DED_CD c
ON d.pdID = c.pdID
WHERE c.column_amt = 30
And you don't have any silly column names with data embedded in them. (Next
you can work on manageable table names.)

> Can anyone give me a suggestion about how to
> accomplish what I want to do?
As a last resort, dynamic SQL. PLEASE READ THE FOLLOWING and heed the
warnings seriously:
http://www.sommarskog.se/dynamic_sql.html|||>> I have 30 fields [sic] in a table called ADP_Pay_Detail that I need to query w
ith
the exact same query. Each field [sic] is named DED_CD_1, DED_CD_2,
etc. up to DED_CD_30. <<
Rows are not records; fields are not columns; tables are not files.
You need to get a book on RDBMS basics and read the chapter on Normal
Forms. What you have here is a 1950's COBOL file layout with a fake
OCCURS clause.
Apparently SQL is the first compiled language you have ever used. It
is also a declarative language, so writing loops in a proprietary 3GL
is a sign of poor coding.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Colour My World

In SQL Server 2000 Query Analyzer, is there a way to print T-SQL code with
the QA color coding? When I print, even to a color laser printer, everything
including the keywords and comments come out in black and white.
Thanks!>> In SQL Server 2000 Query Analyzer, is there a way to print T-SQL code
Not sure if direct printing is possible or not. This might be silly but if
the code is small enough to fit the screen, you could use a screen print to
a word document and then print it.
Anith|||>> When I print, even to a color laser printer, everything
including the keywords and comments come out in black and white. <<
Just for fun, you might want to research the effect of "colored code"
on maintaining it. There is a standard test for brain damage where you
show the subject a seires of flashcards with the names of colors in
colored ink (I.e. "RED" pritned in green ink) and ask them to call out
the either word or the color. This has nothing to do with being
color-blind.
Since it involves switching brain hemispheres and thus the physical
structure of the brain as a organ, the rate is fairly constant over a
person's lifetime. Unless they get some physical damage to the brain.
Strongly analytical ("left brain, right hand") people have an awful
time with "neon vomit programming tools" because they have to filter
out the colors to abstract the code from the text.
So, I have to ask, why would you ever want to do this? Get a copy of
SQL PROGRAMMING STYLE for some more info on how humans read code. I
did a bit of the work on this back in the 1980's for AIRMICS while I
was a Georgia Tech.|||Well, cut-n-paste it into MS Word - that should do the trick ;)|||Screen capture could work, but the code is many, many pages long.
Is this possible in SQL Server? If not , are there any 3rd party programs
that might work?
Thanks again...
"Anith Sen" wrote:

> Not sure if direct printing is possible or not. This might be silly but if
> the code is small enough to fit the screen, you could use a screen print t
o
> a word document and then print it.
> --
> Anith
>
>|||Nope, tried it. Still black-and-white in MS Word.
"Alexander Kuznetsov" wrote:

> Well, cut-n-paste it into MS Word - that should do the trick ;)
>|||Why would I want color-coded printouts? for the same reason they're
color-coded on the screen. Easier to separate what's code, what's keywords,
and what's comments.
As far as the brain damage research, although it sounds rather intriguing,
considering how many more years of my life I'm going to spend looking at M&M
colored typing, I may be better off not knowing...
"--CELKO--" wrote:

> including the keywords and comments come out in black and white. <<
> Just for fun, you might want to research the effect of "colored code"
> on maintaining it. There is a standard test for brain damage where you
> show the subject a seires of flashcards with the names of colors in
> colored ink (I.e. "RED" pritned in green ink) and ask them to call out
> the either word or the color. This has nothing to do with being
> color-blind.
> Since it involves switching brain hemispheres and thus the physical
> structure of the brain as a organ, the rate is fairly constant over a
> person's lifetime. Unless they get some physical damage to the brain.
>
> Strongly analytical ("left brain, right hand") people have an awful
> time with "neon vomit programming tools" because they have to filter
> out the colors to abstract the code from the text.
> So, I have to ask, why would you ever want to do this? Get a copy of
> SQL PROGRAMMING STYLE for some more info on how humans read code. I
> did a bit of the work on this back in the 1980's for AIRMICS while I
> was a Georgia Tech.
>|||On Fri, 10 Feb 2006 08:51:29 -0800, "Joel"
<Joel@.discussions.microsoft.com> wrote:

>In SQL Server 2000 Query Analyzer, is there a way to print T-SQL code with
>the QA color coding? When I print, even to a color laser printer, everythin
g
>including the keywords and comments come out in black and white.
>Thanks!
I use Macromedia's Homesite. You can set the colors for each entity.
What you see on the screen is what prints. I also use Ultra Edit and
Slick Edit. Both of these allow you to print a selection where
Homesite will only print the entire file. I like the printout from
Homesite best.
--
BettyB -- www.flamingo-code.com
"I have noticed even people who claim everything is
predestined, and that we can do nothing to change it,
look before they cross the road." - Stephen Hawking|||I don't know if it is an option or not, but the 2005 replacement of QA (Mana
gement Studio) does keep
formatting (including colour) when you copy text. Personally, I hate that, b
ut it might be an upside
in these situations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:F6077A67-C933-47CA-A4C5-654D2E7881A8@.microsoft.com...
> Nope, tried it. Still black-and-white in MS Word.
> "Alexander Kuznetsov" wrote:
>|||> Personally, I hate that,
me too. Sometimes I need to paste into notepad - that removes colors
and fonts