0

I am trying to create a derived attribute (Number of seasons derived from first season). I think I need to create a view, but I am not sure.

Below is my code:

Create Table Officials(
Fname varchar2(15) not null,
Lname varchar2(15) not null,
Sport_ID number(10,2),
First_Season number(10,2),
primary key (Lname),
foreign key (Sport_ID) references Sports(Sport_ID)
);

Create View Number_of_Seasons as
Select Fs.*, extract(year from (current_date-First_Season))  as Number_of_Seasons
From Officials;

I keep getting the error:

invalid character

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
MostevilJoeJoe
  • 1
  • 1

2 Answers2

0

Select Fs., extract(year from (current_date-First_Season)) Probably it is not able to locate Fs. in the table created. Be precise about which Column are you referring to.

if you are referring to First_Season, then mention it as how you entered the table.

Sandy
  • 17
  • 3
0

You said you use Oracle Apex. I presume that you ran that code in its SQL Commands; did you? If so, note that - even though you can enter as many commands in there you want, you have to select statement you want to execute and then push the "Run" button (or <CTRL + Enter> on the keyboard).

I created

  • sports table (because it is referenced by officials)
  • officials table
  • number_of_seasons view
    • it is unclear what you meant to say by fs.* as there's nothing like that in query you posted. It looks like a table alias. As the only table you're selecting from is officials, I used that table's alias. Everything works OK (as far as creating is concerned):

enter image description here

On the other hand, there are some other problems.

  • last name is a primary key? What if two (or more) officials have the same last name? Primary key constraint doesn't allow duplicates.

  • You chose kind of strange datatypes. What is first_season column supposed to contain? It is a number with up to two decimal places. Furthermore, it is then used in create view statement. You're subtracting number of days from current_date and get e.g.

    SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select current_date - 5.82 as number_of_seasons from dual;

NUMBER_OF_SEASONS
-------------------
05.04.2023 11:38:49

    which doesn't seem right to me.

  • the same goes for sport_id. A decimal ID? Possible, but I don't think I ever saw people using it.

Therefore, as far as code is OK itself, logic that stands behind it isn't (to me, at least).

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • I've typed your exact code in and I'm still getting the "invalid character" error. As far as the logical stuff, this is just a small example project so it's fine. – MostevilJoeJoe Apr 11 '23 at 18:18
  • As you can see, code I ran works OK. Could you post a screenshot which shows what you did? – Littlefoot Apr 11 '23 at 19:46