ODBC Read Failed Error

[#1]

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);

[sushil353]

hi,

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

what is the exact error..?

[#3]

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

[#4]

Hi,

Is anybody can help me out from the error.

Regards

Chriss

[Gysbert_Wassenaar · Accepted solution]

  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;

[#6]

Hi Gysbert,

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

Regards

Chriss