Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Override Field in Select?

pbreidenich
Contributor

Hello at all,

does Exasol have a possibility to overwrite a field in a select without listing all fields of the view?

SELECT vs1.*
FROM DATENQUELLEN.PS_V_DQ_VERTRIEB_SALESREPORT vs1
WHERE extract(YEAR FROM AP_REPORTDATUM) NOT IN (2020, 2021)
UNION ALL
-- Here override the existing Field AP_REPORTDATUM,
-- without listing all fields from the view.
SELECT vs2.*, TO_DATE(REPLACE(AP_REPORTDATUM, 2019, 2020)) AS AP_REPORTDATUM
FROM DATENQUELLEN.PS_V_DQ_VERTRIEB_SALESREPORT vs2
WHERE extract(YEAR FROM AP_REPORTDATUM) = 2019;

With best regards and thanks,
Peter

1 ACCEPTED SOLUTION

Accepted Solutions

pbreidenich
Contributor

Hi @PeterK 

thanks for the tip. That's also my previous solution respectively in the second select I unfortunately have to list almost 200 fields so that I can overwrite AP_REPORTDATUM from the view. Unfortunately, I cannot create a new field, otherwise it would have to be adjusted in many reports in the BI environment.

Sometimes one can dream that Exasol can do it better than Oracle or the SQL-Standard 😉

Thanks,
Peter

View solution in original post

3 REPLIES 3

PeterK
Xpert

Hi @pbreidenich,

I'm not aware of a way to override a field in that way.
I would suggest adding an additional field (with a different name) to each part of the union:

SELECT vs1.*, AP_REPORTDATUM as newcol
FROM ...
UNION ALL
SELECT vs2.*, TO_DATE(REPLACE(AP_REPORTDATUM, 2019,2020)) AS newcol
FROM  ...

Also, if this SQL is going to be part of a view definition I would suggest avoiding the .* notation because DDL changes to PS_V_DQ_VERTRIEB_SALESREPORT like adding a column or changing the column positions would cause the outer view to become invalid. Although it's annoying to explicitly list all the columns I generally find it to be more resilient.

Best,

Peter

pbreidenich
Contributor

Hi @PeterK 

thanks for the tip. That's also my previous solution respectively in the second select I unfortunately have to list almost 200 fields so that I can overwrite AP_REPORTDATUM from the view. Unfortunately, I cannot create a new field, otherwise it would have to be adjusted in many reports in the BI environment.

Sometimes one can dream that Exasol can do it better than Oracle or the SQL-Standard 😉

Thanks,
Peter

mwellbro
Xpert

Hi @pbreidenich 

if you positively, definitely need to get this done there would still be a way by "implementing your own syntax" through the pre-processor, like:

OPEN SCHEMA OF_CHOICE;

CREATE VIEW SOME_TEST_VIEW AS select 1 as my_key, TO_DATE('20190101','YYYYMMDD') as AP_REPORTDATUM,'some test' as c3 from dual;

CREATE OR REPLACE LUA SCRIPT REPLACE_COL_IN_STAR () RETURNS ROWCOUNT AS
function ParseAndChange(sql_text)
local tx     = sqlparsing.getsqltext()

if ( tx.find(tx,'/*+ REPLACE_COL') ) then

tx = tx.gsub(tx,'\'','\'\'')

col_list = query[[select ' '|| group_concat(column_name order by column_ordinal_position) ||' ' as cols from exa_dba_columns where column_table='SOME_TEST_VIEW';]]

res = query([[ 
with 
 base  as (select ' ]]..tx..[[' as SQL_TEXT from dual )
,base2 as (
select 
 substr(replace(regexp_substr(sql_text,'\/\*\+ REPLACE_COL\(([\s\S.*].*?)\,'),',',''),instr(replace(regexp_substr(sql_text,'\/\*\+ REPLACE_COL\(([\s\S.*].*?)\,'),',',''),'(')+1) source_field
,substr(replace(
  regexp_substr(
   regexp_substr(sql_text,'\/\*\+ REPLACE_COL[s\S].*')
  ,'\,[\s\S].*?\*\/')
 ,'*/',''),2) target_field
from base
)
select 
 regexp_replace(
  SQL_TEXT
 ,'\/\*\+ REPLACE_COL[\s\S].*?\*\/ \*'
 ,replace(']]..col_list[1].COLS..[[', source_field , substr(target_field,1,length(trim(target_field))-1))
)
as SQ from base cross join base2;
]])

sqlparsing.setsqltext(res[1].SQ)

return res[1].SQ
end

sqlparsing.setsqltext(tx)

return 0
end

ParseAndChange('dummy')

/


alter session set sql_preprocessor_script=REPLACE_COL_IN_STAR;

--1st example
select /*+ REPLACE_COL(AP_REPORTDATUM,TO_DATE(REPLACE(AP_REPORTDATUM, 2019, 2020)) AS AP_REPORTDATUM) */ * from SOME_TEST_VIEW
union all
select * from SOME_TEST_VIEW;

--2nd example
select /*+ REPLACE_COL(C3,'lets change this' as c3) */ * from SOME_TEST_VIEW
union all
select * from SOME_TEST_VIEW;

alter session set sql_preprocessor_script='';

As you might glean from the example this is somewhat "hacky" and does (at least in my "jotted-down" version) rely on a view syntactical assumptions - but just to put it out there, it´s possible 😉

mwellbro_0-1649027585218.png


Cheers,
Malte