topic Re: SQL error in QlikView

SQL error

danielnevitt — Thu, 13 Nov 2014 14:35:38 GMT

Hi,

I have the below SQL and I am getting a Script Error stating that the ODBC read failed.

Is there something obvious I am missing?

Any help will be much appreciated.

Regards,

Daniel

//-- _p_office - office

//-- _p_desk - desk

//-- _p_lob - lob

//-- _p_portfolio - portfolio

//-- _p_book - book

//-- _p_strategy - strategy

//-- _p_date_type - date type --> values: Trade Dt, Create Dt

//-- _p_date1 - date From 'yyyy/mm/dd'

//-- _p_date2 - date To 'yyyy/mm/dd'

//-- _p_internal - internal or external --> values: 'External','Internal' or '%' for all

//-- _p_trade_type - trade type --> values belo

//-- _p_compute_dt - computation date for Exposure and PricePL

//-- _p_compute_type - computation mode --> values 'Trade', 'Exposure','PricePL'

//-- _p_trade_num - for single trade selection, 0 for all trades

ODBC CONNECT32 TO [IQ DEV];
//-------- Start Multiple Select Statements ------
select *
from
tempest_tier1.p_edw_trade_pos('Non-US','LPG','%','%','%','%','Trade Dt','2014/01/01',today(),'%','%',today(),'Trade',0) pos
inner join tempest_tier1.v_dw_dim_strategy_un str on (str.strategy_num=pos.strategy_num)
//-------- End Multiple Select Statements ------

Re: SQL error

Thu, 13 Nov 2014 14:43:19 GMT

Hi,

odbc connect32 to then next you need to provide the username and passwords.

and you need to check whether the database is 32 bit or 64 bit

Re: SQL error

agomes1971 — Thu, 13 Nov 2014 14:48:20 GMT

Hi,

I think you're working on a mix mode environment. What is your OS (32 or 64 bit)?

And your oledb or odbc drivers?

Make this analysis...

Regards

André Gomes

Re: Re: SQL error

danielnevitt — Thu, 13 Nov 2014 15:12:38 GMT

Thanks for the replies.

The below works fine, however when I try to add the inner join, I get the error message.

select * from tempest_tier1.p_edw_trade_pos('Non-US','LPG','%','%','%','%','Trade Dt','2000/01/01','2050/01/01','%','%',today(),'Exposure',0)

Regards,

Daniel

Re: SQL error

agomes1971 — Thu, 13 Nov 2014 15:26:54 GMT

Hi,

does this sql statement with the join works on sql?

Regards

André Gomes

Re: SQL error

danielnevitt — Thu, 13 Nov 2014 15:28:57 GMT

Hi Andre

Yes the statement works with the join on sql.

Regards,

Daniel

Re: SQL error

marcus_malinow — Thu, 13 Nov 2014 15:35:55 GMT

Hi Daniel,

would I be right that p_edw_trade_pos is a stored procedure? If so, it's not possible to inner join to a stored procedure result set directly in SQL. I'd suggest splitting it out into two queries, one for the stored proc, and one for the table, and do the inner join in QlikView instead.

Marcus

Re: SQL error

danielnevitt — Thu, 13 Nov 2014 15:48:03 GMT

Hi Marcus,

The p_edw_trade_pos is a stored procedure. Are you able to send an example of how I should split into two queries?

Thanks,

Daniel

Re: SQL error

marcus_malinow — Thu, 13 Nov 2014 15:50:42 GMT

Try something like this:

ResultSet:

LOAD *;

SQL SELECT * FROM [your first stored procedure call];

INNER JOIN (ResultSet)

LOAD *;

SQL SELECT * FROM [your second stored procedure call];