Re: select * from (select * from bob) and other easy stuff

Why do people like inline views (select * from (select * from bob)? I think it is mostly a stupid feature. Especially if the inline select is in the view. I can think of some genuine uses for the thing outside of the from statement things you can't do any other way but isn't it simple to : create view bob_view(.....) as select> 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','265306','14H8','265308', > > '14H6','265311','UGRT','262720','CGNF','262712','CGPD','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 Also, why do this massive decode? Shouldn't you have a look up table to do this conversion? e_type_code -> case_type table 12A -> 265101 etc. internetuser wrote: > 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','265306','14H8','265308', > > '14H6','265311','UGRT','262720','CGNF','262712','CGPD','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