How do I search with dbxquery from a field to allow ALL values to be returned?

[cmrotek]

I have a field setup to accept the value for user , but I can not figure out how to change the code to accept a value that will cause all values to be returned from the table. I will be trying to setup multiple fields to do the same thing with the other values in the table.

| dbxquery query="SELECT \"AUDIT_EVENT\".\"EVENT_TIMESTAMP\",\"AUDIT_EVENT\".\"EVENT_ID\",\"EVENT_VALUE_1\",\"EVENT_VALUE_1_PARAM\",\"EVENT_VALUE_2\",\"PROFILE_ID\" FROM \"ThisDatabase\".\"dbo\".\"AUDIT_EVENT_DETAIL\" JOIN \"AUDIT_EVENT\" ON \"AUDIT_EVENT\".\"EVENT_ID\"=\"AUDIT_EVENT_DETAIL\".\"EVENT_ID\"WHERE EVENT_VALUE_1_PARAM = 'Attempted user name' AND EVENT_VALUE_1 = '$user$'" connection="DEV-SQLAccount"  

No default values will make this work. I am trying to pass "IS NOT NULL" to the the result , but may need to convert a default value of a start to this. However the user must also be able to put a user in the field and still do a search.

[somesoni2]

Try with LIKE command to compare EVENT_VALUE_1 with $user$ and in your text box for user input, use % as default value.

[cmerriman · Accepted solution]

if i'm understanding what you're looking to do, i'm working on a similar thing and i've come up with this solution (using a multiselect input):

...and regexp_like(EVENT_VALUE_1,'$user$')

would go into your dbxquery. your tokens would have these settings:

<input type="multiselect" token="user">
      <label>User</label>
      <choice value="^">All</choice>
      <default>^</default>
      <delimiter>|</delimiter>
      <fieldForLabel>user</fieldForLabel>
      <fieldForValue>user</fieldForValue>
      <search>
        <query>query that grabs all users</query>
      </search>
    </input>

[cmrotek]

Yes it is as easy as using the LIke command instead of the "=" comparison. Then changing the default fields value to a %.

[cmrotek]

Yes it is as simple as using the Like Command and changing the default field values to %.

| dbxquery query="SELECT \"AUDIT_EVENT\".\"EVENT_TIMESTAMP\",\"AUDIT_EVENT\".\"EVENT_ID\",\"EVENT_VALUE_1\",\"EVENT_VALUE_1_PARAM\",\"EVENT_VALUE_2\",\"PROFILE_ID\" FROM \"ThatDatabase\".\"dbo\".\"AUDIT_EVENT_DETAIL\" JOIN \"AUDIT_EVENT\" ON \"AUDIT_EVENT\".\"EVENT_ID\"=\"AUDIT_EVENT_DETAIL\".\"EVENT_ID\"WHERE EVENT_VALUE_1_PARAM = 'Attempted user name' AND EVENT_VALUE_1 Like '$user$'  " connection="SomeDEV-SQLAccount"