While calling procedure getting empty output but data is present in the table ....Can u help on this syntax logic where did i given mistake in the query and variable is not taking in the select query.I am pasting the output and the procedure info

Nani · October 12, 2022, 11:38am

CREATE OR REPLACE PROCEDURE get123(callType varchar(20) , typeName varchar(30) , timeInterval int(11) NULL, inDate date NULL, site_id varchar(100) ) RETURNS void AS DECLARE
msgType varchar(10) = ‘’;
timeInt int = 1800;
selectInterval varchar(200) = ‘’;
orderBy varchar(50) = ‘hour(time)’;
msgTypeFailure varchar(4) = ‘’;
countSelection varchar(200) = ‘’;
site_id_ varchar (100) = ‘all’;

begin
if(site_id is null or site_id=‘ALL’)
then
set @site_id_ = ‘all’;
else
set @site_id_ = “‘site_id’”;
end if;

if(timeInterval != 3600 and timeInterval != 1800 and timeInterval != 900)
then
timeInt = 3600;
echo select timeInt;
else
set @timeInt = “‘timeInterval’”;
echo select timeInt;
end if;

if (typeName=‘SRISM_INCOMING’)
then
set @msgType = ‘14,15’;
set @msgTypeFailure = ‘14’;
set @countSelection = ’ sum(case when ORIG_MSG_TYPE in (14,15) then COUNT else 0 end) tot,sum(case when ORIG_MSG_TYPE=15 then COUNT else 0 end) succ ';

elseif (typeName=‘SRISM_OUTGOING’)
then
set @msgType = ‘15’;
set @msgTypeFailure = ‘15’;
set @countSelection = ’ sum(case when ORIG_MSG_TYPE=15 then COUNT else 0 end) tot,sum(case when ERROR_CODE=255 then COUNT else 0 end) succ ';

elseif (typeName=‘FWDSM_INCOMING’)
then
set @msgType = ‘16,17’;
set @msgTypeFailure = ‘16’;
set @countSelection = 'sum(case when ORIG_MSG_TYPE in (16,17) then COUNT else 0 end) tot,sum(case when ORIG_MSG_TYPE=17 then COUNT else 0 end) succ ';

elseif (typeName=‘FWDSM_OUTGOING’)
then
msgType = ‘17’;
msgTypeFailure = ‘17’;
countSelection = ’ sum(case when ORIG_MSG_TYPE=17 then COUNT else 0 end) tot,sum(case when ERROR_CODE=255 then COUNT else 0 end) succ ';

end if;
ECHO select countSelection;
ECHO SELECT msgType;

if(timeInt = 3600)
then
set @selectInterval = " CONCAT(CAST(hour(time) AS CHAR(2)),’:00’) as selIntr ";

elseif(timeInt = 1800) then
selectInterval = " CONCAT(CAST(hour(TIME) AS CHAR(2)), ‘:’,(CASE WHEN MINUTE(TIME) < 30 THEN ‘00’ ELSE ‘30’ END)) as selIntr ";

elseif(timeInt = 900) then
set @selectInterval = " CONCAT(CAST(hour(TIME) AS CHAR(2)), ‘:’, ( CASE WHEN MINUTE(TIME) < 15 THEN ‘00’ when MINUTE(TIME) <30 then ‘15’ when MINUTE(TIME) <45 then ‘30’ when MINUTE(TIME)> 45 then ‘45’ end)) as selIntr ";

end if;
echo select selectInterval;

if(callType = ‘RATE’)
then
if(inDate = curdate())
then
ECHO select A.selIntr,A.tot,A.succ ,(A.succ/A.tot)*100 succ_per from (select selectInterval,countSelection from SMS_FIREWALL_SUMMARY where ORIG_MSG_TYPE in (msgType) and (time>(now()-interval 24 hour)) and SITE_ID like (case when(strcmp(‘ALL’,site_id_)) then site_id_ else ‘%’ END) group by selIntr order by hour(time) asc)A ;
else
ECHO select “‘A.selIntr’”,"‘A.tot’","‘A.succ’",("‘A.succ’"/"‘A.tot’")*100 succ_per from (select selectInterval,countSelection from SMS_FIREWALL_SUMMARY where ORIG_MSG_TYPE in (msgType) and (date=inDate) and SITE_ID like (case when(strcmp(‘ALL’,site_id_)) then site_id_ else ‘%’ END) group by “‘selIntr’” order by hour(time) asc) A;
end if;
else
if(inDate = curdate())
then
ECHO select ERROR_CODE,sum(COUNT) count from SMS_FIREWALL_SUMMARY_TEST where ORIG_MSG_TYPE=msgTypeFailure and time > (now() - interval 23 hour) and ERROR_CODE!=255 and SITE_ID like (case when(strcmp(‘ALL’,site_id_)) then site_id_ else ‘%’ END)
group by ERROR_CODE order by count desc ;
else
ECHO select ERROR_CODE,sum(COUNT) count from SMS_FIREWALL_SUMMARY_TEST where ORIG_MSG_TYPE=msgTypeFailure and time > (now() - interval 23 hour) and ERROR_CODE!=255 and SITE_ID like (case when(strcmp(‘ALL’,site_id_)) then site_id_ else ‘%’ END)
group by ERROR_CODE order by count desc;

end if;
end if;
END;

O/P

singlestore> CALL get123(‘RATE’,‘FWDSM_OUTGOING’, 3600,‘2022-10-13’,‘ALL’);
±--------+
| timeInt |
±--------+
| 1800 |
±--------+
1 row in set (0.18 sec)

±---------------------------------------------------------------------------------------------------------------------+
| countSelection |
±---------------------------------------------------------------------------------------------------------------------+
| sum(case when ORIG_MSG_TYPE=17 then COUNT else 0 end) tot,sum(case when ERROR_CODE=255 then COUNT else 0 end) succ |
±---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.18 sec)

±--------+
| msgType |
±--------+
| 17 |
±--------+
1 row in set (0.18 sec)

±------------------------------------------------------------------------------------------------------------+
| selectInterval |
±------------------------------------------------------------------------------------------------------------+
| CONCAT(CAST(hour(TIME) AS CHAR(2)), ‘:’,(CASE WHEN MINUTE(TIME) < 30 THEN ‘00’ ELSE ‘30’ END)) as selIntr |
±------------------------------------------------------------------------------------------------------------+
1 row in set (0.18 sec)

Query OK, 0 rows affected (0.24 sec)

Thanks In advance

hanson · October 17, 2022, 9:50pm

I’d recommend taking the big queries and the end, replacing the variables with literals, then get them running. Then add echo select’s to your code to print out the variables before you get to the queries, to make sure they are what you expect.

Nani · October 21, 2022, 7:43am

±---------------------------------------------------------------------------------------------------------------------+
| countSelection |
±---------------------------------------------------------------------------------------------------------------------+
| sum(case when ORIG_MSG_TYPE=17 then COUNT else 0 end) tot,sum(case when ERROR_CODE=255 then COUNT else 0 end) succ |
±---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.18 sec)

This alias names are not coming into the echo query and its showing empty result for this calling in main query.how to pass the column alias name in the query if u observer my procedure …
Please help me to get this its bit urgent…

Ragards
Nani

deyler · October 24, 2022, 6:21pm

Hi Nani can you give us

The schema of your tables
A few rows with INSERT VALUES so we can get some data
The latest stored procedure

Then we can try to reproduce

hanson · October 24, 2022, 9:57pm

is going to fail if you put it in a larger query and try to execute it with dynamic SQL. You can’t use an identifier “COUNT” as a value to be returned from the CASE expression. That is not legal syntax. And if you meant “COUNT” to be a variable name, that won’t work in dynamic SQL either – dynamic SQL can’t refer to variables or parameters from the enclosing stored procedure.

If you want variable values to be injected into your dynamic SQL, create a string that contains the values themselves, not the names of the variables.

While calling procedure getting empty output but data is present in the table ....Can u help on this syntax logic where did i given mistake in the query and variable is not taking in the select query.I am pasting the output and the procedure info

end if; end if; END;

O/P

end if;
end if;
END;