× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I figured it out that I could change the MYPCT to ((ILNEPRICE - 
ILNEUAVCST) / ILNEPRICE) but it would be nice if it would have realized it 
was a field....oh well, it is the easiest fix....at some point I will go 
back and work with the user defined function for info purposes.

thanks for the help


On Tue, 13 Dec 2005 09:20:00 -0500, rob wrote
> 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.


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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.