This discussion is archived
12 Replies Latest reply: May 15, 2013 5:30 AM by Kyle.Thomas RSS

af:query bind variable with a LOV?

ILya Cyclone Explorer
Currently Being Moderated
Hello there,
I'm suddenly stumbled on creating a query panel, that seemed like a simple use case.
I need filter a VO with an attribute which isn't in VO attributes (not in select). So I'm adding a bind variable and where clause like AND (id_attr in (select id_attr from another_table where def_param = :p_def_param) or :p_def_param is null).
As the variable has required=true it appears in query panel and working fine.
But the UI control of the variable is inputText. What I need is choiceList with a view accessor-LOV.
And to my great surprise I can't find a way to change the UI of a bind variable or set a LOV ... Can you?

So basically I need to search a VO with some attribute which is not in sql select with a LOV and selectOneChoice component.
Also I wouldn't like to give up af:query and use ExecuteWithParams not to lose all those query features like advanced query etc.

What would you suggest?

I have also created a sample app to demonstrate the use case: https://www.dropbox.com/s/e4zhvm2oo3msuty/BindVarQueryLovTest.zip

Thanks.
JDev 11.1.2.3

Edited by: ILya Cyclone on May 14, 2013 7:09 PM
  • 1. Re: af:query bind variable with a LOV?
    Kyle.Thomas Newbie
    Currently Being Moderated
    Ilya,

    Try checking this blog post to see if this is what you're trying to accomplish:

    http://www.baigzeeshan.com/2010/03/how-to-create-adf-lov-with-view.html

    I always create separate LOVs in my [view.readonly] packages. You can default the VIEW to use a VIEW LOV (i.e. view.readonly.DepartmentsVOLOV) so that when you're creating your query panel you don't have to manually switch out the inputText component for the SelectOneChoice component.

    Hope this helps your situation.
  • 2. Re: af:query bind variable with a LOV?
    ILya Cyclone Explorer
    Currently Being Moderated
    Hello thomas,
    The problem is there is no View Object attribute on which I could create a LOV or smth, no table column - just a bind variable in WHERE clause.
  • 3. Re: af:query bind variable with a LOV?
    Kyle.Thomas Newbie
    Currently Being Moderated
    So the user just "knows" the *:id_attr*?

    You could create a dummy (transient) LOV VO that hard codes the values. Typically I'd store these values in some sort of manageable TYPE/VALUE reference table so they can be changed without deploying the application again.
  • 4. Re: af:query bind variable with a LOV?
    ILya Cyclone Explorer
    Currently Being Moderated
    So the user just "knows" the :id_attr?

    Nope, the user receives a friendly selectOneChoice component, displaying readable names.
  • 5. Re: af:query bind variable with a LOV?
    Kyle.Thomas Newbie
    Currently Being Moderated
    Where are you getting this "friendly selectOneChoice component, displaying readable names."

    If it already exists in the table then you create a separate VO LOV and replace the inputText with a selectOneChoice component and associate the binding back to the value in the View Object.
  • 6. Re: af:query bind variable with a LOV?
    ILya Cyclone Explorer
    Currently Being Moderated
    kthomas, would you please read the first message carefully?

    I need to have a dropdown list for the bind variable value in the af:query instead of default input text.
    I can not just replace the inputText with a selectOneChoice because inputText is generated by af:query - there's no such component on design time!
  • 7. Re: af:query bind variable with a LOV?
    Kyle.Thomas Newbie
    Currently Being Moderated
    ILya Cyclone wrote:
    kthomas, would you please read the first message carefully?
    Once again:
    I need to have a dropdown list for the bind variable value in the af:query instead of default input text.
    I can not just replace the inputText with a selectOneChoice because inputText is generated by af:query - there's no such component on design time!
    Ilya,

    You're going to have to give people a chance to help you. I did read your question carefully.
    ILya Cyclone wrote:I need filter a VO with an attribute which isn't in VO attributes (not in select). So I'm adding a bind variable and where clause like AND (id_attr = :p_id or :p_id iu null).
    As the variable has required=true it appears in query panel and working fine.
    But the UI control of the variable is inputText. What I need is choiceList with a view accessor-LOV.
    And to my great surprise I can't find a way to change the UI of a bind variable or set a LOV ... Can you?
    So basically I need to search a VO with some attribute which is not in sql select with a LOV and selectOneChoice component.
    Also I wouldn't like to give up af:query and use ExecuteWithParams not to lose all those query features like advanced query etc.
    I have created a sample app pointing to a table that I have with a table that holds a type and a description. The TYPE is the value that is stored in my MAIN table and is the bind variable P_PROBLEM_TYPE.

    I created a VO, LOV VO (this can be STATIC or come from a TBL), and associated the LOV VO to an attribute. So take a look at my screenshots and see if it is something you can handle with your example.

    I find one issue with your statement in your question. You say that you need to filter a VO with an attribute ... and this attribute isn't in the SELECT statement, but you are using id_attr in the WHERE clause with a bind variable of :p_id. If id_attr isn't in the SELECT statement then how can you use a bind variable to filter it in the WHERE clause?

    Anyways ... look at my screenshots and see if it helps with your solution.

    https://docs.google.com/file/d/0B4_rCR8y7GmbNlUtc09LZ01IYUE/edit?usp=sharing
    https://docs.google.com/file/d/0B4_rCR8y7GmbQ2R2akUtelpQaEE/edit?usp=sharing
    https://docs.google.com/file/d/0B4_rCR8y7GmbSnE1c01YUTJSWVU/edit?usp=sharing
    https://docs.google.com/file/d/0B4_rCR8y7GmbR2lld1UzcG83YUk/edit?usp=sharing
    https://docs.google.com/file/d/0B4_rCR8y7GmbMnY5OVJJMGJrY3c/edit?usp=sharing
    https://docs.google.com/file/d/0B4_rCR8y7GmbWmt3ZlZEdFU5ZFk/edit?usp=sharing
    https://docs.google.com/file/d/0B4_rCR8y7Gmba1BHMFdEdlh3NlU/edit?usp=sharing
    https://docs.google.com/file/d/0B4_rCR8y7GmbZUp4bjFPcnVsb1U/edit?usp=sharing
    https://docs.google.com/file/d/0B4_rCR8y7GmbNF9sTFJNRlRkeWc/edit?usp=sharing
  • 8. Re: af:query bind variable with a LOV?
    ILya Cyclone Explorer
    Currently Being Moderated
    Hello kthomas,
    I hope this sample application will sort everything out: https://www.dropbox.com/s/e4zhvm2oo3msuty/BindVarQueryLovTest.zip
  • 9. Re: af:query bind variable with a LOV?
    Kyle.Thomas Newbie
    Currently Being Moderated
    https://docs.google.com/file/d/0B4_rCR8y7GmbU3ZQb185N1JDMnM/edit?usp=sharing

    Here's a brief explanation on what I did.

    - Model
    - Your JobsView was based off your Entity Jobs, yet you modified the WHERE clause to include [WHERE (Jobs.JOB_ID IN (select job_id from employees where department_id = :PDeptId) OR :PDeptId IS NULL)]

    Since this is a search spanning multiple tables I created joins on these tables so you have the ability to include the department_id in main view. You don't have to display this, but this is useful for the af:Query.

    I modified the attribute department_id to be based on an LOV. I modified the bind :PDeptId to "not required" and moved the additional WHERE clause to a viewCriteria.

    When I drag/drop the view criteria onto the page the only thing that id displayed to the user is the DEPARTMENT_ID List of Values.
  • 10. Re: af:query bind variable with a LOV?
    shiko Newbie
    Currently Being Moderated
    may be this is useful
    http://www.youtube.com/watch?v=b_Lh2tjIL04
  • 11. Re: af:query bind variable with a LOV?
    ILya Cyclone Explorer
    Currently Being Moderated
    kthomas, if you just join the tables you will get job titles row number multiplied by corresponding department if id department is not set. And distinct wouldn't help.

    Say, Programmer is in HR and IT depts, you will get the followinf result:

    JOB_ID | JOB_TITLE | DEPT_NAME
    - - - - - - - - - - - - - - - - - - - -
    IT_PROG | Programmer | HR
    IT_PROG | Programmer | IT

    Two rows of Programmer job instead of one.
  • 12. Re: af:query bind variable with a LOV?
    Kyle.Thomas Newbie
    Currently Being Moderated
    Sorry Ilya. For some reason I was thinking you wanted the DEPT_ID. The concept is the same. If you want to leverage the AF:query there has to be a way to associate the xx_id to the xx_id in the LOV VO.

    Edited by: kthomas on May 15, 2013 5:30 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points