Newbee SPL question

I am trying to SELECT some fields from a table into a temporary table, so that I can manipulated the data. I am having a problem creating the table to put them INTO. From the manual I have: DEFINE CityCustomers MULTISET (ROW (Address CHAR(80), City CHAR(80)) Not Null); FOREACH cursor1 FOR SELECT Address,City INTO CityCustomers FROM CUSTOMERS Where City = "New York";END FOR; I suspect my problem in in the DEFINE. Any help would be greatly appreciated. DJCoady

The INTO Clause is for SPL variables. Try: SELECT Address,City FROM CUSTOMERS Where City = "New York" INTO TEMP t_work_addr WITH NO LOG; -- If you have a loged DB "DJinLA" <djinla@aol.com> wrote in message news:20000926160201.03516.00000435@ng-cr1.aol.com... > I am trying to SELECT some fields from a table into a temporary table, so that > I can manipulated the data. I am having a problem creating the table to put > them INTO. > > From the manual I have: > > DEFINE CityCustomers MULTISET (ROW (Address CHAR(80), > City CHAR(80)) > Not Null); > FOREACH cursor1 FOR > SELECT Address,City > INTO CityCustomers > FROM CUSTOMERS > Where City = "New York";> END FOR; > > I suspect my problem in in the DEFINE. > > Any help would be greatly appreciated. > > DJCoady > >

Why not just do it the old fashioned way: SELECT Address,City FROM CUSTOMERS Where City = "New York" INTO TEMP CityCustomerTemp ; Art S. Kagel DJinLA wrote: > > I am trying to SELECT some fields from a table into a temporary table, so that > I can manipulated the data. I am having a problem creating the table to put > them INTO. > > From the manual I have: > > DEFINE CityCustomers MULTISET (ROW (Address CHAR(80), > City CHAR(80)) > Not Null); > FOREACH cursor1 FOR > SELECT Address,City > INTO CityCustomers > FROM CUSTOMERS > Where City = "New York";> END FOR; > > I suspect my problem in in the DEFINE. > > Any help would be greatly appreciated. > > DJCoady

DJinLA wrote: > I am trying to SELECT some fields from a table into a temporary table, so that > I can manipulated the data. I am having a problem creating the table to put > them INTO. -- CREATE TABLE CityCustomers ( Name VARCHAR(32) NOT NULL, Address VARCHAR(80) NOT NULL, City VARCHAR(40) NOT NULL );-- INSERT INTO CityCustomers ( Name, Address, City ) SELECT N1.Name || ' ' || N2.Name, N3.Num || ' ' || N2.Name || ' Street', N4.Name FROM TABLE(SET{'MyTown','YourTown','Normal','New York'}) N4 ( Name ), TABLE(SET{101, 201, 303, 10010, 500}) N3 ( Num ), TABLE(SET{'Bush','Dole','Gore','Clinton'}) N2 ( Name ), TABLE(SET{'Al','George','Bill','Bob'}) N1 ( Name );-- -- CREATE FUNCTION DoIt ( Arg1 LVARCHAR )RETURNS INTEGER DEFINE CCust MULTISET(ROW(Address VARCHAR(80), City VARCHAR(40)) NOT NULL); -- -- OK. I think this is your problem. The elements of the MULTISET -- you have just defined are ROW() instances, not "pairs of -- values". So you need to turn the query result into something -- more reasonable. -- INSERT INTO TABLE(CCust) SELECT ROW ( C.Address, C.City ) FROM CityCustomers C WHERE C.Name LIKE '%' || Arg1 || '%'; RETURN (SELECT COUNT(*) FROM TABLE(CCust)); END FUNCTION; -- EXECUTE FUNCTION DoIt ( 'Gore' );-- -- DROP TABLE CityCustomers; DROP FUNCTION DoIt ( LVARCHAR );-- -- HOWEVER! Unless you're doing something with this MULTISET -- like returning it to the client program, or passing it -- as an argument into another function, then Art & Jay -- are spot on: use the temporary table. Particularly if you have -- a really big intermediate result to work with.Temp tables let you -- create indices and get a grip on it with UPDATE STATS. -- COLLECTION data structures are just big lists in memory. -- They need to be scanned by all operations over them, which makes -- something like SET() difficult because each INSERT needs to -- check the entire contents of the set. -- -- Just because a feature is new doesn't mean its always better. Just -- because a feature has been around for a long time doesn't mean its -- the best one to use now. -- -- By way of a minor rant, why all the questions about COLLECTIONS? -- They're not really very novel or useful (except for a number of corner cases, -- and in queries like the one I have above). It's the data types, people, as the -- guy who just got himself and about twenty of his friends out of the -- small car said. -- grump grump . .