If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Help with sql code

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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 08:50. Reason: updated code
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,806
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,598
>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.
Don't say, show. Don't promise, prove.
Reply With Quote
  #4 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 4,001
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')
Reply With Quote
  #5 (permalink)  
Old
Lead Application Develope
 
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,415
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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');
Reply With Quote
Reply

Tags
database, help needed, oracle, sql

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On