January 10, 2012 at 4:02 pm
Creating a report with Oracle 9i backend database.
Setup two parameters
@startdate
@enddate
Select * from mytable
Where mytable.date Between @startdate And @enddate
I received error message bacause oracle requriment date form as 'DD-MOn-YYYY', such as '10-Jan-2012'
I test the below qury, it works in the report:
Select * from mytable
Where mytable.date Between '1-Jan-2012' And '10-Jan-2012'
What the sql should be to conver @startdate to 'DD-MOn-YYYY'?
I found a qury which can convert date to the correct format. SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-'). However it doesn't work me enven it works as stand alone qury.
So, What correct sql should be to conver @startdate to 'DD-MOn-YYYY' for my report needs?
Thanks a lot!
January 10, 2012 at 8:39 pm
you parameters are datetime in the report, right, so they will convert cleanly to the DATE datatype for oracle?
It sounds like you are using varchars instead...
if your parameters are datetime, that should immediately address the issue, i believe.
if you are building a string for the SQL statement to be executed, then use the built in TO_DATE function, and tell it the format of the string.
Select *
from mytable
Where mytable.date
Between TO_DATE(@startdate,'MM-DD-YYYY')
And TO_DATE(@@enddate,'MM-DD-YYYY')
Lowell
January 11, 2012 at 12:20 am
Thanks your replying.
Follow your syntax, I received error message:
"An error occurred during local report processing,
An error has occurred during report processing,
Query execution failed for dataset "DataSet1"
ORA-01858: a non-numeric character was found where a numeric was expected"
The parameter type either as datetime or text(string) doesn't make difference.
By the way, I only used one @in front of enddate
What I may missed?
January 11, 2012 at 5:22 am
when i said parameters, i mean on the report side, sorry for the confusion.
if you are building a string and executing it from your report, without parameters, you need to follow the full oracle syntax and rules...oracle doesn't allow variables with @ in them.
I don't have oracle handy right now, but here's the basic syntax, I believe.
the param_ replacing @ is not required, iot's just a naming convention and is completely optional, i just used it for clarity.
DECLARE
param_bdate DATE;
param_edate DATE;
SET param_bdate = TO_DATE('01/15/2010','MM/DD/YYYY');
SET param_edate = TO_DATE('01/15/2010','MM/DD/YYYY');
Select * from mytable
Where mytable.date Between param_bdate AND param_edate ;
Lowell
January 11, 2012 at 2:05 pm
Where should I put below code:
DECLARE
param_bdate DATE;
param_edate DATE;
SET param_bdate = TO_DATE('01/15/2010','MM/DD/YYYY');
SET param_edate = TO_DATE('01/15/2010','MM/DD/YYYY');
When I put above in the Query text area. The error message below:
Could not update a list of fields for the query. Verify that you can connect to the dat source and that your query syntax is correct.
ORA-06550: line1, colum 8:
PLS-00103: Encountered the symbol "" whenexpecting ofe of the following:
begin function package pragma procedure subteype type use <an identifier> <a dobule-quoted delimited-identifier> form current cursor.
Thanks a lot
January 11, 2012 at 2:49 pm
I'm assuming the target database is Oracle and that the datatype of mytable.date is DATE.
If this is the case you have two options, option #1 is to write a stored procedure that accepts the two parameters; option #2 is to send the parameters alongside your query which arguable defeats the purpose(*).
Having said that, code below shows how to use bind-variables in your Oracle sql code.
VARIABLE STARTDATE VARCHAR2(11)
EXEC :STARTDATE := '01-JAN-2012'
VARIABLE ENDDATE VARCHAR2(11)
EXEC :ENDDATE := '10-JAN-2012'
SELECT *
FROM MYTABLE
WHERE MYTABLE.DATE BETWEEN TO_DATE(:STARTDATE, 'dd-mon-yyyy')
AND TO_DATE(:ENDDATE, 'dd-mon-yyyy')
;
Hope this helps.
(*) exception would be a really well organized environment where no literals are allowed on queries.
_____________________________________
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.January 25, 2012 at 10:30 pm
during the passed days, I tried many times. However it still doesn't work for me.
The error message said: ORA-00900: invalid SQL statement
It looks like I may not declare the variable or parameter right, or may not put them in the right place.
Such as can I mix the SQL statement with all other inside the qurey build window or not?
Thanks!
January 26, 2012 at 6:03 am
Seattlemsp (1/25/2012)
during the passed days, I tried many times. However it still doesn't work for me.The error message said: ORA-00900: invalid SQL statement
It looks like I may not declare the variable or parameter right, or may not put them in the right place.
Such as can I mix the SQL statement with all other inside the qurey build window or not?
Hard to tell if you do not show us what you are doing but for sure something is wrong with the syntax, check below:
SQL>
SQL> variable startdate varchar2(11)
SQL> exec :startdate := '01-JAN-2011'
PL/SQL procedure successfully completed.
SQL> variable enddate varchar2(11)
SQL> exec :enddate := '31-DEC-2011'
PL/SQL procedure successfully completed.
SQL> select created from v$database where created between to_date(:startdate, 'dd-mon-yyyy') and to_date(:enddate, 'dd-mon-yyyy') ;
CREATED
------------------
11-AUG-11
SQL>
_____________________________________
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.January 27, 2012 at 10:26 am
Paul ---Thanks a lot for contiuning help me.
Your code works in the SQLPlus. How can I convert it into BIDS(SSRS) format, so I can create a report in BIDS(stripdown Microsoft Visual Studio). Then the user can key in the date when run the report via SSRS.
January 27, 2012 at 10:32 am
Paul ---Thanks a lot for contiuning help me.
Your code works in the SQLPlus. How can I convert it into BIDS(SSRS) format, so I can create a report in BIDS(stripdown Microsoft Visual Studio). Then the user can key in the date when run the report via SSRS.
January 27, 2012 at 1:51 pm
I got it!
I made it too complex. It shoudl be just use :mydate instead of @mydate. Only drackback is the user has to put in the correct date format --- DD-Mon-YYYY
Thanks you all!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply