1. 

   jcstevens, Member

   I have a parameter I've been trying to implement, inAppl. I created the parameter query as
   

   select oid, applicationName from coredata_read.dictSoftwareApplication_masterRecord
   union
   select 0, ' All'
   

   
   which presents the user with a list of all possible Applications or ' All' (which returns "0").

   In my report, I can't figure out how to get the All selection working to compare the user's selection with a field named cra.application. If they select All, returning "0", then I want to ignore this part of the where clause completely, but if they select an Application, I want to use 'where inAppl = cra.application'. My where clause looks like this which works for an actual Application but gives no results for All:
   

   where if ( inAppl = 0, inAppl, '%' ) = cra.application
2. 

   myDBR Team, Key Master

   Try:
   

   where ifnull( if ( inAppl = 0, null, inAppl ), cra.application ) = cra.application

   If cra.application can contain null, you need to take care of that as null != null.

   --
   myDBR Team
3. 

   jcstevens, Member

   Yes, cra.application can contain NULL. Where do I incorporate that possiblity into the above snippet?
4. 

   myDBR Team, Key Master

   Just add a condition for it:

   where (ifnull( if ( inAppl = 0, null, inAppl ), cra.application ) = cra.application or (inAppl = 0 and cra.application is null))

   --
   myDBR Team
5. 

   jcstevens, Member

   I tried both of the options above, but both returned all results, ignoring the inAppl selection completely. Just to make sure I'm being clear, inAppl will either result in 0 (if the user chooses All) or an internal varchar value called oid. When I add inAppl to the result set, I see the correct oid value for my inAppl selection of 'Core', but I get all results whether that results cra.application = Core's oid or some other oid.

   Here is my procedure (with commented items removed):
   

   
   select distinct(crp.primaryIdentifier) as CaseID,
   crm.mainDescription as 'Description',
   crm.mainStatus as Status,
   dsam.applicationName
   from coredata_read.caseRoot_primary crp
   inner join coredata_read.caseRoot_main crm on crp.oid=crm.oid
   inner join coredata_read.caseRoot_applications cra on crp.oid=cra.oid
   inner join coredata_read.dictSoftwareApplication_masterRecord dsam on dsam.oid=cra.application
   where (ifnull( if ( inAppl = 0, null, inAppl ), cra.application ) = cra.application or (inAppl = 0 and cra.application is null))
   order by crp.primaryIdentifier ASC;
   

   
   When I select the application of Core, I get 10047 results, many of which are for other applications.

   In a MySQL client, I run the following query and I get the correct 3027 results for all Cases with the application of Core:
   

   select distinct(crp.primaryIdentifier) as CaseID,
   crm.mainDescription as 'Description',
   crm.mainStatus as Status,
   dsam.applicationName
   from coredata_read.caseRoot_primary crp
   inner join coredata_read.caseRoot_main crm on crp.oid=crm.oid
   inner join coredata_read.caseRoot_applications cra on crp.oid=cra.oid
   inner join coredata_read.dictSoftwareApplication_masterRecord dsam on dsam.oid=cra.application
   where dsam.applicationName = 'Core'
   order by crp.primaryIdentifier ASC;
6. 

   myDBR Team, Key Master

   if your dsam.applicationName is a character, you should use a string instead of an integer:

   select oid, applicationName from coredata_read.dictSoftwareApplication_masterRecord
   
   union
   
   select 'All', ' All'
   
   

   
   Likewise, change the where-clause to contain a string instead of an integer.

   --
   myDBR Team
7. 

   jcstevens, Member

   Done. The above logic with 'All' for inAppl selection of All results in 10047 rows where 'Core' results in 3027. This is correct now. Thank you!
8. 

   jcstevens, Member

   One other optional parameter doesn't appear to be working. I have a user parameter called inTextSearch which I'd like to use as a free text search within results that meet all of the other criterion.

   I've added

   and ctt.textValue like concat('%', ifnull(inTextSearch,''), '%')

   to my where clause but when I input a value into inTextSearch, I get the same results as I had without a value. Additionally, I'm getting results that do not contain that value. Is there something else that I need to add to account for this, or is there a good template somewhere to be used as an optional, boolean search?
   Thanks again
9. 

   myDBR Team, Key Master

   That should work. If you have problems, see what then content of inTextSearch is by selecting it. Also, please note that inTextSearch is not by default null but an empty string.

   --
   myDBR Team
10. 

    jcstevens, Member

    I am adding inTextSearch to my selection and I can see the text I entered into the user parameter, but I get the same results with and without an inTextSearch value entered. When I run the query in a client with hardcoded values matching the selection in myDBR, I'm finding that this last and ctt.textValue line is acting strangely. When I execute:
    

    select crp.primaryIdentifier, ctt.textValue
    from coreText_text ctt
    inner join caseRoot_objects cro on cro.objectID=ctt.oid
    inner join caseRoot_primary crp on crp.oid=cro.oid
    where cro.oid in (select crp.oid as 'CaseOID[cid]'
    from coredata_read.caseRoot_primary crp
    inner join coredata_read.caseRoot_main crm on crp.oid=crm.oid
    inner join coredata_read.caseRoot_action crac on crac.oid=crm.oid
    inner join coredata_read.caseRoot_objects cro on cro.oid=crm.oid
    inner join coredata_read.coreText_text ctt on ctt.oid = cro.objectID
    where crac.actionPartyClean in (
    select cun.oid
    from coredata_read.coreUser_name cun
    left outer join coredata_read.caseRoot_action crac on crac.actionPartyClean = cun.oid
    where cun.nameValue like concat('%', ifnull('Walsh,Stephanie',''), '%')) or (substring_index(trim(trailing '.cdb' from crac.actionPartyClean),'\\','-1') like concat('%', ifnull('Walsh,Stephanie',''), '%'))
    and crac.actionStart between '2014-09-07' and '2014-10-09'
    and ctt.textValue like '%initiative%')

    
    I get 1130 rows with only 2 of them containing any reference to 'initiative'. However, when I move the ending parenthesis up a line, essentially pulling everything for the subquery then filering the results of the main query to those containing 'initiative', I get the 2 results I'm digging for:
    

    select crp.primaryIdentifier, ctt.textValue
    from coreText_text ctt
    inner join caseRoot_objects cro on cro.objectID=ctt.oid
    inner join caseRoot_primary crp on crp.oid=cro.oid
    where cro.oid in (select crp.oid as 'CaseOID[cid]'
    from coredata_read.caseRoot_primary crp
    inner join coredata_read.caseRoot_main crm on crp.oid=crm.oid
    inner join coredata_read.caseRoot_action crac on crac.oid=crm.oid
    inner join coredata_read.caseRoot_objects cro on cro.oid=crm.oid
    inner join coredata_read.coreText_text ctt on ctt.oid = cro.objectID
    where crac.actionPartyClean in (
    select cun.oid
    from coredata_read.coreUser_name cun
    left outer join coredata_read.caseRoot_action crac on crac.actionPartyClean = cun.oid
    where cun.nameValue like concat('%', ifnull('Walsh,Stephanie',''), '%')) or (substring_index(trim(trailing '.cdb' from crac.actionPartyClean),'\\','-1') like concat('%', ifnull('Walsh,Stephanie',''), '%'))
    and crac.actionStart between '2014-09-07' and '2014-10-09')
    and ctt.textValue like '%initiative%';

    Can someone please help me understand this? I'm not a coder but this is the project I've been handed and I'm trying to make sense of it.

    Thanks,
    Johnny
11. 

    myDBR Team, Key Master

    When you mix "and" and "or" in SQL, please remember that they have different operator precedence. It is safest to use parentheses to separate the "and" and "or".

    Also, you could consider rewriting the

    substring_index(trim(trailing '.cdb' from crac.actionPartyClean),'\\','-1')

    part as it is not really valid SQL (although MySQL allows quite liberal use of SQL).

    --
    myDBR Team

Reply

You must log in to post.