ODBC --call failed.

[WSalaz01]

This error comes when try to run a query in Access. Does someone knows how to fix it?


ODBC --call failed.
[Oracle][ODBC][Ora]ORA-01013:user requested cancel of current operation (#1013)

Any help on this will be greatly appreaciated. Thanks a lot.

Wil

 

[namliam]

Your query is probably causing a time out....

Go into your sqlnet.ora file or change the timeout in the query, see if that helps...

 

[WSalaz01]

Yes. It did. It was set up at 60...changed to 0. But
I am having difficulties running this simple query that it runs all day and I have to kill it at the end of my date because it does not finish. Here is the statement that I would like some suggestions on how to improve it for faster results:
SELECT code, acct nbr, date, user, sum(amt)
FROM Tbl a
WHERE code IN
('AB',
'BC',
'CD',
'AA',
'BB',
'CC',
'ZZ'
)
AND date BETWEEN TO_DATE ('20070101', 'YYYYMMDD')
AND TO_DATE ('20070131', 'YYYYMMDD')
group by code,acc nbr,date,user

thank you...in advance for all your help..

Wilman

 

[doco]

I am having difficulties

What kind of difficulties? Are you getting errors? What kind?

BTW thanks for asking the question. I was just coming online to do the same as I was experiencing the exact (driving me nuts) problem Except I am querying an SQLServer db or linked tables from an SQL Server. What is the SQL equivalent to the sqlnet.ora file? Here is my query just in case its not a time out issue.

SELECT 
	dbo_neigh_control.neigh_name
      , dbo_parcel_base.district_number
      , dbo_parcel_base.property_class
      , dbo_parcel_base.parcel_id
      , dbo_land_detail.soil_id
      , dbo_parcel_base.legal_acreage
      , dbo_land_types.land_type_desc
      , dbo_land_methods.method_desc
      , dbo_land_detail.true_tax_value1
FROM (((dbo_land_detail 
      INNER JOIN dbo_parcel_base ON dbo_land_detail.lrsn = dbo_parcel_base.lrsn) 
      INNER JOIN dbo_land_types ON dbo_land_detail.land_type = dbo_land_types.land_type) 
      INNER JOIN dbo_neigh_control ON dbo_parcel_base.neighborhood = dbo_neigh_control.neighborhood) 
      INNER JOIN dbo_land_methods ON dbo_land_detail.lcm = dbo_land_methods.method_number
WHERE (((dbo_parcel_base.status)="A") 
      AND ((dbo_land_detail.status)="A") 
      AND (([neigh_name] Like "*" & [Forms]![FLandDetailSearch]![cboHood] & "*" 
		                 Or [Forms]![FLandDetailSearch]![cboHood] Is Null)=True) 

      AND (([method_desc] Like "*" & [Forms]![FLandDetailSearch]![cboMethod] & "*" 
		                  Or [Forms]![FLandDetailSearch]![cboMethod] Is Null)=True)
 
      AND (([property_class]=[Forms]![FLandDetailSearch]![cboPropClass] 
		          Or [Forms]![FLandDetailSearch]![cboPropClass] Is Null)=True) 

      AND ((dbo_parcel_base.district_number)=[Forms]![FLandDetailSearch]![cboArea] 
					     Or [Forms]![FLandDetailSearch]![cboArea] Is Null)=True) 

      AND (([soil_id] Like "*" & [Forms]![FLandDetailSearch]![cboSoilClass] & "*"         
		              Or [Forms]![FLandDetailSearch]![cboSoilClass] Is Null)=True) 

      AND (([land_type_desc] Like "*" & [Forms]![FLanddetailsearch]![cboLandType] & "*" 
		                     Or [Forms]![FLandDetailSearch]![cboLandType] Is Null)=True) 

      AND ((dbo_neigh_control.district_number)=[Forms]![FLandDetailSearch]![cboArea] 
		                               Or [Forms]![FLandDetailSearch]![cboArea] Is Null)=True));

I added whitespace to make it easier to read

 

[namliam]

HOw big is the table?
Is code indexed?

Maybe if you split the query? Create a table first for January then create the group by query??

 

[ByteMyzer]

In an aggregate query, it can help immensely if the filtering criteria on grouped fields is placed in a HAVING clause instead of a WHERE clause, thus:

SELECT code, acct nbr, date, user, sum(amt)
FROM Tbl a
group by code,acc nbr,date,user
HAVING code IN
('AB',
'BC',
'CD',
'AA',
'BB',
'CC',
'ZZ'
)
AND date BETWEEN TO_DATE ('20070101', 'YYYYMMDD')
AND TO_DATE ('20070131', 'YYYYMMDD')

See if this makes a positive difference.

 

[WSalaz01]

...thank you. I made the changes as you suggested. It is runing now..


Thank you very much for you prompt respond on this. wilman

 

[ByteMyzer]

You're welcome. Glad to help.

 

[namliam]

placed in a HAVING clause instead of a WHERE clause, thus:

Hmz, I didnt know that one.... Good to know

 

[ByteMyzer]

Yep, aggregate queries are interesting to deal with.

Here's another good example. Assume the following table structure:

tblTransaction
--------------
PartID
TransType
TransQty

PartID  TransType  TransQty
---------------------------
1111-2  I          12
2244-3  I          23
1111-2  I          17
2244-3  O          5
1111-2  O          11
2244-3  O          13

If I want to query how many total of Part ID 2244-3 were Transaction Type O, I would use:

SELECT PartID, Sum(TransQty) AS TotalQty
FROM tblTransaction
WHERE TransType = 'O'
GROUP BY PartID
HAVING PartID = '2244-3';

TransType is not a grouping field in the SELECT portion of the SQL statement, so the criteria goes into the WHERE clause. PartID, however, is a grouping field, so the criteria belongs in the HAVING clause.

 

[namliam]

That sound logical, allmost sounds like query 101...