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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
13  37998 
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
[....]
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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
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
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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
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
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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
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
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
[...]
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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Matt |
last post by:
When the ASP statement end with a _ character, then the next line cannot
have comment ' character. Is that correct? Since I encountered the following
error:
Microsoft VBScript compilation...
|
by: mirth |
last post by:
I would like to update a decimal column in a temporary table based on
a set of Glcodes from another table. I search for a set of codes and
then want to sum the value for each row matching the...
|
by: Alan |
last post by:
I have many OR in the if statement, any method to simplify?
if (val == 5 | val == 9 | val == 34 | val == 111 | val == 131 | .......)
// ....
thank you
|
by: mark |
last post by:
Access 2000:
I creating a report that has a record source built by the user who selects the
WHERE values. An example is:
SELECT * FROM CHARGELOG WHERE STDATE Between #10/27/2003# And...
|
by: khan |
last post by:
on sale report I want to make a check if customer doesnot pay full
amount then his dues go in to his account recieveable table. I am
trying this in vba and when I complie it on RunSQL it gives me...
|
by: Nerox |
last post by:
Hi, If i write:
#include <stdio.h>
int foo(int);
int main(void){
int a = 3;
foo(a);
}
|
by: Keith |
last post by:
Good Afternoon,
New to .Net. I am trying to pass date/time values to a MS Access query depending on what value is selected from a dropdown list box (January, February, etc). I have declared...
|
by: Gary Wessle |
last post by:
Hi
often I need to read numbers and only keep the highest or lowest.
so I do something like
int uLimit = 0;
int lLimit = 999999999999; //hoping the compiler will not complain
uLimit =...
|
by: Joshepmichel |
last post by:
Please to help me to following problem
I want to do this
1. create Table Name MEMBER on the Database Name "mytestdb",
2. Add the Values to the Table through the Key board Inputs during running...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
