I run under sql server it gives me fixed number but i run same query on metbase it return non fixed value after press spin button. please tell me why?
I take it #tonkho is being populated through the dsa_kehoach_kho SP? Is it supposed to generate deterministic output each time its run? Does it truncate the table first?
Does it change the output of SELECT SUM(…) if you drop the temp table after running the select, then re-run the query?
Is it a problem that you named the temp table differently between the SQL Server console and Metabase? In the SQL Server console its #tonkho but in metabase its tonkho. (My SQL Server is rusty, I don’t remember if the hash is ignored, I know it’s a convention to name temp tables starting with it.)
I tested it and it works fine. After reloading it is still a fixed number. I don't know if my input has many lines of data so there is a problem loading it into the temporary table or not?
My theory is that the session is reusing an earlier temp table, so when you populate it with the SP, there is data in it already that the SP adds to, rather than inserting into an empty table. When you SELECT SUM(…) later, it adds the old and new data together.
As a check, if your SP always outputs the same number of rows, run SELECT COUNT(*) FROM … and verify the same number of rows is in the table each time you run the query. If it’s magically increasing, then this is your problem, most likely.
Metabase maintains a pool of sessions to the database. These sessions are long-lived. Things done in those sessions can potentially impact later activity that happens to use the same session, if you do things Metabase is not aware of, like create temp tables.
I don’t believe Metabase uses transaction control on these sessions, and there could be a bug in the SQL Server connector that doesn’t reset the session state after each query – discarding temp tables, restoring settings, and the like. Thus, they could live on after your query.
My table has more than 2000 rows, but currently the temporary table in metabase only takes a maximum of 2000 rows, right?
Yes, but nothing you’re doing there hits that limit, it’s all in the database. If you hit reload a few times, does the number increase?
it is always fixed at 2000 lines =)))
and I understand the reason, because my ton00 data is under 2000 rows, the number is always fixed, but the 2 columns sl_nhap and sl_xuat are larger than 2000 rows, so every time it reloads, it randomly picks 2000 random rows instead of taking all of them, so it changes continuously and is not fixed.