April 21, 2011 at 6:51 pm
Hello,
I need help to write the following query in Oracle. It seems straightforward, however, I am having a tough time. I am using SQLPlus.
In SQL 2005 I would write it as:
DECLARE @date Datetime
SELECT @date = MAX(CreateDate) FROM tbl1
SELECT * FROM tbl2
WHERE CreationDate = @date
So basically, I am trying to get maximum date from tbl1 and use it in tbl2.
Thanks for your time!
April 23, 2011 at 1:24 pm
-- Please not that I can't test because I do not have Oracle installed
-- but this is basically how you do it.
-- This is any anonymous block
DECLARE v_date DATE
-- Don't used reserved word "Date"
SELECT MAX(SomeDate) FROM tbl1
INTO v_Date
SELECT * FROM tbl2
WHERE CreationDate = v_Date
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 28, 2011 at 4:02 am
Thanks Welsh.
April 28, 2011 at 2:26 pm
Building on Welsh's post, block on sqlplus should look like..
set serverout on
declare mydate date;
begin
select max(CreateDate) into mydate from tbl1;
-- do whateve you have to do with variable mydate --
end;
/
Please note, if you want to return values from inside the block you have to put those values in some place, probably a cursor then show them resorting to dbms_output.put_line() function.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 28, 2011 at 2:31 pm
You are correct for I only included an anonymous block as opposed to a procedure.
A Procedure would In include DECLARE PROCEDURE MyProc
AS...
etc...
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 29, 2011 at 10:59 am
Welsh Corgi (4/28/2011)A Procedure would In include DECLARE PROCEDURE MyProc
I'm sure you mean "create or replace procedure MyProc as..."
Having said that, script I shared earlier runs in sqlplus as poster asked for. 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 29, 2011 at 11:27 am
Yes, Thank you for correcting me. 🙂
CREATE OR REPLACE PROCEDURE procedure_name
[(parameter1 [mode] datatype1.
parameter2 [mode] datatype2,...)]
IS|AS
[local_variable_declarations; ...]
BEGIN
-- actions;
END [procedure_name];
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply