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

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've never understood why one would use this syntax when a simple: select lwind_name,lw_type_cd from land_window ...; Will do the same thing. When the virtual table query is more complex I'd want to set up a VIEW for it anyway since I'm likely to use that same virtuality in other presumably related queries. So I might: create view virt_land_window_complex(....) as select * from .... where ... group by ... having ...; Then just: select lwind_name,lw_type_cd ... from virt_land_window_complex ...; > 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? Clear as glass to me! Sorry. > 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 Why not just: select acs.e_type_code as et_code, acs.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 ct_code, valid_case_type_desc_txt as ct_desc from otrust.valid_case_type as vct, notrust.e_type as acs where acs.case_type = vct.valid_case_type_code and ct_code is not null; I may have made a typo ot mistranslated some multiply aliased names, but that seems VERY simple, straight forward, and easy to read to me! Below you said: 'require one to have a very different mindset when writing code for Informix'. That's true. Mostly KISS applies. Art S. Kagel > 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 >

Totally agree Art. Many times people enjoy complicating things. It appears this is a migration of an overly complicated system. Perhaps going back to basics would help the migration and make it an easier system to maintain after migration. An investment in training classes sounds like it would be value-added. Norma Jean Art S. Kagel 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. >[quoted text clipped - 5 lines] >> select lwind_name,lw_type_cd from table(multiset(select * from >> land_window))> >I've never understood why one would use this syntax when a simple: > >select lwind_name,lw_type_cd from land_window ...;> >Will do the same thing. When the virtual table query is more complex I'd >want to set up a VIEW for it anyway since I'm likely to use that same >virtuality in other presumably related queries. So I might: > >create view virt_land_window_complex(....) as >select * >from .... >where ... >group by ... >having ...>; > >Then just: > >select lwind_name,lw_type_cd ... from virt_land_window_complex ...;> >> I get a message: >> >[quoted text clipped - 23 lines] >> 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? > >Clear as glass to me! Sorry. > >> There also seems to be some rather severe limitations here. Consider >> the following code that works great with Oracle: >[quoted text clipped - 15 lines] >> where acs.case_type = vct.valid_case_type_code and case_type is not >> null > >Why not just: > >select acs.e_type_code as et_code, acs.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 ct_code, > valid_case_type_desc_txt as ct_desc >from otrust.valid_case_type as vct, notrust.e_type as acs >where acs.case_type = vct.valid_case_type_code and ct_code is not null;> >I may have made a typo ot mistranslated some multiply aliased names, but >that seems VERY simple, straight forward, and easy to read to me! > >Below you said: 'require one to have a very different mindset when writing >code for Informix'. That's true. Mostly KISS applies. > >Art S. Kagel > >> 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 >[quoted text clipped - 23 lines] >> Thanks! >> -sw -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200610/1

normajeansebastian via DBMonster.com wrote: > Totally agree Art. > > Many times people enjoy complicating things. Had a friend once, I may have mentioned it before, who firmly believed: "Why make things simple and elegant when you can make them complex and wonderful!?" I guess he wasn't unique. > It appears this is a migration of an overly complicated system. > Perhaps going back to basics would help the migration and make it an easier > system to maintain after migration. Would have been easier to migrate if it had been written simply to begin with. Kagel's First Law of SQL: Every SELECT statement can be written in at least three different ways. If you haven't found all three you're not finished looking! I first posted that above my desk more than 15 years ago BEFORE ANSI syntax or virtual tables. I guess that ups the anti to "at least five different ways"! > An investment in training classes sounds like it would be value-added. And a copy of each of Joe Celko's books on every developer's desk! Art S. Kagel > Norma Jean > > > Art S. Kagel 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. >> >>[quoted text clipped - 5 lines] >> >>>select lwind_name,lw_type_cd from table(multiset(select * from >>>land_window))>> >>I've never understood why one would use this syntax when a simple: >> >>select lwind_name,lw_type_cd from land_window ...;>> >>Will do the same thing. When the virtual table query is more complex I'd >>want to set up a VIEW for it anyway since I'm likely to use that same >>virtuality in other presumably related queries. So I might: <SNIP>