Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2012
    Location
    WALES
    Posts
    2

    Question Help with sql code

    i have been asked to:
    A project of your choice required two programmers and a database designer for 12th of November (or for a date of your choice). Using your data list the name of programmers and database designers who are available for 12th November 2012(or the date you have chosen).


    this is the code i have used to try and do the above:


    SELECT employee.employee_name, projects.project_name, assignments.date_assigned
    FROM employee, projects, assignments
    where projects.project_name = employee.employee_name
    WHERE EMP_JOB_TITLE= 'Programmer'
    OR EMP_JOB_TITLE= 'Database designer'
    and assignments.date_assigned = '12-NOV-12';


    But it is giving me this error:


    ORA-00933: SQL command not properly ended
    00933. 00000 - "SQL command not properly ended"
    *Cause:
    *Action:
    Error at Line: 4 Column: 1


    Any solutions would be greatful




    i have now edited the code:

    SELECT DISTINCT employee.employee_name, projects.project_name, assignments.date_assigned
    FROM employee, projects, assignments
    WHERE EMP_JOB_TITLE= 'Programmer'
    and EMP_JOB_TITLE= 'Database designer'
    or assignments.date_assigned = '12-NOV-12';

    but the result is showing all duplicates even though i have added the "distinct" query at the start ...


    EMPLOYEE_NAME PROJECT_NAME DATE_ASSIGNED
    ------------------------------ -------------------- -------------
    JOHN EDWARDS FOURTHPROJECT 12-NOV-12
    PAUL GREEN THIRDPROJECT 12-NOV-12
    CARLY EVANS FOURTHPROJECT 12-NOV-12
    JOHN EDWARDS FIRSTPROJECT 12-NOV-12
    PAUL GREEN SECONDPROJECT 12-NOV-12
    JOHN EDWARDS SECONDPROJECT 12-NOV-12
    EDWARD MARUBBI THIRDPROJECT 12-NOV-12
    EDWARD MARUBBI SIXTHPROJECT 12-NOV-12
    PHIL GREEN FIRSTPROJECT 12-NOV-12
    PHIL GREEN SECONDPROJECT 12-NOV-12
    PHIL GREEN THIRDPROJECT 12-NOV-12
    CARLY EVANS SIXTHPROJECT 12-NOV-12
    JOHN EDWARDS FIFTHPROJECT 12-NOV-12
    EDWARD MARUBBI FIRSTPROJECT 12-NOV-12
    PAUL GREEN FIRSTPROJECT 12-NOV-12
    PAUL GREEN FOURTHPROJECT 12-NOV-12
    CARLY EVANS SECONDPROJECT 12-NOV-12
    CARLY EVANS FIFTHPROJECT 12-NOV-12
    PHIL GREEN FIFTHPROJECT 12-NOV-12
    EDWARD MARUBBI SECONDPROJECT 12-NOV-12
    CARLY EVANS FIRSTPROJECT 12-NOV-12
    JOHN EDWARDS THIRDPROJECT 12-NOV-12
    EDWARD MARUBBI FIFTHPROJECT 12-NOV-12
    PAUL GREEN SIXTHPROJECT 12-NOV-12
    CARLY EVANS THIRDPROJECT 12-NOV-12
    JOHN EDWARDS SIXTHPROJECT 12-NOV-12
    PHIL GREEN FOURTHPROJECT 12-NOV-12
    PHIL GREEN SIXTHPROJECT 12-NOV-12
    EDWARD MARUBBI FOURTHPROJECT 12-NOV-12
    PAUL GREEN FIFTHPROJECT 12-NOV-12





    i also have been asked to:

    Assign the available employees to the project you have selected to answer Q5 (the above question). (Insert the related values into the Assignment table and leave the assigned hours NULL).
    Last edited by nerdytypegeekgirl; 12-09-12 at 09:50. Reason: updated code

  2. #2
    Join Date
    Nov 2003
    Posts
    2,821
    The keyword WHERE should only appear once.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,649
    >but the result is showing all duplicates even though i have added the "distinct" query at the start ...

    all posted rows are unique, different & distinct (no duplicates)
    what exactly is your definition for being considered "duplicates"?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,009
    Do not use "dates" as you did:
    Code:
    and assignments.date_assigned = '12-NOV-12'
    because '12-NOV-12' is a string, not a date. It looks as date and Oracle recognizes it as date - but just because your NLS settings are such that Oracle knows it is a date. If NLS settings change, your code will fail. Therefore, always use TO_DATE function and specify date format mask:
    Code:
    and assignments.date_assigned = to_date('12-NOV-12', 'dd-mon-rr')

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,434
    Where is your join to employee from assignments?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jan 2004
    Posts
    28
    2 issues here,

    1) you're not joining all the tables together so you're creating a cartesian product.
    2) Looks like you're using AND and OR backwards. I doubt EMP_JOB_TITLE will be both 'PROGRAMMER' and 'DATABASE DESIGNER'. Right not you're saying you want that column to contain both values. Also using OR in the last clause means you'll get people with either those titles or anyone with that date regardless of title. Is that what you mean? Or are you looking for people with either of those titles whose date assigned is 12-NOV-12? If the latter then you might try -

    WHERE employee.column = project.column
    AND employee.column = assignments.column
    AND ( (EMP_JOB_TITLE= 'Programmer'
    OR EMP_JOB_TITLE= 'Database designer')
    AND assignments.date_assigned = '12-NOV-12');

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •