Home » Developer & Programmer » Reports & Discoverer » if condition in Query
if condition in Query [message #160693] Tue, 28 February 2006 01:49 Go to next message
kamar_19
Messages: 52
Registered: June 2005
Location: pakistan
Member
aoa
i have a table which has column ship_date.
i want to show month as if the month is less than current month then it shows current month and it is greater than current month then the orignal month

please reply me as early as possible

regards

qamar
Re: if condition in Query [message #160700 is a reply to message #160693] Tue, 28 February 2006 02:15 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Take a look at CASE expressions. I created a small test. If the month of hire_date in my EMPLOYEES table is equal or less than the current month, it shows sysdate, else it will show the original hire_date:
SQL> alter session set nls_date_format='DD/MM/YYYY'
  2  /

Session altered.

SQL> SELECT hire_date original
  2       , CASE WHEN TO_CHAR(hire_date,'MM') <= TO_CHAR(SYSDATE,'MM')
  3         THEN
  4           sysdate
  5         ELSE
  6           hire_date
  7         END my_date
  8    FROM employees
  9  /

ORIGINAL   MY_DATE
---------- ----------
17/06/1987 17/06/1987
21/09/1989 21/09/1989
13/01/1993 28/02/2006
03/01/1990 28/02/2006
21/05/1991 21/05/1991
25/06/1997 25/06/1997
05/02/1998 28/02/2006
07/02/1999 28/02/2006
17/08/1994 17/08/1994
16/08/1994 16/08/1994
28/09/1997 28/09/1997
...<snip>...
Another option would be the GREATEST built-in:
SQL> SELECT to_char(hire_date,'MM') original
  2       , GREATEST(to_char(hire_date,'MM')
  3                 ,to_char(SYSDATE,'MM')
  4                 ) my_month
  5    FROM employees
  6  /

OR MY
-- --
06 06
09 09
01 02
01 02
05 05
06 06
02 02
02 02
08 08
08 08
09 09
...<snip> ...


MHE
Previous Topic: Trial Balance Report
Next Topic: To avoid recurring values.
Goto Forum:
  


Current Time: Fri Jun 28 15:26:23 CDT 2024