Re: Embedded SQL cont'd

I will certainly explore that Rob....thanks for the tip


On Tue, 13 Dec 2005 09:38:29 -0500, rob wrote
> The cool thing about the UDF is that if you are finding you or the 
> users calculating this field a lot in your queries, then they can 
> just use the view. Drives me nuts sometimes to see a query that 
> joins 5 files and has a dozen calculated fields.  Dang, make it 
> easier on your users and create a view that does all that.
> 
> Rob Berendt
> -- 
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
> 
> rob@xxxxxxxxx 
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 12/13/2005 09:20 AM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> 
> To
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> cc
> 
> Fax to
> 
> Subject
> Re: Embedded SQL cont'd
> 
> It's not this line that it doesn't like:
> ((ILNEPRICE - ILNEUAVCST) / ILNEPRICE) as MYPCT
> 
> It's this line:
>       MYPCT <= :DMARGIN
> 
> There are a couple of ways to resolve this.
> 
> 1 - Change the offending line to:
>       ((ILNEPRICE - ILNEUAVCST) / ILNEPRICE) <= :DMARGIN
> 
> 2 - Create a User Defined Function to do this for you
> http://faq.midrange.com/data/cache/185.html
> Then you should be able to do
> ...
> MYPCTUDF(ILNEPRICE, ILNEUAVCST) as MYPCT
> FROM
>       LOWMARGNLF
> WHERE
>       ILNINVDATE <= :DFDATE AND
>       ILNINVDATE >= :DSDATE AND
>       MYPCTUDF(ILNEPRICE, ILNEUAVCST) <= :DMARGIN
> ORDER BY
>       ILNINV#A
> 
> 3 - Expand #2.  Create a view over the physical file.
> create view myview
> (ILNEPRICE, ILNEUAVCST, mypct, ...)
>  AS SELECT ilneprice, ilneuavcst, mypctudf(ilneprice, ilneuavcst), 
> ... FROM mylib/mytable Use this view in your RPG.
> 
> Rob Berendt
> -- 
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
> 
> "Douglas W. Palme" <dpalme@xxxxxxxxxxx> 
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 12/13/2005 08:48 AM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> 
> To
> "Mid Range List" <midrange-l@xxxxxxxxxxxx>
> cc
> 
> Fax to
> 
> Subject
> Embedded SQL cont'd
> 
> I have a calculated field in my SQL statement that is as follows:
> 
> ...
> ((ILNEPRICE - ILNEUAVCST) / ILNEPRICE) as MYPCT
> FROM
>       LOWMARGNLF
> WHERE
>       ILNINVDATE <= :DFDATE AND
>       ILNINVDATE >= :DSDATE AND
>       MYPCT <= :DMARGIN
> ORDER BY
>       ILNINV#A
> 
> C/END-EXEC
> 
> DFDATE, DSDATE and DMARGIN as parm that are being passed to the program...
> 
> When I run this in interactive mode, everything runs fine....however 
> the sql compiler burps by saying that MYPCT is not a field in the 
> table.....is it not possible to use this in embedded SQL?  Or do I 
> have to do something different?
> 
> If you bought it, it was hauled by a truck - somewhere, sometime.
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) 
> mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To 
> subscribe, unsubscribe, or change list options, visit: 
> http://lists.midrange.com/mailman/listinfo/midrange-l or email: 
> MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment 
> to review the archives at http://archive.midrange.com/midrange-l.
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) 
> mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To 
> subscribe, unsubscribe, or change list options, visit: 
> http://lists.midrange.com/mailman/listinfo/midrange-l or email: 
> MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment 
> to review the archives at http://archive.midrange.com/midrange-l.
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) 
> mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To 
> subscribe, unsubscribe, or change list options, visit: 
> http://lists.midrange.com/mailman/listinfo/midrange-l or email: 
> MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment 
> to review the archives at http://archive.midrange.com/midrange-l.


If you bought it, it was hauled by a truck - somewhere, sometime.