This is probably the wrong forum for this, but I thought it might start
some discussion.
The INSERT statement, in its current form, has problems being formatted
so a human reader can follow it. In particular, for a table with many
columns, the only to put the values in close proximity with the column
names is horizontally, which can lead to lines of 1000s of characters in
length which are difficult to read and edit. What I would like to see is
an optional from of the INSERT statement with syntax like the SET clause
of the UPDATE statement, which keeps the values in close proximity with
the column names and also allows reasonable length lines for human
reading and editing. Clearly this is just syntactic sugar because the
new syntax does not add any power or functionality to the INSERT statement. 12 1586
Bob Stearns wrote: This is probably the wrong forum for this, but I thought it might start some discussion.
The INSERT statement, in its current form, has problems being formatted so a human reader can follow it. In particular, for a table with many columns, the only to put the values in close proximity with the column names is horizontally, which can lead to lines of 1000s of characters in length which are difficult to read and edit. What I would like to see is an optional from of the INSERT statement with syntax like the SET clause of the UPDATE statement, which keeps the values in close proximity with the column names and also allows reasonable length lines for human reading and editing. Clearly this is just syntactic sugar because the new syntax does not add any power or functionality to the INSERT statement.
So you want something similar to a "natural" join, just as a natural insert.
INSERT INTO T NATURAL SELECT x AS a, Y as b FROM T
with a and b being columns of T in some order.
Note that a natural join is a join in which columns with the same are
matched.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Serge Rielau wrote: Bob Stearns wrote:
This is probably the wrong forum for this, but I thought it might start some discussion.
The INSERT statement, in its current form, has problems being formatted so a human reader can follow it. In particular, for a table with many columns, the only to put the values in close proximity with the column names is horizontally, which can lead to lines of 1000s of characters in length which are difficult to read and edit. What I would like to see is an optional from of the INSERT statement with syntax like the SET clause of the UPDATE statement, which keeps the values in close proximity with the column names and also allows reasonable length lines for human reading and editing. Clearly this is just syntactic sugar because the new syntax does not add any power or functionality to the INSERT statement.
So you want something similar to a "natural" join, just as a natural insert.
INSERT INTO T NATURAL SELECT x AS a, Y as b FROM T with a and b being columns of T in some order.
Note that a natural join is a join in which columns with the same are matched.
Cheers Serge
That appears to be just what I was thinking about. Unfortunately, I can
find no reference to 'NATURAL' either in the SQL Reference manuals or in
the reference material at the infocenter. The behavior you indicate is,
of course, the desired one, but I would like to read more about the
NATURAL modifier especially since this is not a join.
Bob Stearns wrote: That appears to be just what I was thinking about. Unfortunately, I can find no reference to 'NATURAL' either in the SQL Reference manuals or in the reference material at the infocenter. The behavior you indicate is, of course, the desired one, but I would like to read more about the NATURAL modifier especially since this is not a join.
Is there also an UNNATURAL select? ;-)
Dan
--
Daniel A. Morgan http://www.psoug.org da******@x.washington.edu
(replace x with u to respond)
DA Morgan wrote: Bob Stearns wrote:
That appears to be just what I was thinking about. Unfortunately, I can find no reference to 'NATURAL' either in the SQL Reference manuals or in the reference material at the infocenter. The behavior you indicate is, of course, the desired one, but I would like to read more about the NATURAL modifier especially since this is not a join.
Is there also an UNNATURAL select? ;-)
I guess the "defender of [geese and] all things natural" (Larry M.)
would know.
Bob,
The concept of a natural join is actually in the SQL Standard, but it's
not supported by DB2. To be honest I can think of about dozens of things
I'd like to see before I'd start worrying about that sort of sugar....
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Serge Rielau wrote: DA Morgan wrote:
Bob Stearns wrote:
That appears to be just what I was thinking about. Unfortunately, I can find no reference to 'NATURAL' either in the SQL Reference manuals or in the reference material at the infocenter. The behavior you indicate is, of course, the desired one, but I would like to read more about the NATURAL modifier especially since this is not a join. Is there also an UNNATURAL select? ;-) I guess the "defender of [geese and] all things natural" (Larry M.) would know.
Bob,
The concept of a natural join is actually in the SQL Standard, but it's not supported by DB2. To be honest I can think of about dozens of things I'd like to see before I'd start worrying about that sort of sugar....
Cheers Serge
Is there a standard DB2 schema.table (containing only one row) one can
use for selecting constants from. That could be used for this purpose,
modulo the convenience of column reordering.
Bob Stearns wrote: Serge Rielau wrote:
DA Morgan wrote:
Bob Stearns wrote:
That appears to be just what I was thinking about. Unfortunately, I can find no reference to 'NATURAL' either in the SQL Reference manuals or in the reference material at the infocenter. The behavior you indicate is, of course, the desired one, but I would like to read more about the NATURAL modifier especially since this is not a join.
Is there also an UNNATURAL select? ;-) I guess the "defender of [geese and] all things natural" (Larry M.) would know.
Bob,
The concept of a natural join is actually in the SQL Standard, but it's not supported by DB2. To be honest I can think of about dozens of things I'd like to see before I'd start worrying about that sort of sugar....
Cheers Serge
Is there a standard DB2 schema.table (containing only one row) one can use for selecting constants from. That could be used for this purpose, modulo the convenience of column reordering.
Something similar to DUAL in Oracle.
--
Daniel A. Morgan http://www.psoug.org da******@x.washington.edu
(replace x with u to respond)
There is "sysibm.sysdummy1" which contains only one row with one value:
'Y'. But you can achieve the same using a "select * from
table(values('Y')) as t1(val1)", where t1 is the table name and val1
the name of the firts column. I hope this is what you were looking for.
Janick
Janick Bernet wrote: There is "sysibm.sysdummy1" which contains only one row with one value: 'Y'. But you can achieve the same using a "select * from table(values('Y')) as t1(val1)", where t1 is the table name and val1 the name of the firts column. I hope this is what you were looking for.
Janick
I recently gave a talk where 25% of my time was spent on VALUES.
VALUES is supported since DB2 V2 and it is part of the SQL staandard,
yet it is unknown to shockingly many....
Quick rundown:
VALUES is a table constructure.
VALUES (1)
=>
((1))
VALUES (1, 2), (3, 4)
=> ((1, 2), (3, 4))
VALUES (CAST(? AS INT), CAST(? AS FLOAT)), (?, ?), (?, ?)
=> Finds in three rows from the application.
Note that teh ? of one (the first for convenience) row need to be cast
to establish data types.
You can use VALUES ANYWHERE(!) a SELECT is allowed.
The TABLE (or LATERAL) keyword is used to allow "lateral" correlation.
So you can do things like PIVOT or multi row INSERT:
CREATE TABLE sales(year INT, q1 INT, q2 INT, q3 INT, q4 INT);
INSERT INTO sales VALUES
(2001, 20, 30, 15, 10),
(2002, 35, 31, 29, 19),
(2002, 41, 43, 38, 25);
SELECT S.year, Q.quarter, Q.sales
FROM sales AS S,
TABLE(VALUES(1, S.q1),
(2, S.q2),
(3, S.q3),
(4, S.q4))
AS Q(quarter, sales);
YEAR QUARTER SALES
----------- ----------- -----------
2001 1 20
2001 2 30
2001 3 15
2001 4 10
2002 1 35
2002 2 31
2002 3 29
2002 4 19
2002 1 41
2002 2 43
2002 3 38
2002 4 25
12 record(s) selected.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
I actually prefer (with tabbing not shown here):
INSERT INTO
A
(
Col1,
Col2,
Col3,
Col4,
Col5
)
VALUES
(
Val1,
Val2,
Val3,
Val4,
Val5
)
Especially when instead of a VALUES() an INSERT is used, i find this
wonderfully readable and a lot easier for quick formatting than
anything else. (I format nearly everything in a monospace font with
tabs.)
B.
Brian Tkatch wrote: I actually prefer (with tabbing not shown here):
INSERT INTO A ( Col1, Col2, Col3, Col4, Col5 ) VALUES ( Val1, Val2, Val3, Val4, Val5 )
Especially when instead of a VALUES() an INSERT is used, i find this wonderfully readable and a lot easier for quick formatting than anything else. (I format nearly everything in a monospace font with tabs.)
B.
That works well if the list is less than one video page, but is
miserable to check / change when the list goes over one page. I'm
looking for human usability; someone (Weinberg, I think) said the
purpose of a program is not to communicate with a computer but rather
with the following programmer, even if it yourself.
>> VALUES (CAST(? AS INT), CAST(? AS FLOAT)), (?, ?), (?, ?)
=> Finds in three rows from the application. Note that the ? of one
(the first for convenience) row need to be cast to establish data
types. <<
That is a trick I did not know!
Don't forget that you can put scalar subquery expresions in the
VALUES() list, too.
> but is miserable to check / change when the list goes over one page.
Not really. Personally, i use notepad, and opening up a second window
is so easy, comparison is a snap. A spreadsheet makes this *very* easy
when it is in a column, and a couple of un*x commands could do the
same. IOW, with a little bit of work, the columnized version works very
well.
An added plus, is that the statement is split into two clear sections,
making them modular should a similar statement be needed. This would be
lost in the proposed solution, as would an INSERT that relies on COLUMN
order. While normally that is a Bad Idea (tm), it works very well for
keeping two data sources in sync (import/export or cross-schema) and
two TABLEs CREATEd with the same statement.
Admittedly, SQL itself is inconsistent. The proposal is nice, and it
would make some code easier to write, especially when the requirement
is to "INSERT it if it isn't there/UPDATE if it is". Perhaps the
propsal would be better to allow INSERT formatting for UPDATE and
vice-versa, then it would simply be the programmer's choice.
B. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Christopher T King |
last post by:
Okay, so this is really two requests in one, and they're both kinda
outlandish, but I'm gonna post them nonetheless:
I've always thought xrange() to be ugly; it looks to be a lot of typing
just...
|
by: F Jamitzky |
last post by:
It is rather easy to define functions in python that mimic the special
ruby syntactic sugar like:
5.times { print "Hello World!" }
or
.each { |food| eat food }
In python these fragments...
|
by: Neil Zanella |
last post by:
Hello,
It seems to me that C# properties are nothing more than syntactic sugar for
getters and setters. I wonder whether others hold a different point of view.
Basically, what more do they have...
|
by: Bas |
last post by:
Hi group,
just out of curiosity, is there a list of all the syntactic sugar that
is used in python? If there isn't such a list, could it be put on a
wiki somewhere? The bit of sugar that I do...
|
by: glomde |
last post by:
i I would like to extend python so that you could create hiercical
tree structures (XML, HTML etc) easier and that resulting code would be
more readable than how you write today with packages like...
|
by: Sam Kong |
last post by:
Hi,
While discussing C#'s using statement, a guy and I had an argument.
In C# spec (15.13), there's an explanation like the following.
using (R r1 = new R()) {
r1.F();
}
is precisely...
|
by: Helmut Jarausch |
last post by:
Hi,
are decorators more than just syntactic sugar in python 2.x and what
about python 3k ?
How can I find out the predefined decorators?
Many thanks for your help,
Helmut Jarausch
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |