 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Mark Grover
Guest
|
 Posted: Thu Apr 15, 2004 8:08 pm Post subject: Need help with Query |
 |
|
I need help with an MS-SQL query. I have a database table that has an
integer field called disposition. I only have 7 dispositions that it could
actually be, and they are numbered 1 through 7. (That is the actual field
value.)
I would like my query to return 7 fields named after the disposition. So if
the field value of disposition is "1" then I would like a field named
"Postponed" to be equal to "1", if disposition field is "2" then a Field
named "Withdrawn" would be equal to "1", if disposition field is "3" then a
Field named "Cancelled" is equal to "1", etc, etc.
How can I do this within an SQL query?
I know how to do it with the Delphi query component, by adding fields that
are calculated, and doing the aforementioned in the oncalc method, but I was
wondering if it could be done within the query itself.
|
|
| Back to top |
|
 |
Ray Marron
Guest
|
| Posted: Thu Apr 15, 2004 10:48 pm Post subject: Re: Need help with Query |
|
|
"Mark Grover" <mgrover @ lgamerica.com> wrote
| Quote: | I need help with an MS-SQL query. I have a database
table that has an integer field called disposition. I only have
7 dispositions that it could actually be, and they are
numbered 1 through 7. (That is the actual field value.)
I would like my query to return 7 fields named after the
disposition. [SNIP]
[...]
I know how to do it with the Delphi query component, by
adding fields that are calculated, and doing the
aforementioned in the oncalc method, but I was wondering
if it could be done within the query itself.
|
It's easy if you have another table that holds those values:
CREATE TABLE dispositions (
dispo_id INT NOT NULL PRIMARY KEY,
dispo_name VARCHAR(50) NOT NULL UNIQUE
);
INSERT INTO dispositions VALUES (1, 'Postponed');
etc. etc...
In your query, you just join the tables:
SELECT m.foo, m.bar, d.dispo_name
FROM mytable m, dispositions d
WHERE d.dispo_id = m.disposition;
In doing so, you might also want a foreign key constraint on the
mytable.disposition field to that it always has a value of one of the keys
in the dispositions file.
You could do it with a big CASE statement, but why bother when you have an
SQL server at your disposal?  If your descriptions ever change or you add
any new dispositions, all you have to do is update one record, not scores of
CASE statements or calculated fields in Delphi.
--
Ray Marron
|
|
| Back to top |
|
|
Jeffrey Miller
Guest
|
| Posted: Sat Apr 17, 2004 12:52 am Post subject: Re: Need help with Query |
|
|
"Mark Grover" <mgrover @ lgamerica.com> wrote
| Quote: | I would like my query to return 7 fields named after the disposition. So
if |
Select
case when disposition = 1 then 1 else 0 end as Postponed,
case when disposition = 2 then 1 else 0 end as Withdrawn,
case when disposition = 3 then 1 else 0 end as Cancelled,
etc
|
|
| Back to top |
|
|
Ray Marron
Guest
|
| Posted: Mon Apr 19, 2004 2:39 pm Post subject: Re: Need help with Query |
|
|
"Ray Marron" <me (AT) privacy (DOT) net> wrote
[SNIP correct answer to a different question]
Oops - I misread your question. Sorry!
--
Ray Marron
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
