MySQL Gurus,
I'm converting some reports from a MSSQL database for usage on a MySQL Database, and don't seem to understand how the DECLARE works in MySQL. Below is the SQL code for the report, as works in MSSQL. I read that DECLARE can only be use in a nested function, I belive, but that just does not sound right to me.
Current Report SQL: (I parse & replace the values of Current & Pending from my app code)
DECLARE @Current int;
DECLARE @Pending int;
SET @Current = [1];
SET @Pending = [3];
Select Ticket.TIcketID,
ISNULL((Select LocationName from Location where LocationID = Ticket.SiteCurrentLocation), 'Invalid Location') as [Current Location],
ISNULL((Select LocationName from Location where LocationID = Ticket.SitePendingLocation), 'Invalid Location') as [Pending Location]
from Ticket
where
(SitePendingLocation > 0 AND SitePendingLocation <> SiteCurrentLocation) AND
(SiteCurrentLocation = @Current OR @Current = 0) AND
(SitePendingLocation = @Pending OR @Current = 0)
Any insight?
Thanks - Andrew
EDIT
Working, converted script - that it may help others:
SET @Current = '1';
SET @Pending = '1';
Select Ticket.TIcketID,
IFNULL((Select LocationName from Location where LocationID = Ticket.SiteCurrentLocation), 'Invalid Location') as `Current Location`,
IFNULL((Select LocationName from Location where LocationID = Ticket.SitePendingLocation), 'Invalid Location') as `Pending Location`
from Ticket
where
(SitePendingLocation > 0 AND SitePendingLocation <> SiteCurrentLocation) AND
(SiteCurrentLocation = @Current OR @Current = 0) AND
(SitePendingLocation = @Pending OR @Current = 0)
