|
I have a general SQL Question, and hope that I can find an answer here: Example:
What I need is to call myproc and also get the max(emp_id). My first try:
Drawback: myproc will be called 2 times Is there a way to get the same result with avoiding the double call of myproc |
|
Question: How do I write a query that makes no sense? Answer: Use a feature that makes no sense, such as LATERAL or WINDOW:) In this case LATERAL doesn't seem to help, but brute-force WINDOW seems to work... select max ( emp_id ) over all_rows_window as max_emp,
*
from myproc()
window all_rows_window as ( rows between unbounded preceding and unbounded following );
max_emp emp_id firstname lastname project_name
----------- ----------- ---------- ---------- ------------
2 1 James smith Project1
2 2 Michael Smith Project2
2 2 Michael Smith Project3
Volker's suggestion of OVER() makes the code shorter... select min ( emp_id ) over() as min_emp,
max ( emp_id ) over() as max_emp,
min ( firstname ) over() as min_firstname,
max ( firstname ) over() as max_firstname,
min ( lastname ) over() as min_lastname,
max ( lastname ) over() as max_lastname,
min ( project_name ) over() as min_project_name,
max ( project_name ) over() as max_project_name,
*
from myproc();
min_emp max_emp min_firstname max_firstname min_lastname max_lastname min_project_name max_project_name emp_id firstname lastname project_name
----------- ----------- ------------- ------------- ------------ ------------ ---------------- ---------------- ----------- ---------- ---------- ------------
1 2 James Michael smith smith Project1 Project3 1 James smith Project1
1 2 James Michael smith smith Project1 Project3 2 Michael Smith Project2
1 2 James Michael smith smith Project1 Project3 2 Michael Smith Project3
Well, if the query really must avoid using the underlying tables and must not call the stored procedure twice, I guess a WINDOW function is the way to go. Note, you can omit the WINDOW clause here as the default window sizes are identical to "rows between unbounded preceding and unbounded following" (but omitting that may be less comprehensible). select max(emp_id) over () as max_emp, * from myproc();
(10 Jul '20, 02:22)
Volker Barth
Replies hidden
Replies hidden
Thanks for the help, but the answer from @Volker would help better in my case. But nice to to know about WINDOW function (it is new for me).
(10 Jul '20, 11:37)
Baron
Replies hidden
1
With OVER() you can calculate MIN, MAX, etc, on all the columns all at once, with no GROUP BY, and only one call to myproc() is executed. The performance is probably excellent since all the rows are being retrieved anyway.
(10 Jul '20, 13:41)
Breck Carter
Yeah, but... it was just the example that made no sense. The bigger question is "How do I use aggregate functions without the limitations imposed by GROUP BY?" OVER() is just like leaving out the GROUP BY altogether by specifying a "WINDOW over everything". I think I've written a lot of GROUP BY queries that would be simpler with OVER().
(10 Jul '20, 13:53)
Breck Carter
> may be less comprehensible No, no, it's brilliant! You're thinking outside the window! :) The OVER() clause opens up a whole realm of new possibilities! Any day without a GROUP BY is a good day!
(10 Jul '20, 13:59)
Breck Carter
@breck carter, thanks again, it worth to go with OVER().
(10 Jul '20, 18:46)
Baron
|
|
I am no SQL wizard, but did you mean something like this? Or are you trying to avoid two queries in myproc? create or replace procedure myproc ( out max_id int )
begin
select max(e.emp_id) into max_id from Employees e, Projects p where e.emp_id = p.emp_id;
select e.emp_id, firstname, lastname, project_name from Employees e, Projects p where e.emp_id = p.emp_id;
end;
BEGIN
declare my_max int;
select * from myproc( my_max );
select my_max;
END
Well, "SQL Wizards" would probably omit procedural logic and use joins and derived tables and probably a local view (common table expression), such as: with CTE as
(select e.emp_id, firstname, lastname, project_name
from Employees e inner join Projects p on e.emp_id = p.emp_id)
select CTE_Max.*, CTE.*
from (select max(emp_id) as max_emp from CTE) CTE_Max
cross join CTE
And IMVHO for most queries selecting some "max row" result set, one would not use a CROSS JOIN to list a max value and all rows but only those rows who fit the max value, so using something like "inner join CTE on max_emp = CTE.emp_id" or the like...:)
(10 Jul '20, 02:31)
Volker Barth
Dang! In a previous life, you probably wrote APL code. :-)
(10 Jul '20, 02:46)
JBSchueler
Replies hidden
No, I had to look up what APL is - but I guess I got the point:)
(10 Jul '20, 02:50)
Volker Barth
@JBSchueler, the point is that I dont want to change the original procedure (which is quite complicated enough). I want to keep the same procedure and use its result, so decided to go with a wrapper procedure and a temp table. Thanks
(10 Jul '20, 12:08)
Baron
|
|
You are really trying to get two pieces of unrelated information in one query... Me? I would do it like this select * from myproc() Order By emp_id DESC The very first row will be the Max emp_id .. If your report is custom, pick it out and use it in your report... If you are using a commercial reporting tool it probably has functions to pick out the MAX or First as well... Yes your approach is OK, but my example was very simple (compared to my real case). In reality the procedure is much more bigger, and I have to make aggregate function on more than one column (not only on emp_id). Moreover, I am requested to deliver Max/Min values for some columns (so order by can not be the solution).
(10 Jul '20, 11:29)
Baron
Replies hidden
> aggregate function on more than one column See update to earlier WINDOW answer.
(10 Jul '20, 13:45)
Breck Carter
|
Is this question focusing on using a procedure or more generally on how to get both all (or some) rows of a table/join and additionally an aggregated value? And do you really want the result set to contain all values from the join or just some of them (or just the one with the max emp_id)? As usual, a sample result set would be helpful.
I have already the procedure myproc (written since years) which is pretty complicated and returns big amount of data (several thousands of rows * several tens of columns).
I am using this myproc to build a report, and now decided to add some header data to the report (resulting from aggregate functions on some of the existing columns).
Since I dont want to change the procedure itself, I decided to go with this approach.
So, to your question, yes I want really the result set to contain all values.
Well, then Breck's approach with WINDOW functions should be fine for you - or build a wrapper procedure that selects that procedure's result set into a temporary table, does some more calculations/aggregating on the latter and selects from the temporary table.
Since I need to make different aggregate functions on different columns, then the best solution for me is with temp table and wrapper procedure (Despite I avoid always using temp tables).
BTW, my first try has another big drawback:
Since two consequent calls on the same procedure could deliver different results.
@Volker, BTW, what is the difference (from performance point of view) between writing the results of the procedure in a temp table, and declaring a cursor on the result set of the procedure?
I don't know - but I suggest to ask that as a separate question...