Re: Splitting rows without temp tables
Follow-up to a question about splitting one row into several without temp tables. Serge Rielau suggested joining to an inline TABLE(VALUES(...)) construct (standard SQL, used in DB2); Obnoxio argued SELECT ... INTO TEMP already does the job, even on read-only/HDR secondaries, and the two bickered over which is cleaner. The poster (Dev) explained his real constraint: a vendor SOAP interface permits only plain SELECTs — no SELECT INTO, no table creation, no control blocks — with the allowed subset undocumented, so he must find limits by trial and error. He then found Informix has no standalone VALUES statement; Serge confirmed it was only a feature suggestion. No working solution is recorded, beyond advice to ask the vendor how they intend such queries to be done.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Serge Rielau said: > > PS: Can I propose features too? > INNER JOIN TABLE(VALUES(2, 'beginning'), ....) AS repeat (tkqsig, newcol) > Values is very powerful for that exact purpose: tables on the fly. This smacks to me of SQL mental masturbation. Why not just use the well-known "SELECT ... INTO TEMP" mechanism? -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com
"This smacks to me of SQL mental masturbation. Why not just use the well-known "SELECT ... INTO TEMP" mechanism? " Probably because: "and I have read-only access, so no temp tables allowed" Did you ever read the questions in exams at school? Me either.
Obnoxio The Clown wrote: > Serge Rielau said: > >>PS: Can I propose features too? >>INNER JOIN TABLE(VALUES(2, 'beginning'), ....) AS repeat (tkqsig, newcol) >>Values is very powerful for that exact purpose: tables on the fly. > > > This smacks to me of SQL mental masturbation. Why not just use the > well-known "SELECT ... INTO TEMP" mechanism? > I showed you mine, now show me yours... I don't kwow where you're going with this. Cheers Serge -- Serge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab
> > Did you ever read the questions in exams at school? > Do they have exams at Clown school?
theusarools@hotmail.co.uk said: > > "This smacks to me of SQL mental masturbation. Why not just use the > well-known "SELECT ... INTO TEMP" mechanism? " > > Probably because: > > "and I have read-only access, so no temp tables allowed" > > Did you ever read the questions in exams at school? > > Me either. I'm interested in this concept of read-only access not allowing temporary tables. You can even build temporary tables on an HDR secondary, the ultimate read-only database. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com
Serge Rielau said: > > Obnoxio The Clown wrote: >> Serge Rielau said: >> >>>PS: Can I propose features too? >>>INNER JOIN TABLE(VALUES(2, 'beginning'), ....) AS repeat (tkqsig, >>> newcol) >>>Values is very powerful for that exact purpose: tables on the fly. >> >> >> This smacks to me of SQL mental masturbation. Why not just use the >> well-known "SELECT ... INTO TEMP" mechanism? >> > I showed you mine, now show me yours... I don't kwow where you're going > with this. Why do you need to extend SQL with a new mechanism for on the fly tables when one already exists? -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com
Clive Eisen said: > > >> >> Did you ever read the questions in exams at school? >> > Do they have exams at Clown school? Oh, yes! I got an "A" in pie-throwing. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com
"I'm interested in this concept of read-only access not allowing temporary tables. You can even build temporary tables on an HDR secondary, the ultimate read-only database." Just testing.... ;-p
Obnoxio The Clown wrote: > Serge Rielau said: > >>Obnoxio The Clown wrote: >> >>>Serge Rielau said: >>> >>> >>>>PS: Can I propose features too? >>>>INNER JOIN TABLE(VALUES(2, 'beginning'), ....) AS repeat (tkqsig, >>>>newcol) >>>>Values is very powerful for that exact purpose: tables on the fly. >>> >>> >>>This smacks to me of SQL mental masturbation. Why not just use the >>>well-known "SELECT ... INTO TEMP" mechanism? >>> >> >>I showed you mine, now show me yours... I don't kwow where you're going >>with this. > > > Why do you need to extend SQL with a new mechanism for on the fly tables > when one already exists? > I don't need to extend SQL. It's already in the standard. So how do you get those, say, 10 rows into the temp table? Submitting 10 INSERT statements (reallyfast in a client/server environment)? Or using the obvious UNION ALL syntax with SET which has kludge written all over it? Cheers Serge -- Serge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab
Serge Rielau said: > > Obnoxio The Clown wrote: >> Serge Rielau said: >> >>>Obnoxio The Clown wrote: >>> >>>>Serge Rielau said: >>>> >>>>>PS: Can I propose features too? >>>>>INNER JOIN TABLE(VALUES(2, 'beginning'), ....) AS repeat (tkqsig, >>>>>newcol) >>>>>Values is very powerful for that exact purpose: tables on the fly. >>>> >>>> >>>>This smacks to me of SQL mental masturbation. Why not just use the >>>>well-known "SELECT ... INTO TEMP" mechanism? >>>> >>> >>>I showed you mine, now show me yours... I don't kwow where you're going >>>with this. >> >> Why do you need to extend SQL with a new mechanism for on the fly tables >> when one already exists? >> > I don't need to extend SQL. It's already in the standard. > So how do you get those, say, 10 rows into the temp table? > Submitting 10 INSERT statements (reallyfast in a client/server > environment)? Tell you what, Serge: I'll use vi to busk the insert statements, you knock yourself out with your SQL arcana. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com
In message <1138103099.747262.129670@o13g2000cwo.googlegroups.com>, theusarools@hotmail.co.uk writes >"This smacks to me of SQL mental masturbation. Why not just use the >well-known "SELECT ... INTO TEMP" mechanism? " > >Probably because: > >"and I have read-only access, so no temp tables allowed" But a lot of queries silently create temp tables! Have you actually tried the 'INTO TEMP' syntax? Or are you somewhere with some bizarre interface between you and the DB that parses out that syntax? -- Surfer! Email to: ramwater at uk2 dot net
> But a lot of queries silently create temp tables! Have you actually > tried the 'INTO TEMP' syntax? Or are you somewhere with some bizarre > interface between you and the DB that parses out that syntax? Got it it one. In fact, the whole problem arises because the last version of their product simply gave us access to the database, but the next version has decided the World Will Be a Better Place(tm) if noone has access to their database ever, and they give us a vetted SOAP interface keyhole to ask questions through. Hooray! (Not that I'm bitter or anything.) The whole process has been one of me proposing a solution, the developer saying "no, that doesn't work either" and then us querying the company in question who says "no, thats not allowed." You'd think they would just give us a list of what was allowed, wouldn't you? All they'll say is "select statements only", which as you all know has the possibility of encompassing a vast swathe of SQL, depending on what you include as part of SELECT. From the evidence they're obviously using a much narrower (but secret!) definition than that, but we're forced to find the boundaries through trial and error. "select into" is not allowed. Explicit table creation is not allowed (temp or otherwise). Any form of control statements or blocks appears to be out. Which is how I got to the admittedly rather ugly beast you see before you. I will look into the VALUES structure; I am not familiar with it. Thanks for the suggestion. - rob.
"blah blah blah is not allowed" show them the problem you are trying to solve and ask them how to get the answer you want. The vendor probably already has a way that they want you to do this.
Is there really a separate VALUES statement in Informix (as opposed to just the VALUES clause of an INSERT statement, which doesn't really seem to be what Serge was talking about)? I couldn't find any referrence to it in the IDS manuals I'm using online. Tried just experimenting with it but couldn't get it to work.
Dev wrote: > Is there really a separate VALUES statement in Informix (as opposed to > just the VALUES clause of an INSERT statement, which doesn't really > seem to be what Serge was talking about)? I couldn't find any > referrence to it in the IDS manuals I'm using online. Tried just > experimenting with it but couldn't get it to work. > I brought this up as a suggestion for a new feature request. VALUES is in the SQL standard and IMHO packs quite a punch. Cheers Serge -- Serge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab