0

I have 2 tables: person_concern and person. They both have a code column and person has a dept column. I want to select data from person_concern table where the dept column in person table = 30.

Basically the person table has a different code for each row and then the person is put in a department. So i can have multiple rows in the person table with the same dept field.

The person_concern table is for writing problem concerns for people. It has a code to know which person its referring to which id get from the person table. So i want to select data from that person_concern table where the code matches the code from the person table and that person is from a certain dept such as 30.

Hope that makes sense... Here's what ive tried so far but get an invalid number error.

select
PERSON_CONCERNS.CODE
PERSON_CONCERNS.ENTRY_DATE
PERSON_CONCERNS.ENTRY_OPR
PERSON_CONCERNS.DISCUSSION
from PERSON_CONCERNS
inner join PERSON on PERSON_CONCERNS.CODE = PERSON.CODE
where PERSON.DEPT = 30
user3178424
  • 37
  • 1
  • 7
  • can you share the table information? I.e. column's datatype? And the select list as well. – San Jan 09 '14 at 16:51
  • I added the select list. CODE is a number ENTRY_DATE is a date ENTRY_OPR is a number DISCUSSION is a varchar2 dept is a number. – user3178424 Jan 09 '14 at 16:56
  • 1
    Check the `DATATYPE` of DEPT column of the PERSON table. Your problem may be [similar](http://stackoverflow.com/questions/12549029/sql-error-ora-01722-invalid-number). – user2989408 Jan 09 '14 at 16:56
  • Can you add the column datatypes as well i.e. is person.dept an int or char type? – shadowjfaith Jan 09 '14 at 16:56
  • You are missing some commas, making your query invalid. If you do have them in your actual code, please stop us from having to second guess and just copy/paste _what you actually have_. – CompuChip Jan 09 '14 at 16:57
  • Post the datatype of `PERSON_CONCERNS.CODE`, `PERSON.CODE` and PERSON.DEPT. If first two fields have same datatype then its ok. If dept is char or varchar then try using `PERSON.DEPT='30'` – San Jan 09 '14 at 16:59
  • Sorry for missing commas this is actually being done in SQR and the whole code is very long. I went and double checked my PERSON.DEPT field and it is actually set to varchar2, not sure why the person who made the table did that... adding the '30' worked thank you. – user3178424 Jan 09 '14 at 17:08

1 Answers1

0

I think you are just missing the commas on your field names but need to see the actual code you are running along with the create table statements and some sample data to be sure.

select
    PERSON_CONCERNS.CODE,
    PERSON_CONCERNS.ENTRY_DATE,
    PERSON_CONCERNS.ENTRY_OPR,
    PERSON_CONCERNS.DISCUSSION,
from PERSON_CONCERNS
    inner join PERSON on PERSON_CONCERNS.CODE = PERSON.CODE
where PERSON.DEPT = 30
Bradley Gatewood
  • 248
  • 2
  • 9