SQL issue

(4 posts) (2 voices)

Tags:

No tags yet.

  1. ken@pesttrend.com, Member

    Having a problem with CASE statements in the WHERE clause. The MyDBR interpreter seems to be ignoring the CASE statement in my SQL. It returns all the possible rows every time.

    Have I got the CASE statement syntax wrong. This SQL worked fine in our old reporting engine which is why I'm thinking it is the MyDBR interpreter.

    In the following "param_actionstatus" is a parameter passed into the stored procedure.

    SQL is as follows:

    SELECT
    *
    FROM
    --My tables--
    WHERE
    CASE param_actionstatus
    WHEN 'open' THEN actionentry.actionstatus='open'
    WHEN 'all' THEN (actionentry.actionstatus='closed' OR actionentry.actionstatus<>'closed')
    WHEN 'unallocated' THEN (actionentry.actionstatus='open' OR actionentry.actionstatus='Awaiting Allocation')
    WHEN 'nonupdateclosed' THEN (actionentry.actionstatus='closed' AND actionentry.inspectionresults='No updates')
    ELSE actionentry.actionstatus=param_actionstatus
    END

  2. myDBR Team, Key Master

    Hi,
    there is no "myDBR interpreter". myDBR passes the stored procedure call to the database engine which executes it as any other query from any other client. if you get all the rows from the table, there is another explanation for it.

    Can you show the full report code? What is the value you pass in as a parameter? If you do not want to show it here in the forum, you can open a support ticket for it.

    --
    myDBR Team

  3. ken@pesttrend.com, Member

    The full procedure is as follows:

    DROP PROCEDURE IF EXISTS sp_pt_4_action_list
    $$
    CREATE PROCEDURE `sp_pt_4_action_list`(param_groupownercompanyid INT, param_selectedcompanyid INT, param_actionstatus VARCHAR(100), param_actiontype VARCHAR(100), param_actionpriority VARCHAR(100), param_dayssinceactionraised INT, param_yearssinceactionraised INT, param_allocateid VARCHAR(100), param_locationid VARCHAR(100))
    BEGIN

    /* Report drill throughs */

    /* Report general styling */
    select 'dbr.headerstyle', mydbr_style('tableheaderstyle');
    select 'dbr.resultclass', 'myborder';
    select 'dbr.colstyle', 'actionid', mydbr_style('center column data');

    /* Report Title */
    select 'dbr.title', 'R4: Actions/Recommendations';

    select 'dbr.export.options', 'header.title', 'R4: Actions/Recommendations';

    /* Report specific commands and styling */
    select 'dbr.rowclass', 'actionclass';

    /* Report hidden columns */
    select 'dbr.hidecolumn', 'actionclass';
    select 'dbr.hidecolumn', 'actionentryid';
    select 'dbr.hidecolumn', 'actionpriority';
    select 'dbr.hidecolumn', 'actiontype';

    /* Report SQL */
    SELECT
    IF(actionpriority = 'High','redclass', IF(actionpriority = 'Medium','orangeclass','greenclass')) AS 'actionclass',
    (
    SELECT
    companyname
    FROM
    f42910_flytrend2.company
    WHERE
    companyid = member.companymemberid
    ) AS 'Facility [sitename]',
    (
    SELECT
    CAST(
    CASE actiontype
    WHEN 'trendaction' THEN CONCAT('TR',actionnumber)
    WHEN 'groupaction' THEN CONCAT('Grp',actionnumber)
    WHEN 'generalaction' THEN CONCAT('Gen',actionnumber)
    WHEN 'contractaction' THEN CONCAT('Crt',actionnumber)
    WHEN 'recommendation' THEN CONCAT('Rec',actionnumber)
    WHEN 'auditaction' THEN CONCAT('Aud',actionnumber)
    END
    AS CHAR)
    ) AS 'Action id [actionid]',
    dateactionraised AS 'Date action raised[dateactionraised]',
    entrytext AS 'Description[entrytext]',
    (
    SELECT
    CASE actionentry.locationid
    WHEN 0 THEN ('All Locations')
    ELSE (SELECT locationname FROM f42910_flytrend2.location WHERE locationid=actionentry.locationid)
    END
    )AS 'Location[actionlocationname]',
    SUBSTRING(
    ( SELECT
    username
    FROM
    f42910_flytrend2.users
    WHERE
    userid =allocateid
    ),1,30)AS 'Current Action Owner[ownerusername]',
    actionstatus AS 'Action Status[actionstatus]',
    inspectionresults AS 'Action Update[inspectionresults]',
    CAST(ifnull(actionverificationdate, 'Open') as char) AS 'Action Close Date[actionverificationdate]',
    IFNULL(SUBSTRING(
    ( SELECT
    username
    FROM
    f42910_flytrend2.users
    WHERE
    userid =actionverifiedby
    ),1,30), 'Open') AS 'Action Closed By[actionverifiedby]',
    actionentryid,
    actionpriority,
    actiontype
    FROM
    f42910_flytrend2.actionentry
    LEFT JOIN f42910_flytrend2.efk
    ON efk.efkid=actionentry.efkid
    LEFT JOIN f42910_flytrend2.location
    ON efk.locationid = location.locationid
    INNER JOIN
    (
    SELECT companymemberid
    FROM f42910_flytrend2.company
    INNER JOIN f42910_flytrend2.corporategroupdef
    ON companyid = groupownercompanyid
    INNER JOIN f42910_flytrend2.groupmemberlink
    ON corporategroupdef.corporategroupdefid = groupmemberlink.corporategroupdefid
    WHERE
    (corporategroupdef.groupownercompanyid = param_groupownercompanyid
    OR corporategroupdef.groupclientcompanyid = param_selectedcompanyid)
    AND membershipstate = 'Approved'
    UNION
    SELECT param_selectedcompanyid AS companymemberid
    ) AS member
    ON member.companymemberid = actionentry.companyid
    WHERE
    actionentry.companyid LIKE IF(param_selectedcompanyid='all', '%', param_selectedcompanyid)
    AND
    (
    CASE param_actionstatus
    WHEN 'Open' THEN actionentry.actionstatus <>'Closed'
    WHEN 'Closed' THEN actionentry.actionstatus = 'Closed'
    WHEN 'all' THEN (actionentry.actionstatus='Closed' OR actionentry.actionstatus<>'Closed')
    WHEN 'Unallocated' THEN (actionentry.actionstatus='Open' OR actionentry.actionstatus='Awaiting Allocation')
    WHEN 'nonupdateclosed' THEN (actionentry.actionstatus='Closed' AND actionentry.inspectionresults='No updates')
    ELSE actionentry.actionstatus=param_actionstatus
    END
    )
    AND
    (
    CASE param_actiontype
    WHEN 'all' THEN actionentry.actiontype <> 'all'
    WHEN 'norec' THEN actionentry.actiontype <> 'recommendation'
    ELSE actionentry.actiontype=param_actiontype
    END
    )
    AND
    (
    CASE param_actionpriority
    WHEN 'all' THEN actionentry.actionpriority <> 'all'
    ELSE actionentry.actionpriority=param_actionpriority
    END

    )
    AND dateactionraised > (CURDATE()- INTERVAL param_dayssinceactionraised DAY)
    AND dateactionraised > (CURDATE()- INTERVAL param_yearssinceactionraised YEAR)
    AND actionentry.allocateid LIKE IF(param_allocateid='all', '%', param_allocateid)
    AND actionentry.locationid LIKE IF(param_locationid='all', '%', param_locationid)
    ORDER BY
    '[sitename]', dateactionraised, actionentry.efkid, actionentryid;

    END
    $$

  4. myDBR Team, Key Master

    Have you tried running the procedure from the command line so you see that the result is the same as from myDBR? You can see what command myDBR ran from the Statistics and you can copy and paste the same command 'call sp_pt_4_action_list(...)' from there.

    Difficult to pinpoint the reason why all the rows would come as a result.

    With a quick look, you've got couple of errors in the report:

    • ORDER BY '[sitename]' does not mean anything as it is treated as a string
    • param_selectedcompanyid is defined as INT, yet you compare it against a string 'all'

    Try simplifying the WHERE-part by just leaving the CASE part if that is what you suspect does not work.

    --
    myDBR Team


Reply

You must log in to post.