Unknown system variable when creating procedure in MySQL [closed]
-2
I don't know how to create a stored procedure and using select in while statement. This query will come to this error "unknown system variable 'tableview'". Anyone help me please to repair it. Thanks for your helping.
CREATE PROCEDURE tablebts()
BEGIN
DECLARE x INT;
SET x=0;
SET tableview;
WHILE x < 10 DO
SET x=x + 1;
SET tableview = SELECT idbts
FROM (SELECT idbts, namesite, COUNT(idbts) AS jumlah
FROM (SELECT ts.site_id AS idbts, tps.msisdn AS msisdn, tb.site_name AS namesite
FROM tb_profile_subscribers tps
INNER JOIN tb_sitemap ts
ON tps.lac=ts.lac2g
or tps.lac=ts.lac3g
AND tps.cellid=ts.ci2g
OR tps.cellid=ts.ci3g_850
OR tps.cellid=ts.ci3g_2100_1
OR tps.cellid=ts.ci3g_2100_2
OR tps.cellid=ts.ci3g_2100_3
INNER JOIN tb_bts tb
ON ts.site_id=tb.site_id
GROUP BY msisdn) msisdn
GROUP BY idbts
ORDER BY jumlah DESC
LIMIT 1 OFFSET x) AS tabelbts
END WHILE;
SELECT tableview;
END$$
I also put x variable in tableview query (the word "offset x"). Is that true? Help me please and thanks in advance
mysql
edited Dec 22 '18 at 15:13
marc_s
572k12811071254
asked Nov 22 '18 at 7:15
MeyLinMeyLin
44
closed as off-topic by emix, Nick, Madhur Bhaiya, tripleee, Matthieu Brucher Nov 22 '18 at 8:00
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "This question was caused by a problem that can no longer be reproduced or a simple typographical error. While similar questions may be on-topic here, this one was resolved in a manner unlikely to help future readers. This can often be avoided by identifying and closely inspecting the shortest program necessary to reproduce the problem before posting." – emix, Nick, Madhur Bhaiya, tripleee, Matthieu Brucher
If this question can be reworded to fit the rules in the help center, please edit the question.
|
show 10 more comments
-2
I don't know how to create a stored procedure and using select in while statement. This query will come to this error "unknown system variable 'tableview'". Anyone help me please to repair it. Thanks for your helping.
CREATE PROCEDURE tablebts()
BEGIN
DECLARE x INT;
SET x=0;
SET tableview;
WHILE x < 10 DO
SET x=x + 1;
SET tableview = SELECT idbts
FROM (SELECT idbts, namesite, COUNT(idbts) AS jumlah
FROM (SELECT ts.site_id AS idbts, tps.msisdn AS msisdn, tb.site_name AS namesite
FROM tb_profile_subscribers tps
INNER JOIN tb_sitemap ts
ON tps.lac=ts.lac2g
or tps.lac=ts.lac3g
AND tps.cellid=ts.ci2g
OR tps.cellid=ts.ci3g_850
OR tps.cellid=ts.ci3g_2100_1
OR tps.cellid=ts.ci3g_2100_2
OR tps.cellid=ts.ci3g_2100_3
INNER JOIN tb_bts tb
ON ts.site_id=tb.site_id
GROUP BY msisdn) msisdn
GROUP BY idbts
ORDER BY jumlah DESC
LIMIT 1 OFFSET x) AS tabelbts
END WHILE;
SELECT tableview;
END$$
I also put x variable in tableview query (the word "offset x"). Is that true? Help me please and thanks in advance
mysql
edited Dec 22 '18 at 15:13
marc_s
572k12811071254
asked Nov 22 '18 at 7:15
MeyLinMeyLin
44
closed as off-topic by emix, Nick, Madhur Bhaiya, tripleee, Matthieu Brucher Nov 22 '18 at 8:00
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "This question was caused by a problem that can no longer be reproduced or a simple typographical error. While similar questions may be on-topic here, this one was resolved in a manner unlikely to help future readers. This can often be avoided by identifying and closely inspecting the shortest program necessary to reproduce the problem before posting." – emix, Nick, Madhur Bhaiya, tripleee, Matthieu Brucher
If this question can be reworded to fit the rules in the help center, please edit the question.
7
You forgetDECLARE tableview INT;?
– Salman A
Nov 22 '18 at 7:18
2
Like @SalmanA said you forgot to declare thetableviewproperty. But whats your goals with this query? Because you override constantly thetableviewvariable and so basically you only return the 10th record?
– Yoram de Langen
Nov 22 '18 at 7:24
If you return a value, it would be better to create aFUNCTION
– gaborsch
Nov 22 '18 at 7:35
You may find this a useful read stackoverflow.com/questions/11754781/…
– P.Salmon
Nov 22 '18 at 7:35
@Salman A I don't wanna make the table view as integer, it looks like a create view of table. There's no problem when I try the select query, the problem when I make it a while for looping the offset in that query. Do you have another suggestion without using procedure?
– MeyLin
Nov 22 '18 at 7:55
|
show 10 more comments
-2
-2
-2
I don't know how to create a stored procedure and using select in while statement. This query will come to this error "unknown system variable 'tableview'". Anyone help me please to repair it. Thanks for your helping.
CREATE PROCEDURE tablebts()
BEGIN
DECLARE x INT;
SET x=0;
SET tableview;
WHILE x < 10 DO
SET x=x + 1;
SET tableview = SELECT idbts
FROM (SELECT idbts, namesite, COUNT(idbts) AS jumlah
FROM (SELECT ts.site_id AS idbts, tps.msisdn AS msisdn, tb.site_name AS namesite
FROM tb_profile_subscribers tps
INNER JOIN tb_sitemap ts
ON tps.lac=ts.lac2g
or tps.lac=ts.lac3g
AND tps.cellid=ts.ci2g
OR tps.cellid=ts.ci3g_850
OR tps.cellid=ts.ci3g_2100_1
OR tps.cellid=ts.ci3g_2100_2
OR tps.cellid=ts.ci3g_2100_3
INNER JOIN tb_bts tb
ON ts.site_id=tb.site_id
GROUP BY msisdn) msisdn
GROUP BY idbts
ORDER BY jumlah DESC
LIMIT 1 OFFSET x) AS tabelbts
END WHILE;
SELECT tableview;
END$$
I also put x variable in tableview query (the word "offset x"). Is that true? Help me please and thanks in advance
mysql
edited Dec 22 '18 at 15:13
marc_s
572k12811071254
asked Nov 22 '18 at 7:15
MeyLinMeyLin
44
I don't know how to create a stored procedure and using select in while statement. This query will come to this error "unknown system variable 'tableview'". Anyone help me please to repair it. Thanks for your helping.
CREATE PROCEDURE tablebts()
BEGIN
DECLARE x INT;
SET x=0;
SET tableview;
WHILE x < 10 DO
SET x=x + 1;
SET tableview = SELECT idbts
FROM (SELECT idbts, namesite, COUNT(idbts) AS jumlah
FROM (SELECT ts.site_id AS idbts, tps.msisdn AS msisdn, tb.site_name AS namesite
FROM tb_profile_subscribers tps
INNER JOIN tb_sitemap ts
ON tps.lac=ts.lac2g
or tps.lac=ts.lac3g
AND tps.cellid=ts.ci2g
OR tps.cellid=ts.ci3g_850
OR tps.cellid=ts.ci3g_2100_1
OR tps.cellid=ts.ci3g_2100_2
OR tps.cellid=ts.ci3g_2100_3
INNER JOIN tb_bts tb
ON ts.site_id=tb.site_id
GROUP BY msisdn) msisdn
GROUP BY idbts
ORDER BY jumlah DESC
LIMIT 1 OFFSET x) AS tabelbts
END WHILE;
SELECT tableview;
END$$
I also put x variable in tableview query (the word "offset x"). Is that true? Help me please and thanks in advance
mysql
mysql
edited Dec 22 '18 at 15:13
marc_s
572k12811071254
asked Nov 22 '18 at 7:15
MeyLinMeyLin
44
edited Dec 22 '18 at 15:13
marc_s
572k12811071254
asked Nov 22 '18 at 7:15
MeyLinMeyLin
44
edited Dec 22 '18 at 15:13
marc_s
572k12811071254
edited Dec 22 '18 at 15:13
marc_s
572k12811071254
edited Dec 22 '18 at 15:13
marc_s
572k12811071254
572k12811071254
asked Nov 22 '18 at 7:15
MeyLinMeyLin
44
asked Nov 22 '18 at 7:15
MeyLinMeyLin
44
asked Nov 22 '18 at 7:15
MeyLinMeyLin
44
44
closed as off-topic by emix, Nick, Madhur Bhaiya, tripleee, Matthieu Brucher Nov 22 '18 at 8:00
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "This question was caused by a problem that can no longer be reproduced or a simple typographical error. While similar questions may be on-topic here, this one was resolved in a manner unlikely to help future readers. This can often be avoided by identifying and closely inspecting the shortest program necessary to reproduce the problem before posting." – emix, Nick, Madhur Bhaiya, tripleee, Matthieu Brucher
If this question can be reworded to fit the rules in the help center, please edit the question.
closed as off-topic by emix, Nick, Madhur Bhaiya, tripleee, Matthieu Brucher Nov 22 '18 at 8:00
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "This question was caused by a problem that can no longer be reproduced or a simple typographical error. While similar questions may be on-topic here, this one was resolved in a manner unlikely to help future readers. This can often be avoided by identifying and closely inspecting the shortest program necessary to reproduce the problem before posting." – emix, Nick, Madhur Bhaiya, tripleee, Matthieu Brucher
If this question can be reworded to fit the rules in the help center, please edit the question.
7
You forgetDECLARE tableview INT;?
– Salman A
Nov 22 '18 at 7:18
2
Like @SalmanA said you forgot to declare thetableviewproperty. But whats your goals with this query? Because you override constantly thetableviewvariable and so basically you only return the 10th record?
– Yoram de Langen
Nov 22 '18 at 7:24
If you return a value, it would be better to create aFUNCTION
– gaborsch
Nov 22 '18 at 7:35
You may find this a useful read stackoverflow.com/questions/11754781/…
– P.Salmon
Nov 22 '18 at 7:35
@Salman A I don't wanna make the table view as integer, it looks like a create view of table. There's no problem when I try the select query, the problem when I make it a while for looping the offset in that query. Do you have another suggestion without using procedure?
– MeyLin
Nov 22 '18 at 7:55
|
show 10 more comments
7
You forgetDECLARE tableview INT;?
– Salman A
Nov 22 '18 at 7:18
2
Like @SalmanA said you forgot to declare thetableviewproperty. But whats your goals with this query? Because you override constantly thetableviewvariable and so basically you only return the 10th record?
– Yoram de Langen
Nov 22 '18 at 7:24
If you return a value, it would be better to create aFUNCTION
– gaborsch
Nov 22 '18 at 7:35
You may find this a useful read stackoverflow.com/questions/11754781/…
– P.Salmon
Nov 22 '18 at 7:35
@Salman A I don't wanna make the table view as integer, it looks like a create view of table. There's no problem when I try the select query, the problem when I make it a while for looping the offset in that query. Do you have another suggestion without using procedure?
– MeyLin
Nov 22 '18 at 7:55
7
7
You forget
DECLARE tableview INT;?– Salman A
Nov 22 '18 at 7:18
You forget
DECLARE tableview INT;?– Salman A
Nov 22 '18 at 7:18
2
2
Like @SalmanA said you forgot to declare the
tableview property. But whats your goals with this query? Because you override constantly the tableview variable and so basically you only return the 10th record?– Yoram de Langen
Nov 22 '18 at 7:24
Like @SalmanA said you forgot to declare the
tableview property. But whats your goals with this query? Because you override constantly the tableview variable and so basically you only return the 10th record?– Yoram de Langen
Nov 22 '18 at 7:24
If you return a value, it would be better to create a
FUNCTION– gaborsch
Nov 22 '18 at 7:35
If you return a value, it would be better to create a
FUNCTION– gaborsch
Nov 22 '18 at 7:35
You may find this a useful read stackoverflow.com/questions/11754781/…
– P.Salmon
Nov 22 '18 at 7:35
You may find this a useful read stackoverflow.com/questions/11754781/…
– P.Salmon
Nov 22 '18 at 7:35
@Salman A I don't wanna make the table view as integer, it looks like a create view of table. There's no problem when I try the select query, the problem when I make it a while for looping the offset in that query. Do you have another suggestion without using procedure?
– MeyLin
Nov 22 '18 at 7:55
@Salman A I don't wanna make the table view as integer, it looks like a create view of table. There's no problem when I try the select query, the problem when I make it a while for looping the offset in that query. Do you have another suggestion without using procedure?
– MeyLin
Nov 22 '18 at 7:55
|
show 10 more comments
1 Answer
1
active
oldest
votes
1
Not related to your problem but there are somethings you need to fix too.
- You have to use parenthesis to force correct operator order
Right now you have A OR B AND C OR D Because AND have precedence you really have
A OR ( B AND C ) OR D
instead you need
(A OR B) AND (C OR D)
Or you can use
INoperator to improve reading
INNER JOIN tb_sitemap ts
ON tps.lac IN ( ts.lac2g, ts.lac3g )
AND tps.cellid IN ( ts.ci2g, ts.ci3g_850, ts.ci3g_2100_1,
ts.ci3g_2100_2, ts.ci3g_2100_3 )
answered Nov 22 '18 at 7:31
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
Thanks for ur helping. But there's no problem about select query. It's success to view a table. The problem is just about looping the offset value (x). Do u have any suggestion? Thanks before
– MeyLin
Nov 22 '18 at 8:00
But in the brackets u have explained me, just be used for "AND". I make it as "OR"
– MeyLin
Nov 22 '18 at 8:45
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Not related to your problem but there are somethings you need to fix too.
- You have to use parenthesis to force correct operator order
Right now you have A OR B AND C OR D Because AND have precedence you really have
A OR ( B AND C ) OR D
instead you need
(A OR B) AND (C OR D)
Or you can use
INoperator to improve reading
INNER JOIN tb_sitemap ts
ON tps.lac IN ( ts.lac2g, ts.lac3g )
AND tps.cellid IN ( ts.ci2g, ts.ci3g_850, ts.ci3g_2100_1,
ts.ci3g_2100_2, ts.ci3g_2100_3 )
answered Nov 22 '18 at 7:31
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
Thanks for ur helping. But there's no problem about select query. It's success to view a table. The problem is just about looping the offset value (x). Do u have any suggestion? Thanks before
– MeyLin
Nov 22 '18 at 8:00
But in the brackets u have explained me, just be used for "AND". I make it as "OR"
– MeyLin
Nov 22 '18 at 8:45
add a comment |
1
Not related to your problem but there are somethings you need to fix too.
- You have to use parenthesis to force correct operator order
Right now you have A OR B AND C OR D Because AND have precedence you really have
A OR ( B AND C ) OR D
instead you need
(A OR B) AND (C OR D)
Or you can use
INoperator to improve reading
INNER JOIN tb_sitemap ts
ON tps.lac IN ( ts.lac2g, ts.lac3g )
AND tps.cellid IN ( ts.ci2g, ts.ci3g_850, ts.ci3g_2100_1,
ts.ci3g_2100_2, ts.ci3g_2100_3 )
answered Nov 22 '18 at 7:31
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
Thanks for ur helping. But there's no problem about select query. It's success to view a table. The problem is just about looping the offset value (x). Do u have any suggestion? Thanks before
– MeyLin
Nov 22 '18 at 8:00
But in the brackets u have explained me, just be used for "AND". I make it as "OR"
– MeyLin
Nov 22 '18 at 8:45
add a comment |
1
1
1
Not related to your problem but there are somethings you need to fix too.
- You have to use parenthesis to force correct operator order
Right now you have A OR B AND C OR D Because AND have precedence you really have
A OR ( B AND C ) OR D
instead you need
(A OR B) AND (C OR D)
Or you can use
INoperator to improve reading
INNER JOIN tb_sitemap ts
ON tps.lac IN ( ts.lac2g, ts.lac3g )
AND tps.cellid IN ( ts.ci2g, ts.ci3g_850, ts.ci3g_2100_1,
ts.ci3g_2100_2, ts.ci3g_2100_3 )
answered Nov 22 '18 at 7:31
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
Not related to your problem but there are somethings you need to fix too.
- You have to use parenthesis to force correct operator order
Right now you have A OR B AND C OR D Because AND have precedence you really have
A OR ( B AND C ) OR D
instead you need
(A OR B) AND (C OR D)
Or you can use
INoperator to improve reading
INNER JOIN tb_sitemap ts
ON tps.lac IN ( ts.lac2g, ts.lac3g )
AND tps.cellid IN ( ts.ci2g, ts.ci3g_850, ts.ci3g_2100_1,
ts.ci3g_2100_2, ts.ci3g_2100_3 )
answered Nov 22 '18 at 7:31
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
edited Nov 22 '18 at 14:10
answered Nov 22 '18 at 7:31
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
answered Nov 22 '18 at 7:31
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
answered Nov 22 '18 at 7:31
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
36k63876
Thanks for ur helping. But there's no problem about select query. It's success to view a table. The problem is just about looping the offset value (x). Do u have any suggestion? Thanks before
– MeyLin
Nov 22 '18 at 8:00
But in the brackets u have explained me, just be used for "AND". I make it as "OR"
– MeyLin
Nov 22 '18 at 8:45
add a comment |
Thanks for ur helping. But there's no problem about select query. It's success to view a table. The problem is just about looping the offset value (x). Do u have any suggestion? Thanks before
– MeyLin
Nov 22 '18 at 8:00
But in the brackets u have explained me, just be used for "AND". I make it as "OR"
– MeyLin
Nov 22 '18 at 8:45
Thanks for ur helping. But there's no problem about select query. It's success to view a table. The problem is just about looping the offset value (x). Do u have any suggestion? Thanks before
– MeyLin
Nov 22 '18 at 8:00
Thanks for ur helping. But there's no problem about select query. It's success to view a table. The problem is just about looping the offset value (x). Do u have any suggestion? Thanks before
– MeyLin
Nov 22 '18 at 8:00
But in the brackets u have explained me, just be used for "AND". I make it as "OR"
– MeyLin
Nov 22 '18 at 8:45
But in the brackets u have explained me, just be used for "AND". I make it as "OR"
– MeyLin
Nov 22 '18 at 8:45
add a comment |
7
You forget
DECLARE tableview INT;?– Salman A
Nov 22 '18 at 7:18
2
Like @SalmanA said you forgot to declare the
tableviewproperty. But whats your goals with this query? Because you override constantly thetableviewvariable and so basically you only return the 10th record?– Yoram de Langen
Nov 22 '18 at 7:24
If you return a value, it would be better to create a
FUNCTION– gaborsch
Nov 22 '18 at 7:35
You may find this a useful read stackoverflow.com/questions/11754781/…
– P.Salmon
Nov 22 '18 at 7:35
@Salman A I don't wanna make the table view as integer, it looks like a create view of table. There's no problem when I try the select query, the problem when I make it a while for looping the offset in that query. Do you have another suggestion without using procedure?
– MeyLin
Nov 22 '18 at 7:55