retrieve distinct values with different fields name and type from two table oracle

1
votes
  • table1 :id,year,month,...
  • table2 :id,date,...

What I am trying to do is to retrieve the distinct values of months from both tables for a specific year (example:2017) and for a specific id(note that table1 have year and month as separate fields while table2 have a complete date as a single field)

I wrote this query:

SELECT DISTINCT month 
  FROM table1
  JOIN table2 
    ON table1.month = table2.(to_char(date,'mm'))
 WHERE table1.year=2017
   AND id=406;

I am getting this error:

 ORA-01747: invalid user.table.column, table.column, or column specification
01747. 00000 -  "invalid user.table.column, table.column, or column specification"

as for the scenario, table1 contains a piece of pay element and table2 contain another pay element, an employee could have an entry in both for the same month or a unique entry in one of the tables for another month, the issue is that I need to count the number of months paid out of 12 (I do not want the same month to be repeated)

2
sqloracledistinct

2 Answers

2
votes

The Alias will come inside the function call.

You probably want:

select distinct t1.month
from table1 t1
join table2 t2 on t1.month = to_char(t2.date, 'mm')
where t1.year = 2017
    and id = 406;

Also, use short meaningful aliases to aid readability.

1
votes

Try the following query. Note that joining the two tables by date should require comparing both the year and the month.

select distinct t1.month
from table1 t1
inner join table2 t2
    on t1.month = to_char(t2.date, 'mm') and
       t1.year = to_char(t2.date, 'yyyy')
where t1.year = 2017 and
      id = 406;

You should change your table design and not store the day, month, and year in separate columns.