Re: Problems with SQL stmt in Hyperscript

In article <1993Aug2.150749.19901@porthos.cc.bellcore.com> feliciah@base.bellcore.com (F Hemingway) writes: >Hi all, > >I am using this SQL statement in Hyperscript: > > select milestone_cat, initiative_id, vsmapjct.projid, vsmapjct.majrelid, sum(plan_st_fin_diff), sum(act_st_fin_diff) , qtr, yr from vsmapjct,>vsmaipxr where vsmapjct.projid = vsmaipxr.projid and vsmapjct.majrelid = vsmaipxr.majrelid group by initia' >tive_id, vsmapjct.projid, vsmapjct.majrelid, milestone_cat,qtr, yr order by initiative_id , vsmapjct.projid, vsmapjct.majrelid, milestone_cat, qtr, >yr; > >The number of records retrieved from the database (using DATALINK) is 15, which is incorrect. The number of records retrieved from the database >directly using native isql is 40 which is correct. I check the number of rows from the database by sett' >ing num_rows = DB:DBGetVal() right after the DB:DBSEND and DB:DBEXEC() commands. > >Help, this was suppose to be cut into production last Friday. I can fax the entire script (6 pages) to whomever if it's needed. > >Thanks a bunch > > >Felicia Hemingway >(908) 699-8182 >feliciah@base.bellcore.com >BELLCORE Yes, I've had similar problems with group by statements. From the collective wisdom of the Net I learned that there is an undocumented (or maybe it is in a READ ME file somewhere) function called DBSetFlags() which you should call before sending your query. Apparently DataLink cannot handle group by clauses. DBSetFlags(1) will turn off DataLink's parsing and the query is sent to your server without being parsed by DataLink. The followng example should work: define query query = "select .... group by ..." call DB:DBSetFlags(1) call DB:ExecuteCommand(query) call DB:WingzOutput() I hope this helps! Brian O'Sullivan bosulliv@mitre.org MITRE Corp. Washington, DC NASA HQ site