Click here to Skip to main content
14,364,290 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month



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