SQL Join

[Fromm603]

Is there a best practice or easy method for doing an SQL join? I am in the server module creating an instance list, the filter allows for search on a description field, that is not in my primary file, my primary file has a code. I use that code to get from file A to B (along with a hard coded key value).

Here's my SQL from the IBMi. (the word CASES is where I'd substitute the filter input description value)

SELECT IM.*, SR.SRNDSC FROM im join sr on IM.IMFGRP = SR.SRNref
WHERE sr.srnid = 'FG' and SR.SRNDSC like '%' || trim('CASES') ||
'%' ORDER BY im.imitno

I assume the SELECT_SQL can do this, I just don't have an example on syntax.

[BrendanB]

Hi,

you are on the right path...

simply do:

Code: Select all

define_com class(#prim_alph) name(#SqlStatement)

#SqlStatement := "SELECT IM.*, SR.SRNDSC FROM im join sr on IM.IMFGRP = SR.SRNref WHERE sr.srnid = 'FG' and SR.SRNDSC like '%' || trim('" + #CASES + "') || '%' ORDER BY im.imitno"

but change IM.* to the actual fields you need....

then

Code: Select all

SELECT_SQL fields(#field1 #field2 #srndsc) using(#SqlStatement)

* do what you need with the values....

endselect

I find it best to be specific about what fields you want returned. the order is important as well, as the returned values from the SQL are mapped into your fields.

HTH

Brendan.

[atostaine]

I did A BUNCH of Select_Sql recently. If you can create views to make your selections and joins happen in the DB and you don't have to worry about them in LANSA.

If you are connecting to IBM i the joblog will be a huge help. For instance, you specify 10 fields in the SELECT statement but a different number in the SELECT_SQL, you'll see that there.

[Fromm603]

Top notch gents !!! Thanks

[Fromm603]

Yikes,
I get this error on the IBMi
Message . . . . : Fatal Error : Component : FVL_IM__5 Server Module
Statement : 78 Message : DBM Error : Column specified (1) is out of range
for SQLDA (max is 2) Routine : Derive_Column_Map_from_Func.

Statement 78
#SqlStatement := "SELECT IM.IMITNO IM.IMDESC FROM IM join SR on IM.IMFGRP = SR.SRNref WHERE sr.srnid = 'FG' and SR.SRNDSC like '%' || trim('" + #IMFGRP + "') || '%' ORDER BY im.imitno"

[atostaine]

Does your select_Sql command have IM.IMITNO IM.IMDESC fields in it?.

Don't you need a comma in between the two fields?

[Fromm603]

Thanks Art, it was simply the missing comma between fields on the SQL.

[René Houba]

Another technique is to create a view on your iSeries.

Example:

Create View VWWOPS1 AS(
select VBSWOP.WOPCDE, VBSWOP.STSCDE, VBSWOP.GBRODG,VBSWOP.BWSCDE
, VBSWOP.MCDCDE, VBSWOP.ORDCDE, VBSWOP.WOPDAT, VBSWOP.WOPRD1,
VBSWOP.WOPOP1, VBSWOP.NOTCDE, VBSVRD.ARTCDE, VBSVRD.GENCDE,
VBSVRD.BSTCDE, VBSVRD.PARCDE, VBSVRD.WOUVLG, VBSVRD.ARTOMZ , VBSPAR.PRCCDE,
VBSPAR.OGSTJR, VBSPER.TCTCDE, VBSTCT.RELTLR
FROM VBSWOP
LEFT JOIN VBSVRD ON (VBSWOP.WOPCDE = VBSVRD.WOPCDE AND
VBSVRD.WOUVLG = 1)
LEFT JOIN VBSPAR ON VBSVRD.PARCDE = VBSPAR.PARCDE
LEFT JOIN VBSPER ON (VBSPAR.PRCCDE = VBSPER.PRCCDE AND
VBSPAR.OGSTJR = VBSPER.OGSTJR)
LEFT JOIN VBSTCT ON VBSPER.TCTCDE = VBSTCT.TCTCDE ) ;

Example above creates a VIEW called VWWOPS1 and is based on data of files VBSWOP, VBSVRD, VBSPAR, VBSPER and VBSTCT.

Now in LANSA, create this view as an OTHER file.
Use this OTHER file in your LANSA RDMLX.

This technique makes selecting data like this MUST MUST faster.



Kind regards,
René

[atostaine]

Wow never thought of importing the view as it's own file. That's brilliant.

[René Houba]

Hi Art,

We use this technique in some VLF projects where we have a very complex filled Instance List. Because the selection in done already by the VIEW, this instance list is filled very quick.