SQL Join

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
Fromm603
Posts: 14
Joined: Fri Apr 26, 2019 10:51 pm

SQL Join

Post by Fromm603 » Wed Jun 12, 2019 7:30 am

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
Posts: 24
Joined: Tue Nov 24, 2015 10:29 am

Re: SQL Join

Post by BrendanB » Wed Jun 12, 2019 8:55 am

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
Posts: 372
Joined: Wed Jan 20, 2016 7:38 am

Re: SQL Join

Post by atostaine » Thu Jun 13, 2019 12:27 am

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.
Art Tostaine

Fromm603
Posts: 14
Joined: Fri Apr 26, 2019 10:51 pm

Re: SQL Join

Post by Fromm603 » Thu Jun 13, 2019 2:56 am

Top notch gents !!! Thanks

Fromm603
Posts: 14
Joined: Fri Apr 26, 2019 10:51 pm

Re: SQL Join

Post by Fromm603 » Thu Jun 13, 2019 6:11 am

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
Posts: 372
Joined: Wed Jan 20, 2016 7:38 am

Re: SQL Join

Post by atostaine » Thu Jun 13, 2019 7:22 am

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

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

Fromm603
Posts: 14
Joined: Fri Apr 26, 2019 10:51 pm

Re: SQL Join

Post by Fromm603 » Thu Jun 13, 2019 7:38 am

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

René Houba
Posts: 57
Joined: Thu Nov 26, 2015 7:03 am

SQL Join

Post by René Houba » Fri Jun 28, 2019 7:20 am

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
Posts: 372
Joined: Wed Jan 20, 2016 7:38 am

Re: SQL Join

Post by atostaine » Fri Jun 28, 2019 7:25 am

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

René Houba
Posts: 57
Joined: Thu Nov 26, 2015 7:03 am

Re: SQL Join

Post by René Houba » Fri Jun 28, 2019 6:18 pm

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.

Post Reply