Arow-typecolumnisa columnthatisdefinedonanamedROWtypeor unnamedROWtype.YouusethesameSQLsyntax toquerya namedROW typeandan unnamedrow-typecolumn.
Aqueryona row-typecolumnreturnsdatafromallthefieldsoftheROW type.Afieldisa componentdatatypewithina ROWtype.Forexample,the addresscolumnoftheemployeetablecontainsthestreet,city,state,andzip fields.Figure3-7showshow toconstructaquerythatreturnsallfieldsofthe addresscolumn.
Toaccessindividual fieldsthatacolumncontains,usesingle-dotnotation to projecttheindividual fieldsofthecolumn.Forexample,supposeyouwantto accessspecific fieldsfromtheaddresscolumnoftheemployeetable.The followingSELECTstatementprojects thecityand statefields fromthe addresscolumn.
name Paul, J.
address ROW(102 Ruby, Belmont, CA, 49932, 1000)
name Davis, J.
address ROW(133 First, San Jose, CA, 85744, 4900) .
. .
Figure3-6.QueryResult
SELECT address FROM employee
Figure3-7.Query
address ROW(102 Ruby, Belmont, CA, 49932, 1000) address ROW(133 First, San Jose, CA, 85744, 4900) address ROW(152 Topaz, Willits, CA, 69445, 1000)) .
. .
Figure3-8.QueryResult
SELECT address.city, address.state FROM employee
Youconstructa queryonanunnamedrow-typecolumninthesamewayyou constructaqueryonanamedrow-typecolumn.For example,supposeyou wanttoaccessdatafromthes_addresscolumnofthestudenttablein Figure3-2.Youcanusedotnotationtoquerytheindividualfields ofacolumn thataredefinedonan unnamedrowtype.Figure3-11showshowtoconstruct aSELECTstatementonthestudenttablethatreturnsrowsforthecityand statefields ofthes_address column.
FieldProjections
Donotconfusefieldswith columns.Columns areonlyassociatedwith tables, andcolumnprojectionsuseconventionaldotnotationof theform
name_1.name_2foratable andcolumn,respectively.Afieldisacomponent datatypewithina ROWtype.WithROWtypes(andthecapabilitytoassigna ROWtype toasinglecolumn),youcanprojectindividualfieldsofa column withsingledotnotation oftheform:name_a.name_b.name_c.name_d.Informix databaseserversusethefollowingprecedence rulestointerpretdotnotation: 1. table_name_a .column_name_b.field_name_c.field_name_d
2. column_name_a.field_name_b.field_name_c.field_name_d
Whenthemeaningofaparticularidentifier isambiguous,thedatabaseserver usesprecedence rulestodeterminewhichdatabase objecttheidentifier specifies.Considerthefollowingtwostatements:
city state Belmont CA San Jose CA Willits CA . . .
Figure3-10.QueryResult
SELECT s_address.city, s_address.state FROM student
Figure3-11.Query city state Belmont CA Mount Prospect IL Greeley CO . . .
CREATE TABLE b (c ROW(d INTEGER, e CHAR(2))) CREATE TABLE c (d INTEGER)
InthefollowingSELECTstatement, theexpressionc.dreferencescolumndof tablec(ratherthanfielddofcolumn cintableb)becauseatableidentifier hasa higherprecedencethanacolumn identifier:
SELECT * FROM b,c WHERE c.d = 10
Toavoidreferencingthewrong databaseobject,youcanspecifythefull notationfora fieldprojection.Suppose, forexample,youwanttoreference fielddofcolumncintableb(notcolumndoftablec).Thefollowing statementspecifies thetable,column,and fieldidentifiersoftheobjectyou wanttoreference:
SELECT * FROM b,c WHERE b.c.d = 10
Important: Althoughprecedencerulesreducethechance ofthedatabase servermisinterpretingfieldprojections,itisrecommendedthat youuseuniquenamesfor alltable,column,and fieldidentifiers.
UsingFieldProjectionstoSelectNestedFields
Typicallytherowtype isa column,butyoucanuseanyrow-typeexpression forfieldprojection.Whentherow-typeexpressionitselfcontains otherrow types,theexpressioncontains nestedfields.Toaccessnestedfields withinan expressionorindividual fields,usedotnotation. Toaccessallthefieldsof the rowtype,useanasterisk(*).Thissectiondescribesbothmethodsofrow-type access.
Fora discussionofhowtousedotnotation andasterisknotationwith row-typeexpressions,seetheExpressionsegmentintheIBM Informix:Guide toSQLSyntax.
SelectingIndividualFieldsofaRowType: Consider theaddresscolumnof theemployeetable,whichcontainsthefieldsstreet,city,state,andzip.In addition,thezipfieldcontainsthenestedfields:z_codeandz_suffix.(You mightwanttoreviewtherowtypeand tabledefinitionsofFigure3-1onpage 3-2.)Aqueryonthezipfieldreturnsrowsforthez_codeand z_suffixfields. However,youcanspecifythata queryreturnsonlyspecificnested fields. Figure3-13 showshowtousedotnotationtoconstructa SELECTstatement thatreturnsrowsforthez_codefieldoftheaddresscolumn only.
SELECT address.zip.z_code FROM employee
UsingAsteriskNotationtoAccessAllFieldsofa RowType Asterisknotationissupportedonlywithintheselectlistof aSELECT statement.Whenyouspecifythecolumnnamefora row-typecolumn ina projectionlist,thedatabase serverreturnsvaluesforallfields ofthecolumn. Youcanalso useasterisknotationwhenyouwanttoprojectall thefields withina ROWtype.
Figure3-15 usesasterisknotation toreturnallfields oftheaddress columnin theemployeetable.
Theasterisknotation makesit easiertoperformsomeSQLtasks. Supposeyou createafunction new_row()thatreturnsa row-typevalue andyouwantto callthis functionand inserttherowthatisreturnedintoa table.Thedatabase serverprovidesnoeasywaytohandlesuchoperations.However,Figure3-17 showshowtouseasterisknotation toreturnallfields ofnew_row()and insertthereturnedfields intothetab_2table.
z_code 39444 6500 76055 19004 . . .
Figure3-14.QueryResult
SELECT address.* FROM employee
Figure3-15.Query
address ROW(102 Ruby, Belmont, CA, 49932, 1000) address ROW(133 First, San Jose, CA, 85744, 4900) address ROW(152 Topaz, Willits, CA, 69445, 1000)) .
. .
Figure3-16.QueryResult
INSERT INTO tab_2 SELECT new_row(exp).* FROM tab_1
ForinformationabouthowtousetheINSERT statement,seeChapter6, “ModifyingData,”onpage6-1.
Important: Anexpressionthatusesthe.*notation isevaluated onlyonce.