After the migration of my oracle db im getting below error:
ORA-00904: "from$_subquery$_014"."TRN_TERMINAL_SOFTWARE_VERSION": invalid identifier
below is my query:
SELECT
*
FROM
(
SELECT
SUM(trn_amt) AS amount,
SUM(trn_count) AS COUNT ,
timetype AS period ,
seq ,
ROW_NUMBER() OVER (ORDER BY vw.seq DESC) AS RowNumber
FROM
YSD_STORE_FORWARD_V vw
INNER JOIN store_dimension std ON vw.stm_id=std.std_id
INNER JOIN card_type_dimension ctd ON vw.CTD_CARD_ABBV=ctd.CTD_CARD_ABBV
INNER JOIN STPLDBTEMP.store_group_relation sgr ON std.STD_ID= sgr.sgr_store_id
INNER JOIN location_dimension ld ON ld.lod_id=std.std_location_id
WHERE
start_date BETWEEN 20170405 AND 20170405
AND std.std_corp_id ='1939'
AND sgr.sgr_sgm_id ='2160'
AND ctd.CTD_CARD_ABBV IN ('VE','VI','VS','VD','AX','AE','MD','MC','MF','XX'
,'DB','JB','NV','DS','EB','EBC','EBF','EBW','VF','VG','WX','DC','GF','PL'
,'PW','NM','BG','GC','FL','FO','FT','FW','INV','IV','IN','CP')
AND LD.LOD_COUNTRY_ID = 1
GROUP BY
timetype ,
seq
ORDER BY
4
);
I'm not using column in my query still I'm getting this error, Please help me out YSD_STORE_FORWARD_V its a View which is using materialized view contain TRN_TERMINAL_SOFTWARE_VERSION column but my working is fine.
SELECT * FROM YSD_STORE_FORWARD_V? Do the columns match up with the columns you are using in the above query? – MT0TRN_TERMINAL_SOFTWARE_VERSION? (try descTRN_TERMINAL_SOFTWARE_VERSION) 2. Does your user (running the query above) hasREADprivilege on it? 3. Does it reside on the same schema and on the same database? – g00dyorder byoutside? The execution plan might help identify where that Oracle-generated subquery name fits in to the overall query. Sounds like it might be a parser bug though.... Which version is this, and which version did you migrate from, and how? – Alex Poole