Helen Borrie wrote:

>
>
> Some clients built with older versions of IBO might process this
> statement without exceptions, even though it isn't valid SQL. I dare
> say the same is true for older versions of FIBPlus, which is the
> interface layer beneath IBExpert. The whole purpose of nudging
> Firebird into tighter compliance and more correct logic is to *avoid*
> these blunders.
>
> Indeed it might well be a genuine singleton error, caused by IBO
> actually finding a different output set from your subqueries than the
> one returned by IBExpert. One of the major sources of blunders in SQL
> is people assuming that, if they don't get exceptions, their results
> must be right. If you get an exception when you pass a statement
> through IBO's interface, you can pretty much count on its being an
> exception!
>
> However ---!! do check that you have the correct firebird.msg file
> being accessed by (client? server?) as you should be seeing a
> different "first" exception, most likely the "ambiguous statement" one
> (isc_code 335544708) or possibly the "context is already in use one"
> (isc_code 335544425) for the logic error I annotated below.
>
> You *must* study the compatibility section of the Firebird 2 release
> notes to see why statements like this are no longer tolerated. IBO 4.8
> does a lot of stuff underneath to ensure that qualifier associations
> are right for Firebird 2. You need to be most thorough about using
> proper qualification from now on. Firebird users have had 4 years to
> fix up badly qualified statements....so it's not like it fell out of
> the sky! Firebird 2.1 will give you a new config setting to allow you
> to revert to "bad old ways" if you must, but I strongly recommend
> against touching it if you're going to develop your software in
> parallel with IBO support for the proper rules. Besides, it's a
> temporary config setting that will be removed after a period of grace.
>
> select
> cf.fileid,
> cf.refid,
> (select r.fullname from register r
> where r.regid = cf.refid) as Client,
> cf.filerefnum,
> (select ft.file_typedesc from file_types ft
> where ft.file_typeid = cf.filetypeid) as FileType,
> cf.subject,
> cf.casenum,
> cf.bu_by,
> cf.next_actiondate,
> (select txr.txreasondesc from tx_reasons txr
> where txr.txreasonid = cf.next_actiontype) as ActionType,
> /* the original form of the following might have been the error
> that caused the singleton exception: it needs a separate cursor */
> (select r2.fullname from register r2
> where r2.regid = cf.next_actionby) as Action_By,
> 'Case Files' AS Src
> from casefiles cf
> where f_weekofyear(cf.next_actiondate) = f_weekofyear(cf.current_date)
> and cf.is_active=1
>
> <http://groups.yahoo.com/group/IBObjects;_ylc=X3oDMTJjaHB2ZjBnBF9TAzk3MzU5NzE0BGdycElkAzQwMjkzMARncnBzcElkAzE3MDUwMDcxODMEc2VjA2Z0cgRzbGsDaHBmBHN0aW1lAzEyMDczMTA2NjI->
> <http://docs.yahoo.com/info/terms/>
> <mailto:IBObjects-unsubscribe@yahoogroups.com?subject=>
> grp-vitnav a{ padding: 0 1px; } #ygrp-actbar{ clear: both; margin:
> 25px 0; white-space:nowrap; color: #666; text-align: right; }
> #ygrp-actbar .left{ float: left; white-space:nowrap; }
> .bld{font-weight:bold;} #ygrp-grft{ font-family: Verdana; font-size:
> 77%; padding: 15px 0; } #ygrp-ft{ font-family: verdana; font-size:
> 77%; border-top: 1px solid #666; padding: 5px 0; } #ygrp-mlmsg #logo{
> padding-bottom: 10px; } #ygrp-reco { margin-bottom: 20px; padding:
> 0px; } #ygrp-reco #reco-head { font-weight: bold; l #vithd{ font-size:
> 77%; font-family: Verdana; font-weight: bold; color: #333;
> text-transform: uppercase; } #ygrp-vital ul{ padding: 0; margin: 2px
> 0; } #ygrp-vital ul li{ list-style-type: none; clear: both; border:
> 1px solid #e0ecee; } #ygrp-vital ul li .ct{ font-weight: bold; color:
> #ff7900; float: right; width: 2em; text-align:right; padding-right:
> .5em; } #ygrp-vital ul li .cat{ font-weight: bold; } #ygrp-vital a{
> text-decoration: none; } #ygrp-vital a:hover{ text-decoration:
> underline; } #ygrp-sponsor #hd{ color: #999; font-size: 77%; }
> #ygrp-sponsor #ov{ padding: 6px 13px; background-color: #e0ecee;
> margin-bottom: 20px; } #ygrp-sponsor #ov ul{ padding: 0 0 0 8px;
> margin: 0; } #ygrp-sponsor #ov n-bottom: 20px; padding: 0 8px; }
> #ygrp-sponsor .ad{ padding: 8px 0; } #ygrp-sponsor .ad #hd1{
> font-family: Arial; font-weight: bold; color: #628c2a; font-size:
> 100%; line-height: 122%; } #ygrp-sponsor .ad a{ text-decoration: none;
> } #ygrp-sponsor .ad a:hover{ text-decoration: underline; }
> #ygrp-sponsor .ad p{ margin: 0; } o{font-size: 0; } .MsoNormal{
> margin: 0 0 0 0; } #ygrp-text tt{ font-size: 120%; }
> blockquote{margin: 0 0 0 4px;} .replbq{margin:4} -->

I forgot all about the "fully-qualified" fields. After fully-qualifying
all the fields, the query now works like a charm. Gracias.

Jamez