Visual LANSA Forum

Delete From_File(CF1234)
Where((#DTEDATE = #wk_date) *And (#DTRECTYP = "RC") *And (#DTCMR = "M") *And (#DTPCDE = *Blanks)
*And (#DTDESC.Substring( 1 8 ) = "<*ERROR*")) Io_Error(*NEXT)

The above statement is not working, I did a SQL delete it work
Delete from cf1234 where dtedate=20191001 and DTRECTYP='RC' and
dtcmr='M'
and substring(DTDESC,1,8)='<*ERROR*'

BUT the SQL_Select using (#SQLStatement) is not executing.

Any suggestion any one? Thanks!

Could it be the extra *And (#DTPCDE = *Blanks) that you have in the DELETE that you don't have in the SQL statement. Not sure if you can delete using SELECT_SQL USING.

#1 NEVER use a where clause in LANSA unless you are using SELECT_SQL

why?
because it will actually read every record in the file and the where is burned into the code as if statements inline

#2 The statement you have is non debuggable because you can't step it

Try doing a select with the same conditions, exactly like you have it

SELECT .....
if (conditions)
DELETE
endif
ENDSELECT

And then you can see if your where clause is pulling in any records or not.
If the record matches you can then do a DELETE with no key (that is delete the record you just read).

At a minimum you can determine if the where clause works.

#3 Still nothing...just do a select with no keys and see if you get ANY RECORDS at all. Maybe it is a library list issue?

Thank you! Got it to work with Select and Delete......

don't forget to update the title for your post if it is resolved

original title (SOLVED)

Thanks,
Paul