Home » SQL & PL/SQL » SQL & PL/SQL » STAR - PROGRAM
icon4.gif  STAR - PROGRAM [message #394639] Sun, 29 March 2009 02:00 Go to next message
MR[T]
Messages: 62
Registered: March 2009
Member
i am making a program about horoscope.

When user enter his date of birth ,program tell him his STAR .

First of all i write this query

declare
INPUT_DATE DATE := '&ANY_DATE';
BEGIN
IF TO_CHAR(INPUT_DATE,'DD MON') BETWEEN '07-JUL' AND '22-AUG'
THEN
&D('UR SIGN IS LEO' );
ELSE
&D('UR NOT LEO');
END IF ;
END ;
/
Enter value for any_date: 10-AUG-08
your SIGN IS LEO


IT WORKS FINE


...
BUT WHEN I WRITE THE FOLLOWING QUERY IT GENERATES AN ERROR



DECLARE

INPUT_DATE DATE := '&ANY_DATE';
BEGIN

IF TO_CHAR(INPUT_DATE,'DD MON') BETWEEN '07-JUL' AND '22-AUG'
THEN
&D('UR SIGN IS LEO' ) ;

ELSEIF TO_CHAR(INPUT_DATE,'DD MON') BETWEEN '23-AUG' AND '22-SEP'
THEN
&D('UR SIGN IS PICES' );

ELSE
&D('UR STAR IS NOT IN OUR DATABASE');
END IF ;

END ;
/


Re: STAR - PROGRAM [message #394640 is a reply to message #394639] Sun, 29 March 2009 02:16 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
BUT WHEN I WRITE THE FOLLOWING QUERY IT GENERATES AN ERROR

I do not see any error message in your post.
I see only invalid PL/SQL code. ELSEIF is not valid keyword in Oracle; you may easily find it when studying PL/SQL User's Guide and Reference, available with another Oracle documentation books e.g. online on http://tahiti.oracle.com/.

INPUT_DATE DATE := '&ANY_DATE';

Are you aware, that you depend on implicit conversion using NLS_DATE_FORMAT session parameter?
'&ANY_DATE' is string. Use TO_DATE function to convert it to DATE type explicitly.
IF TO_CHAR(INPUT_DATE,'DD MON') BETWEEN '07-JUL' AND '22-AUG'

Similar problem. You are comparing strings using lexical comparison rule.
So e.g. '01-AUG' does not conform to this condition, as it is less than '07-JUL' ('1' < '7').
Similarly, as '07-JUL' < '08-JAN' < '22-AUG', January 8th conforms this condition.
Use more proper format for comparison (e.g. 'MMDD').
Re: STAR - PROGRAM [message #394646 is a reply to message #394639] Sun, 29 March 2009 04:20 Go to previous messageGo to next message
MR[T]
Messages: 62
Registered: March 2009
Member
Thanks for your reply , Im unable to understand how to convert '&ANY_DATE' into date .
Following program is not giving any error but its not giving required output ..




DECLARE
INPUT_DATE DATE := '&ANY_DATE';
NEW_DATE DATE;
BEGIN

NEW_DATE := TO_DATE(INPUT_DATE) ;

IF TO_CHAR(NEW_DATE,'DD MON') BETWEEN '07-JUL' AND '22-AUG'
THEN
&D('UR SIGN IS LEO' ) ;

ELSIF TO_CHAR(NEW_DATE,'DD MON') BETWEEN '23-AUG' AND '22-SEPT'
THEN
&D('UR SIGN IS PICES' );

ELSE
&D('UR STAR IS NOT IN OUR DATABASE');
END IF ;

END ;

OUTPUT
/
Enter value for any_date: 10-aug-08
your SIGN IS LEO

PL/SQL procedure successfully completed.




Enter value for any_date: 25-AUG-08
UR STAR IS NOT IN OUR DATABASE

PL/SQL procedure successfully completed.

Re: STAR - PROGRAM [message #394649 is a reply to message #394646] Sun, 29 March 2009 05:19 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
MR[T wrote on Sun, 29 March 2009 11:20
Thanks for your reply , Im unable to understand how to convert '&ANY_DATE' into date .

flyboy wrote on Sun, 29 March 2009 09:16
Use TO_DATE function to convert it to DATE type explicitly.

TO_DATE function is documented in SQL Reference, available e.g. online on http://tahiti.oracle.com/.
MR[T wrote on Sun, 29 March 2009 11:20
Following program is not giving any error but its not giving required output ..

As '23-AUG' > '22-SEPT' (as '23' > '22'), nothing falls into this interval.
flyboy wrote on Sun, 29 March 2009 09:16
Use more proper format for comparison (e.g. 'MMDD').

When using this format, comparing leads to '0823' <= '0825' <= '0922'.
Re: STAR - PROGRAM [message #394828 is a reply to message #394640] Mon, 30 March 2009 08:08 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
[quote title=flyboy wrote on Sun, 29 March 2009 03:16]
Quote:

IF TO_CHAR(INPUT_DATE,'DD MON') BETWEEN '07-JUL' AND '22-AUG'

Similar problem. You are comparing strings using lexical comparison rule.
So e.g. '01-AUG' does not conform to this condition, as it is less than '07-JUL' ('1' < '7').



Flyboy, just so as to not confuse the OP, I think you meant '0' < '7' or '01' < '07'
Re: STAR - PROGRAM [message #394845 is a reply to message #394639] Mon, 30 March 2009 09:18 Go to previous messageGo to next message
MR[T]
Messages: 62
Registered: March 2009
Member
I want to create a program that;
Whnen user enter His date OF birth , program tell him his ZODIAC SIGN .

here is my program ., Now i am taking users input and convert it into date using to_date function . and now it generates an error ..

declare
INPUT_DATE DATE := '&ANY_DATE';
BEGIN
IF TO_DATE('INPUT_DATE','FMDD MON') BETWEEN '1-JUN' AND '10-DEC'
THEN
&D('UR SIGN IS LEO' );
ELSE
&D('UR NOT LEO');
END IF ;
END ;


Enter value for any_date: 10-aug-08
declare
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 4

Re: STAR - PROGRAM [message #394846 is a reply to message #394845] Mon, 30 March 2009 09:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to take a step back and get several bits of basic PL/SQL straight.

'1-JUN' and '10-DEC' are strings
If you compare something to a string, you will, generally, perform a string comparison.
This works by working from the left hand side of the string to the right hand side of each string, 1 character at a time, and comparing the ascii values of the 2 characters being looked at.
As soon as one string has an ascii value higher than the other, then the comparison is finished.

So '11' > '10'

But '11' > '1000000'

Similarly, '12-Jan-2009' > '11-Jan-2009'

But '12-Jan-2009' > '11-Jan-2010'
and '12-Jan-2009' > '10-Aug-2009'

You need to wrap those strings in calls to TO_DATE, giving you
BETWEEN TO_DATE('1-JUN','dd-mon') AND TO_DATE('10-DEC','dd-mon')
, assuming you're happy with using the current year as a default.

Secondly, you are using TO_DATE:
TO_DATE('INPUT_DATE','FMDD MON')

This expects a string in the format 'DD MON' or 'D MON'
You are passing in a string in format 'DD-MON-YYYY'

This will raise an exception.
Re: STAR - PROGRAM [message #394853 is a reply to message #394639] Mon, 30 March 2009 10:04 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
There are two function to make conversion between DATE and VARCHAR2 data types:
TO_CHAR converts DATE variable to VARCHAR2.
TO_DATE converts VARCHAR2 variable to DATE.

Anything between two quotes is VARCHAR2: so '1-DEC' is VARCHAR2.
How should Oracle behave if you would state '30-FEB'? Should it act differently than for '1-MAR' (e.g. raise an exception)? If so, it would not be able to treat VARCHAR2s like e.g. 'PISCES' at all. It is on you to specify that the given string shall be converted to DATE data type.

Assigned/compared variables shall have same types. Oracle is able to make implicit conversions, however it is not a good way to rely on them.

Also read (again) about string comparison rules when comparing VARCHAR2s. If you insist on that string format, the only way to make correct comparison is to list all dates inside IN condition:
IF TO_CHAR(INPUT_DATE,'DD MON') IN ( '07 JUL', '08 JUL', <etc.>, '22 AUG' )

Of course, it is unnecessarily long; however as you are unable to change format to 'MMDD', this is the only correct way.

Moreover, just one comment on this:
TO_DATE('INPUT_DATE','FMDD MON')

As anything between two quotes is VARCHAR2, you use VARCHAR2 value 'INPUT_DATE' (not variable). It is impossible to convert it to DATE, that's why the error.
Re: STAR - PROGRAM [message #394854 is a reply to message #394639] Mon, 30 March 2009 10:09 Go to previous messageGo to next message
MR[T]
Messages: 62
Registered: March 2009
Member
thanks alot for your help : )
I hav understand a bit the use of to_date function .
Now i am using to_date function on both sides
and its not giving me any error but still its not giving required output .




declare
INPUT_DATE DATE := '&ANY_DATE';
BEGIN
IF INPUT_DATE BETWEEN TO_DATE('1-AUG','DD-MON') AND TO_DATE('10-AUG','DD-MON')
THEN
&D('UR SIGN IS LEO' );
ELSE
&D('UR NOT LEO');
END IF ;
END ;
/


Enter value for any_date: 10-AUG-08
your NOT LEO

PL/SQL procedure successfully completed.



Can you please write a correct query , that it would help me more to understand the problem.
Re: STAR - PROGRAM [message #394857 is a reply to message #394639] Mon, 30 March 2009 10:13 Go to previous messageGo to next message
MR[T]
Messages: 62
Registered: March 2009
Member
thanks alot Flyboy ,Now its working great .. Smile

Re: STAR - PROGRAM [message #394858 is a reply to message #394854] Mon, 30 March 2009 10:27 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
MR[T wrote on Mon, 30 March 2009 17:09
Now i am using to_date function on both sides

Not everywhere.
INPUT_DATE DATE := '&ANY_DATE';

Left side is DATE. Right side is VARCHAR2. Although it may "work" fine sometimes. Additionally, year contains 4 digits; seems like Y2K problem did not happen strongly enough.
MR[T wrote on Mon, 30 March 2009 17:09
and its not giving me any error but still its not giving required output.

Normally, DATE contains year. When it is not specified in the date format, it takes the current one.
So INPUT_DATE contains year 2008 (or maybe 8 as year is entered with 2 digits).
Dates in compared interval contain current year (2009). INPUT_DATE (year 8 or 2008) does not belong to this interval.

So, comparing VARCHAR2s is not a bad idea; however you have to use the format which preserves order; e.g. 'MMDD', as already suggested. The only special treating is necessary for the year beginning; however you may split it to two conditions (BETWEEN '<lower bound>' AND '1231') OR (BETWEEN '0101' AND '<upper bound>').
Previous Topic: Select Query - Case When?
Next Topic: drop and create index partition [merged]
Goto Forum:
  


Current Time: Sat Dec 10 22:05:26 CST 2016

Total time taken to generate the page: 0.13008 seconds