DELETE from file with substring selection not working (RESOLVED)

This Q&A forum allows users to post and respond to "How Do I Do ....." questions. Please do not use to report (suspected) errors - you must use your regional help desk for this. The information contained in this forum has not been validated by LANSA and, as such, LANSA cannot guarantee the accuracy of the information.
Post Reply
eddie9394
Posts: 23
Joined: Tue Aug 14, 2018 11:03 pm

DELETE from file with substring selection not working (RESOLVED)

Post by eddie9394 » Tue Oct 01, 2019 2:12 am

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!

caseywhite
Posts: 119
Joined: Thu May 26, 2016 1:17 am

Re: DELETE from file with substring selection not working

Post by caseywhite » Tue Oct 01, 2019 9:08 am

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.

LANSAGuru
Posts: 68
Joined: Thu Mar 24, 2016 5:31 am

Re: DELETE from file with substring selection not working

Post by LANSAGuru » Wed Oct 02, 2019 1:14 am

#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?

eddie9394
Posts: 23
Joined: Tue Aug 14, 2018 11:03 pm

Re: DELETE from file with substring selection not working

Post by eddie9394 » Wed Oct 02, 2019 5:37 am

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

LANSAGuru
Posts: 68
Joined: Thu Mar 24, 2016 5:31 am

Re: DELETE from file with substring selection not working

Post by LANSAGuru » Wed Oct 02, 2019 7:19 am

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

original title (SOLVED)

Thanks,
Paul

Post Reply