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

I completely agree. In fact I had posted a similar comment but google ate it somehow and I didn't care enough to post it again. Views are good things. These "temporary views" are overused. I can only think of a few times that they make sense (and only in the select clause for things that just can't be done with regular views, see posts on rotating a table.) Somehow this is Oracle's fault for overselling this feature. I had a friend who had just gone through an Oracle demo from a salesbot, excitedly tell me about this new Oracle feature where you could include select statements in the from clause. I asked him how this differed from views, he stammered that it was cool. I can't be everywhere to head off this "misuse" of technology. Somethings are better left in demo's. Art S. Kagel wrote: > 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 > >

I just noticed that Google didn't eat my earlier post denouncing inline views but posted it 4 times instead. This is clearly very different than eating it. I am so sorry, that we don't allow newsgroups (except non-technical recreational (I mean p*rn for the euphemistically challenged) but that is another diatribe) at our office and I have to post using Google's tool. bozon wrote: > I completely agree. In fact I had posted a similar comment but google > ate it somehow and I didn't care enough to post it again. > > Views are good things. These "temporary views" are overused. I can only > think of a few times that they make sense (and only in the select > clause for things that just can't be done with regular views, see posts > on rotating a table.) Somehow this is Oracle's fault for overselling > this feature. I had a friend who had just gone through an Oracle demo > from a salesbot, excitedly tell me about this new Oracle feature where > you could include select statements in the from clause. I asked him how > this differed from views, he stammered that it was cool. I can't be > everywhere to head off this "misuse" of technology. Somethings are > better left in demo's. > > Art S. Kagel wrote: > > 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 > > >

bozon wrote: > I just noticed that Google didn't eat my earlier post denouncing inline > views but posted it 4 times instead. This is clearly very different > than eating it. I am so sorry, that we don't allow newsgroups (except > non-technical recreational (I mean p*rn for the euphemistically > challenged) but that is another diatribe) at our office and I have to > post using Google's tool. > > bozon wrote: >> I completely agree. In fact I had posted a similar comment but google >> ate it somehow and I didn't care enough to post it again. >> >> Views are good things. These "temporary views" are overused. I can only >> think of a few times that they make sense (and only in the select >> clause for things that just can't be done with regular views, see posts >> on rotating a table.) Somehow this is Oracle's fault for overselling >> this feature. I had a friend who had just gone through an Oracle demo >> from a salesbot, excitedly tell me about this new Oracle feature where >> you could include select statements in the from clause. I asked him how >> this differed from views, he stammered that it was cool. I can't be >> everywhere to head off this "misuse" of technology. Somethings are >> better left in demo's. To create views you need privileges the app developer typically should not have. You typically don't want to clutter you db schema with everyone's ad-hoc views. Even worse you sure don't want to create/drop these beasts ad-hoc. If one looks at data warehousing SQL can get really complex and can't be broken apart easily without loosing performance. Nested sub queries are a must in that context. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab IOD Conference http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Serge Rielau said: > To create views you need privileges the app developer typically should > not have. You typically don't want to clutter you db schema with > everyone's ad-hoc views. Even worse you sure don't want to create/drop > these beasts ad-hoc. No, it's much better to clutter up your application with incomprehensible SQL instead. > If one looks at data warehousing SQL can get really complex Only if your database design is shit. ;o) -- Bye now, Obnoxio "... no bill is required as no value was provided." -- Christine Normile -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

But many of these views should exist permanently because they are used over and over again. The DBA can create these and then the developer's job is easier. Not to mention the porting job might be easier because your main SQL is simpiler. The complexity is contained in the views which are in one place instead of spread around in the code. I agree nested subqueries are necessary but this isn't the same as putting a select statement in the from clause which is the common idiom of this misuse. Serge Rielau wrote: > bozon wrote: > > I just noticed that Google didn't eat my earlier post denouncing inline > > views but posted it 4 times instead. This is clearly very different > > than eating it. I am so sorry, that we don't allow newsgroups (except > > non-technical recreational (I mean p*rn for the euphemistically > > challenged) but that is another diatribe) at our office and I have to > > post using Google's tool. > > > > bozon wrote: > >> I completely agree. In fact I had posted a similar comment but google > >> ate it somehow and I didn't care enough to post it again. > >> > >> Views are good things. These "temporary views" are overused. I can only > >> think of a few times that they make sense (and only in the select > >> clause for things that just can't be done with regular views, see posts > >> on rotating a table.) Somehow this is Oracle's fault for overselling > >> this feature. I had a friend who had just gone through an Oracle demo > >> from a salesbot, excitedly tell me about this new Oracle feature where > >> you could include select statements in the from clause. I asked him how > >> this differed from views, he stammered that it was cool. I can't be > >> everywhere to head off this "misuse" of technology. Somethings are > >> better left in demo's. > To create views you need privileges the app developer typically should > not have. You typically don't want to clutter you db schema with > everyone's ad-hoc views. Even worse you sure don't want to create/drop > these beasts ad-hoc. > > If one looks at data warehousing SQL can get really complex and can't be > broken apart easily without loosing performance. Nested sub queries are > a must in that context. > > Cheers > Serge > -- > Serge Rielau > DB2 Solutions Development > IBM Toronto Lab > > IOD Conference > http://www.ibm.com/software/data/ondemandbusiness/conf2006/

In my experience complex queries don't live in the application. There is no-one glueing them together on foot. Instead BI tooling produces these queries. Some queries may be regulars (packaged reports), but many are simply based on whatever some business user came up with today. And that business user doesn't have a clue how to even spell SQL. I met in Vienna with customers who run >100TB warehouses. You don't get that big with a bad design. Just accept the fact that different usages of the DBMS require different language capabilities. Just because YOU haven't needed nested subqueries doesn't mean IDS doesn't need them for good reasons. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab IOD Conference http://www.ibm.com/software/data/ondemandbusiness/conf2006/

If you have the views to simplify the logic then the BI tools should be able to use them to simplify the user's queries. I am open to the "select * from (select * from bob)" on an as needed basis, but I know that this construct gets overused and is therefore used when a simplier method would do. > I met in Vienna with customers who run >100TB warehouses. You don't get > that big with a bad design. I have seen bad designs in 500 gig databases but I haven't worked with larger databases so I can't say. Serge Rielau wrote: > In my experience complex queries don't live in the application. > There is no-one glueing them together on foot. > Instead BI tooling produces these queries. > Some queries may be regulars (packaged reports), but many are simply > based on whatever some business user came up with today. And that > business user doesn't have a clue how to even spell SQL. > I met in Vienna with customers who run >100TB warehouses. You don't get > that big with a bad design. > Just accept the fact that different usages of the DBMS require different > language capabilities. Just because YOU haven't needed nested subqueries > doesn't mean IDS doesn't need them for good reasons. > > Cheers > Serge > > > > -- > Serge Rielau > DB2 Solutions Development > IBM Toronto Lab > > IOD Conference > http://www.ibm.com/software/data/ondemandbusiness/conf2006/

bozon wrote: > If you have the views to simplify the logic then the BI tools should be > able to use them to simplify the user's queries. These BI tools work against multiple DBMS. It is a major endeavor to convince the vendors to customize for any given product. Every feature can get abused of course. One can argue against stacking views 10 levels deep or using SERIAL, or .... Everything in moderation :-) Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab IOD Conference http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Serge Rielau said: > In my experience complex queries don't live in the application. Yeah? Well, in my experience that is true for 95% of bad SQL. The stuff generated by report writers is 5% of the pain *I* have to deal with. And I actually spend my life with actual customers. ;o) > I met in Vienna with customers who run >100TB warehouses. You don't get > that big with a bad design. Oh yeah? The databases are 100TB, but they were actually only supposed to be 100GB. :o) > Just accept the fact that different usages of the DBMS require different > language capabilities. Just because YOU haven't needed nested subqueries > doesn't mean IDS doesn't need them for good reasons. Bollocks. ;o) -- Bye now, Obnoxio "... no bill is required as no value was provided." -- Christine Normile -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

Serge Rielau wrote: > bozon wrote: > > If you have the views to simplify the logic then the BI tools should be > > able to use them to simplify the user's queries. I meant the BI tools would use the views as tables, not create views on the fly. > These BI tools work against multiple DBMS. > It is a major endeavor to convince the vendors to customize for any > given product. > > Every feature can get abused of course. > One can argue against stacking views 10 levels deep or using SERIAL, or .... Yes, but I think that certain things get abused more than others. And sometimes they are substitutes for knowing what you are doing. For example, it is perfectly acceptable to use distinct but I have seen it abused so often I am now suspiscious. I have seen queries missing joins but the "distinct" clause makes it look OK. The developer will then ask why is their query running slowly when it only returns a couple of rows. I say because you are joining a 100,000,000 row table to 1,000,000 row table without a join clause but you are throwing away most of the rows because they are duplicates. So you really returned a google number of rows and then filtered it with distinct. select distinct status::boolean from big_table_a, bigger_table_b wherea.id > 100 > > Everything in moderation :-) > > Cheers > Serge > -- > Serge Rielau > DB2 Solutions Development > IBM Toronto Lab > > IOD Conference > http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Serge Rielau wrote: > bozon wrote: > >> I just noticed that Google didn't eat my earlier post denouncing inline >> views but posted it 4 times instead. This is clearly very different >> than eating it. I am so sorry, that we don't allow newsgroups (except >> non-technical recreational (I mean p*rn for the euphemistically >> challenged) but that is another diatribe) at our office and I have to >> post using Google's tool. >> >> bozon wrote: >> >>> I completely agree. In fact I had posted a similar comment but google >>> ate it somehow and I didn't care enough to post it again. >>> >>> Views are good things. These "temporary views" are overused. I can only >>> think of a few times that they make sense (and only in the select >>> clause for things that just can't be done with regular views, see posts >>> on rotating a table.) Somehow this is Oracle's fault for overselling >>> this feature. I had a friend who had just gone through an Oracle demo >>> from a salesbot, excitedly tell me about this new Oracle feature where >>> you could include select statements in the from clause. I asked him how >>> this differed from views, he stammered that it was cool. I can't be >>> everywhere to head off this "misuse" of technology. Somethings are >>> better left in demo's. > > To create views you need privileges the app developer typically should > not have. You typically don't want to clutter you db schema with > everyone's ad-hoc views. Even worse you sure don't want to create/drop > these beasts ad-hoc. > > If one looks at data warehousing SQL can get really complex and can't be > broken apart easily without loosing performance. Nested sub queries are > a must in that context. Any VIEW that's encoded into an application belongs in the DB where it can be reviewed and shared. If it's in the code, it's not dynamic, so some planning is not only possible but desirable. If the app programmers don't have the permissions (except on DEV we don't give that away here) they should be submitted to the DBAs for review, possible optimization, correction, and generalization, and installation on test and production. They should NEVER be buried inside an application where noone can see them or share them. But, as Gumby says, what do I know? I'm only doing DBA, DB Design, Architecture, Application Design, and coding for 24+ years. Art S. Kagel

Art S. Kagel wrote: > Serge Rielau wrote: >> bozon wrote: >> >>> I just noticed that Google didn't eat my earlier post denouncing inline >>> views but posted it 4 times instead. This is clearly very different >>> than eating it. I am so sorry, that we don't allow newsgroups (except >>> non-technical recreational (I mean p*rn for the euphemistically >>> challenged) but that is another diatribe) at our office and I have to >>> post using Google's tool. >>> >>> bozon wrote: >>> >>>> I completely agree. In fact I had posted a similar comment but google >>>> ate it somehow and I didn't care enough to post it again. >>>> >>>> Views are good things. These "temporary views" are overused. I can only >>>> think of a few times that they make sense (and only in the select >>>> clause for things that just can't be done with regular views, see posts >>>> on rotating a table.) Somehow this is Oracle's fault for overselling >>>> this feature. I had a friend who had just gone through an Oracle demo >>>> from a salesbot, excitedly tell me about this new Oracle feature where >>>> you could include select statements in the from clause. I asked him how >>>> this differed from views, he stammered that it was cool. I can't be >>>> everywhere to head off this "misuse" of technology. Somethings are >>>> better left in demo's. >> >> To create views you need privileges the app developer typically should >> not have. You typically don't want to clutter you db schema with >> everyone's ad-hoc views. Even worse you sure don't want to create/drop >> these beasts ad-hoc. >> >> If one looks at data warehousing SQL can get really complex and can't >> be broken apart easily without loosing performance. Nested sub queries >> are a must in that context. > > Any VIEW that's encoded into an application belongs in the DB where it > can be reviewed and shared. If it's in the code, it's not dynamic, so > some planning is not only possible but desirable. > > If the app programmers don't have the permissions (except on DEV we > don't give that away here) they should be submitted to the DBAs for > review, possible optimization, correction, and generalization, and > installation on test and production. They should NEVER be buried inside > an application where noone can see them or share them. > > But, as Gumby says, what do I know? I'm only doing DBA, DB Design, > Architecture, Application Design, and coding for 24+ years. I give up, please re-read my posts. I'm not talking about crummy SQL written by app programmers at all. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab IOD Conference http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Serge Rielau said: > I give up, please re-read my posts. I'm not talking about crummy SQL > written by app programmers at all. No. But you did make the rather amusing assertion that views that hide database complexity should not be kept in the database. :o) -- Bye now, Obnoxio "... no bill is required as no value was provided." -- Christine Normile -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.