Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: CASE WHEN in mySQL not working with Parameters?

  1. #1

    Default CASE WHEN in mySQL not working with Parameters?

    Hi,

    I have a mySQL query with a CASE WHEN statement. If I use parameters (like ${startDate}), the query does not work. I dont get a result. If I replace the paramaters with real data, like "2010-01-01", the query works.

    It looks as if the parameters dont get passed to the CASE WHEN statement.

    Dirk

  2. #2
    Join Date
    May 2010
    Posts
    133

    Default

    CHOOSE THE StartDate as String Parameter .
    If you are comparing the dates then there is no need to choose like statement you can get this as follow
    case when date(date_join)=${startDate}

  3. #3

    Default

    Thanks, I just tried what you suggested, but it still wont work sadly

    This is my original query:

    SELECT
    SUM(CASE WHEN kuendigungsdatum >= ${endDate} THEN (laufzeit - PERIOD_DIFF(DATE_FORMAT(kuendigungsdatum,"%Y%m"), DATE_FORMAT(${endDate},"%Y%m"))) ELSE laufzeit END) / COUNT(merchantNumber)
    FROM
    posFdLaufzeit
    WHERE salesAgentCode = ${salesAgentCode} AND activeDate > "2008-12-31" AND activeDate BETWEEN ${startDate} AND ${endDate}

    Now I rewrote it and added the DATE() function as you suggested:

    SELECT
    SUM(CASE WHEN DATE(kuendigungsdatum) >= DATE(${endDate}) THEN (laufzeit - PERIOD_DIFF(DATE_FORMAT(DATE(kuendigungsdatum),"%Y%m"),DATE_FORMAT(DATE(${endDate}),"%Y%m"))) ELSE laufzeit END) / COUNT(merchantNumber)
    FROM
    posFdLaufzeit
    WHERE salesAgentCode = ${salesAgentCode} AND activeDate > "2008-12-31" AND activeDate BETWEEN DATE(${startDate}) AND DATE(${endDate})


    I also changed my input parameters to String & Textbox instead of Date (SQL) & Datepicker.

    But still, the result is empty. It makes no use of the parameters.
    Last edited by dirks; 08-31-2010 at 08:37 AM.

  4. #4
    Join Date
    May 2010
    Posts
    133

    Default

    Try This

    SELECT
    SUM(CASE WHEN DATE(kuendigungsdatum) >= ${endDate} THEN (laufzeit - PERIOD_DIFF(DATE_FORMAT(DATE(kuendigungsdatum),"%Y %m"),DATE_FORMAT(DATE(${endDate}),"%Y%m"))) ELSE laufzeit END) / COUNT(merchantNumber)
    FROM
    posFdLaufzeit
    WHERE salesAgentCode = ${salesAgentCode} AND activeDate > "2008-12-31" AND activeDate BETWEEN ${startDate}) AND ${endDate}

  5. #5

    Default

    Thanks, but that doesnt work either.

    Even something very simple like this doesnt work, so I assume there is something wrong with parameters.
    SELECT
    CASE WHEN ${A} = 1 THEN "ONE" ELSE "NOT ONE" END;

    I even tried IF() instead, but that doesnt work also.

  6. #6
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    What does the log say?
    I have found on more than one occasion that I was mixing incorrect types, which only showed up in the log...
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  7. #7

    Default

    Does the Report Designer Preview has a log? I couldnt find one.

  8. #8
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    I made a debug copy of the report-designer.bat (and called it report-designer-debug.bat) where I changed
    set PENTAHO_JAVA=javaw
    to
    set PENTAHO_JAVA=java

    And then ran the debug version. This then has a window with a lot of chatter, but it shows you the actual queries that get sent to your DB.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  9. #9
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Starting with PRD-3.6 you can also configure log4j via the config-file that you can find in the resources directory. (Well, the same also works for older versions, but you have to create the log4j.xml file from scratch.)
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  10. #10

    Default

    Name:  case-when.jpg
Views: 165
Size:  25.1 KB

    It seem my parameter gets replaced with NULL. Whats up with that?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.