How do I Put a 'Literal' Value in a Report Column?

How do I Put a 'Literal' Value in a Report Column?

Post by Sue Cra » Wed, 09 Feb 2005 00:55:31



------=_NextPart_000_0003_01C50CA7.AFB4F170
Content-Type: text/plain;
        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

This is a pretty basic question, but I'm learning SQL from a book and =
it's very very frustrating!  =20

I'm writing a report listing animals from our shelter and whether they =
have been adopted.  I am selecting the animal name field and a field =
called 'adopted' from a table called 'animal'.  The values in 'adopted' =
are either:  1 (meaning yes, this animal was adopted) or 0 (no, this =
animal has not been adopted).=20
=20
So far I:
"SELECT Name, Adopted FROM animal".  This prints a column of animal =
names and a column labeled "Adopted" with row after row of 0's and 1's.  =
I want my output report to say 'Y' if 'adopted' =3D 1 or 'N' if adopted =
=3D 0. =20

I've spent all day studying "Insert Into", "Update Where", "If... Then", =
looking at SQL manuals (so far I've studied 3 of them), and trying to =
find an example in existing code.  I get syntax errors for everything I =
try.  This is a very simple thing that's done all the time -- but I =
can't figure out how in SQL. =20

Can someone help me?
Thanks,
Sue
------=_NextPart_000_0003_01C50CA7.AFB4F170--

 
 
 

How do I Put a 'Literal' Value in a Report Column?

Post by Duncan Hi » Wed, 09 Feb 2005 00:57:46


On Monday 07 February 2005 07:57, Sue Cram might have typed:

Quote:> I'm writing a report listing animals from our shelter and whether they have
> been adopted.  I am selecting the animal name field and a field called
> 'adopted' from a table called 'animal'.  The values in 'adopted' are
> either:  1 (meaning yes, this animal was adopted) or 0 (no, this animal has
> not been adopted).

> So far I:
> "SELECT Name, Adopted FROM animal".  This prints a column of animal names
> and a column labeled "Adopted" with row after row of 0's and 1's.  I want
> my output report to say 'Y' if 'adopted' = 1 or 'N' if adopted = 0.

Two options:
1) Do it in the program that actually makes the query.
2) Use MySQL's CASE statement to convert values on the fly to Y and N.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql


 
 
 

How do I Put a 'Literal' Value in a Report Column?

Post by SGr.. » Wed, 09 Feb 2005 01:09:32


--=_alternative 0058835085256FA1_=
Content-Type: text/plain; charset="US-ASCII"


Quote:> This is a pretty basic question, but I'm learning SQL from a book
> and it's very very frustrating!

> I'm writing a report listing animals from our shelter and whether
> they have been adopted.  I am selecting the animal name field and a
> field called 'adopted' from a table called 'animal'.  The values in
> 'adopted' are either:  1 (meaning yes, this animal was adopted) or 0
> (no, this animal has not been adopted).

> So far I:
> "SELECT Name, Adopted FROM animal".  This prints a column of animal
> names and a column labeled "Adopted" with row after row of 0's and
> 1's.  I want my output report to say 'Y' if 'adopted' = 1 or 'N' if
> adopted = 0.

> I've spent all day studying "Insert Into", "Update Where", "If...
> Then", looking at SQL manuals (so far I've studied 3 of them), and
> trying to find an example in existing code.  I get syntax errors for
> everything I try.  This is a very simple thing that's done all the
> time -- but I can't figure out how in SQL.

> Can someone help me?
> Thanks,
> Sue

Don't feel bad. I've got a little bit of experience and I still need to
hit the manual for some things.  There are actually two functions you can
use in your SELECT statement to make a choice between two or more values:
IF() and CASE...WHEN ...THEN...END  .

In your situation, the IF() function will work and should be the easiest
to understand. Try this:

SELECT Name, IF(Adopted=1,'Y','N') as Adopted FROM animal;

The IF() works like this: If the condition is true, use the first option,
otherwise use the second. In your case, you want your SELECT statement to
say 'Y' whenever Adopted is 1 and 'N' when it isn't. Make sense?

Some additional reading:
http://dev.mysql.com/doc/mysql/en/functions.html (chapter on MySQL
functions)
http://dev.mysql.com/doc/mysql/en/control-flow-functions.html (case and if
functions)

If you run into more questions later and can't find your answers in your
books (not even in the online manual) come on back and we will help you
through it.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 0058835085256FA1_=--

 
 
 

How do I Put a 'Literal' Value in a Report Column?

Post by Artem Koltso » Wed, 09 Feb 2005 01:12:53


see 12.2 Control Flow functions of MySQL manual

select name, if(adopted=3D0,'N','Y') from animal;

> -----Original Message-----

> Sent: Monday, February 07, 2005 2:58 AM

> Subject: How do I Put a 'Literal' Value in a Report Column?
>=20
>=20
> This is a pretty basic question, but I'm learning SQL from a=20
> book and it's very very frustrating!  =20
>=20
> I'm writing a report listing animals from our shelter and=20
> whether they have been adopted.  I am selecting the animal=20
> name field and a field called 'adopted' from a table called=20
> 'animal'.  The values in 'adopted' are either:  1 (meaning=20
> yes, this animal was adopted) or 0 (no, this animal has not=20
> been adopted).=20
> =20
> So far I:
> "SELECT Name, Adopted FROM animal".  This prints a column of=20
> animal names and a column labeled "Adopted" with row after=20
> row of 0's and 1's.  I want my output report to say 'Y' if=20
> 'adopted' =3D 1 or 'N' if adopted =3D 0. =20
>=20
> I've spent all day studying "Insert Into", "Update Where",=20
> "If... Then", looking at SQL manuals (so far I've studied 3=20
> of them), and trying to find an example in existing code.  I=20
> get syntax errors for everything I try.  This is a very=20
> simple thing that's done all the time -- but I can't figure=20
> out how in SQL. =20
>=20
> Can someone help me?
> Thanks,
> Sue
>=20

=A0
Attention:
Any views expressed in this message are those of the individual sender, e=
xcept where the message states otherwise and the sender is authorized to =
state them to be the views of any such entity. The information contained =
in this message and or attachments is intended only for the person or ent=
ity to which it is addressed and may contain confidential and/or privileg=
ed material.  If you received this in error, please contact the sender an=
d delete the material from any system and destroy any copies.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

 
 
 

1. mysql 'enum' column values


mixo,
         I can't see an easy way of getting the enum for a particular
column. The best I could come up with (and hopefully someone can improve on
this) is to execute a "MySQLDump -d mydatabase>schema.txt" and then use PHP
(or whatever language you're using) to find the table.column name, search
for "enum(", parse out the values and put them into a text file or make an
PHP include file out of it.  Since this only needs to be done when the
enum's have changed, and this process formats it properly for your
language, loading the enum list will be quite fast at run time.

I thought of using "Describe table" but that will wrap a long enum list
onto several lines in columnar format and is much harder to parse than a
MySQLDump.

Mike

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)



Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

2. JPEG image compression FAQ, part 1/2

3. CASTing 'unknown' or working with string literals...

4. Turbo C diff's for Minix 1.2; here they are ! Part 2 of 9

5. ERROR 1054 at line 52: Unknown column 'nan' in 'field list'

6. A.T.C.

7. via apache: "Server error:Unknown column '...' in 'field list'"

8. Please Help!

9. copy old column's values to new column

10. 'gmake install' with DESTDIR puts files in bad place

11. Load Infile import puts ' " ' in front of each field?

12. 'CHARACTER SET' specification in a column's data type ?

13. Problems while doing mysqldump '<'