 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Informix Topics / October 2006select * from (select * from bob) and other easy stuff
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 [quoted text clipped - 6 lines] > 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 > New Informix user - coming from Oracle here. I'm baffled. > [quoted text clipped - 22 lines] > 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" [quoted text clipped - 3 lines] > 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 > 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 - 6 lines] > 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)); > 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 > > 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? > 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. >> 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. [quoted text clipped - 27 lines] > 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' > 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' > 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' How is this going to give me the same output as the query I had? For example: thisisawindowname, THIS IS A WINDOW NAME thisisanotherwindowname, THIS IS ANOTHER WINDOW NAME ? Thanks! -sw > thisisawindowname, THIS IS A WINDOW NAME > thisisanotherwindowname, THIS IS ANOTHER WINDOW NAME it won't - just pointing out that you don't *require* upper function in the select clause in order to include it in the where clause. It isn't apparent to me why you would need to show it twice (once uppercased and once in it's natural case). If you already knew that - disregard. I like Fernado's suggestion (provide some schemas and what exactly you are trying to accomplish). My best guess (but without the links between the tables it's a best some murky sql) is below. It's late and I'm going by memory here on the case in the where clause - but I believe you can use that syntax (I never have found a reason to do so though). I think you can also use the something like this in the where clause: "where case __ when __ then __ end IN (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, notrust.e_type et where et.e_type_code = vct.et_code -- the join i doubt is true (guess) and vct.case_type is not null AND CASE et.e_type_code WHEN '12A' THEN '265101' WHEN 'SCHL' THEN '262714' WHEN 'GPGT' THEN '262730' WHEN 'TNCGT' THEN '262713' -- not putting all the elements from your decode function ELSE '' END = vct.valid_case_type_code All: Thanks for the comments. Putting all the table and schema info here isn't too practical I think - I'm getting data from multiple databases, which contain many tables, etc. You guys don't want to see all that stuff. (: In any event, I found a solution that works. Now I'm wondering if there is a better way to do it, or if there is a more database-agnostic way to do it. Here is the solution that works: create view refill_all_entlmnt_types (et_code,et_text,ct_code,ct_text) as SELECT et_code,et_text,valid_case_type_code ct_code, valid_case_type_desc_txt ct_text FROM TABLE( MULTISET( SELECT et.entlmnt_type_code et_code,et.entlmnt_type_desc_txt et_text, decode (entlmnt_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 FROM notrust@server1:akinfrmx.entitlement_type et) ),OUTER(notrust@server1:akinfrmx.valid_case_type vct) WHERE ct_code = valid_case_type_code; The databases I'm using have a lot of tables and so forth - I dont think you really want me to post schemas and such for them here. (: At any rate, I devised a working solution. Now I just wonder if there is a better way to do it, or if there is a more database-agnostic way of doing it. Here is what I've got now: /*VIEW: refill_all_entlmnt_types */ create view refill_all_entlmnt_types (et_code,et_text,ct_code,ct_text) as SELECT et_code,et_text,valid_case_type_code ct_code,valid_case_type_desc_txt ct_text FROM TABLE( MULTISET( SELECT et.entlmnt_type_code et_code,et.entlmnt_type_desc_txt et_text, decode (entlmnt_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 FROM notrust@ak0779_gcdb:akinfrmx.entitlement_type et) ),OUTER(notrust@ak0779_gcdb:akinfrmx.valid_case_type vct) WHERE ct_code = valid_case_type_code ;/*END OF VIEW DEFINITION*/ p.s.: I thought I posted this once but it didn't look like it "took" - sorry if it gets duplicated. It seems you have some sort of 'unconventional' db design on your hands. Best of luck. > At any rate, I devised a working solution. Now I just wonder if there > is a better way to do it, or if there is a more database-agnostic way [quoted text clipped - 24 lines] > p.s.: I thought I posted this once but it didn't look like it "took" - > sorry if it gets duplicated. 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 [quoted text clipped - 12 lines] > 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. > 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 - 81 lines] > Thanks! > -sw 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 [quoted text clipped - 12 lines] > 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. > 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 - 81 lines] > Thanks! > -sw Why the long decode statement shouldn't this be in a small conversion table? e_type_code | case_type 12A | 265101 etc. Also, why use the select * from (select * from <table>) construct it seems like a simple select could take its place unless I am missing something. > internetuser > select [quoted text clipped - 9 lines] > from notrust.e_type) acs > where acs.case_type = vct.valid_case_type_code and case_type is not null Before we engage in beloved database flame wars which always produce some enlightning thoughts, let's just say it's very frustrating to port an application from one database to another, even in environments which promise to abstract the underlying database, such as Java frameworks. Check this link, it might help you: http://www-128.ibm.com/developerworks/db2/library/long/dm-0608marino/ > 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. ... > 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. ... > 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: ... > 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 [quoted text clipped - 7 lines] > features that require one to have a very different mindset when writing > code for Informix. Can anyone shed some light? > 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 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 >> 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 - 61 lines] >> Thanks! >> -sw > 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 > [quoted text clipped - 13 lines] >>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> 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. > > 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 - 123 lines] > > 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. > 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. [quoted text clipped - 137 lines] > > > 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 [quoted text clipped - 16 lines] >> 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  SignatureSerge 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 sh.t. ;o) SignatureBye 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. > > 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 [quoted text clipped - 34 lines] > 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 SignatureSerge 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. > In my experience complex queries don't live in the application. > There is no-one glueing them together on foot. [quoted text clipped - 18 lines] > 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. 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 SignatureSerge 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 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 where a.id > 100 > Everything in moderation :-) > [quoted text clipped - 7 lines] > 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) SignatureBye 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. >> 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 [quoted text clipped - 25 lines] > 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 >>> 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 [quoted text clipped - 38 lines] > 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 SignatureSerge 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) SignatureBye 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.