The group by clause is not required, and may be giving the sytem indigestion. You are already restricting your total to the specific stock number.
"Alan Wolfe" <[EMAIL PROTECTED]> wrote: >Not sure if this sheds any more light on it either but we just got another one of >these errors for the following sql: > >sel sum(qtyremaining) from trandetail >where tranid in (sel tranid from tranmaster where districtno='73676' and >trantype='dw' and sget(reqno,2,1)='pr') >and stockno='6352-PP' group by stockno > >and the oterro log just returns a -1 for SQL_ERROR on a perfectly ok (as far as i can >tell) peice of sql, whats wierd is that only a couple minutes earlier this same sql >went through without error so i know it isnt the sql thats the problem: > >SQLFreeStmt ****************** ENTER 11/06/2003 13:23:13.828 > � � hStmt � � � � � � � HSTMT � � � (0x27F8260) 41910880 > � � fOption � � � � � � UWORD � � � (0x0) 0 >SQLExecDirect ****************** EXIT 11/06/2003 13:23:13.828 > � � hStmt � � � � � � � HSTMT � � � (0x297B7B4) 43497396 > � � szSqlStr � � � � � �UCHAR * � � "sel sum(qtyremaining) from trandetail �where > tranid in (sel tranid from tranmaster where districtno='73676' and trantype='dw' and > sget(reqno,2,1)='pr') �and stockno='6352-PP' group by stockno �" > � � cbSqlStr � � � � � �SDWORD � � �(0xC1) 193 > � � RETURN = (FFFFFFFF) -1 � � � � �SQL_ERROR > >SQLError ****************** ENTER 11/06/2003 13:23:13.828 > � � hEnv � � � � � � � �HENV � � � �(0x0) 0 > � � hDbc � � � � � � � �HDBC � � � �(0x0) 0 > � � hStmt � � � � � � � HSTMT � � � (0x297B7B4) 43497396 > � � szSqlState � � � � �UCHAR * � � "@o" > � � pfNativeError � � � SDWORD * � �(0x3A0063) 3801187 � � 0x1EFF630 > � � szErrorMsg � � � � �UCHAR * � � "OUTPUT PARAM" > � � cbErrorMsgMax � � � SWORD � � � (0x200) 512 > � � pcbErrorMsg � � � � SWORD * � � (0x3CF8) 15608 � � 0x1EFF63C >SQLError ****************** EXIT 11/06/2003 13:23:13.828 > � � hEnv � � � � � � � �HENV � � � �(0x0) 0 > � � hDbc � � � � � � � �HDBC � � � �(0x0) 0 > � � hStmt � � � � � � � HSTMT � � � (0x297B7B4) 43497396 > � � szSqlState � � � � �UCHAR * � � "00000" > � � pfNativeError � � � SDWORD * � �(0x0) 0 � � 0x1EFF630 > � � szErrorMsg � � � � �UCHAR * � � "" > � � cbErrorMsgMax � � � SWORD � � � (0x200) 512 > � � pcbErrorMsg � � � � SWORD * � � (0x0) 0 � � 0x1EFF63C > � � RETURN = (64) 100 � � � � �SQL_NO_DATA_FOUND > >SQLError ****************** ENTER 11/06/2003 13:23:13.828 > � � hEnv � � � � � � � �HENV � � � �(0x0) 0 > � � hDbc � � � � � � � �HDBC � � � �(0x0) 0 > � � hStmt � � � � � � � HSTMT � � � (0x297B7B4) 43497396 > � � szSqlState � � � � �UCHAR * � � "" > � � pfNativeError � � � SDWORD * � �(0x43014320) 1124156192 � � 0x1EFEF00 > � � szErrorMsg � � � � �UCHAR * � � "OUTPUT PARAM" > � � cbErrorMsgMax � � � SWORD � � � (0x200) 512 > � � pcbErrorMsg � � � � SWORD * � � (0xFFFFEE88) -4472 � � 0x1EFEF04 >SQLError ****************** EXIT 11/06/2003 13:23:13.828 > � � hEnv � � � � � � � �HENV � � � �(0x0) 0 > � � hDbc � � � � � � � �HDBC � � � �(0x0) 0 > � � hStmt � � � � � � � HSTMT � � � (0x297B7B4) 43497396 > � � szSqlState � � � � �UCHAR * � � "00000" > � � pfNativeError � � � SDWORD * � �(0x0) 0 � � 0x1EFEF00 > � � szErrorMsg � � � � �UCHAR * � � "" > � � cbErrorMsgMax � � � SWORD � � � (0x200) 512 > � � pcbErrorMsg � � � � SWORD * � � (0x0) 0 � � 0x1EFEF04 > � � RETURN = (64) 100 � � � � �SQL_NO_DATA_FOUND > �----- Original Message ----- > �From: Alan Wolfe > �To: RBASE-L Mailing List > �Sent: Thursday, November 06, 2003 12:37 PM > �Subject: [RBASE-L] - Re: strange errors > > > �Hi Buddy, > > �In our case, this error pops up at random positions in sql that works most of the > time. > > �It would be like it does the same select 1000 times and out of those, 1 of the > times it fails like this. > > �When theres a witango error we have it email us what you'd normaly see at the > witango error screen so we are able to see the sql that caused the error. �When we > execute this sql at the R> prompt or through oterro it works fine, and it works the > other 999 times so it's pretty strange. > � �----- Original Message ----- > � �From: Walker, Buddy > � �To: RBASE-L Mailing List > � �Sent: Thursday, November 06, 2003 12:28 PM > � �Subject: [RBASE-L] - Re: strange errors > > > � �Alan > � � �That (This type of data source is not nupported) has happened to me. What I've > done was to set a direct DBMS or Search Action and didn't select an ODBC database. > In another words the data source was blank > > � �Buddy > > > � �-----Original Message----- > � �From: Alan Wolfe [mailto:[EMAIL PROTECTED] > � �Sent: Thursday, November 06, 2003 2:16 PM > � �To: RBASE-L Mailing List > � �Subject: [RBASE-L] - Re: strange errors > > > � �im not sure if this helps but another part of this puzzle is that in the > witango.log it says this when those errors are hit: > > � �06/11/2003 10:15:38 156.3.113.43 [EMAIL PROTECTED] 3392 4 247312 �[Error] -101 > General error during data source operation. > � �06/11/2003 10:15:41 156.3.113.43 [EMAIL PROTECTED] 3932 1 256 �[Error] -109 This > type of data source is not supported by the server license. > � �06/11/2003 10:15:41 156.3.113.43 [EMAIL PROTECTED] 3932 1 257 �[Error] -4 Unable > to connect to the specified data source. Verify that data source is properly > configured and that database server is online. > > � �I attached the errors from the log in case that sheds any more light on things. > � � �----- Original Message ----- > � � �From: Alan Wolfe > � � �To: RBASE-L Mailing List > � � �Sent: Thursday, November 06, 2003 11:05 AM > � � �Subject: [RBASE-L] - strange errors > > > � � �hey everyone, > > � � �About a year and a half ago or so, when we were using r:tango 2000 on NT 4, we > started getting a couple wierd errors from oterro saying "general error during data > source operation". > > � � �This is normaly pretty normal because usualy you have another error before that > like it will say syntax error and then "general error...", but for these errors its > 2 "general error..." messages with no further explanation. > > � � �Er...here i have an example of what i mean. �Normaly an error is like this, > where it has an error message, then the general error message: > > � � > �------------------------------------------------------------------------------------------ > � � �Error > � � �An error occurred while processing your request: > > � � �APPFILE Path:/pvglobal/ > � � �APPFILE Name:ppo.taf > � � � Position: update_tranmaster_befo_acct_scr > � � �Class: DBMS > � � �Main Error Number: 122 > � � �Secondary Error Number: 0 > � � �Main Error Message: [RBTI][Oterro ODBC Driver][PVGLOBAL]-ERROR- Column needdate > must be a valid DATE. ( 122) > � � �Secondary Error Message: 22005 > > � � �APPFILE Path:/pvglobal/ > � � �APPFILE Name:ppo.taf > � � � Position: update_tranmaster_befo_acct_scr > � � �Class: Internal > � � �Main Error Number: -101 > � � �Secondary Error Number: 0 > � � �Main Error Message: General error during data source operation. > � � �Secondary Error Message: > > � � �APPFILE Path:/pvglobal/ > � � �APPFILE Name:ppo.taf > � � � Position: update_tranmaster_befo_acct_scr > � � �Class: Internal > � � �Main Error Number: -101 > � � �Secondary Error Number: 0 > � � �Main Error Message: General error during data source operation. > � � �Secondary Error Message: > � � > �------------------------------------------------------------------------------------------ > > � � �But these wierd errors we have been getting give no detail as to what the error > is: > > � � > �------------------------------------------------------------------------------------------- > > � � �Error > � � �An error occurred while processing your request: > > � � �APPFILE Path:/ > � � �APPFILE Name:utils.tcf > � � � Position: sel_transfers > � � �Class: Internal > � � �Main Error Number: -101 > � � �Secondary Error Number: 0 > � � �Main Error Message: General error during data source operation. > � � �Secondary Error Message: > > > � � �APPFILE Path:/ > � � �APPFILE Name:utils.tcf > � � � Position: sel_transfers > � � �Class: Internal > � � �Main Error Number: -101 > � � �Secondary Error Number: 0 > � � �Main Error Message: General error during data source operation. > � � �Secondary Error Message: > > � � > �------------------------------------------------------------------------------------------- > > � � �the wierd thing about these errors too is that it doesnt seem to matter what > the sql is when they error, it just happens randomly. > > � � �It works 99.999% of the time, which is really good except that small percent is > starting to grow large enough to become a problem. > > � � �some example sql's its failed on are: > > � � �-------------------------------------------------------------------------------- > � � �sel sum(acctidamount) from tranacctid where acctid=87594 and transtate in > ('A','B') and tranid not in (sel tranid from tranmaster where districtno='64436' and > trantype='cty') > > � � �sel sum(acctidamount) from tranacctid where TranState='A' and acctid=113631 and > tranid not in (sel tranid from tranmaster where trantype='CTY' and > districtno='64436') > > � � �sel count(distinct tranid) from tranactions where (tranaction is null or > tranaction='purchasing') and tranid in (sel tranid from tranmaster where > districtno='64436') and userno=986 > � � �-------------------------------------------------------------------------------- > > � � �but like i said, it works almost all the time, and when it fails, if we take > the sql it failed on and paste it in at the R> prompt or something, it works fine, > so i think it might be a configuration issue. > > � � �we were hoping r:tango 5 would clear this up, we are on windows 2003 server, > and on new hardware but still have this same old problem. > > � � �a point of interest might be that we recently moved some common functionality > (to get the balance of an account) into a tcf and since then, it has caused this > error to happen more often in new places. > > � � �Anyone know what the problem might be or have any ideas? > -- Albert Berry Full Time Consultant to PSD Solutions 350 West Hubbard, Suite 210 Chicago, IL 60610 312-828-9253 Ext. 32 __________________________________________________________________ McAfee VirusScan Online from the Netscape Network. Comprehensive protection for your entire computer. Get your free trial today! http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397 Get AOL Instant Messenger 5.1 free of charge. Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promo=380455
