SQL case statement doesn't work with multiple parameter pentaho cde

Question

I have a question: I have a table with a simple selector that filtered the table by user. at this moment I want the selector to be multiple in order to filter the data by several users. by default I put the user ' Todos' (all in english), in case of choosing the user ' Todos' I choose the column of the table to select all users. Otherwise I take the selected user.

SELECT DISTINCT 
    p.pname AS project, 
    SUM(w.timeworked / 3600) OVER () AS sum_all_hours, 
    SUM(w.timeworked / 3600) OVER (PARTITION BY p.pname) AS suma_proyecto 
FROM            
    jira.jiraissue j, 
    jira.worklog w, 
    jira.project p 
WHERE
    w.issueid = j.id 
    AND j.project = p.id 
    AND w.author IN (${Autor})
    AND p.pname IN ('Area Económica', 
                    'Proyectos Clinicos', 
                    'Proyectos. Con sus componentes', 
                    'Despliege y Soporte') 
    AND TO_CHAR(w.startdate,'yyyy-mm-dd') >= ${FromDate} 
    AND TO_CHAR(w.startdate,'yyyy-mm-dd') <= ${ToDate}

This code works fine but omits the user ' Todos'.

If I put the following code, the user works ' Todos' and if I select a single user but the multiple selection does not show me anything.

SELECT DISTINCT p.pname                                              AS project, 
                Sum(w.timeworked / 3600) OVER ()                     AS sum_all_hours, 
                Sum(w.timeworked / 3600) OVER (partition BY p.pname) AS suma_proyecto 
FROM            jira.jiraissue j, 
                jira.worklog w, 
                jira.project p 
WHERE           w.issueid=j.id 
AND             j.project=p.id 

 AND            w.author in(
                CASE 
                                WHEN ${Autor} = ' Todos' THEN author 
                                else ${Autor}

                END) 


AND             p.pname IN ('Area Económica', 
                            'Proyectos Clinicos', 
                            'Proyectos. Con sus componentes', 
                            'Despliege y Soporte') 
AND             to_char(w.startdate,'yyyy-mm-dd') >=${FromDate} 
AND             to_char(w.startdate,'yyyy-mm-dd') <=${ToDate}

Difficult to see what the issue is, but I notice that ' Todos' starts with a space. Is there a chance you are trimming the search parameter or something? — Joe Love
I put the space, I wanted to show the first option in the list. — ilanas
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (25 years ago) and its use is discouraged — marc_s

kfinity kfinity · Accepted Answer · 2018-10-10T15:22:18

Since I don't have pentaho CDE, I'm not sure why your case expression isn't working - it looks fine. I think it might have to do with the way pentaho handles parameters. I'd suggest writing your logic a different way, e.g. :

AND (w.author in (${Autor})
     OR ${Autor} = ' Todos')

That might help if the problem is just the way pentaho expands multiple-value parameters. But if the problem is that it doesn't like having a named parameter more than once in the query... that's trickier, I have no idea.

SQL case statement doesn't work with multiple parameter pentaho cde

1 Answers