HELP with SELECT command - replace ??

[Xkarlos77]

A have problem

i have a table, which have attribute ENUM ('YES','NO')
and I want this values change in SELECT command.

I want replace YES and no with ANOTHER string, is it possible ??


thank you.

 

[KarveR]

OK heres a quick stab at this:

update table set field = concat('NO',somethingelse) where field = 'YES'; ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[swampBoogie]

select case enumColumn when 'YES' then 'JA'
when 'NO' then 'NEJ' end
from t

 

[Xkarlos77]

thank you, thats it !!

 

[Dfly]

Is it possible to add the TableName to a query using CASE, so it could be used like an 'if...then' statement?
Ashford

SELECT FROM tableName CASE columnName
WHEN 'true' THEN 'text string-A'
WHEN 'false' THEN 'text string-B';

 

[swampBoogie]

case is an expression so it can not be used to replace a tablename.

I don't understand from your example what you are trying to do.

 

[Dfly]

I was thinking CASE would act like a subquery.

Here's an example. The company sells books. Some of the titles have images and long summaries; others, only a brief description.

What I want is a query command for
SELECT * FROM inventory
WHERE authorLast = 'Doe'

Add to the above query:
(1) show the link "more details", if 'column_summary' is not blank.
(2) show the "image", if 'column_img' is not blank.

 

[swampBoogie]

This is still very ambiguous.

Describe all tables involved (as create table statements), give some sample data and the expected result.

 

[Dfly]

Clear as the Mississippi River. Right? What I want to show on the page is:

TITLE, PRICE, DESCRIPTION,
IMAGE (if there is an image entry for this specific title),
More Details LINK (if there is a SUMMARY entry).

What I'm trying to solve is the "if":
If there is an image, show it; else, do nothing.
If there is a summary, create the link; else, do nothing.

One solution would be a Perl Module to process the basic SELECT * query; process the data; then create the html page. I'm looking for an easier way via mySQL.

Ashford

--------------------------------------------------------
TABLES AND EXAMPLES OF DATA

I took out some of the irrelevant columns, NOT NULL, and DEFAULT info to make for an easier read.

CREATE TABLE inventory (
code int(11) unsigned ,
title varchar(100) ,
authorLast varchar(40) ,
description varchar(255) ,
summary varchar(255) ,
link varchar(255) ,
image varchar(255) ,
stock mediumint(8) ,
price decimal(7,2) ,
PRIMARY KEY (code),
) TYPE=ISAM PACK_KEYS=1;

--------------------------------

Examples of column data:

`description` = HBDJ, 185 pp, pub. 2002

`summary` = <!--#include virtual=&quot;/cgi-bin/catalog/include/book_1.inc&quot; -->

'link' = a href=path/to/template.html

'image' = !MY

http://WWW!/grfx/img_1.jpg

---------------------------------------------

CREATE TABLE templates (
name varchar(50) ,
file_html varchar(20) ,
query text ,
title varchar(40) ,
sortBy varchar(255) ,
PRIMARY KEY (name)
) TYPE=ISAM PACK_KEYS=1;

---------------------------------------------

Examples of column data:

INSERT INTO templates VALUES (
'sale_auth_all',
'shop.html',
'SELECT * FROM inventory WHERE sale_status = '1',
'Great Deals: authors',
'<!--#include virtual=&quot;/cgi-bin/catalog/include/sort_sale.inc&quot; -->'
);

---------------------------------------------

 

[KarveR]

hmm

select if(imagefield = &quot;&quot;,&quot;no image&quot;,&quot;<url=whatever&quot; ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[Dfly]

Thanks for responding, KarveR. Didn't work.

The site I'm working on uses the PerlShop Shopping Cart DBI module. I found the example below in the documentation. Nested query uses the &quot;execute&quot; command. Not sure the syntax would work for all programs, but it might.
Ashford

# Create basic select query
<psdbi command=query qid=product>
SELECT * FROM inventory WHERE type='Basic'
</psdbi>

# Call for results to above query
<psdbi command=result qid=product>

# Process a Perl command on the results
<psdbi1 command=execute>
{
  my $output;
  if (!product.stock! == 0)
  {
     $output = 'SOLD OUT';
  }
  else
  {
     $output = 'Price: !product.price!';
</psdbi1>

</psdbi>
# Be sure to close all open tags