0

I'd like to create a view from the query below, but the query contains a sub query which I need to apply a filter to (the place is marked with a comment). I'm wondering if it's possible somehow to adjust the query to have the subquery filtered by those params.

The query

select
 form_name,
 nvl(form_pravo,0) as form_pravo,
 nvl(fl_pravo,0) as menu_pravo,
 menu_nazov as menu_name    
from(
SELECT 
    form_name,
    pravo as form_pravo
  FROM (
    SELECT 
        f.ID as form_id,
        f.NAME as form_name,
        f.XF_MODULY_ID as modul_id,
        u.ID as uziv_id,
        f.XF_MODULY_ID       
          FROM xf_forms f, xf_uziv u) uf
       LEFT JOIN
        xp_forms_uzivs fu ON form_id  = fu.XF_FORM_ID AND uziv_id = fu.XF_UZIV_ID
            where modul_id = 14 and uziv_id = 1 -- <--HERE
            )
        full join (
            select 
                SUBOR,
                FL_PRAVO,
                menu_nazov,
                uziv_id,
                modul_id
            from xv_uziv_menu              
        ) um on form_name = um.subor
        where uziv_ID = 1 and modul_id = 14
  • 1
    do you want to change the parameters dynamically, in which case a view is not the object of choice, or.... – kevinsky May 20 '15 at 16:12
  • or what ? I could rewrite the query but I dont know how – user49126 May 20 '15 at 20:12
  • 1
    do you want to change the parameters of the view on demand? What you have will create a view which does not change unless the underlying data is changed. If you want to adjust the parameters then a cursor would do. Please clarify what you want to do, what is the problem you want to solve? – kevinsky May 20 '15 at 20:21
  • Can you could use the sys_context method described in stackoverflow.com/questions/9024696/… – sjk May 21 '15 at 6:31
0

In MySQL You can try a function

create function modul_id() returns INTEGER DETERMINISTIC NO SQL return @modul_id;
create view v (your select... where modul_id = modul_id()...);
select * from (select @modul_id:=14 modul_id) m, v;

not tested on your query...

  • That syntax doesn't work on Oracle, so it's not useful. – Colin 't Hart May 26 '15 at 9:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.