438,287 Members | 1,309 Online 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
13 Replies

 P: n/a On Mar 9, 1:31 pm, Gregor KovaÄ

 P: n/a Lennart wrote: On Mar 9, 1:31 pm, Gregor KovaÄ 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 1B 2C 2It 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Ä

 P: n/a Lennart wrote: On Mar 9, 2:25 pm, Gregor KovaÄ Lennart wrote: On Mar 9, 1:31 pm, Gregor KovaÄ 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 1B 2C 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 atable in a GUI mask. So the application that generates this query must beaware that the table is empty and generate appropriate SQL.In the line of:SELECT * FROM (VALUES ('a', 1) EXCEPT ALL VALUES ('b', 2)) ASTEMP(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Ä >>> [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Ä 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 generateSELECT * 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 begenerated (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Ä 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 generateSELECT * 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 anested 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. 