cant@mrmarx.msc.com (Jim Cant) (03/28/91)
I have several questions and would appreciate any help I can get from people on the net. What is a good general reference for SQL that would cover generating polished reports from databases in particular. I suspect a text book rather than user documentation for a specific database is what I need. I need to solve problems such as the following. 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. Is this the right approach? Is it a good one? Will it work at all? (I am using Sybase on the IBM RS6000 running AIX v3.1) Are there any good third party products for report generation from SQL databases. Are there any other database oriented newsgroups? Any for SQL or for particular products? Thanks in advance Jim Cant p.s. This is my first posting from a new Email address; would somebody please respond just to test the mail system. thanks Jim Cant cant@mrmarx.msc.com Mainstream Software Corp. or 411 Waverly Oaks Road uunet!mrmarx.msc.com!cant Waltham, MA 02154 (617) 894-3399
nico@Unify.Com (Nico Nierenberg) (03/30/91)
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. > >What is a good general reference for SQL that would cover generating >polished reports from databases in particular. I suspect a text book >rather than user documentation for a specific database is what I >need. I need to solve problems such as the following. > >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. > >Is this the right approach? Is it a good one? Will it work at all? >(I am using Sybase on the IBM RS6000 running AIX v3.1) > >Are there any good third party products for report generation from >SQL databases. An outstanding product for this is our Accell/SQL package which includes a procedural report writer. Simply factual information folks. It runs very nicely with Sybase which is an outstanding DBMS. > >Are there any other database oriented newsgroups? Any for SQL or for >particular products? > > >Thanks in advance > > >Jim Cant > >p.s. This is my first posting from a new Email address; would somebody >please respond just to test the mail system. thanks > > >Jim Cant cant@mrmarx.msc.com >Mainstream Software Corp. or >411 Waverly Oaks Road uunet!mrmarx.msc.com!cant >Waltham, MA 02154 (617) 894-3399 -- --------------------------------------------------------------------- Nicolas Nierenberg "No matter where you go, Unify Corp. there you are." nico@unify
cooper@beno.CSS.GOV (Dale Cooper) (04/02/91)
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
nico@Unify.Com (Nico Nierenberg) (04/03/91)
In article <49526@seismo.CSS.GOV> cooper@beno.CSS.GOV (Dale Cooper) writes: >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 >>--------------------------------------------------------------------- >>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 agree that temporary tables are useful, but not in this case. The only thing I don't like about the Oracle example is that it is specific to Oracle. I might have said that using front end tools there are any number of ways to do it. Another interesting join example is the non equi-join. In this case you can solve problems like computing tax liability from an income value.. Imagine a tax table like; minincome |maxincome |basetax |rate ---------------------------------------------------------- 0 |12000 |0 |.15 12001 |30000 |1800 |.20 30001 |9999999999999 |5400 |.33 Then if you have an employee table with names and salary, you can compute (a very nominal tax) by; select name,salary, basetax + (salary - basetax) * rate from employee,tax where salary between minincome and maxincome; > >Dale Cooper, DBA D - dumped on >Center for Seismic Studies B - by >Arlington, VA A - all -- --------------------------------------------------------------------- Nicolas Nierenberg "No matter where you go, Unify Corp. there you are." nico@unify