Is it possible to select from view using a column that isnt selected in the query?-CodePudding

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..

CodePudding user response：

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