The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

How to get the Query which is fired.

Highlighted
Mahen M
Honored Contributor

How to get the Query which is fired.

Hi,
I am using Demand Management -> Search Requests
In this I am entering a Request type and clicking 'Advanced Search'... In this I am giving values to some fields and searching.

My requirement is How can I get the Query which gets fired when I click 'Search Requests'?

In Custom Portlets if we introduce any bugs, It will throw an error with the Query, Is there is any similar setup available?

Please advice.

Regards,
Mahendran M
14 REPLIES
Erik Cole
Honored Contributor

Re: How to get the Query which is fired.

Mahendran,

Go to workbench > Edit > Debug Settings

Check 'Enable JDBC Logging'

Go to the dashboard and run/refresh the portlet.

Go back and turn off JDBC logging.

Look for the SQL in server/kintana/log/jdbc.xxxxx.log file
Darshan Bavisi
Occasional Visitor

Re: How to get the Query which is fired.

Hi Mahendran,

Other option is to ask the DBA to trace your this query and the DBA should also be able to give you the entire query fired on the schema.
Mahen M
Honored Contributor

Re: How to get the Query which is fired.

I got the query by enabling the jdbc log in WB, but this Query is not useful for me, Let me state my requirement here. I have a portlet, I am having a filter field, I want to make this filter field multiselect, if I give single value in the filter I am getting output in the portlet, if I give more than one value it is not returning any rows.

I saw the executed portlet query and I saw the multiselect line like this

AND O.ORG_UNIT_NAME = 'Test1,Test2'

How can I modify my data source such that the query will become

AND O.ORG_UNIT_NAME = 'Test1','Test2'

Please advice

Regards,
Mahendran M

Regards,
Mahendran M
Jim Esler
Honored Contributor

Re: How to get the Query which is fired.

Set up your filter directive like this:

AND ot.object_type_id IN ([P.OBJECT_TYPE])
Mahen M
Honored Contributor

Re: How to get the Query which is fired.

Unfortunately the Parameter value is also a String and I am keeping the validation change as a last option. Is there is any other way? Please advice.

Regards,
Mahendran M
Mahen M
Honored Contributor

Re: How to get the Query which is fired.

Jim,
Do you know how this works if the Object type parameter is a Multiselect Field which is seperated by '#@#'?

I wrote the query but it throws Invalid number error.

Regards,
Mahendran M
Jim Esler
Honored Contributor

Re: How to get the Query which is fired.

I would expect your query to include something like this when the substitution is done:

AND O.ORG_UNIT_NAME IN ('Test1','Test2')

You should not have to do the substitution for the delimiter yourself.
Erik Cole
Honored Contributor

Re: How to get the Query which is fired.

Mahendran,

In your portlet definition choose one of the autocomplete list validations for Org Unit.

Click the Multiselect radio button on the filter form, and give it a token name.

For filter SQL, try

AND O.ORG_UNIT_NAME IN ([VP.YOUR_TOKEN.TO_STRING])

Note the lack of single quotes around the token like you would normally use.
Mahen M
Honored Contributor

Re: How to get the Query which is fired.

Hi Erik,
Have you done this before? If so can you share the snap shots.
Thanks in advance.

Regards,
Mahendran M
Erik Cole
Honored Contributor

Re: How to get the Query which is fired.

Sure, here you go.
Mahen M
Honored Contributor

Re: How to get the Query which is fired.

Hi Erik,
I had a look at your document. I think every resource has one or no Roles assigned to him. How can I write a Query if I can assign more than one role to a single user.

Say if I have the three following roles assigned to my ID

Business Analyst
Consultant
Support Analyst

If I want to find the people who has the following roles in the portlet

Consultant
Support Analyst

My name should come only once in the portlet output.

How is this condition possible? Please advice.

Regards,
Mahendran M
Erik Cole
Honored Contributor

Re: How to get the Query which is fired.

If your query is returning multiple rows for the same user, just do a GROUP BY user name.
Mahen M
Honored Contributor

Re: How to get the Query which is fired.

I am confused... I have already tried the group by, I got the attached error.

Regards,
Mahendran M
Erik Cole
Honored Contributor

Re: How to get the Query which is fired.

I'm kind of confused now about what you're trying to do.

This query is returning a lot of fields, and it's pretty much guaranteed that some (or all) of the values will be different for each request_id. That's why the error - SQL can't know which ones to return for a given request_id.

Can you maybe restate what it is you're trying to do, with a simple example?
//Add this to "OnDomLoad" event