I am new to TOAD and Oracle. This is SQL code, How do I do this in TOAD to get the result set and without the popup window which ask for a value input. This process will run daily to extract data, so flexibility is needed with the dates.
DECLARE @tempdate datetime
SET @tempdate = (SELECT case when DATEPART(dw,GETDATE()) = 2 then dateadd(dd,-3,getdate() )
else dateadd(dd,-1,getdate() )
End
)
Select * from ABC.TRAN where ENTRYDATE = @tempdate
I was able to come up with this so far. I am very new to TOAD and Oracle and need to have this done for a project.
DECLARE tempdate DATE;
BEGIN
SELECT case when (SELECT RTRIM(to_char(sysdate,‘day’)) from dual) = ‘monday’
then TRUNC(SYSDATE) -3
Else SYSDATE -1
End
into tempdate
FROM dual;
SELECT * from ABC.TRAN where ENTRYDATE = tempdate;
END;
I am getting the error
ORA-06550: line 12 column 1: PLS-00428: an INTO clause is expected in this SELECT statement
on the line
SELECT * from ABC.TRAN where ENTRYDATE = tempdate;
I need to generate the output to screen and also to a (|) delimited file.
If you want Toad to export data to a delimited file, then you should be creating SQL, not PL/SQL (It’s PL/SQL if it starts with DECLARE or BEGIN).
You don’t need to use a variable. You can just put the code that you are using to get the variable into your query.
Also you don’t need to select sysdate from dual. You can just specify sysdate.
Try this in the editor, and use F9 to run it.
select *
from ABC.TRAN
where ENTRYDATE = case when RTRIM(to_char(sysdate,‘day’)) = ‘monday’
then TRUNC(SYSDATE) -3
else SYSDATE -1
end;
Assuming this produces a result, to export to delimited text, right-click in the grid below and choose “Export Dataset”. Then you can change export format to “Delimited text” and the rest should be self explanatory.
Hi Mara_b,
Is your project education or work related? As John has replied further down this trail, it's easy to get the data into a csv file from Toad, however, do you have to "show your working" to get the data out into the file? Ie, do the export in SQL commands, or is Toad's grid export option sufficient?
Do you also have to use a variable for the date?
I notice below, that you trunc(sysdate) in one case, but not the other - is that deliberate? Not truncating the date means that the time portion remains for "yesterday" but not for the friday date, that becomes 00:00:00 on the friday. Oracle dates run from dd/mm/yyyy at 00:00:00 to dd/mm/yyyy at 23:59:59 by the way. Or whatever format you prefer for your dates - mm/dd/yyyy if you must! 
To_char(sysdate, 'd') will return a number from 1 to 7. With 1 being sunday usually. Depending on your setting for nls_territory, sunday can also be returned as 7 - some countries have sunday as the first day of the week, while others prefer it as the last day of the week.
In a pl/sql code's select statement, you must use the into clause. So your second select needs an into. You look to need a ref cursor to collect tge record set returned by the select.
It's never a good idea to select * either. It means that any additional columns added to the table render your code obsolete until you find each and every select * and add a new variable to the into clause to hold the additional columns. Only select the columns you need - plus it saves memory and network traffic too.
Good luck.
Cheers,
Norm. [TeamT]
On 18 January 2018 14:32:27 GMT+00:00, Mara_B bounce-Mara_B@toadworld.com wrote:
RE: How do I get multiple data records from a query using variables
Reply by Mara_B
I was able to come up with this so far. I am very new to TOAD and Oracle and need to have this done for a project.
DECLARE tempdate DATE;
BEGIN
SELECT case when (SELECT RTRIM(to_char(sysdate,'day')) from dual) = 'monday'
then TRUNC(SYSDATE) -3
Else SYSDATE -1
End
into tempdate
FROM dual;
SELECT * from ABC.TRAN where ENTRYDATE = tempdate;
END;
I am getting the error
ORA-06550: line 12 column 1: PLS-00428: an INTO clause is expected in this SELECT statement
on the line
SELECT * from ABC.TRAN where ENTRYDATE = tempdate;
I need to generate the output to screen and also to a (|) delimited file.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
Thanks John That worked perfectly
Thanks Norm, I needed the To_char