Select_Sql and Other Files

[sotis]

Hello,

In the case where a specific field of an "Other file" has a different name in the LANSA Repository than the name of the field in the Other file, which field name do I use in the Select_Sql command?

best,
Sotiris

[René Houba]

Hi Sotis,

It depends on the parameter of the LANSA SELECT_SQL statement

In the fields parameter you should always use the field name that has been generated when you did the Load Other.
But if you use the free format parameter Using, in there you should use the field names of the table (that has been loaded).

In example code below, I did a Load of an Other file named IMA.
The table contains fields like IMAIID, IMACCT, IMACDT, etc.
These fields where already in the LANSA Repository, so during the load, LANSA generated for example IMAIID_1, IMACCT_1, IMACDT_1.

In the fields parameter I use the _1 fields now:
Select_Sql Fields(#IMAIID_1 #IMACCT_1 #IMACDT_1 .........

The Using parameter uses the fields as in the table - without _1 of course (otherwise, when you run the application, SQL ends in error because you use an unknown column).

*****************************************************************************
* Check if image is present in table
*
#found := N
#Select_Using_SQL1 := ('SELECT "T1"."IMAIID", "T1"."IMACCT", "T1"."IMACDT", "T1"."IMAGLS", "T1"."IMAPID", "T1"."IMAPCS", "T1"."IMASDM", "T1"."IMAEDM", "T1"."IMASUB", "T1"."IMAOBJ___F" FROM "LVSDFLIB"."IMA" "T1" WHERE "T1"."IMAIID" = ''') + #Afbeelding + *QUOTE

Select_Sql Fields(#IMAIID_1 #IMACCT_1 #IMACDT_1 #IMAGLS_1 #IMAPID_1 #IMAPCS_1 #IMASDM_1 #IMAEDM_1 #IMASUB_1 #IMAOBJ___F) From_Files((IMA)) Using(#Select_Using_SQL1)
#found := J
Endselect

If (#found = J)
Message Msgtxt('BINGO!!! Image available.')
#ToolbarButton1.Image <= #VI_CHECK
Else
Message Msgtxt('What a pitty. Image not available.')
#ToolbarButton1.Image <= #VI_CROSS
Endif

*****************************************************************************

Kind regards,
René