1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help wth SQL query please. NUBE HERE. cannot figure out the 3rd condition

Discussion in 'SQL PL/SQL' started by nerd_buzz, May 14, 2013.

  1. nerd_buzz

    nerd_buzz Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi guys, i needed help figuring out the code for the "salary review date". I am a basic beginner trying to get used to SQL. the problem is attached as a pic. Thank You SO much ! I even attached what i have so far :)
     

    Attached Files:

  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Are you looking Practice3 solution? Can you please explain exactly what you are looking for ?
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Follow this example

    Code (SQL):
    SELECT NEXT_DAY(TRUNC(ADD_MONTHS(SYSDATE,6),'MON')-1,2) FROM DUAL;
     
    nerd_buzz likes this.
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    TO get day as Fourteenth or Twenty like that you need to use as following:

    Code (Text):

    select to_char(sysdate,'Ddspth') from dual;
    Output:
    --------
    Fourteenth
     
     
    nerd_buzz likes this.
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your query is fine except for the formatting of the date string; this can be a bit difficult for one new to SQL*Plus. Let's look at the string, what it needs to look like and how you can code the to_char format string to achieve the desired results.

    Code (SQL):
    SQL>
    SQL> --
    SQL> -- Return the Day of the week spelled out
    SQL> --
    SQL> SELECT last_name, hire_date, to_char(add_months(hire_date, 6), 'Day') review
      2  FROM employees
      3  WHERE rownum < 5
      4  /
     
    LAST_NAME                 HIRE_DATE REVIEW
    ------------------------- --------- ---------
    King                      17-JUN-87 Thursday
    Kochhar                   21-SEP-89 Wednesday
    De Haan                   13-JAN-93 Tuesday
    Hunold                    03-JAN-90 Tuesday
     
    4 ROWS selected.
     
    SQL>
    SQL> --
    SQL> -- Return the day of the month, spelled out with the proper suffix
    SQL> --
    SQL> SELECT last_name, hire_date, to_char(add_months(hire_date, 6), 'Ddspth') review
      2  FROM employees
      3  WHERE rownum < 5
      4  /
     
    LAST_NAME                 HIRE_DATE REVIEW
    ------------------------- --------- --------------
    King                      17-JUN-87 Seventeenth
    Kochhar                   21-SEP-89 Twenty-FIRST
    De Haan                   13-JAN-93 Thirteenth
    Hunold                    03-JAN-90 Third
     
    4 ROWS selected.
     
    SQL>
    SQL> --
    SQL> -- Add additional non-format text to the output
    SQL> --
    SQL> SELECT last_name, hire_date, to_char(add_months(hire_date, 6), 'Day", the "Ddspth" of "Month, RRRR') review
      2  FROM employees
      3  WHERE rownum < 5
      4  /
     
    LAST_NAME                 HIRE_DATE REVIEW
    ------------------------- --------- ------------------------------------------------
    King                      17-JUN-87 Thursday , the Seventeenth OF December , 1987
    Kochhar                   21-SEP-89 Wednesday, the Twenty-FIRST OF March    , 1990
    De Haan                   13-JAN-93 Tuesday  , the Thirteenth OF July     , 1993
    Hunold                    03-JAN-90 Tuesday  , the Third OF July     , 1990
     
    4 ROWS selected.
     
    SQL>
    SQL> --
    SQL> -- Add additional non-format text to the output
    SQL> -- Return the following Monday for the date generated
    SQL> --
    SQL> SELECT last_name, hire_date, to_char(next_day(add_months(hire_date, 6), 'MONDAY'), 'Day", the "Ddspth" of "Month, RRRR') review
      2  FROM employees
      3  /
     
    LAST_NAME                 HIRE_DATE REVIEW
    ------------------------- --------- ------------------------------------------------
    King                      17-JUN-87 Monday   , the Twenty-FIRST OF December , 1987
    Kochhar                   21-SEP-89 Monday   , the Twenty-Sixth OF March    , 1990
    De Haan                   13-JAN-93 Monday   , the Nineteenth OF July     , 1993
    Hunold                    03-JAN-90 Monday   , the Ninth OF July     , 1990
    Ernst                     21-MAY-91 Monday   , the Twenty-Fifth OF November , 1991
    Austin                    25-JUN-97 Monday   , the Twenty-Ninth OF December , 1997
    Pataballa                 05-FEB-98 Monday   , the Tenth OF August   , 1998
    Lorentz                   07-FEB-99 Monday   , the Ninth OF August   , 1999
    Greenberg                 17-AUG-94 Monday   , the Twentieth OF February , 1995
    Faviet                    16-AUG-94 Monday   , the Twentieth OF February , 1995
    Chen                      28-SEP-97 Monday   , the Thirtieth OF March    , 1998
    Sciarra                   30-SEP-97 Monday   , the Sixth OF April    , 1998
    Urman                     07-MAR-98 Monday   , the Fourteenth OF September, 1998
    Popp                      07-DEC-99 Monday   , the Twelfth OF June     , 2000
    Raphaely                  07-DEC-94 Monday   , the Twelfth OF June     , 1995
    Khoo                      18-MAY-95 Monday   , the Twentieth OF November , 1995
    Baida                     24-DEC-97 Monday   , the Twenty-Ninth OF June     , 1998
    Tobias                    24-JUL-97 Monday   , the Twenty-Sixth OF January  , 1998
    Himuro                    15-NOV-98 Monday   , the Seventeenth OF May      , 1999
    Colmenares                10-AUG-99 Monday   , the Fourteenth OF February , 2000
    Weiss                     18-JUL-96 Monday   , the Twentieth OF January  , 1997
    Fripp                     10-APR-97 Monday   , the Thirteenth OF October  , 1997
    Kaufling                  01-MAY-95 Monday   , the Sixth OF November , 1995
    Vollman                   10-OCT-97 Monday   , the Thirteenth OF April    , 1998
    Mourgos                   16-NOV-99 Monday   , the Twenty-SECOND OF May      , 2000
    Nayer                     16-JUL-97 Monday   , the Nineteenth OF January  , 1998
    Mikkilineni               28-SEP-98 Monday   , the Twenty-Ninth OF March    , 1999
    Landry                    14-JAN-99 Monday   , the Nineteenth OF July     , 1999
    Markle                    08-MAR-00 Monday   , the Eleventh OF September, 2000
    Bissot                    20-AUG-97 Monday   , the Twenty-Third OF February , 1998
    Atkinson                  30-OCT-97 Monday   , the Fourth OF May      , 1998
    Marlow                    16-FEB-97 Monday   , the Eighteenth OF August   , 1997
    Olson                     10-APR-99 Monday   , the Eleventh OF October  , 1999
    Mallin                    14-JUN-96 Monday   , the Sixteenth OF December , 1996
    Rogers                    26-AUG-98 Monday   , the FIRST OF March    , 1999
    Gee                       12-DEC-99 Monday   , the Nineteenth OF June     , 2000
    Philtanker                06-FEB-00 Monday   , the Seventh OF August   , 2000
    Ladwig                    14-JUL-95 Monday   , the Fifteenth OF January  , 1996
    Stiles                    26-OCT-97 Monday   , the Twenty-Seventh OF April    , 1998
    Seo                       12-FEB-98 Monday   , the Seventeenth OF August   , 1998
    Patel                     06-APR-98 Monday   , the Twelfth OF October  , 1998
    Rajs                      17-OCT-95 Monday   , the Twenty-SECOND OF April    , 1996
    Davies                    29-JAN-97 Monday   , the Fourth OF August   , 1997
    Matos                     15-MAR-98 Monday   , the Twenty-FIRST OF September, 1998
    Vargas                    09-JUL-98 Monday   , the Eleventh OF January  , 1999
    Russell                   01-OCT-96 Monday   , the Seventh OF April    , 1997
    Partners                  05-JAN-97 Monday   , the Seventh OF July     , 1997
    Errazuriz                 10-MAR-97 Monday   , the Fifteenth OF September, 1997
    Cambrault                 15-OCT-99 Monday   , the Seventeenth OF April    , 2000
    Zlotkey                   29-JAN-00 Monday   , the Thirty-FIRST OF July     , 2000
    Tucker                    30-JAN-97 Monday   , the Fourth OF August   , 1997
    Bernstein                 24-MAR-97 Monday   , the Twenty-Ninth OF September, 1997
    Hall                      20-AUG-97 Monday   , the Twenty-Third OF February , 1998
    Olsen                     30-MAR-98 Monday   , the Fifth OF October  , 1998
    Cambrault                 09-DEC-98 Monday   , the Fourteenth OF June     , 1999
    Tuvault                   23-NOV-99 Monday   , the Twenty-Ninth OF May      , 2000
    King                      30-JAN-96 Monday   , the Fifth OF August   , 1996
    Sully                     04-MAR-96 Monday   , the Ninth OF September, 1996
    McEwen                    01-AUG-96 Monday   , the Third OF February , 1997
    Smith                     10-MAR-97 Monday   , the Fifteenth OF September, 1997
    Doran                     15-DEC-97 Monday   , the Twenty-SECOND OF June     , 1998
    Sewall                    03-NOV-98 Monday   , the Tenth OF May      , 1999
    Vishney                   11-NOV-97 Monday   , the Eighteenth OF May      , 1998
    Greene                    19-MAR-99 Monday   , the Twentieth OF September, 1999
    Marvins                   24-JAN-00 Monday   , the Thirty-FIRST OF July     , 2000
    Lee                       23-FEB-00 Monday   , the Twenty-Eighth OF August   , 2000
    Ande                      24-MAR-00 Monday   , the Twenty-Fifth OF September, 2000
    Banda                     21-APR-00 Monday   , the Twenty-Third OF October  , 2000
    Ozer                      11-MAR-97 Monday   , the Fifteenth OF September, 1997
    Bloom                     23-MAR-98 Monday   , the Twenty-Eighth OF September, 1998
    Fox                       24-JAN-98 Monday   , the Twenty-Seventh OF July     , 1998
    Smith                     23-FEB-99 Monday   , the Thirtieth OF August   , 1999
    Bates                     24-MAR-99 Monday   , the Twenty-Seventh OF September, 1999
    Kumar                     21-APR-00 Monday   , the Twenty-Third OF October  , 2000
    Abel                      11-MAY-96 Monday   , the Eighteenth OF November , 1996
    Hutton                    19-MAR-97 Monday   , the Twenty-SECOND OF September, 1997
    Taylor                    24-MAR-98 Monday   , the Twenty-Eighth OF September, 1998
    Livingston                23-APR-98 Monday   , the Twenty-Sixth OF October  , 1998
    GRANT                     24-MAY-99 Monday   , the Twenty-Ninth OF November , 1999
    Johnson                   04-JAN-00 Monday   , the Tenth OF July     , 2000
    Taylor                    24-JAN-98 Monday   , the Twenty-Seventh OF July     , 1998
    Fleaur                    23-FEB-98 Monday   , the Twenty-Fourth OF August   , 1998
    Sullivan                  21-JUN-99 Monday   , the Twenty-Seventh OF December , 1999
    Geoni                     03-FEB-00 Monday   , the Seventh OF August   , 2000
    Sarchand                  27-JAN-96 Monday   , the Twenty-Ninth OF July     , 1996
    Bull                      20-FEB-97 Monday   , the Twenty-Fifth OF August   , 1997
    Dellinger                 24-JUN-98 Monday   , the Twenty-Eighth OF December , 1998
    Cabrio                    07-FEB-99 Monday   , the Ninth OF August   , 1999
    Chung                     14-JUN-97 Monday   , the Fifteenth OF December , 1997
    Dilly                     13-AUG-97 Monday   , the Sixteenth OF February , 1998
    Gates                     11-JUL-98 Monday   , the Eighteenth OF January  , 1999
    Perkins                   19-DEC-99 Monday   , the Twenty-Sixth OF June     , 2000
    Bell                      04-FEB-96 Monday   , the Fifth OF August   , 1996
    Everett                   03-MAR-97 Monday   , the Eighth OF September, 1997
    McCain                    01-JUL-98 Monday   , the Fourth OF January  , 1999
    Jones                     17-MAR-99 Monday   , the Twentieth OF September, 1999
    Walsh                     24-APR-98 Monday   , the Twenty-Sixth OF October  , 1998
    Feeney                    23-MAY-98 Monday   , the Thirtieth OF November , 1998
    OConnell                  21-JUN-99 Monday   , the Twenty-Seventh OF December , 1999
    GRANT                     13-JAN-00 Monday   , the Seventeenth OF July     , 2000
    Whalen                    17-SEP-87 Monday   , the Twenty-FIRST OF March    , 1988
    Hartstein                 17-FEB-96 Monday   , the Nineteenth OF August   , 1996
    Fay                       17-AUG-97 Monday   , the Twenty-Third OF February , 1998
    Mavris                    07-JUN-94 Monday   , the Twelfth OF December , 1994
    Baer                      07-JUN-94 Monday   , the Twelfth OF December , 1994
    Higgins                   07-JUN-94 Monday   , the Twelfth OF December , 1994
    Gietz                     07-JUN-94 Monday   , the Twelfth OF December , 1994
     
    107 ROWS selected.
     
    SQL>
    The additional text is added using double quotes -- you can add just about any text you might like to a date format string as long as you enclose that text in double quotes.
     
  6. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi

    Try with the below query.


    Code (SQL):
    SELECT   hiredate  
            ,to_char (hiredate ,'Day')||' , the '||to_char (hiredate ,' Ddspth')||' of'||to_char (hiredate ,' Month, YYYY') Review
    FROM emp

    output:

    HIREDATE    REVIEW

    11/17/1981  Tuesday   , the  Seventeenth OF November , 1981
    5/1/1981    Friday    , the  FIRST OF May      , 1981
    6/9/1981    Tuesday   , the  Ninth OF June     , 1981
    4/2/1981    Thursday  , the  SECOND OF April    , 1981
    12/9/1982   Thursday  , the  Ninth OF December , 1982
    12/3/1981   Thursday  , the  Third OF December , 1981
    12/17/1980  Wednesday , the  Seventeenth OF December , 1980
    2/20/1981   Friday    , the  Twentieth OF February , 1981
    2/22/1981   Sunday    , the  Twenty-SECOND OF February , 1981
    9/28/1981   Monday    , the  Twenty-Eighth OF September, 1981
    9/8/1981    Tuesday   , the  Eighth OF September, 1981
    1/12/1983   Wednesday , the  Twelfth OF January  , 1983
    12/3/1981   Thursday  , the  Third OF December , 1981
    1/23/1982   Saturday  , the  Twenty-Third OF January  , 1982


     


    Regards
    Sambasiva Reddy
     
    nerd_buzz likes this.
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please see my response to this -- there is an easier way to generate that date string with the additional text with only one call to to_char().
     
    nerd_buzz likes this.
  8. nerd_buzz

    nerd_buzz Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    omg David, i only wish in my dreams to be as fluent, accurate and precise as you in sql. its only my third day working on it. i hope i can achieve success. THANK YOU TO EVERYONE, thanks for being such a help. i truly appreciate it :)
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I have 24 years of experience doing this -- I didn't not enter this arena knowing what I do now. Patience is a virtue, and it will be rewarded. Knowledge comes, experience is gained, you become better at what you do.

    You have a long way to go but you'll get there. :)
     
  10. nerd_buzz

    nerd_buzz Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    WOW! any good books you could suggest? if not ill post in a diff forum :)
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Several, actually.

    For a good understanding about Oracle: Expert Oracle Database Architecture, Thomas Kyte
    For a deeper understanding of the inner workings of Oracle: Cost-based Oracle Fundamentals, Jonathan Lewis
    Covering areas DBAs need to know: Oracle Core, Jonathan Lewis
    For PL/SQL: Oracle PL/SQL Programming, Steven Feuerstein

    The online Oracle documentation is also an excellent source of information, found here: http://tahiti.oracle.com

    For SQL*Plus: Oracle SQL*Plus: The Definitive Guide, Jonathan Gennick

    There are other texts as well, depending upon how far you decide to go with this. Many of the remaining books are for those with considerable experience. I will leave those for a later time.
     
    nerd_buzz likes this.