OLEDB Read Failed Error

[Anonymous]

I have the following Script:

When i  load the above script i get an OLEDB Read Failed error message.  What is wrong in the Script?

[antoniotiman]

Hi Christopher,

[Script Number] field there is not in the Group By

Regards,

Antonio

[sasiparupudi1]

Try with a resident load than a preceeding load

Temp:

SQL Select Branch,

"Retail Pharmacy",

"Script Number"

From PharmaMarketAudit.dbo.tblDatawarehouseConsolodatedCleanStage;

Noconcatenate Load

Branch,

[Retail Pharmacy],

[Script Number]

Count(Distinct [Script Number]) as Rx

Resident Temp

Group By

Branch,

[Retail Pharmacy],

[Script Number];

Drop Table Temp;

[Anonymous]

Hi Christopher,

Do not use aggregation directly while loading the data. Probably it should be the reason for you to get the error.

First load the data and then use resident load while doing aggregation. Sasidhar has given the script in this thread. It should work for you to resolve the issue.

-GDK

[Anonymous]

Noted with thanks Antonio.  However the script takes forever to execute and i am having to force it to end

[Anonymous]

Thank you Sasidhar.  After loading the temp table, the script takes forever to execute the resident load and i am having to force it to quit without ending the script execution

[Anonymous]

Thanks Gowtham, please see my responses above

[Anonymous]

I did a distinct SQL SELECT in the temp table load and a straight count in the resident load to reduce the amount of memory use

[antoniotiman]

Christopher,

move Group by from LOAD to Select

This should be faster..

SQL Select

Branch,[Retail Pharmacy],[Script Number],

Count(Distinct [Script Number]) as Rx

Group By

Branch,

[Retail Pharmacy],

[Script Number]

from PharmaMarketAudit.dbo.tblDatawarehouseConsolodatedCleanStage ;

However, if You Group By [Script Number] then Count(Distinct [Script Number]) is always 1.

Perhaps remove DISTINCT from Count or remove [Script Number] from Select Fields List and from Group By.

[mayuresh_d]

as per me here group by is issue....

Please remove group by and load ..then take resident and then aggr value. else aggr in sql script don't agg in preceding load.