Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!usc!cs.utexas.edu!asuvax!ukma!seismo!beno!cooper
From: cooper@beno.CSS.GOV (Dale Cooper)
Newsgroups: comp.databases
Subject: Re: Reports from SQL databases
Keywords: SQL reports
Message-ID: <49526@seismo.CSS.GOV>
Date: 2 Apr 91 15:36:02 GMT
References: <5197@mrmarx.UUCP> <vg3u435@Unify.Com>
Sender: usenet@seismo.CSS.GOV
Organization: Center for Seismic Studies, Arlington, VA
Lines: 93

In article <vg3u435@Unify.Com> nico@Unify.Com (Nico Nierenberg) writes:
>In article <5197@mrmarx.UUCP> cant@mrmarx.msc.com (Jim Cant) writes:
>>I have several questions and would appreciate any help I can get from
>>people on the net.
>>

[stuff deleted]

>>Here is a general question.  I need to print reports with fields such
>>as sex and would like the printed output to say "male" or "female".  The
>>database contains 1 and 0 for these values.  How can I test each
>>record returned by a select statement for the value of sex and write
>>out "male" or "female" as appropriate.  One solution that occurs is to
>>create a second temporary table, put in the records of generated by the 
>>querry with a additional text field, then update the text field with
>>"male" or "female" depending on the value of the sex field.  The use 
>>this temporary table as the source of the data for the report.

>A very simple solution is to create a "sex" table that contains two
>rows.  
>
>cd	|	text
>---------------------
>
>0	|	female
>1	|	male
>
>
>Then simply join this table to whatever other table(s) are being
>selected based on the "cd" field.  For example
>
>select name, sex.text from person,sex
>where person.sexcode = sex.cd;
>
>
>The temporary table idea would work but it is a lot of extra effort,
>and resource use.

>>Thanks in advance
>>Jim Cant
>>Waltham, MA  02154		(617) 894-3399
>
>
>---------------------------------------------------------------------
>Nico Nierenberg
>Unify Corp.				 there you are."
>nico@unify

Temporary tables are often VERY useful (absolutely no flame intended to you
Nico), but more times than not there is more than one way to skin a cat 
(I love that phrase)  ;).  Methods of success are entirely dependent on type 
database, resource restrictions and RDBMS limitations, so my answers may not 
be very useful to your environment.  Check with your own documentation before
you try these.

If you're using Oracle, you could use the DECODE function.  The syntax for
that statement would be something like:

	select name,decode(sexcode,1,'male',0,'female','unknown') sex
		from person;

This would return:

NAME       SEX
---------- -------
jones      male
smith      female
johnson    female

where the database table would contain:

NAME       SEXCODE
---------- -------
jones      1
smith      0
johnson    0


Another sneaky way to do this is using a UNION statement.  This is sort of
an "implied if" statement, if you will.  Looks silly but it works.  I WOULDN'T
do something like this if there was a large number of possibilities or the
database table was large...you would get crushed on performance.  Anyhow, it 
would look something like this:

	select name,'male' from person where sexcode = 1
	union
	select name,'female' from person where sexcode = 0;

Hope this helps.

Dale Cooper, DBA				D - dumped on
Center for Seismic Studies			B - by
Arlington, VA					A - all