SEO

Hi

I have a tablefield which gets its values from a select query, eg

tblField = select val1, val2, val3
FROM tbl1

My problem is that in the table tbl1, val2 has 3 different values in
it which are abbreviations. On my tablefield however, i need to
display the whole thing. EG in tbl1, val2 can be SOS, PTO or ET. If
the select query returns PTO for val2 then i would need to display
Please Turn Over.

Hope this makes sense and any help would be appreciated!

The ideal way would be as part of the SQL to perform a join to a
reference table. This would then allow you to retrieve the description
for each val2 occurance. So create a table tbl2 with two columns (val2
and val2_description), populate it with abbreviations and descriptions
and then change the SQL to be something like this: -

tblField = SELECT
t.val1, t.val2, r.val2_description, t.val3
FROM
tbl1 t,
tbl2 r
WHERE
tbl2.val2 = tbl1.val2

If you cannot be certain you will have all the values of val2 in the
tbl2 reference table then you would use a tbl1 LEFT JOIN tbl2 and
ensure you put a IFNULL(r.val2_description,'No description') on the
SELECT statement.. shout if you don't understand this concept.

Depending on your Ingres version you can also use a CASE statement in
the SQL - but I wouldn't think that was a good idea (before anybody
slates me for suggesting...)

If you don't want to go down the SQL route, (guessing ABF because of
the tblField := statement)... you could just loop through the records
in the table field (it is an array remember) and update the necessary
description fields... (see unloadtable as an example).

Hope this helps

Gary

Hi Metman,

It looks like you need a simple case statement...

select val1,
case val2 when 'SOS' then 'Is good on pies'
when 'PTO' then 'Please Turn Over'
when 'ET' then 'phone home'
else 'I dont know'
end as val2,
val3
from tbl

Martin Bowes
-----Original Message-----
From: info-ingres-bounces@kettleriverconsulting.com
[mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of
metman
Sent: 21 April 2008 17:20
To: info-ingres@kettleriverconsulting.com
Subject: [Info-Ingres] changing result of select query in tablefield

Hi

I have a tablefield which gets its values from a select query, eg

tblField = select val1, val2, val3
FROM tbl1

My problem is that in the table tbl1, val2 has 3 different values in
it which are abbreviations. On my tablefield however, i need to
display the whole thing. EG in tbl1, val2 can be SOS, PTO or ET. If
the select query returns PTO for val2 then i would need to display
Please Turn Over.

Hope this makes sense and any help would be appreciated!
_______________________________________________
Info-Ingres mailing list
Info-Ingres@kettleriverconsulting.com
http://www.kettleriverconsulting.com...fo/info-ingres

"Martin Bowes" <martin.bowes@ctsu.ox.ac.uk> wrote in message
news:mailman.174.1208856038.2607.info-ingres@kettleriverconsulting.com...
> Hi Metman,
>
> It looks like you need a simple case statement...
>
> select val1,
> case val2 when 'SOS' then 'Is good on pies'
> when 'PTO' then 'Please Turn Over'
> when 'ET' then 'phone home'
> else 'I dont know'
> end as val2,
> val3
> from tbl
>
> Martin Bowes

While it does solve the immediate problem, I would consider it bad practice
because it means that whenever the data is updated with a new abbreviation,
the SQL code will also have to be updated.

As mentioned previously, the better solution is a join to a table where the
expanded abbreviation is available.

Paul

> -----Original Message-----
> From: info-ingres-bounces@kettleriverconsulting.com
> [mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of
> metman
> Sent: 21 April 2008 17:20
> To: info-ingres@kettleriverconsulting.com
> Subject: [Info-Ingres] changing result of select query in tablefield
>
> Hi
>
> I have a tablefield which gets its values from a select query, eg
>
> tblField = select val1, val2, val3
> FROM tbl1
>
> My problem is that in the table tbl1, val2 has 3 different values in
> it which are abbreviations. On my tablefield however, i need to
> display the whole thing. EG in tbl1, val2 can be SOS, PTO or ET. If
> the select query returns PTO for val2 then i would need to display
> Please Turn Over.
>
> Hope this makes sense and any help would be appreciated!
> _______________________________________________
> Info-Ingres mailing list
> Info-Ingres@kettleriverconsulting.com
> http://www.kettleriverconsulting.com...fo/info-ingres
>

> Depending on your Ingres version you can also use a CASE statement in
> the SQL

Something to watch out for if you're using ABF/4GL, I don't think you
can use the case statement. Certainly with Ingres 2.6 it's not
recognised, and I think - please correct me if wrong - this is still
the case (pardon the pun) with the latest release.
GJ

On Apr 22, 2008, at 10:22 AM, G Jones wrote:

>> Depending on your Ingres version you can also use a CASE statement in
>> the SQL
>
> Something to watch out for if you're using ABF/4GL, I don't think you
> can use the case statement. Certainly with Ingres 2.6 it's not
> recognised, and I think - please correct me if wrong - this is still
> the case (pardon the pun) with the latest release.

I do believe that's still true. However, if you want to badly enough,
you can stuff the query into a string and execute immediate the
string. That won't work for selects, so it doesn't help the OP, but
it's a workaround for non-select queries.

Ideally ABF would learn about CASE. Sounds like a community project
to me...

Karl