Youare notlimited toselectingcolumnsbyname.Youcanlistan expressionin theProjection clauseofaSELECTstatementtoperformcomputationson columndataandtodisplayinformationderivedfromthecontentsofoneor morecolumns.
Anexpressionconsistsof acolumnname,a constant,a quotedstring,a keyword,oranycombinationofthese itemsconnected byoperators.Itcan alsoincludehostvariables(programdata)whentheSELECTstatementis embeddedina program.
ArithmeticExpressions
Anarithmeticexpressioncontainsat leastoneofthearithmeticoperatorslisted in2-41thefollowingtableandproducesa number.
Operator Operation
+ addition
- subtraction
* multiplication
/ division
YoucannotuseTEXTorBYTEcolumns inarithmeticexpressions.
WithDynamicServer,youcannotspecifyBLOBorCLOBinarithmetic expressions.
Arithmeticoperations enableyoutoseetheresultsof proposedcomputations withoutactuallyalteringthedatainthedatabase.Youcanaddan INTO description unit_price football $960.00 volleyball $840.00 baseball gloves $800.00 18-spd, assmbld $685.90 irons/wedge $670.00 basketball $600.00 12-spd, assmbld $549.00 10-spd, assmbld $499.99 football $480.00 bicycle brakes $480.00
TEMPclausetosavethealtereddatainatemporarytable forfurther reference,computations,orimpromptu reports.Figure2-82 calculatesa 7 percentsalestaxontheunit_pricecolumnwhentheunit_priceis$400or more(butdoesnotupdateitinthedatabase).
Theresultappearsin theexpressioncolumn,asFigure2-83shows.
Figure2-84 calculatesasurchargeof $6.50onorders whenthequantity orderedisless than5.
Theresultappearsin theexpressioncolumn,asFigure2-85shows. SELECT stock_num, description, unit_price, unit_price * 1.07
FROM stock
WHERE unit_price >= 400
Figure2-82.Query
stock_num description unit_price (expression)
1 baseball gloves $800.00 $856.00 1 baseball gloves $450.00 $481.50 4 football $960.00 $1027.20 4 football $480.00 $513.60 7 basketball $600.00 $642.00 8 volleyball $840.00 $898.80 102 bicycle brakes $480.00 $513.60 111 10-spd, assmbld $499.99 $534.99 112 12-spd, assmbld $549.00 $587.43 113 18-spd, assmbld $685.90 $733.91 203 irons/wedge $670.00 $716.90
Figure2-83.QueryResult
SELECT item_num, order_num, quantity, total_price, total_price + 6.50 FROM items
WHERE quantity < 5
Figure2-86 calculatesanddisplays intheexpression columntheinterval betweenwhen thecustomer callwas received(call_dtime)andwhenthecall wasresolved(res_dtime),indays,hours,and minutes.
UsingDisplayLabels: Youcanassignadisplaylabeltoa computedor deriveddatacolumntoreplace thedefaultcolumnheaderexpression.In Figure2-82,Figure2-84,andFigure2-88,thederiveddataappears inthe item_num order_num quantity total_price (expression)
1 1001 1 $250.00 $256.50 1 1002 1 $960.00 $966.50 2 1002 1 $240.00 $246.50 1 1003 1 $20.00 $26.50 2 1003 1 $840.00 $846.50 1 1004 1 $250.00 $256.50 2 1004 1 $126.00 $132.50 3 1004 1 $240.00 $246.50 4 1004 1 $800.00 $806.50 . . . 1 1023 2 $40.00 $46.50 2 1023 2 $116.00 $122.50 3 1023 1 $80.00 $86.50 4 1023 1 $228.00 $234.50 5 1023 1 $170.00 $176.50 6 1023 1 $190.00 $196.50
Figure2-85.QueryResult
SELECT customer_num, call_code, call_dtime, res_dtime - call_dtime
FROM cust_calls ORDER BY customer_num
Figure2-86.Query
customer_num call_code call_dtime (expression)
106 D 1998-06-12 08:20 0 00:05 110 L 1998-07-07 10:24 0 00:06 116 I 1997-11-28 13:34 0 03:13 116 I 1997-12-21 11:24 5 20:55 119 B 1998-07-01 15:00 0 17:21 121 O 1998-07-10 14:05 0 00:01 127 I 1998-07-31 14:30
expressioncolumn.Figure2-88 alsopresentsderived values,butthecolumn thatdisplaysthederivedvalueshasthedescriptive headertaxed.
Figure2-89 showsthatthelabeltaxedisassignedtotheexpressioninthe projectionlist thatdisplays theresultsof theoperationunit_price * 1.07.
InFigure2-90,thelabelsurchargeisdefinedforthecolumnthatdisplays the resultsof the operationtotal_price + 6.50.
Thesurchargecolumnislabeledintheoutput,asFigure2-91shows. SELECT stock_num, description, unit_price,
unit_price * 1.07 taxed FROM stock
WHERE unit_price >= 400
Figure2-88.Query
stock_num description unit_price taxed
1 baseball gloves $800.00 $856.00 1 baseball gloves $450.00 $481.50 4 football $960.00 $1027.20 4 football $480.00 $513.60 7 basketball $600.00 $642.00 8 volleyball $840.00 $898.80 102 bicycle brakes $480.00 $513.60 111 10-spd, assmbld $499.99 $534.99 112 12-spd, assmbld $549.00 $587.43 113 18-spd, assmbld $685.90 $733.91 203 irons/wedge $670.00 $716.90
Figure2-89.QueryResult
SELECT item_num, order_num, quantity,
total_price, total_price + 6.50 surcharge FROM items
WHERE quantity < 5
Figure2-92 assignsthelabelspantothecolumnthatdisplaystheresultsof subtractingtheDATETIMEcolumncall_dtime fromtheDATETIMEcolumn res_dtime.
Thespancolumnislabeledintheoutput,asFigure2-93shows.
CASEExpressions
ACASE expressionisaconditionalexpression, whichissimilartotheconcept oftheCASEstatementin programminglanguages.YoucanuseaCASE expressionwhenyouwanttochangethewaydataisrepresented.TheCASE item_num order_num quantity total_price surcharge
1 1001 1 $250.00 $256.50 1 1002 1 $960.00 $966.50 2 1002 1 $240.00 $246.50 1 1003 1 $20.00 $26.50 2 1003 1 $840.00 $846.50 . . . 1 1023 2 $40.00 $46.50 2 1023 2 $116.00 $122.50 3 1023 1 $80.00 $86.50 4 1023 1 $228.00 $234.50 5 1023 1 $170.00 $176.50 6 1023 1 $190.00 $196.50
Figure2-91.QueryResult
SELECT customer_num, call_code, call_dtime, res_dtime - call_dtime span
FROM cust_calls ORDER BY customer_num
Figure2-92.Query
customer_num call_code call_dtime span
106 D 1998-06-12 08:20 0 00:05 110 L 1998-07-07 10:24 0 00:06 116 I 1997-11-28 13:34 0 03:13 116 I 1997-12-21 11:24 5 20:55 119 B 1998-07-01 15:00 0 17:21 121 O 1998-07-10 14:05 0 00:01 127 I 1998-07-31 14:30
expressionallowsa statementtoreturnoneofseveralpossibleresults, dependingonwhichofseveralconditiontestsevaluatestoTRUE.
TEXTorBYTE valuesare notallowed inaCASEexpression.
Considera columnthatrepresentsmaritalstatusnumericallyas1,2,3,4 with thecorresponding valuesmeaningsingle,married,divorced, widowed.In somecases,youmightprefertostoretheshortvalues(1,2,3,4)fordatabase efficiency,butemployeesinhumanresourcesmight preferthemore
descriptivevalues(single,married,divorced,widowed).TheCASEexpression makessuchconversionsbetweendifferentsetsofvalueseasy.
DynamicServer
TheCASEexpressionalsosupportsextendeddatatypesand castexpressions. EndofDynamic Server
ThefollowingexampleshowsaCASEexpressionwithmultiple WHEN clausesthatreturnsmoredescriptivevaluesforthemanu_codecolumnofthe stocktable.IfnoneoftheWHENconditionsistrue,NULListhedefault result.(YoucanomittheELSENULLclause.)
SELECT CASE
WHEN manu_code = "HRO" THEN "Hero" WHEN manu_code = "SHM" THEN "Shimara" WHEN manu_code = "PRC" THEN "ProCycle" WHEN manu_code = "ANZ" THEN "Anza" ELSE NULL
END FROM stock
Youmust includeat leastoneWHENclausewithintheCASEexpression; subsequentWHENclausesand theELSE clauseareoptional.IfnoWHEN conditionevaluatestotrue,theresultingvalue isNULL.YoucanusetheIS NULLexpressiontohandleNULLresults.ForinformationonhandlingNULL values,seetheIBM Informix:GuidetoSQLSyntax.
Figure2-94 showsasimple CASEexpressionthatreturnsa characterstring valuetoflaganyordersfromtheorderstablethathavenotbeenshippedto thecustomer.
ForinformationabouthowtousetheCASE expressiontoupdateacolumn, see“Usinga CASEExpressiontoUpdate aColumn”onpage6-23.
SortingonDerivedColumns
WhenyouwanttouseORDERBYonanexpression,youcanuseeither the displaylabelassignedtotheexpressionor aninteger,asFigure2-96 and Figure2-98 show.
Figure2-96 retrievesthesame datafromthecust_callstableasFigure2-92.In Figure2-96,theORDER BYclausecausesthedatatobe displayedin
SELECT order_num, order_date, CASE
WHEN ship_date IS NULL THEN "order not shipped" END
FROM orders
Figure2-94.Query
order_num order_date (expression)
1001 05/20/1998 1002 05/21/1998 1003 05/22/1998 1004 05/22/1998 1005 05/24/1998
1006 05/30/1998 order not shipped 1007 05/31/1998 . . . 1019 07/11/1998 1020 07/11/1998 1021 07/23/1998 1022 07/24/1998 1023 07/24/1998
Figure2-95.QueryResult
SELECT customer_num, call_code, call_dtime, res_dtime - call_dtime span
FROM cust_calls ORDER BY span
ascendingorderofthederived valuesinthespan column,asFigure2-97 shows.
Figure2-98 usesanintegertorepresenttheresultoftheoperationres_dtime -call_dtimeand retrievesthesame rowsthatappearinFigure2-97.