LET statement in 4GL

I want to prepare an SQL statement from the following LET: LET selstmt = "UNLOAD TO ", DELETE_DIR CLIPPED, gr_item.isn USING "<<<<<<<<<<", "dp", " SELECT * FROM process WHERE process.isn = ", gr_item.isn The prepare statement is falling over with a SQL -201 synntax error. When I DISPLAY the selstmt from the program the output is: UNLOAD TO /unloaddir/12345678dp SELECT * FROM process WHERE process.isn =12345678 If I cut and paste this into dbaccess it unloads the row correctly. Any suggestions?

DarkEmperor <darkemperor_uk@yahoo.co.uk> wrote in message news:8f9co4$rgc$1@news7.svr.pol.co.uk... > I want to prepare an SQL statement from > the following LET: > LET selstmt = "UNLOAD TO ", DELETE_DIR CLIPPED, gr_item.isn USING > "<<<<<<<<<<", "dp", " SELECT * FROM process WHERE process.isn = ", > gr_item.isn > The prepare statement is falling over with a SQL -201 synntax error. > When I DISPLAY the selstmt from the program the output is: > UNLOAD TO /unloaddir/12345678dp SELECT * FROM process WHERE process.isn => 12345678 > If I cut and paste this into dbaccess it unloads the row correctly. > Any suggestions? > Try putting single quotes around the filename Eg LET selstmt = "UNLOAD TO '", DELETE_DIR CLIPPED, gr_item.isn USING "<<<<<<<<<<", "dp'", " SELECT * FROM process WHERE process.isn = ", gr_item.isn UNLOAD TO filename works fine in SQL bue you require UNLOAD TO "filename" in 4GL

DarkEmperor wrote: > > I want to prepare an SQL statement from > the following LET: > LET selstmt = "UNLOAD TO ", DELETE_DIR CLIPPED, gr_item.isn USING > "<<<<<<<<<<", "dp", " SELECT * FROM process WHERE process.isn = ", > gr_item.isn > The prepare statement is falling over with a SQL -201 synntax error. > When I DISPLAY the selstmt from the program the output is: > UNLOAD TO /unloaddir/12345678dp SELECT * FROM process WHERE process.isn => 12345678 > If I cut and paste this into dbaccess it unloads the row correctly. > Any suggestions? That's because UNLOAD is a dbaccess built in command and also a 4GL built-in operator. UNLOAD is NOT an SQL statement that the server recognizes so you cannot PREPARE it since PREPARE sends the statement to the server to be optimized and the server does not recognize it. Doing so it akin to PREPARING a FOREACH statement. I believe you can PREPARE the SELECT statement then: UNLOAD TO "filename" sel_stmt_name Art S. Kagel

"Art S. Kagel" wrote: > DarkEmperor wrote: > > > > I want to prepare an SQL statement from > > the following LET: > > LET selstmt = "UNLOAD TO ", DELETE_DIR CLIPPED, gr_item.isn USING > > "<<<<<<<<<<", "dp", " SELECT * FROM process WHERE process.isn = ", > > gr_item.isn > > The prepare statement is falling over with a SQL -201 synntax error. > > When I DISPLAY the selstmt from the program the output is: > > UNLOAD TO /unloaddir/12345678dp SELECT * FROM process WHERE process.isn => > 12345678 > > If I cut and paste this into dbaccess it unloads the row correctly. > > Any suggestions? > > That's because UNLOAD is a dbaccess built in command and also a 4GL built-in > operator. UNLOAD is NOT an SQL statement that the server recognizes so you > cannot PREPARE it since PREPARE sends the statement to the server to be > optimized and the server does not recognize it. Doing so it akin to > PREPARING a FOREACH statement. > > I believe you can PREPARE the SELECT statement then: > > UNLOAD TO "filename" sel_stmt_name> Actually, it's simpler than that. You can create the SELECT statement as a string and then supply the string where Art wrote sel_stmt_name. The UNLOAD statement does the PREPARE etc for you. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN #include <disclaimer.h>