Is there an efficient way to avoid UNIONs of fixed values in SQL?
For example with Oracle: I want some temporary table to be in used for a Merge:
MERGE INTO my_target_table
USING (
WITH tbl1 AS ( SELECT 'a' col1 FROM dual -- <--- THIS
UNION SELECT 'foo' col1 FROM dual -- <--- IS
UNION SELECT 'doh' col1 FROM dual -- <--- CRAPPY
),
tbl2 AS ( SELECT 'b' col2, 'c' col3 FROM dual -- <--- THIS
UNION SELECT 'x' col2, 's' col3 FROM dual -- <--- ALSO
)
SELECT col1, col2, col3 FROM tbl1 CROSS JOIN tbl2
) my_source_view
ON ( my_target_table.col1 = my_source_view.col1
AND my_target_table.col2 = my_source_view.col2
)
WHEN MATCHED THEN UPDATE
SET my_target_table.col3 = my_source_view.col3
WHEN NOT MATCHED THEN INSERT( col1, col2, col3)
VALUES(my_source_view.col1, my_source_view.col2, my_source_view.col3)
;
The SELECT UNION SELECT pattern here is really an开发者_开发知识库noying and I find it hideous (repetitive, verbose).
Do you have a trick to forge similar queries, be it Oracle specific or not?
Thank you
Benoit,
You can use this for tables with one column:
SQL> with tbl1 as
2 ( select column_value col1
3 from table(sys.dbms_debug_vc2coll('a','foo','doh'))
4 )
5 select *
6 from tbl1
7 /
COL1
------------------------------------------------------------
a
foo
doh
3 rows selected.
For more than one column you'd need to create two types (or use existing ones), like this:
SQL> create type ot is object
2 ( col1 varchar2(1)
3 , col2 varchar2(1)
4 );
5 /
Type created.
SQL> create type ntt is table of ot;
2 /
Type created.
SQL> with tbl2 as
2 ( select *
3 from table(ntt(ot('b','c'),ot('x','s')))
4 )
5 select *
6 from tbl2
7 /
C C
- -
b c
x s
2 rows selected.
Regards,
Rob.
In SQL Server 2008 you can use the VALUES clause. Not sure if this is available in Oracle or not but you do specifically request all techniques.
Example syntax
SELECT col1 FROM (VALUES ('a'),('foo'), ('doh')) T (col1)
There are several ways to do this (as already indicated above, here are a few more)
all of these are from the asktom question: http://tkyte.blogspot.com/2006/06/varying-in-lists.html and http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:210612357425
var mystring varchar2
begin
:mystring := trim('a,foo,doh,b,x');
end;
/
SELECT
substr(v.mystring, v.start_of_string, v.next_comma_location - v.start_of_string) "The String"
FROM (SELECT comma_location + 1 start_of_string,
mystring,
nvl(lead(comma_location, 1) over(ORDER BY comma_location), mystring_length + 1) AS
next_comma_location
FROM (SELECT :mystring mystring,
instr(:mystring, ',', LEVEL) comma_location,
length(:mystring) mystring_length
FROM dual
CONNECT BY LEVEL < length(:mystring))) v
WHERE v.start_of_string < v.next_comma_location;
--or http://laurentschneider.com/wordpress/2007/12/predefined-collections.html
select * from table(sys.odcivarchar2List('a','foo','doh','b','x'));
COLUMN_VALUE
----------------
a
foo
doh
b
x
you can treat these as tables
Well, I agree that
SELECT 'a' col1 FROM dual
UNION SELECT 'foo' col1 FROM dual
UNION SELECT 'doh' col1 FROM dual
is crappy. Would you agree that
SELECT 'a' col1 FROM dual
UNION ALL SELECT 'foo' col1 FROM dual
UNION ALL SELECT 'doh' col1 FROM dual
is not?
EDIT
If you don't like some part of the grammar, you might try dynamic SQL (but do realize that this is your personal preference, so try to restrain from doing it on bigger systems).
Here's a link http://www.oracle-base.com/articles/misc/DynamicInLists.php
加载中,请稍侯......
精彩评论