I have this query for example:
CREATE OR REPLACE VIEW xx AS
SELECT TO_CHAR(tsc.id) AS status,
CASE WHEN tsc.description IS NULL THEN CAST('' as NVARCHAR2(50)) ELSE tsc.description END AS description,
SUM(CASE WHEN tr.USER_TYPE = 1 THEN 1 ELSE 0 END) AS "1",
SUM(CASE WHEN tr.USER_TYPE = 2 THEN 1 ELSE 0 END) AS "2",
SUM(CASE WHEN tr.USER_TYPE = 3 THEN 1 ELSE 0 END) AS "3",
SUM(CASE WHEN tr.USER_TYPE = 5 THEN 1 ELSE 0 END) AS "5",
SUM(CASE WHEN tr.USER_TYPE IS NOT NULL THEN 1 ELSE 0 END) AS total
FROM TRANSACTION_STATUS_CODES tsc
LEFT JOIN TRANSACTIONS tr ON tsc.id = tr.status AND tr.User_Type BETWEEN 1 AND 5 AND tr.status != 1 AND tr.update_date BETWEEN TO_DATE('2022-01-01', 'yyyy-mm-dd HH24:MI:SS') AND
TO_DATE('2023-01-04', 'yyyy-mm-dd HH24:MI:SS')
LEFT JOIN TRANSACTION_USER_TYPES ut ON ut.id = tr.user_type
WHERE tsc.id != 1
GROUP BY tsc.id, tsc.description;
I have this line inside:
AND tr.update_date BETWEEN TO_DATE('2022-01-01', 'yyyy-mm-dd HH24:MI:SS') AND TO_DATE('2023-01-04', 'yyyy-mm-dd HH24:MI:SS')
Im trying to create that view and then select all from view given an Update_date column as a where clause, something like either :
SELECT * FROM xx WHERE transactions.update_date IN (SELECT transactions.update_date FROM transactions WHERE transactions.update_date BETWEEN TO_DATE('2023-01-04', 'yyyy-mm-dd HH24:MI:SS') AND TO_DATE('2023-01-04', 'yyyy-mm-dd HH24:MI:SS'));
----
select * from xx where update_date between date1 and date2
nothing Ive tried seem to be working, and tbh thats kinda wierd that it has to be in the select statement of the view in order for my view to recognize it because the tables are joined so why the heck wouldnt my view recognize it? is that really how things are working in oracle? or am I missing something? to be honest it will kinda freak me out if theres no workaround because this doesnt make any sense..
That's how it goes. Imagine view as a table - it contains its columns. You can't select something that doesn't exist. For example, table contains 3 columns:
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Create a view that contains only one column from that table:
SQL> create or replace view xx as select dname from dept;
View created.
SQL> desc xx
Name Null? Type
----------------------------------------- -------- ----------------------------
DNAME VARCHAR2(14)
Can you select column that doesn't exist in that view? Of course you can not:
SQL> select loc from xx;
select loc from xx
*
ERROR at line 1:
ORA-00904: "LOC": invalid identifier
SQL>
The same goes for your view, although it is based on several tables.
If you want to be able to select other columns, you have to include them into SELECT column list. It is also probably a good idea NOT to filter data on date column while creating a view:
CREATE OR REPLACE VIEW xx
AS
SELECT TO_CHAR (tsc.id) AS status,
CASE
WHEN tsc.description IS NULL THEN CAST ('' AS NVARCHAR2 (50))
ELSE tsc.description
END AS description,
SUM (CASE WHEN tr.USER_TYPE = 1 THEN 1 ELSE 0 END) AS "1",
SUM (CASE WHEN tr.USER_TYPE = 2 THEN 1 ELSE 0 END) AS "2",
SUM (CASE WHEN tr.USER_TYPE = 3 THEN 1 ELSE 0 END) AS "3",
SUM (CASE WHEN tr.USER_TYPE = 5 THEN 1 ELSE 0 END) AS "5",
SUM (CASE WHEN tr.USER_TYPE IS NOT NULL THEN 1 ELSE 0 END) AS total,
tr.update_date --> newly added
FROM TRANSACTION_STATUS_CODES tsc
LEFT JOIN TRANSACTIONS tr
ON tsc.id = tr.status
AND tr.User_Type BETWEEN 1 AND 5
AND tr.status != 1
/* --> remove that filter
AND tr.update_date BETWEEN TO_DATE ('2022-01-01',
'yyyy-mm-dd HH24:MI:SS')
AND TO_DATE ('2023-01-04',
'yyyy-mm-dd HH24:MI:SS')
*/
LEFT JOIN TRANSACTION_USER_TYPES ut ON ut.id = tr.user_type
WHERE tsc.id != 1
GROUP BY tsc.id, tsc.description, tr.update_date --> added TR.UPDATE_DATE