Informix SE on Solaris, using ACE report writer...

Hello all - I am new to Informix and, indeed, to SQL as a whole ... I am able to do selects, simple param[] calls, and so on, but I cannot do two very basic things that I need to be able to do and was hoping that someone on here could point me in the right direction - 1) I can do select * stuff from orders where order_date=today into temp foo; which selects everything from the orders table and throws it into a table called foo, and a column called stuff (aliased) ... this is fine, but I *cannot* then go "back" to look at this table - I've tried database@server.foo.stuff, database.owner.foo.stuff, and the like, but I just cannot read this into anything! And, since I have 3 selects going on, I cannot read this into a variable, like select * into variable from orders where order_date=today ... so this is out the window, unfortunately. :( 2) I can do select * from orders where order_date=today but I cannot do: let foobar = (select * from orders where order_date = today); as I should be able to ... in fact, I can't use the LET keyword ANYWHERE! When isql goes to compile the .ACE file into an .ARC file, it whines about this and give me an error file instead. <sigh> As it can be seen, I've turned everywhere, and hopefully someone on here will have a sense of what I am trying to do and steer me in the right direction. Thanks!

Unless I'm missing something, you can only have one SQL statement in an ACE report. What you might want to do is create a stored procedure that figures out all the records you want and call the stored procedure in the SQL section of the ACE report. In article <Pine.SOL.3.95.1000725104153.21675A-100000@dana.ucc.nau.edu>, Chris Pilson <cp@dana.ucc.nau.edu> wrote: > Hello all - I am new to Informix and, indeed, to SQL as a whole ... I am > able to do selects, simple param[] calls, and so on, but I cannot do two > very basic things that I need to be able to do and was hoping that someone > on here could point me in the right direction - > 1) I can do > select * stuff from orders where order_date=today into > temp foo;> which selects everything from the orders table and throws it into > a table called foo, and a column called stuff (aliased) ... this > is fine, but I *cannot* then go "back" to look at this table - > I've tried database@server.foo.stuff, database.owner.foo.stuff, > and the like, but I just cannot read this into anything! And, > since I have 3 selects going on, I cannot read this into a > variable, like > select * into variable from orders whereorder_date=today > ... so this is out the window, unfortunately. :( > > 2) I can do > select * from orders where order_date=today > but I cannot do: > let foobar = (select * from orders where order_date = > today);> as I should be able to ... in fact, I can't use the LET keyword > ANYWHERE! When isql goes to compile the .ACE file into an .ARC > file, it whines about this and give me an error file instead. > > <sigh> As it can be seen, I've turned everywhere, and hopefully someone > on here will have a sense of what I am trying to do and steer me in the > right direction. > > Thanks! > > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

Chris Pilson wrote: > Hello all - I am new to Informix and, indeed, to SQL as a whole ... I am > able to do selects, simple param[] calls, and so on, but I cannot do two > very basic things that I need to be able to do and was hoping that someone > on here could point me in the right direction - > 1) I can do > select * stuff from orders where order_date=today into > temp foo; Does orders have ONLY ONE column? If not then the temp table foo will have <N> columns named after the columns of orders and NO column named 'stuff' unless orders has one. You must alias each column separately, taht's the way SQL works. If what you want is one column in foo, named 'stuff' containing the concatenation of the actual columns then you have to do that (assume orders has columns a, b, c, & d): SELECT a || b || c || d stuff FROM orders WHERE order_date = TODAY INTO TEMP foo; Now that does not seem to solve your problem, though: > > which selects everything from the orders table and throws it into > a table called foo, and a column called stuff (aliased) ... this > is fine, but I *cannot* then go "back" to look at this table - > I've tried database@server.foo.stuff, database.owner.foo.stuff, > and the like, but I just cannot read this into anything! And, As I said, foo is likely created and filled but has NO column named 'stuff'. > > since I have 3 selects going on, I cannot read this into a > variable, like > select * into variable from orders where order_date=today > ... so this is out the window, unfortunately. :( Why not? > > > 2) I can do > select * from orders where order_date=today > but I cannot do: > let foobar = (select * from orders where order_date = > today); > Because you cannot select multiple columns of multiple rows into a scalar > variable! > > as I should be able to ... in fact, I can't use the LET keyword > ANYWHERE! When isql goes to compile the .ACE file into an .ARC > file, it whines about this and give me an error file instead. > > <sigh> As it can be seen, I've turned everywhere, and hopefully someone > on here will have a sense of what I am trying to do and steer me in the > right direction. Not everywhere. Did you RTFM? Look at the example reports? Learn SQL? <Wow, I made it all the way to July before flaming someone this year. Cool last year I blew it in May! Flame off> Art S. Kagel

You need to read the documentation on writing ACE reports, which are very easy to do. Basically: An ACE report falls into a number of sections: DATABASE mydb END DEFINE variables... parameters... END SELECT select-statements END FORMAT PAGE HEADER PRINT page heading stuff ON EVERY ROW PRINT every row stuff END You only have SELECT statements in the SELECT section. You can have one statement getting the output of a previous one if necessary, but the output of the final statement is processed row by row and the report is printed. Simple select statement: SELECT * FROM orders ORDER BY order_no END More complex: SELECT columns FROM things INTO TEMP foo_temp; SELECT foo_temp.columns. some-other-table.columns FROM foo_temp, some-other-table WHERE some-other-table.join_column = foo_temp.join_column ORDER BY order-by-column END Hope this helps. ACE is well.... ACE? In article <Pine.SOL.3.95.1000725104153.21675A-100000@dana.ucc.nau.edu>, Chris Pilson <cp@dana.ucc.nau.edu> writes >Hello all - I am new to Informix and, indeed, to SQL as a whole ... I am >able to do selects, simple param[] calls, and so on, but I cannot do two >very basic things that I need to be able to do and was hoping that someone >on here could point me in the right direction - > 1) I can do > select * stuff from orders where order_date=today into > temp foo;> which selects everything from the orders table and throws it into > a table called foo, and a column called stuff (aliased) ... this > is fine, but I *cannot* then go "back" to look at this table - > I've tried database@server.foo.stuff, database.owner.foo.stuff, > and the like, but I just cannot read this into anything! And, > since I have 3 selects going on, I cannot read this into a > variable, like > select * into variable from orders where order_date=today > ... so this is out the window, unfortunately. :(> > 2) I can do > select * from orders where order_date=today > but I cannot do: > let foobar = (select * from orders where order_date = > today);> as I should be able to ... in fact, I can't use the LET keyword > ANYWHERE! When isql goes to compile the .ACE file into an .ARC > file, it whines about this and give me an error file instead. > ><sigh> As it can be seen, I've turned everywhere, and hopefully someone >on here will have a sense of what I am trying to do and steer me in the >right direction. > >Thanks! > > -- Surfer!