I created a view that is required by a 3rd party company for creating a tool for us. They are saying that the GET call requires the view to have a primary key. The underlying table has a PK column called IDFaktura, but the view does not count this as a PK. So, I try to do the following:
CREATE OR REPLACE VIEW view_FAKTURA (extnr, nr, fakturadate,
constraint view_FAKTURA_pk primary key (idfaktura) disable novalidate)
AS SELECT idfaktura, extnr, nr, fakturadate FROM table_faktura WHERE
fakturadate >= to_date('01.12.2019', 'dd.mm.yyyy') AND fakturadate <
to_date('31.12.2019', 'dd.mm.yyyy');
But get the error: SQL Error: ORA-00904: "table_FAKTURA"."IDFAKTURA": invalid identifier 00904. 00000 - "%s: invalid identifier"
Why does it give me this error? From what I was able to find online it should work.
CodePudding user response：
You are defining the PK on column idfaktura, but you aren't creating a column with that name. As you are selecting four columns in the view query you seem to have intended to have it there (rather than really wanting the PK on extnr say), so you just need to include it in the column list for the view itself:
CREATE OR REPLACE VIEW view_FAKTURA (idfaktura, extnr, nr, fakturadate,
-------------------------------------^^^^^^^^^^
constraint view_FAKTURA_pk primary key (idfaktura) disable novalidate) AS
SELECT idfaktura, extnr, nr, fakturadate
FROM table_faktura
WHERE fakturadate >= to_date('01.12.2019', 'dd.mm.yyyy')
AND fakturadate < to_date('31.12.2019', 'dd.mm.yyyy');
Incidentally, you might find it a bit simpler to use date literals:
...
WHERE fakturadate >= DATE '2019-12-01'
AND fakturadate < DATE '2019-12-31';
And also if that is supposed to cover the entire month, you probably actually want:
...
WHERE fakturadate >= DATE '2019-12-01'
AND fakturadate < DATE '2020-01-01';
as your current range will exclude all data from December 31st. Which may be what you want, but of course, but looks unlikely.