Convert SQL to RDMLX?

[atostaine]

I have a SQL Statement similar to this that I'd like to convert to RDMLX. The group_by is a problem for RDMLX? This SQL writes one record per group, with a total of QTY. I'd like to write one list entry per group. I've been looking at Keep_total but haven't tried that yet.

I'd rather convert this than use Select_Sql.

insert into Workfile (
shp,sea,sty,clr, fab,mod,msz,qt3)
select ls.shp,ls.sea, ls.sty, ls.clr, ls.fab, ls.mod, ls.msz, sum(ls.qty)
from locsku00 ls
where ls.rid>:invrid and ls.shp=:shp and ls.sty=:sty
and (:clr='*ALL' or clr=:clr)
and (:fab='*ALL' or fab=:fab)
and (:mod='*ALL' or mod=:mod)
and (:msz='*ALL' or msz=:msz)
and (:siz='*ALL' or siz=:siz)
and lp.reg='RCV'
group by
:#sdjb#,ls.shp,
ls.sea, ls.sty, ls.clr,
ls.fab, ls.mod, ls.msz
;

Any ideas appreciated.

Art

[soa]

Why wouldn't you just build a select_sql statement with using then put the insert inside the loop?

[atostaine]

It stinks that I have to create my where by using SqlSelect := 'sty = ' + #quote + '12345' + quote . Lots of room for errors.

Art

[caseywhite]

You should be able to do this.

#SQL := "sty='" + "12345" + "'"

That is use double quotes instead of single quotes to wrap literals which allows you to use single quotes in the string.

[soa]

I think having to handle quotes is a small price to pay for accessing the full functionality of sql within an RDMLX function. The speed improvements over traditional SELECT and FECH type coding can be astounding.

[atostaine]

The where parameter of the command should let me build a string with my field names in there.

The quotes aren’t the only thing. You have numeric and dates
To deal with also.

[soa]

I just built this a couple of minutes ago. Without select_sql I would have had to build two select loops with appropriate logicals and a fetch. I was able to test this select logic in the green screen sql function before hand and I know that if I was to debug this I could add a breakpoint to the select_sql statement then cut and paste to strsql command to check the statement out and modify the text on the fly. Like I said a lot of power, easy to use.

If Cond(#CRSID_CT *EQ *ZERO)
#wrk5000 := 'select STDID, STDSTS, STDNMF, STDGNM from STDENT'
#wrk5000 += ' where CALYR=' + #CALYR_W.AsString
#wrk5000 += ' and STUDYR=' + #STUDYR_W.AsString
#wrk5000 += ' and STDMSI=' + #SCHID_CT.AsString
#wrk5000 += ' order by UPPER(STDNMF), UPPER(STDGNM)'
Else
#wrk5000 := 'select A.STDID, B.STDSTS, B.STDNMF, B.STDGNM from STSCCR A'
#wrk5000 := ' join STDENT B on A.CALYR=B.CALYR and A.STUDYR=B.STUDYR and A.STDID=B.STDID'
#wrk5000 += ' where CALYR=' + #CALYR_W.AsString
#wrk5000 += ' and STUDYR=' + #STUDYR_W.AsString
#wrk5000 += ' and SCHID=' + #SCHID_CT.AsString
#wrk5000 += ' and CRSID=' + #CRSID_CT.AsString
#wrk5000 += ' and STDSTS=' + #QUOTE + 'ACTIV' + #QUOTE
#wrk5000 += ' order by UPPER(STDNMF), UPPER(STDGNM)'
Endif

Select_Sql Fields(#STDID #STDSTS #STDNMF #STDGNM) Io_Error(*NEXT) Using(#WRK5000)
endselect

[atostaine]

This doesn’t look like lowcode to me.

[soa]

What is your question? This is how you do SQL with RDML. Perhaps you could make a suggestion in the 'Product Enhancement Forum'

[atostaine]

That’s a good idea. I will submit it.

Sql db access is where modern platforms are but LANSA’s OAM’s don’t fit in there very well.

Don’t get mad. I’m a lansa fanboy since 1999.

[soa]

I agree that in an sql world i/o modules don't make as much sense as they did the rpg days. It should be possible to implement all the validation rules in the repository as native sql constraints and access routes as views etcetera and basically do away with i/o modules but I guess there's only so much they can do. I noticed in the last epc that they've quietly allowed the use of ALTER TABLE for adding fields to files rather than the old $$ copy which is a step forward. It would also be nice if RDML had native a .Net Entity Framework style way of accessing databases but I'm not holding my breath!

Resources are limited but what LANSA has recently delivered with VLWEB Material Design is such a huge leap forward and allows us to easily deliver modern looking web applications for the first time. I'd rather they put their effort in this area even if it means I have to keep on handcrafting SQL!

(not mad)

[atostaine]

Resources are limited but what LANSA has recently delivered with VLWEB Material Design is such a huge leap forward and allows us to easily deliver modern looking web applications for the first time. I'd rather they put their effort in this area even if it means I have to keep on handcrafting SQL!
(not mad)

LOL. Ok. I agree VLWEB is fantastic. We are live with many applications on Ipad's and every other kind of device.

Art