here is the table:
create table t1 (code varchar2(10), language varchar2(10),
dscr varchar2(10));
with this data:
insert into t1 values ('X','EN','english_dscr');
insert into t1 values ('X','FR','french_dscr');
how can i write a query or create a view to return the english record
when i do not in clude the language column in the where part of the query.
example.
select * from t1 where code='X';
to return the english record.
or
select * from t1 where code='X' and language='FR'
to return the french record.
thanks for your solution.
Unfortunately, it won't solve my problem.
i need to query, not based on my default language but on the language as
requested by the user who desires the info.
------------------------------------------------------------------------------------
You can use your "own" context as well via create context -- this uses the
default one that is there for all (but could be unreliable for the same reason
-- anything could change it) -- but shows the concept:
ops$tkyte@ORA9IR2> drop table t1;
Table dropped.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t1 (code varchar2(10), language varchar2(10),
2 dscr varchar2(20));
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 values ('X','EN','english_dscr');
1 row created.
ops$tkyte@ORA9IR2> insert into t1 values ('X','FR','french_dscr');
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view v
2 as
3 select * from t1
4 where language = case when sys_context( 'userenv', 'client_info') in (
'EN', 'FR' )
5 then sys_context( 'userenv', 'client_info' )
6 else 'EN'
7 end;
View created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from v;
CODE LANGUAGE DSCR
---------- ---------- --------------------
X EN english_dscr
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info( 'FR' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select * from v;
CODE LANGUAGE DSCR
---------- ---------- --------------------
X FR french_dscr
ops$tkyte@ORA9IR2>
