VALUES statement

[Gregor =?UTF-8?B?S292YcSN?=]

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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Lennart]

On Mar 9, 1:31 pm, Gregor Kova� <gregor.ko...@m ikropis.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

[....]

[Gregor =?UTF-8?B?S292YcSN?=]

Lennart wrote:

On Mar 9, 1:31 pm, Gregor Kova� <gregor.ko...@m ikropis.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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Serge Rielau]

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

[Gregor =?UTF-8?B?S292YcSN?=]

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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Lennart]

On Mar 9, 2:25 pm, Gregor Kova� <gregor.ko...@m ikropis.siwrote :

Lennart wrote:

On Mar 9, 1:31 pm, Gregor Kova� <gregor.ko...@m ikropis.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

[Gregor =?UTF-8?B?S292YcSN?=]

Lennart wrote:

On Mar 9, 2:25 pm, Gregor Kova� <gregor.ko...@m ikropis.siwrote :

>Lennart wrote:

On Mar 9, 1:31 pm, Gregor Kova� <gregor.ko...@m ikropis.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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Serge Rielau]

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

[Lennart]

On Mar 9, 3:36 pm, Gregor Kova� <gregor.ko...@m ikropis.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

[...]

[Gregor =?UTF-8?B?S292YcSN?=]

Lennart wrote:

On Mar 9, 3:36 pm, Gregor Kova� <gregor.ko...@m ikropis.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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-