Youcanalso usetheLENGTH,USER,CURRENT,andTODAYfunctions anywhereinan SQLexpressionthatyouwouldusea constant.Inaddition, youcanincludetheDBSERVERNAMEfunction inaSELECTstatementto displaythenameofthedatabaseserverwherethecurrentdatabaseresides.
Youcanusethesefunctionstoselectan expressionthatconsistsentirelyof constantvaluesoran expressionthatincludescolumndata.Inthefirst instance,theresult isthesamefor allrowsofoutput.
Inaddition,youcanusetheHEXfunctiontoreturnthehexadecimal encoding ofanexpression,theROUNDfunctiontoreturn theroundedvalue ofan expression,andtheTRUNCfunctiontoreturn thetruncatedvalue ofan expression.Formore informationontheprecedingfunctions,seethe IBM Informix:GuidetoSQLSyntax.
Usingthe LENGTHFunction
InFigure4-77,theLENGTHfunctioncalculatesthenumberof bytesinthe combinedfname andlnamecolumns foreachrowwherethelengthof companyisgreaterthan15.
sname (expression)
West Virginia West Virginia --- Arizona Arizona ---
Figure4-76.QueryResult
SELECT customer_num,
LENGTH (fname) + LENGTH (lname) namelength FROM customer
WHERE LENGTH (company) > 15
AlthoughtheLENGTHfunctionmight notbeusefulwhenyouworkwith DB–Access,it canbe importanttodeterminethestringlength forprograms andreports.TheLENGTHfunction returnstheclippedlengthofa
CHARACTERorVARCHARstringandthefullnumberofbytesina TEXTor BYTEstring.
Usingthe USERFunction
UsetheUSERfunction whenyouwanttodefinea restrictedviewof atable thatcontainsonlyrowsthatincludeyour userid.For informationabouthow tocreateviews,seetheIBM Informix:DatabaseDesign andImplementationGuide andtheGRANTand CREATEVIEWstatementsintheIBM Informix:Guideto SQLSyntax.
Figure4-79 returnstheusername(loginaccountname)oftheuserwho executesthequery.Itisrepeatedonceforeachrow inthetable.
Iftheuser nameof thecurrentuser isrichc,Figure4-79retrievesonlythose rowsinthecust_callstable where user_id =richc,asFigure4-80 shows. customer_num namelength 101 11 105 13 107 11 112 14 115 11 118 10 119 10 120 10 122 12 124 11 125 10 126 12 127 10 128 11
Figure4-78.QueryResult
SELECT * FROM cust_calls WHERE user_id = USER
Usingthe TODAYFunction
TheTODAYfunctionreturnsthecurrentsystem date.IfFigure4-81isissued whenthecurrentsystem dateisJuly10,1998,itreturnsthisonerow.
Usingthe DBSERVERNAMEandSITENAME Functions
Youcaninclude thefunction DBSERVERNAME(oritssynonym,
SITENAME)ina SELECTstatementtofindthenameofthedatabaseserver. YoucanquerytheDBSERVERNAMEforanytablethathasrows, including systemcatalogtables.
customer_num 110
call_dtime 1998-07-07 10:24 user_id richc
call_code L
call_descr Orderplacedone month ago(6/7) not received. res_dtime 1998-07-07 10:30
res_descr Checked withshipping (EdSmith). Ordersent yesterday-we werewaiting for goods fromANZ. Nexttime will callwith delayif necessary customer_num 119 call_dtime 1998-07-01 15:00 user_id richc call_code B
call_descr Billdoes notreflect creditfrom previous order res_dtime 1998-07-02 08:21
res_descr Spokewith JaneAkant inFinance. Shefound the errorand is sending new billto customer
Figure4-80.QueryResult
SELECT * FROM orders WHERE order_date = TODAY
Figure4-81.Query
order_num 1018 order_date 07/10/1998 customer_num 121
ship_instruct SW corner of Biltmore Mall backlog n po_num S22942 ship_date 07/13/1998 ship_weight 70.50 ship_charge $20.00 paid_date 08/06/1998
InFigure4-83,youassignthelabelservertotheDBSERVERNAMEexpression andalsoselectthetabidcolumnfromthesystablessystem catalogtable.This tabledescribesdatabasetables, andtabidisthetableidentifier.
TheWHEREclauserestricts thenumbersofrowsdisplayed.Otherwise,the databaseservernamewould bedisplayedonceforeachrowofthesystables table.
Usingthe HEXFunction
InFigure4-85,theHEXfunction returnsthehexadecimal formatof two columnsinthecustomertable,asFigure4-86 shows.
SELECT DBSERVERNAME server, tabid FROM systables WHERE tabid <= 4 Figure4-83.Query server tabid montague 1 montague 2 montague 3 montague 4
Figure4-84.QueryResult
SELECT HEX (customer_num) hexnum, HEX (zipcode) hexzip FROM customer Figure4-85.Query hexnum hexzip 0x00000065 0x00016F86 0x00000066 0x00016FA5 0x00000067 0x0001705F 0x00000068 0x00016F4A 0x00000069 0x00016F46 0x0000006A 0x00016F6F . . .
Usingthe DBINFOFunction
YoucanusetheDBINFOfunction inaSELECTstatementtofindanyofthe followinginformation:
v Thenameof adbspacecorrespondingtoa tablespacenumberorexpression
v Thelastserialvalueinsertedinatable
v Thenumberofrowsprocessedbyselects,inserts,deletes,updates,and executeroutinestatements
v ThesessionIDofthecurrentsession
v Thenameof thehostcomputeronwhichthedatabaseserverruns
v Theexactversionofthedatabaseservertowhichaclientapplication is connected
YoucanusetheDBINFOfunction anywherewithin SQLstatementsand withinSPLroutines.
Figure4-87 showshowyoumightusetheDBINFOfunctiontofindout the nameofthehostcomputeronwhichthedatabaseserverruns.
WithouttheFIRST 1 clauseto restrictthevaluesinthe tabid,thehost name ofthecomputeronwhichthedatabaseserverrunswouldbe repeatedfor eachrowofthesystablestable.Figure4-89showshowyoumightusethe DBINFOfunctiontofind outthecompleteversionnumberand thetype of thecurrentdatabase server.
FormoreinformationabouthowtousetheDBINFOfunction tofind
informationaboutyour currentdatabaseserver,databasesession,ordatabase, seetheIBM Informix:GuidetoSQLSyntax.
SELECT FIRST 1 DBINFO(’dbhostname’) FROM systables
Figure4-87.Query
(constant)
lyceum
Figure4-88.QueryResult
SELECT FIRST 1 DBINFO(’version’,’full’) FROM systables
Usingthe DECODEFunction
YoucanusetheDECODEfunctiontoconvertan expressionofonevalue to anothervalue.TheDECODEfunction hasthefollowingform:
DECODE(test, a, a_value, b, b_value, ..., n, n_value, exp_m )
DECODEreturnsa_valuewhenaequalstest,andreturnsb_valuewhenb equalstest,and,ingeneral,returnsn_value whennequalstest.
Ifseveralexpressionsmatchtest,DECODEreturnsn_valueforthefirst
expressionfound.Ifnoexpressionmatches test,DECODE returnsexp_m;ifno expressionmatches testand thereisnoexp_m,DECODE returnsNULL.
TheDECODEfunction doesnotsupportargumentsoftype TEXTorBYTE.
Supposeanemployeetableexiststhatincludesemp_idandevaluation columns.Supposealso thatexecutionof Figure4-90 ontheemployeetable returnstherowsthatFigure4-91 shows.
Insomecases,youmight wantto converta setofvalues.Forexample, supposeyouwanttoconvertthedescriptive valuesoftheevaluationcolumn inthepreceding exampletocorrespondingnumericvalues.Figure4-92 shows howyoumight usetheDECODE functiontoconvertvaluesfromthe
evaluationcolumntonumericvaluesforeachrowintheemployeetable.
SELECT emp_id, evaluation FROM employee
Figure4-90.Query emp_id evaluation 012233 great 012344 poor 012677 NULL 012288 good 012555 very good
Figure4-91.QueryResult
SELECT emp_id, DECODE(evaluation, "poor", 0, "fair", 25, "good", 50, "very good", 75, "great", 100, -1) AS evaluation
FROM employee
Youcanspecifyanydatatypefortheargumentsof theDECODE function providedthattheargumentsmeet thefollowingrequirements:
v Theargumentstest,a,b,...,nallhavethesamedatatype orevaluatetoa commoncompatibledatatype.
v Theargumentsa_value,b_value,...,n_value allhavethesame datatypeor evaluatetoacommoncompatibledatatype.
Usingthe NVLFunction(IDS)
YoucanusetheNVLfunctiontoconvertanexpressionthatevaluatesto NULLtoa valuethatyouspecify.TheNVLfunction acceptstwoarguments: thefirst argumenttakesthenameoftheexpressiontobe evaluated;the secondargumentspecifies thevalue thatthefunctionreturnswhenthefirst argumentevaluatestoNULL. Ifthefirst argumentdoesnotevaluateto NULL,thefunction returnsthevalue ofthefirstargument.Supposeastudent tableexiststhatincludesname andaddresscolumns. Supposealsothat executionof Figure4-94 onthestudenttablereturnstherowsthatFigure4-95 shows.
Figure4-96 includestheNVLfunction, whichreturnsa newvalueforeach rowinthetablewheretheaddresscolumncontains aNULLvalue.
emp_id evaluation 012233 100 012344 0 012677 -1 012288 50 012555 75 . . .
Figure4-93.QueryResult
SELECT name, address FROM student
Figure4-94.Query
name address
John Smith 333 Vista Drive Lauren Collier 1129 Greenridge Street Fred Frith NULL
Susan Jordan NULL
Youcanspecifyanydatatypefortheargumentsof theNVL function
providedthatthetwoargumentsevaluatetoacommoncompatibledatatype.
Ifbothargumentsof theNVL functionevaluatetoNULL,thefunctionreturns NULL.