Get foreign key between two dates

Question

It is necessary to analyze the fluctuations in exchange rates, depending on the sign of the zodiac. I would like to insert foreign key (zodiak_id) in the follow table:

create table CURRENCY (
  CUR_ID number NOT NULL,  
  CUR_DATE DATE not null,
  CUR_NAME varchar2(3) not null,
  VALUE NUMBER not null,
  Zodiac_id number,
  constraint PK_CUR primary key (CUR_ID),
  CONSTRAINT FK_ZOD FOREIGN KEY (Zodiac_id) REFERENCES Zodiac(Zodiac_id)
  );

of this table:

create table Zodiac (
  Zodiac_id number not null,
  Zodiac_name VARCHAR2(15) not null,
  START_PERIOD  date,
  END_PERIOD  date,  
  constraint PK_Zod primary key (Zodiac_id)
  );

The sqls i wrote is below:

insert into CURRENCY cr(Zodiac_id)
select Zodiac_id from ZODIAC z
where cr.CUR_DATE >= z.START_PERIOD and cr.CUR_DATE <= z.END_PERIOD;

But get this error: SQL Error: ORA-00904: "CR"."CUR_DATE": invalid identifier

Thanks in advance for your help.

You need to join Zodiac and CURRENCY together lookup inner join for how to do this. — JStead, Jan 04 '15 at 21:48

score 1 · Accepted Answer · answered Jan 04 '15 at 21:55

1

Try this;

 insert into CURRENCY  (Zodiac_id)
 select Z.Zodiac_id from Zodiac Z
 inner join CURRENCY CR ON CR.Zodiac_id=Z.Zodiac_id
 WHERE CR.CUR_DATE BETWEEN Z.START_PERIOD AND Z.END_PERIOD

answered Jan 04 '15 at 21:55

Yasin Bilir

194
3
17

SQL Error: ORA-00942: table or view does not exist Maybe something with aliases? – Alex Jan 04 '15 at 22:01
nope, query seems right, either you are not using the right schema to run this query or you dont have the privilege to access the table. – Yasin Bilir Jan 04 '15 at 22:17
check this out ; http://stackoverflow.com/questions/16129912/sql-error-ora-00942-table-or-view-does-not-exist – Yasin Bilir Jan 04 '15 at 22:25

score 0 · Answer 2 · answered Jan 04 '15 at 21:47

0

It's because you're inserting only the zodiac_id into the CURRENCY table, but cur_date is a required field. You've marked it with a "not null" constraint in the create table statement.

answered Jan 04 '15 at 21:47

eaolson

14,717
7
43
58

Get foreign key between two dates

2 Answers2