New Informix user - coming from Oracle here. I'm baffled.

Why doesn't something simple like:

select * from (select * from bob)

work?

Also, if you do something like :

select upper(bob) jane from sometable
where jane = 'SMITH'
order by jane

it doesn't work either. It lets jane appear in the order by, but not
the where clause.

So how are you supposed to limit your results based off of aliased
(virtual) columns? This comes up for pretty much any time you use a
function against a column to get some result ("decode" is what I am
working with at the moment).

So far I find this flavour of SQL very limiting. Can someone help?

Thanks!
-sw

> Why doesn't something simple like:
> select * from (select * from bob)
> work?

SELECT * FROM TABLE(MULTISET(SELECT * FROM bob))

> Also, if you do something like :
> select upper(bob) jane from sometable
> where jane = 'SMITH'
> order by jane
> it doesn't work either. It lets jane appear in the order by, but not
> the where clause.
> So how are you supposed to limit your results based off of aliased
> (virtual) columns? This comes up for pretty much any time you use a
> function against a column to get some result ("decode" is what I am
> working with at the moment).

The inline table issue was already clarified.
Probably one of the next versions will allow the "usual" syntax.

The other questions:

.... WHERE UPPER(jane) = "SMITH"
ORDER BY 1;

You have to repeat the expression... it's probably annoying if you're
not used to it, but the background job is the same.
You can use numeric placeholders in ORDER BY and GROUP BY clauses.

Regards


internetuser wrote:
> New Informix user - coming from Oracle here. I'm baffled.
>
> Why doesn't something simple like:
>
> select * from (select * from bob)
>
> work?
>
> Also, if you do something like :
>
> select upper(bob) jane from sometable
> where jane = 'SMITH'
> order by jane
>
> it doesn't work either. It lets jane appear in the order by, but not
> the where clause.
>
> So how are you supposed to limit your results based off of aliased
> (virtual) columns? This comes up for pretty much any time you use a
> function against a column to get some result ("decode" is what I am
> working with at the moment).
>
> So far I find this flavour of SQL very limiting. Can someone help?
>
> Thanks!
> -sw

Ugh. How awkward. OK, thanks for the tips. I'm running into some issues
though (see below), which make things seem pretty bizarre to me.

>SELECT * FROM TABLE(MULTISET(SELECT * FROM bob))

It seems to be pretty useless though because using something like this:

select lwind_name,lw_type_cd from table(multiset(select * from
land_window))

I get a message:

[Error Code: -9930, SQL State: IX000]
Byte, Text, Serial or Serial8 datatypes in collection types not
allowed.

If you can't do something this basic because of a serial column, it's
rather lame and useless. It seems like Informix is going out of its way
to make me write multiple statements or use temp tables to avoid doing
things all at once like I've been doing with Oracle.

> The other questions:
> WHERE UPPER(jane) = "SMITH"
> ORDER BY 1;
>
> You have to repeat the expression... it's probably annoying if you're not used to it,
> but the background job is the same. You can use numeric placeholders in ORDER
> BY and GROUP BY clauses.

OK, so I have to do something like this:

select lwind_name, lower(lwind_name) winName
from land_window where lower(lwind_name) = 'coastal plain'

Well, it's more than just annoying. It makes the statements longer,
harder to read, and gives me twice as much to maintain, for no good
reason as far as I can tell. Is there really no better way?

There also seems to be some rather severe limitations here. Consider
the following code that works great with Oracle:

select
et_code,et_desc,case_type as ct_code,valid_case_type_desc_txt as
ct_desc
from notrust.valid_case_type vct,
(select e_type_code as ET_CODE,e_type_desc_txt as ET_DESC,
decode (e_type_code,'12A','265101','12B','265102','12C',' 265200',

'IL-SS','265202','POOL','265204','12CH8','265208','IL-S','265211','14H1',

'265301','14H2','265302','14H3','265303','14H5','2 65306','14H8','265308',

'14H6','265311','UGRT','262720','CGNF','262712','C GPD','262711','MH','262710',
'SCHL','262714','GPGT','262730','TNCGT','262713') as case_type
from notrust.e_type) acs
where acs.case_type = vct.valid_case_type_code and case_type is not
null

Trying to use this with Informix, we see right away that the long
decode statement has to be repeated in the where clause rather than
using "case_type", however, if you do that, you get the message:

[Error Code: -293, SQL State: IX000] I
IS [NOT] NULL predicate may be used only with simple columns.

So not only is repeating it annoying, it doesn't even work. On top of
that, you've got to move to this verbose method of using in-line tables
and hope that you're not using a serial value.

I guess I've got some work ahead of me to try to figure this stuff out.

Maybe I can get the database re-designed to avoid the use of serials.
Unfortunately, since the people who implement the database don't have
to write the code to use it, there is probably little chance of that
happening. These are the same people who decided to switch the
database from Oracle 10g to Informix 9 (v.9.4.0.UC3, zero chance of
getting a more recent version, ever) in the middle of my project, after
a lot of database code had already been written. I'm now in the process
of taking what was perfectly working code and make it work with
Informix. There seems to be a number of severe limitations and lack of
features that require one to have a very different mindset when writing
code for Informix. Can anyone shed some light?

Thanks!
-sw

> Ugh. How awkward. OK, thanks for the tips. I'm running into some issues
> though (see below), which make things seem pretty bizarre to me.
> >SELECT * FROM TABLE(MULTISET(SELECT * FROM bob))
> It seems to be pretty useless though because using something like this:
> select lwind_name,lw_type_cd from table(multiset(select * from
> land_window))
> I get a message:
> [Error Code: -9930, SQL State: IX000]
> Byte, Text, Serial or Serial8 datatypes in collection types not
> allowed.

I don't know, this always made perfect sense to me. A "serial" data
type's serial-ness can't be preserved through a virtual table. But my
preference is that when something magick is happening it should be
required that it is explicitly stated (like turning a serial value into
an int value).

> If you can't do something this basic because of a serial column,

You can, you just need to cast it.

SELECT serial_key, other_value
FROM TABLE(MULTISET(SELECT serial_key::INT, other_value FROM booby));

Adam Tauno Williams wrote:
> I don't know, this always made perfect sense to me. A "serial" data
> type's serial-ness can't be preserved through a virtual table.

But while a "serial" may be something interesting before the number is
generated, once a value is actually generated and in the table, isn't
it in fact just a number, sitting there, ripe for the picking? For
example - if I do something like:

select myid,myid+50, v1,v2,v3,v4 from bob

I might get:

1,51,I am not impressed,true,true,true
50,100,I am still not impressed,true,true,true

The first column is a "serial", but what I see are just numbers. It
looks like an integer, it smells like an integer, I can add it to other
integers and get a second column without casting it first. So why
should I have to cast it when using MULTISET ? I don't get it.

On a related note - what is REALLY stored in the serial column? Is it 1
and 50, or is it some offset value from some base value, or whatever?

> You can, you just need to cast it.
> SELECT serial_key, other_value
> FROM TABLE(MULTISET(SELECT serial_key::INT, other_value FROM booby));

Thanks for the tip. I will give this a try.

Cheers!
-sw

On Tue, 2006-09-26 at 11:55 -0700, internetuser wrote:
> Adam Tauno Williams wrote:
> > I don't know, this always made perfect sense to me. A "serial" data
> > type's serial-ness can't be preserved through a virtual table.
> But while a "serial" may be something interesting before the number is
> generated, once a value is actually generated and in the table, isn't
> it in fact just a number, sitting there, ripe for the picking?

No. Being serial does place restrictions on what the application is
free to do with it in regards to updating the record. This is why the
meta-data returned to the application when you do a select doesn't just
say INT, it will be a Serial8 or some such (depending on your
environment).

> For
> example - if I do something like:
> select myid,myid+50,

Sure but then myid+50 is an expression value, myid is still a serial.

> 1,51,I am not impressed,true,true,true
> 50,100,I am still not impressed,true,true,true
> The first column is a "serial", but what I see are just numbers.

Not it your looking at the metadata returned with the query - which all
smart applications do.

> It
> looks like an integer, it smells like an integer, I can add it to other
> integers and get a second column without casting it first.

Yep, which maybe shouldn't be the case. Looks like a concession to the
lazy and hapless to me.

> So why should I have to cast it when using MULTISET ? I don't get it.
> On a related note - what is REALLY stored in the serial column? Is it 1
> and 50, or is it some offset value from some base value, or whatever?

Adam Tauno Williams wrote:
> Yep, which maybe shouldn't be the case. Looks like a concession to the
> lazy and hapless to me.

Or perhaps a logical inconsistancy due to lazy and hapless programmers
responsible for its implementation.

internetuser wrote:
> Adam Tauno Williams wrote:
>> I don't know, this always made perfect sense to me. A "serial" data
>> type's serial-ness can't be preserved through a virtual table.
>
> But while a "serial" may be something interesting before the number is
> generated, once a value is actually generated and in the table, isn't
> it in fact just a number, sitting there, ripe for the picking? For
> example - if I do something like:
>
> select myid,myid+50, v1,v2,v3,v4 from bob
>
> I might get:
>
> 1,51,I am not impressed,true,true,true
> 50,100,I am still not impressed,true,true,true
>
> The first column is a "serial", but what I see are just numbers. It
> looks like an integer, it smells like an integer, I can add it to other
> integers and get a second column without casting it first. So why
> should I have to cast it when using MULTISET ? I don't get it.
>
> On a related note - what is REALLY stored in the serial column? Is it 1
> and 50, or is it some offset value from some base value, or whatever?
>
>> You can, you just need to cast it.
>> SELECT serial_key, other_value
>> FROM TABLE(MULTISET(SELECT serial_key::INT, other_value FROM booby));
>
> Thanks for the tip. I will give this a try.
>
> Cheers!
> -sw
>
Can you give us the bob table schema and exactly what you're trying to do?
It would be better if we could see it, instead of trying stuff that
doesn't work for you...

Regards.

as a side note, you don't have to include lwind_name twice in the
select clause.

select lwind_name winName
from land_window
where lower(lwind_name) = 'coastal plain'


internetuser wrote:
> OK, so I have to do something like this:
>
> select lwind_name, lower(lwind_name) winName
> from land_window where lower(lwind_name) = 'coastal plain'