topic Re: ODBC Read Failed Error in QlikView

ODBC Read Failed Error

Fri, 17 May 2013 11:01:26 GMT

Hi,

As I am getting a ODBC read failed error, as I could not find where I am wrong.

* Date1 is not as a table field

CurrentData:

Load

Date1,

Customer_id,

Customer_Sub,

Customer_Location,

"INVOICE_NUMBER",

"INVOICE_AMOUNT",

If(Today() - [DUEDATE] <= 30,Count(INVOICE_NUMBER)) as NbOverdueInv,

If(Today() - [DUEDATE] <= 30,Sum(INVOICE_AMOUNT)) as OverdueInvAMT,

Count(INVOICE_NUMBER) as NumInvoices,

Sum(INVOICE_AMOUNT) as TotInvoiceAmt;

SQL SELECT

GetDate() as Date1,

Customer_id,

Customer_Sub,

Customer_Location,

INVOICE_NUMBER,

INVOICE_AMOUNT,

DUEDATE

FROM Learn.dbo.FBHH Group by Customer_id,Customer_Sub, Customer_Location,"INVOICE_NUMBER","INVOICE_AMOUNT",DUEDATE;

STORE CurrentData into CurrentData.qvd(qvd);

Re: ODBC Read Failed Error

sushil353 — Fri, 17 May 2013 11:05:07 GMT

hi,

have u created any connection string to connect to the odbc database??

what is the exact error..?

Re: ODBC Read Failed Error

Fri, 17 May 2013 11:33:35 GMT

Hi Sushil Kumar,

Thanks for your reply, Yes, I am using connection string to ODBC CONNECT TO Ms_SQL;

Please find the attached error.bmp

Regards

Chriss

Re: ODBC Read Failed Error

Fri, 17 May 2013 12:36:43 GMT

Hi,

Is anybody can help me out from the error.

Regards

Chriss

Re: ODBC Read Failed Error

Gysbert_Wassenaar — Fri, 17 May 2013 16:48:27 GMT

SQL SELECT

GetDate() as Date1,

Customer_id,

Customer_Sub,

Customer_Location,

INVOICE_NUMBER,

INVOICE_AMOUNT,

DUEDATE

FROM Learn.dbo.FBHH Group by Customer_id,Customer_Sub, Customer_Location,"INVOICE_NUMBER","INVOICE_AMOUNT",DUEDATE;

You use a group by clause without using any aggregation functions like sum or count in the select part of the sql statement. I think the best solution is to rewrite the sql query to do all the calculations:

SQL SELECT

GetDate() as Date1,

Customer_id,

Customer_Sub,

Customer_Location,

INVOICE_NUMBER,

INVOICE_AMOUNT,

DUEDATE

CASE WHEN GETDATE() - [DUEDATE] <= 30 THEN Count(INVOICE_NUMBER) END as NbOverdueInv,

CASE WHEN GETDATE() - [DUEDATE] <= 30 THEN Sum(INVOICE_AMOUNT) END as OverdueInvAMT,

Count(INVOICE_NUMBER) as NumInvoices,

Sum(INVOICE_AMOUNT) as TotInvoiceAmt;

FROM Learn.dbo.FBHH Group by GETDATE(),Customer_id,Customer_Sub, Customer_Location,"INVOICE_NUMBER","INVOICE_AMOUNT",DUEDATE;

Re: ODBC Read Failed Error

Mon, 20 May 2013 05:57:21 GMT

Hi Gysbert,

Awesome, the script is working fine. can you tell me why this is not worked when I use if condition.

Regards

Chriss