From_File as a variable parm

[adale]

Is it possible to use a variable value in the "From_File" in a Select statement?

I need to run a series of select statements to gather some count info across several files. I was hoping to use an initial list with the different file names listed, and then use the SELCT statement with the file name input as a parm. So far, all my attempts result in an IDE error message "Table not found".

[stevelee67]

not in an RDML or standard SELECT statement.

you can do this with SELECT_SQL, RDMLX only, with the USING parameter.

the upside: a heckuva lot of flexibility
the downside: repository features basically gone

for your use case (looking for record counts) it could be fairly easy:

MTHROUTINE uGetRecordCount
DEFINE_MAP *INPUT #STD_TEXT #iFileName help('input file name')
DEFINE_MAP *RESULT #STD_NUM #oRecords help('record count')

#oRecords := *ZERO

#W_SQL := 'SELECT COUNT(1) FROM &1'
SELECT_SQL FIELDS(#LISTCOUNT) USING(#W_SQL)
#oRecords := #LISTCOUNT
LEAVE
ENDSELECT

ENDROUTINE

to use in code:
#zRecords := #com_owner.uGetRecordCount('fileX')
#zRecords := #com_owner.uGetRecordCount('fileY')
#zRecords := #com_owner.uGetRecordCount('fileZ')

[adale]

Interesting. Thanks for the insight! I will give it a go.

[adale]

Is there an equivalent "delete" function that would work with a variable parm as the from file name?
I don't see a delete_sql?

[davidbalansa]

From the SELECT_SQL (Free format) documentation, it states you can enter the delete statement in the using parameter.


https://docs.lansa.com/16/en/lansa015/i ... 257C_____0

Screenshot 2025-11-26 063050.png (24.59 KiB) Viewed 7605 times

[Dino]

Debated here:

viewtopic.php?f=3&t=2595&p=7680&hilit=D ... _sql#p7803

[stevelee67]

Is there an equivalent "delete" function that would work with a variable parm as the from file name?
I don't see a delete_sql?

i've had mixed success with non-SELECT statements. i've done UPDATEs, INSERTs, and DELETEs but they don't always work for me. i chalk it up to something in the SQL parser engine because the code seems to work when i use ACS's SQL editor.

the format from @davidbalansa's note is the way i've done it and gotten it to work but it doesn't always cooperate. that said, it would still be the first thing i would try.

but if that doesn't work, i've actually found that doing a USE SYSTEM_COMMAND with parameters something like (X 'RUNSQL SQL(''DELETE FROM FILE <file>'') ') is more reliable (pay attention to the COMMIT parameter on RUNSQL... i find COMMIT(*NONE) works best.