You are too modest - your SQL is well and concisely written given the task you are undertaking. A few pointers:
t1.name <> t2.name
is always true if t1.name = REPLACE(t2.name, 'DUP_', '')
- you can drop the former
- usually you want
union all
. union
means union all
then drop duplicates. It might make no difference in this case but always using union all
is a good habit unless you explicitly want to drop any duplicates.
if you are willing for the numeric comparisons to happen after casting to varchar, the following might be worth considering:
create view test_attribs_cast as
select id, name, attr1, attr2, cast(attr3 as varchar(2000)) as attr3,
cast(attr4 as varchar(2000)) as attr4, attr5
from test_attribs;
create view test_attribs_unpivot as
select id, name, 1 as attr#, attr1 as attr from test_attribs_cast union all
select id, name, 2, attr2 from test_attribs_cast union all
select id, name, 3, attr3 from test_attribs_cast union all
select id, name, 4, attr4 from test_attribs_cast union all
select id, name, 5, attr5 from test_attribs_cast;
select 'attr'||t1.attr# as different, t1.id as id_1, t2.id as id_2, t1.name,
t2.name as name_dup, t1.attr as val1, t2.attr as val2
from test_attribs_unpivot t1 join test_attribs_unpivot t2 on(
t1.id<>t2.id and
t1.name = replace(t2.name, 'DUP_', '') and
t1.attr#=t2.attr# )
where t1.attr<>t2.attr or (t1.attr is null and t2.attr is not null)
or (t1.attr is not null and t2.attr is null);
the second view is a kind of unpivot
operation - if you are on at least 11g you can do this more concisely with the unpivot
clause - see here for an example
- I'm say don't go down the procedural route if you can do it in SQL, but...
- Dynamic SQL is probably worth considering despite the problems you mention with testing and maintenance
--EDIT--
To answer the more general side of the question, there are techniques to reduce repetition in SQL, including:
But you can't bring OO ideas into the SQL world directly - in many cases repetition is fine if the query is readable and well-written, and it would be unwise to resort to dynamic SQL (for example) just to avoid repetition.
The final query including Leigh's suggested change and a CTE instead of a view could look something like this:
with t as ( select id, name, attr#,
decode(attr#,1,attr1,2,attr2,3,attr3,4,attr4,attr5) attr
from test_attribs
cross join (select rownum attr# from dual connect by rownum<=5))
select 'attr'||t1.attr# as different, t1.id as id_1, t2.id as id_2, t1.name,
t2.name as name_dup, t1.attr as val1, t2.attr as val2
from t t1 join test_attribs_unpivot t2
on( t1.id<>t2.id and
t1.name = replace(t2.name, 'DUP_', '') and
t1.attr#=t2.attr# )
where t1.attr<>t2.attr or (t1.attr is null and t2.attr is not null)
or (t1.attr is not null and t2.attr is null);
I think these answers are all getting a) way too complicated, and b) off-track relative to what user2023861 wants to accomplish:
"I am trying to declare a variable, then select that variable." means two different things in either PL/SQL or in SQLplus, and perhaps two more things in DBArtisan. To accomplish the objective in SQLplus, use "Substitution variables"...excellent background is found here.
My example, doesn't precisely follow your SQL Server T-SQL example, but it's close and is NOT procedural (read: no anonymous PL/SQL block.)
PL/SQL with Substitution variables:
DEFINE min_salary = 1000
DEFINE mgrID = 7
select *
from employees
where
salary > &min_salary
and mgrID = &mgrID
;
All at the SQL-Plus prompt....from my fictional employees table, I got this result:
SALARY MGRID
---------- ----------
2000 7
1001 7
2 row selected.
If you want to make this semi-procedural, then you use BIND Variables and the declarative syntax of PL/SQL similar to T-SQL. (In my opinion this is a HUGE waste of effort, but is included to help clarify variable types.)
SQLPlus using PL/SQL Bind variables:
VARIABLE v_in_min_salary number;
VARIABLE v_mgrID number; /* vars are defined within SQL+ */
VARIABLE v_out_salary number;
VARIABLE v_out_mgrID number;
BEGIN
:v_in_min_salary := 1000; /* vars are assigned values in PL/SQL */
:v_mgrID := 7;
select salary, mgrID
into :v_out_salary, :v_out_mgrID
from employees
where salary > :v_in_min_salary and mgrID = :v_mgrID
;
end;
/
PRINT :v_in_min_salary /* here the in and out vars are displayed */
PRINT :v_mgrID
PRINT :v_out_salary
PRINT :v_out_mgrID
Fully-procedural approach using PL/SQL:
set serveroutput on; /* environment setting within SQLplus to see output */
DECLARE
v_in_min_salary number := 1000;
v_mgrID number :=7;
v_out_salary number;
v_out_mgrID number;
BEGIN
SELECT salary, mgrID
into :v_out_salary, :v_out_mgrID
from employees
where salary > :v_in_min_salary and mgrID = :v_mgrID
;
DBMS_OUTPUT.PUT_LINE('Salary Output is= '||:v_out_salary);
DBMS_OUTPUT.PUT_LINE('Manager ID = '||:v_out_mgrID);
END;
/
So, when do I use each case? I use SQLplus Substitution variables 75% of the time when I write SQL scripts that I want to parameterize the input for. Executing a script at the SQLplus prompt allows passing inbound arguments, so my favorite Substitution variables are &1 and &2 as in the following example:
@get_table_size.sql <schema> <table_name>
which in practice becomes
@get_table_size jason employees
and executes SQL code similar to
SELECT extents
FROM all_tables
WHERE UPPER(table_owner) = upper('&1') /* inbound first argument */
and UPPER(table_name) = upper('&2') /* inbound 2nd argument */
;
Notice how the inbound arguments are assigned into substitution variables, then used in the SQL statement. I never use the middle example, and I always use the 3rd examples (procedural PL/SQL) when writing functions, procs, and packages.
Best Answer
In Oracle you use (for dynamic SQL):
Or
Like the examples here: https://www.guru99.com/dynamic-sql-pl-sql.html