By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,287 Members | 1,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,287 IT Pros & Developers. It's quick & easy.

VALUES statement

P: n/a
Hi!

With VALUES you can do something like:
SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS TEMP(LETTER, NUMBER)
which will give you:
LETTER NUMBER
------ ------
A 1
B 2
C 2

It there some way with VALUES that would give me:
LETTER NUMBER
------ ------

So no results, empty result set?
So SELECT COUNT(*) FROM VALUES..... returns 0.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Mar 9 '07 #1
Share this Question
Share on Google+
13 Replies


P: n/a
On Mar 9, 1:31 pm, Gregor Kovač <gregor.ko...@mikropis.siwrote:
Hi!

With VALUES you can do something like:
SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS TEMP(LETTER, NUMBER)
which will give you:
LETTER NUMBER
------ ------
A 1
B 2
C 2

It there some way with VALUES that would give me:
LETTER NUMBER
------ ------
SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS TEMP(LETTER,
NUMBER) where 1=0

/Lennart

[....]

Mar 9 '07 #2

P: n/a
Lennart wrote:
On Mar 9, 1:31 pm, Gregor Kovač <gregor.ko...@mikropis.siwrote:
>Hi!

With VALUES you can do something like:
SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS TEMP(LETTER,
NUMBER) which will give you:
LETTER NUMBER
------ ------
A 1
B 2
C 2

It there some way with VALUES that would give me:
LETTER NUMBER
------ ------

SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS TEMP(LETTER,
NUMBER) where 1=0

/Lennart

[....]
Yes, this could work, but the problem is that VALUES .... gets generated,
and the where clause not.
Let me clearify. :)))
I'm using VALUES statement to generate an SQL statement using values in a
table in a GUI mask. So the application that generates this query must be
aware that the table is empty and generate appropriate SQL.
In the line of:
SELECT * FROM (VALUES ('a', 1) EXCEPT ALL VALUES ('b', 2)) AS TEMP(LETTER,
NUMBER)

Best regards,
Kovi

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Mar 9 '07 #3

P: n/a
What about this?
SELECT * FROM (VALUES (NULL, NULL), -- Fixed
(1, 'a'), (2, 'b'), ... -- generated
) AS X(c1, c2) WHERE NOT (c1 IS NULL AND c2 IS NULL) -fixed

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 9 '07 #4

P: n/a
Serge Rielau wrote:
SELECT * FROM (VALUES (NULL, NULL), * * *-- Fixed
(1, 'a'), (2, 'b'), ... -- generated
) AS X(c1, c2) WHERE NOT (c1 IS NULL AND c2 IS NULL)
Hi!

This wouldn't work since the part (1, 'a'), (2, 'b'), ... will not be
generated (the GUI table is empty, remember ?) and SELECT * FROM (VALUES
(NULL, NULL)) AS X(c1, c2) WHERE NOT (c1 IS NULL AND c2 IS NULL) yields
[Error Code: -584, SQL State: 42608] Invalid use of NULL or DEFAULT.

I like the EXCEPT ALL more. :))))

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Mar 9 '07 #5

P: n/a
On Mar 9, 2:25 pm, Gregor Kovač <gregor.ko...@mikropis.siwrote:
Lennart wrote:
On Mar 9, 1:31 pm, Gregor Kovač <gregor.ko...@mikropis.siwrote:
Hi!
With VALUES you can do something like:
SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS TEMP(LETTER,
NUMBER) which will give you:
LETTER NUMBER
------ ------
A 1
B 2
C 2
It there some way with VALUES that would give me:
LETTER NUMBER
------ ------
SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS TEMP(LETTER,
NUMBER) where 1=0
/Lennart
[....]

Yes, this could work, but the problem is that VALUES .... gets generated,
and the where clause not.
I don't understand. Assuming you get this as input:

String s = "SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS
TEMP(LETTER, NUMBER)";

couldn't you just add the where clause as:

s = s + " where 1=0";

before firing off the query?
Let me clearify. :)))
I'm using VALUES statement to generate an SQL statement using values in a
table in a GUI mask. So the application that generates this query must be
aware that the table is empty and generate appropriate SQL.
In the line of:
SELECT * FROM (VALUES ('a', 1) EXCEPT ALL VALUES ('b', 2)) AS TEMP(LETTER,
NUMBER)
This will not generate an empty result set. Did you mean

SELECT * FROM (VALUES ('a', 1) INTERSECT VALUES ('b', 2)) AS
TEMP(LETTER, NUMBER) ?

But how do you assure that there are no duplicates (i.e. the
intersection is truly empty)?

/Lennart

Mar 9 '07 #6

P: n/a
Lennart wrote:
On Mar 9, 2:25 pm, Gregor Kovač <gregor.ko...@mikropis.siwrote:
>Lennart wrote:
On Mar 9, 1:31 pm, Gregor Kovač <gregor.ko...@mikropis.siwrote:
Hi!
>With VALUES you can do something like:
SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS TEMP(LETTER,
NUMBER) which will give you:
LETTER NUMBER
------ ------
A 1
B 2
C 2
>It there some way with VALUES that would give me:
LETTER NUMBER
------ ------
SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS TEMP(LETTER,
NUMBER) where 1=0
/Lennart
[....]

Yes, this could work, but the problem is that VALUES .... gets generated,
and the where clause not.

I don't understand. Assuming you get this as input:

String s = "SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS
TEMP(LETTER, NUMBER)";

couldn't you just add the where clause as:

s = s + " where 1=0";

before firing off the query?
Because the query itself is written by the user. And if the user does:
SELECT * FROM {GET_TABLE(GUI_TABLE)} WHERE LETTER = 'A'
then the application would generate
SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS TEMP(LETTER, NUMBER)
WHERE 1=0 WHERE LETTER = 'A'
which is an invalid SQL. Do you agree?
>Let me clearify. :)))
I'm using VALUES statement to generate an SQL statement using values in a
table in a GUI mask. So the application that generates this query must be
aware that the table is empty and generate appropriate SQL.
In the line of:
SELECT * FROM (VALUES ('a', 1) EXCEPT ALL VALUES ('b', 2)) AS
TEMP(LETTER, NUMBER)

This will not generate an empty result set. Did you mean

SELECT * FROM (VALUES ('a', 1) INTERSECT VALUES ('b', 2)) AS
TEMP(LETTER, NUMBER) ?

But how do you assure that there are no duplicates (i.e. the
intersection is truly empty)?

/Lennart
ups.. :)
I meant
SELECT * FROM (VALUES ('a', 1) EXCEPT ALL VALUES ('a', 1)) AS TEMP(LETTER,
NUMBER)

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Mar 9 '07 #7

P: n/a
Gregor Kovač wrote:
Serge Rielau wrote:
>SELECT * FROM (VALUES (NULL, NULL), -- Fixed
(1, 'a'), (2, 'b'), ... -- generated
) AS X(c1, c2) WHERE NOT (c1 IS NULL AND c2 IS NULL)

Hi!

This wouldn't work since the part (1, 'a'), (2, 'b'), ... will not be
generated (the GUI table is empty, remember ?)
and SELECT * FROM (VALUES
(NULL, NULL)) AS X(c1, c2) WHERE NOT (c1 IS NULL AND c2 IS NULL) yields
[Error Code: -584, SQL State: 42608] Invalid use of NULL or DEFAULT.
All to need is to cast the NULLs.
(CAST(NULL AS INT), CAST(NULL AS VARCHAR(10)), ...
I like the EXCEPT ALL more. :))))
Anything that works...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 9 '07 #8

P: n/a
On Mar 9, 3:36 pm, Gregor Kovač <gregor.ko...@mikropis.siwrote:
[...]
Because the query itself is written by the user. And if the user does:
SELECT * FROM {GET_TABLE(GUI_TABLE)} WHERE LETTER = 'A'
then the application would generate
SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS TEMP(LETTER, NUMBER)
WHERE 1=0 WHERE LETTER = 'A'
which is an invalid SQL. Do you agree?
Absolutely. How about (I'm using python):

if (s.find('WHERE') 0):
s = s + ' AND '
else:
s = s + ' WHERE '
s = s + '1=0'

Test:

[lelle@53dbd181 lelle]$ cat aa.py
#!/usr/bin/python

s1 = "SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS
TEMP(LETTER, NUMBER)"
s2 = "SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS
TEMP(LETTER, NUMBER) WHERE LETTER = 'A'"
for s in [ s1, s2 ]:
print 'Input: %s' % (s)
if (s.find('WHERE') 0):
s = s + ' AND '
else:
s = s + ' WHERE '
s = s + '1=0'
print 'Result: %s' % (s)
>>>>
[lelle@53dbd181 lelle]$ ./aa.py
Input: SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS
TEMP(LETTER, NUMBER)
Result: SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS
TEMP(LETTER, NUMBER) WHERE 1=0
Input: SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS
TEMP(LETTER, NUMBER) WHERE LETTER = 'A'
Result: SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS
TEMP(LETTER, NUMBER) WHERE LETTER = 'A' AND 1=0
/Lennart

[...]

Mar 9 '07 #9

P: n/a
Lennart wrote:
On Mar 9, 3:36 pm, Gregor Kovač <gregor.ko...@mikropis.siwrote:
[...]
>Because the query itself is written by the user. And if the user does:
SELECT * FROM {GET_TABLE(GUI_TABLE)} WHERE LETTER = 'A'
then the application would generate
SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS TEMP(LETTER,
NUMBER) WHERE 1=0 WHERE LETTER = 'A'
which is an invalid SQL. Do you agree?

Absolutely. How about (I'm using python):

if (s.find('WHERE') 0):
s = s + ' AND '
else:
s = s + ' WHERE '
s = s + '1=0'

Test:

[lelle@53dbd181 lelle]$ cat aa.py
#!/usr/bin/python

s1 = "SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS
TEMP(LETTER, NUMBER)"
s2 = "SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS
TEMP(LETTER, NUMBER) WHERE LETTER = 'A'"
for s in [ s1, s2 ]:
print 'Input: %s' % (s)
if (s.find('WHERE') 0):
s = s + ' AND '
else:
s = s + ' WHERE '
s = s + '1=0'
print 'Result: %s' % (s)
>>>>>

[lelle@53dbd181 lelle]$ ./aa.py
Input: SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS
TEMP(LETTER, NUMBER)
Result: SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS
TEMP(LETTER, NUMBER) WHERE 1=0
Input: SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS
TEMP(LETTER, NUMBER) WHERE LETTER = 'A'
Result: SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS
TEMP(LETTER, NUMBER) WHERE LETTER = 'A' AND 1=0
/Lennart

[...]
Sounds OK, but :))) (there is always a but :)) )
when the GUI table is empty then the part ('A', 1), ('B', 2), ('C', 2) can
only be made up.

Best regards,
Kovi

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Mar 9 '07 #10

P: n/a
Serge Rielau wrote:
Gregor Kovač wrote:
>Serge Rielau wrote:
>>SELECT * FROM (VALUES (NULL, NULL), -- Fixed
(1, 'a'), (2, 'b'), ... -- generated
) AS X(c1, c2) WHERE NOT (c1 IS NULL AND c2 IS NULL)

Hi!

This wouldn't work since the part (1, 'a'), (2, 'b'), ... will not be
generated (the GUI table is empty, remember ?)
and SELECT * FROM (VALUES
(NULL, NULL)) AS X(c1, c2) WHERE NOT (c1 IS NULL AND c2 IS NULL) yields
[Error Code: -584, SQL State: 42608] Invalid use of NULL or DEFAULT.
All to need is to cast the NULLs.
(CAST(NULL AS INT), CAST(NULL AS VARCHAR(10)), ...
Yep, I'm doing just that. Thanks. :))))
>I like the EXCEPT ALL more. :))))
Anything that works...

Cheers
Serge

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Mar 9 '07 #11

P: n/a
Lennart wrote:
On Mar 9, 3:36 pm, Gregor Kovač <gregor.ko...@mikropis.siwrote:
[...]
>Because the query itself is written by the user. And if the user does:
SELECT * FROM {GET_TABLE(GUI_TABLE)} WHERE LETTER = 'A'
then the application would generate
SELECT * FROM (VALUES ('A', 1), ('B', 2), ('C', 2)) AS TEMP(LETTER,
NUMBER) WHERE 1=0 WHERE LETTER = 'A'
which is an invalid SQL. Do you agree?

Absolutely. How about (I'm using python):

if (s.find('WHERE') 0):
s = s + ' AND '
That's not reliably. First, WHERE can be a column/table name or be in a
nested subquery. Next, AND binds stronger that OR. So adding "AND 1= 0"
to a where clause like this doesn't have the desired effect:

SELECT ... FROM ... WHERE a = 1 OR b = 2
--
Knut Stolze
DB2 z/OS Admin Enablement
IBM Germany
Mar 12 '07 #12

P: n/a
Knut Stolze wrote:
[...]
That's not reliably. First, WHERE can be a column/table name or be in a
nested subquery. Next, AND binds stronger that OR. So adding "AND 1= 0"
to a where clause like this doesn't have the desired effect:

SELECT ... FROM ... WHERE a = 1 OR b = 2
Yes, you are right but since the users are entitled to write there own
queries there are much worse problems to worry about. Let's say someone
writes a query like:

select * from old table (
delete from the_very_important_table
) X where ...

/Lennart
Mar 12 '07 #13

P: n/a
Lennart wrote:
Knut Stolze wrote:
[...]
>That's not reliably. First, WHERE can be a column/table name or be in a
nested subquery. Next, AND binds stronger that OR. So adding "AND 1= 0"
to a where clause like this doesn't have the desired effect:

SELECT ... FROM ... WHERE a = 1 OR b = 2

Yes, you are right but since the users are entitled to write there own
queries there are much worse problems to worry about. Let's say someone
writes a query like:

select * from old table (
delete from the_very_important_table
) X where ...
Sure. Not to mention GROUP BY, HAVING, ORDER BY etc.

--
Knut Stolze
DB2 z/OS Admin Enablement
IBM Germany
Mar 12 '07 #14

This discussion thread is closed

Replies have been disabled for this discussion.