PS. errata, change var1/var2 for col1/col2..
-- SQL START
SET ANSI_WARNINGS OFF
SELECT
id_col,
col1,
col2,
-col1 AS col1_neg,
-col2 AS col2_neg
INTO output_table_temp
FROM input_table;
UPDATE output_table_temp SET col1 = NULL WHERE col1 <= 0
UPDATE output_table_temp SET col2 = NULL WHERE col2 <= 0
UPDATE output_table_temp SET col1_neg = NULL WHERE col1_neg <= 0
UPDATE output_table_temp SET col2_neg = NULL WHERE col2_neg <= 0
DECLARE @avg_col1 AS float
DECLARE @std_col1 AS float
DECLARE @avg_col2 AS float
DECLARE @std_col2 AS float
DECLARE @avg_col1_neg AS float
DECLARE @std_col1_neg AS float
DECLARE @avg_col2_neg AS float
DECLARE @std_col2_neg AS float
SET @avg_col1 = (SELECT avg(log(col1)) FROM output_table_temp)
SET @std_col1 = (SELECT stdev(log(col1)) FROM output_table_temp)
SET @avg_col2 = (SELECT avg(log(col2)) FROM output_table_temp)
SET @std_col2 = (SELECT stdev(log(col2)) FROM output_table_temp)
SET @avg_col1_neg = (SELECT avg(log(col1_neg)) FROM output_table_temp)
SET @std_col1_neg = (SELECT stdev(log(col1_neg)) FROM output_table_temp)
SET @avg_col2_neg = (SELECT avg(log(col2_neg)) FROM output_table_temp)
SET @std_col2_neg = (SELECT stdev(log(col2_neg)) FROM output_table_temp)
SELECT
id_col,
(log(col1) - @avg_col1) / @std_col1 AS col1,
(log(col2) - @avg_col2) / @std_col2 AS col2,
(log(col1_neg) - @avg_col1_neg) / @std_col1_neg AS col1_neg,
(log(col2_neg) - @avg_col2_neg) / @std_col2_neg AS col2_neg
INTO output_table
FROM output_table_temp;
DROP TABLE output_table_temp;
On Tuesday, 7 April 2020 16:09:10 UTC-3, Iván Gómez Villafañe wrote:
>
> Hi Mike, thanks for your reply.
>
> Regarding context, here's a sample sql with 2 cols:
>
> -- SQL START
>
> SET ANSI_WARNINGS OFF
>
> SELECT
> id_col,
> col1,
> col2,
> -var1 AS var1_neg,
> -var2 AS var2_neg
> INTO output_table_temp
> FROM input_table;
>
> UPDATE output_table_temp SET col1 = NULL WHERE col1 <= 0
> UPDATE output_table_temp SET col2 = NULL WHERE col2 <= 0
> UPDATE output_table_temp SET var1_neg = NULL WHERE var1_neg <= 0
> UPDATE output_table_temp SET var2_neg = NULL WHERE var2_neg <= 0
>
> DECLARE @avg_col1 AS float
> DECLARE @std_col1 AS float
> DECLARE @avg_col2 AS float
> DECLARE @std_col2 AS float
> DECLARE @avg_var1_neg AS float
> DECLARE @std_var1_neg AS float
> DECLARE @avg_var2_neg AS float
> DECLARE @std_var2_neg AS float
>
> SET @avg_col1 = (SELECT avg(log(col1)) FROM output_table_temp)
> SET @std_col1 = (SELECT stdev(log(col1)) FROM output_table_temp)
> SET @avg_col2 = (SELECT avg(log(col2)) FROM output_table_temp)
> SET @std_col2 = (SELECT stdev(log(col2)) FROM output_table_temp)
> SET @avg_var1_neg = (SELECT avg(log(var1_neg)) FROM output_table_temp)
> SET @std_var1_neg = (SELECT stdev(log(var1_neg)) FROM output_table_temp)
> SET @avg_var2_neg = (SELECT avg(log(var2_neg)) FROM output_table_temp)
> SET @std_var2_neg = (SELECT stdev(log(var2_neg)) FROM output_table_temp)
>
> SELECT
> id_col,
> (log(col1) - @avg_col1) / @std_col1 AS col1,
> (log(col2) - @avg_col2) / @std_col2 AS col2,
> (log(var1_neg) - @avg_var1_neg) / @std_var1_neg AS var1_neg,
> (log(var2_neg) - @avg_var2_neg) / @std_var2_neg AS var2_neg
> INTO output_table
> FROM output_table_temp;
>
> DROP TABLE output_table_temp;
>
> Without SET ANSI_WARNINGS OFF, the final output_table is not created and
> output_table_temp is not dropped. So the warnings probably correspond to the
> previous SET batch. Keep in mind, 2 columns is fine, the problem arises when
> executing around 20 columns.
>
>
> On Tuesday, 7 April 2020 15:49:47 UTC-3, Mike Bayer wrote:
>>
>> this warning is not related to SQLAlchemy it has to do with the structure
>> of SQL you are passing to connection.execute(). some background I found
>> via googling here:
>>
>>
>> https://stackoverflow.com/questions/11384292/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation-in-aqua
>>
>> Assuming you are passing string SQL to SQLAlchemy's conn.execute(),
>> nothing special happens in SQLAlchemy and I would advise experimenting with
>> the pyodbc cursor directly in order to figure out why some parts of your
>> statements seem to be "ignored" - it's not clear what is telling you "the
>> last part of the SQL is never actually executed" since there's not really
>> any example or context provided here.
>>
>>
>>
>> On Tue, Apr 7, 2020, at 2:25 PM, Iván Gómez Villafañe wrote:
>>
>> I have my own tran() function, as the doc suggests:
>>
>> engine = sqlalchemy.create_engine(
>> 'mssql+pyodbc://@SHINKEN/supervielle?trusted_connection=yes&driver=ODBC+Driver+13+for+SQL+Server'
>> , fast_executemany = True)
>> def tran(sql,engine):
>> with engine.begin() as conn:
>> conn.execute(sql)
>>
>> I execute with it custom sql strings, such as one obtained from another
>> custom complex sql-string generation function which obtains a new table
>> with log + z-transform of columns that have many 0's, therefore there's a
>> lot of NULL values going around. In the sql, 0's get turned into null so
>> that I can log, and when I then set variables and new columns as log(),
>> each column returns the warning:
>>
>> Warning: Null value is eliminated by an aggregate or other SET operation.
>>
>> When running the function on 4 columns, everything is fine. When running
>> it on 20, tran() executes as if it were fine, *but the last part of the
>> sql is never actually executed* and I receive no warning/error
>> whatsoever on the Python console. Please note this DOES run on MS SQL SMS,
>> I tried pasting the sql and executing there, and I do receive all the
>> warnings, but it completes fine. It appears that when running with
>> sqlalchemy, after a certain amount of warnings the execution is dropped.
>>
>> Anyway, I solved the issue by setting the following at the start of the
>> sql:
>>
>> SET ANSI_WARNINGS OFF
>>
>> Nevertheless, I suspect there's something not quite working as it should.
>> Maybe I'm doing something wrong, or maybe sqlalchemy could be updated to
>> deal with this kind of scenario, or maybe there's a setting I should look
>> into in MS SQL 2017.
>>
>> Any insights? Thanks.
>>
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to sqlal...@googlegroups.com.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/c80049c9-698d-4976-849a-abc422d15bf0%40googlegroups.com
>>
>> <https://groups.google.com/d/msgid/sqlalchemy/c80049c9-698d-4976-849a-abc422d15bf0%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
>>
>>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/e3b64fd3-7855-4eb6-90f0-c58844a558a7%40googlegroups.com.
