Click here to Skip to main content
14,383,763 members
Rate this:
Please Sign up or sign in to vote.
See more:
I converted SQL query into MySQL query, SQL query give me records, but in Mysql query is doesn't provide any records and neither give any error

//Following is my SQL query
USE [Trackdb]
GO
/****** Object:  StoredProcedure [dbo].[GetRecords]    Script Date: 06-11-2019 09:48:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetRecords]
AS 
BEGIN
DECLARE @Date VARCHAR(15)

create table #TempHotel
(
    CatID Varchar(50), 
    Cnt INT, 
    Date Date
)

create table #TempRestaurant
(
    CatID Varchar(50), 
    Cnt INT, 
    Date Date
)

DECLARE Track_CURSOR CURSOR  
LOCAL  FORWARD_ONLY  FOR  
SELECT Distinct(Convert(date,ms_date)) FROM tbltrackingrtt WHERE ms_date > '2017-11-01' AND heirarchy LIKE '%*145*%' AND category <> 0 AND category <> 145
OPEN Track_CURSOR  
    FETCH NEXT FROM Track_CURSOR INTO  @Date
WHILE @@FETCH_STATUS = 0  
BEGIN  
    --PRINT  'EMP_ID: ' + @Date --+  '  EMP_NAME '+@EMP_NAME +'  EMP_SALARY '  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  '  EMP_CITY ' +@EMP_CITY  

    INSERT INTO #TempHotel
    SELECT TOP 100 Category AS CatID, Count(id) Cnt, convert(Date,ms_date) Date FROM tbltrackingrtt 
    WHERE heirarchy LIKE '%*145*%' AND category <> 0 AND category <> 145 AND CONVERT(DATE,ms_date) = @Date
    GROUP BY Category, convert(Date,ms_date)
    ORDER BY convert(Date,ms_date) ASC, Count(id) DESC

    INSERT INTO #TempRestaurant
    SELECT TOP 100 Category AS CatID, Count(id) Cnt, convert(Date,ms_date) Date FROM tbltrackingrtt 
    WHERE heirarchy LIKE '%*169*%' AND category <> 0 AND category <> 169 AND CONVERT(DATE,ms_date) = @Date
    GROUP BY Category, convert(Date,ms_date)
    ORDER BY convert(Date,ms_date) ASC, Count(id) DESC

    FETCH NEXT FROM Track_CURSOR INTO @Date

END  
SELECT * FROM #TempHotel
SELECT * FROM #TempRestaurant

CLOSE Track_CURSOR  
DEALLOCATE Track_CURSOR 

DROP table #TempHotel
DROP table #TempRestaurant

END


// Following is my Mysql query

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetRecords`()
BEGIN
DECLARE NOT_FOUND INT DEFAULT 0;
DECLARE v_Date VARCHAR(15);

DECLARE Track_CURSOR 
CURSOR FOR
SELECT Distinct CAST(ms_date as Date) FROM tbltrackingrtt WHERE ms_date > '2017-11-01' AND heirarchy LIKE '%*145*%' AND category <> 0 AND category <> 145;
create temporary table TempHotel
(
    CatID Varchar(50), 
    Cnt INT, 
    Date Date
);

create temporary table TempRestaurant
(
    CatID Varchar(50), 
    Cnt INT, 
    Date Date
);
OPEN Track_CURSOR;
   FETCH  Track_CURSOR INTO  v_Date;
WHILE NOT_FOUND = 0  
DO  
    -- PRINT  'EMP_ID: ' + @Date --+  '  EMP_NAME '+@EMP_NAME +'  EMP_SALARY '  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  '  EMP_CITY ' +@EMP_CITY  

    INSERT INTO TempHotel
    SELECT Category AS CatID, Count(id) Cnt, CAST(ms_date as Date) as Date FROM tbltrackingrtt 
    WHERE heirarchy LIKE '%*145*%' AND category <> 0 AND category <> 145 AND CAST(ms_date as Date) = v_Date
    GROUP BY Category, CAST(ms_date as Date)
    ORDER BY CAST(ms_date as Date) ASC, Count(id) DESC LIMIT 100;

    -- select TempHotel;

    INSERT INTO TempRestaurant
    SELECT  Category AS CatID, Count(id) Cnt, CAST(ms_date as Date) as Date  FROM tbltrackingrtt 
    WHERE heirarchy LIKE '%*169*%' AND category <> 0 AND category <> 169 AND CAST(ms_date as Date) = v_Date
    GROUP BY Category, CAST(ms_date as Date)
    ORDER BY CAST(ms_date as Date) ASC, Count(id) DESC LIMIT 100;

    FETCH Track_CURSOR INTO v_Date;

END WHILE;  
SELECT * FROM TempHotel;
SELECT * FROM TempRestaurant

CLOSE;
DROP table TempHotel;
DROP table TempRestaurant;


Anyone help me on this, I don't what I am doing wrong here, is there is any problem in MySQL cursor

What I have tried:

i tried everything but couldnt find any solution
Posted
Updated 5-Nov-19 22:36pm
v2
Comments
digimanus 6-Nov-19 3:35am
   
What is the error/exception/message that hints the problem?
St0rmi 7-Nov-19 7:37am
   
does the query

SELECT Distinct CAST(ms_date as Date) FROM tbltrackingrtt WHERE ms_date > '2017-11-01' AND heirarchy LIKE '%*145*%' AND category <> 0 AND category <> 145;

give you a result when executed directly in SQL workbench?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100