Progress help Blank Title 113541

Yahoo Archive
1
This is a valid Progress query I can execute from ASP or Squirrel (ODBC).
Progress does have subqueries, I just don't know how it would look in ABL
syntax ("for each, blah blah blah").



Note the final 6 column names from "ALLTIME_QUOTED_DOLLARS" to
"YTD_BOOKED_DOLLARS" are actually subquery results from the LEFT JOINS
below.



Coming from SQL, too, I love my subqueries.



SELECT

C.NAME,

C.CUSTID,

C.ADDRESS1, C.ADDRESS2, C.CITY, C.STATE, C.ZIP, C.COUNTRY,
C.PHONENUM,

'http://maps.google.com/maps?q=' + C.ADDRESS1 + ', ' + C.CITY + ', '
+ C.STATE + ', ' + C.ZIP AS 'MAPLINK',

C.SALESREPCODE AS 'SALESREP_CODE',

S.NAME AS 'SALESREP_NAME',

C.ESTDATE AS 'CUST_SINCE',

ALLTIME_QUOTED_DOLLARS,

ALLTIME_BOOKED_DOLLARS,

TMON_QUOTED_DOLLARS,

TMON_BOOKED_DOLLARS,

YTD_QUOTED_DOLLARS,

YTD_BOOKED_DOLLARS

FROM PUB.CUSTOMER C

INNER JOIN PUB.SALESREP S ON S.COMPANY = C.COMPANY AND S.SALESREPCODE
= C.SALESREPCODE

LEFT JOIN

(SELECT COMPANY, CUSTNUM, SUM((NUMBER08 * NUMBER03) *
CONFIGUNITPRICE) AS ALLTIME_QUOTED_DOLLARS FROM PUB.QUOTEDTL GROUP BY
COMPANY, CUSTNUM) D ON D.COMPANY = C.COMPANY AND D.CUSTNUM = C.CUSTNUM

LEFT JOIN

(SELECT COMPANY, CUSTNUM, SUM(DOCUNITPRICE * ORDERQTY) AS
ALLTIME_BOOKED_DOLLARS FROM PUB.ORDERDTL GROUP BY COMPANY, CUSTNUM) B ON
B.COMPANY = C.COMPANY AND B.CUSTNUM = C.CUSTNUM

LEFT JOIN

(SELECT COMPANY, CUSTNUM, SUM((NUMBER08 * NUMBER03) *
CONFIGUNITPRICE) AS TMON_QUOTED_DOLLARS FROM PUB.QUOTEDTL

WHERE QUOTENUM IN (SELECT QUOTENUM FROM PUB.QUOTEHED WHERE
DATEQUOTED >= (SYSDATE()-365))

GROUP BY COMPANY, CUSTNUM) DD ON DD.COMPANY = C.COMPANY AND
DD.CUSTNUM = C.CUSTNUM

LEFT JOIN

(SELECT COMPANY, CUSTNUM, SUM(DOCUNITPRICE * ORDERQTY) AS
TMON_BOOKED_DOLLARS FROM PUB.ORDERDTL

WHERE ORDERNUM IN (SELECT ORDERNUM FROM PUB.ORDERHED WHERE
ORDERDATE >= (SYSDATE()-365))

GROUP BY COMPANY, CUSTNUM) BB ON BB.COMPANY = C.COMPANY AND
BB.CUSTNUM = C.CUSTNUM

LEFT JOIN

(SELECT COMPANY, CUSTNUM, SUM((NUMBER08 * NUMBER03) *
CONFIGUNITPRICE) AS YTD_QUOTED_DOLLARS FROM PUB.QUOTEDTL

WHERE QUOTENUM IN (SELECT QUOTENUM FROM PUB.QUOTEHED WHERE
YEAR(DATEQUOTED) = YEAR(SYSDATE()))

GROUP BY COMPANY, CUSTNUM) DDD ON DDD.COMPANY = C.COMPANY AND
DDD.CUSTNUM = C.CUSTNUM

LEFT JOIN

(SELECT COMPANY, CUSTNUM, SUM(DOCUNITPRICE * ORDERQTY) AS
YTD_BOOKED_DOLLARS FROM PUB.ORDERDTL

WHERE ORDERNUM IN (SELECT ORDERNUM FROM PUB.ORDERHED WHERE
YEAR(ORDERDATE) = YEAR(SYSDATE()))

GROUP BY COMPANY, CUSTNUM) BBB ON BBB.COMPANY = C.COMPANY AND
BBB.CUSTNUM = C.CUSTNUM

ORDER BY C.NAME





From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Brian Roberts
Sent: Thursday, September 06, 2012 12:13 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] progress help





Progress does not have an equivalent of SQL sub queries at all. Since
you're coming from SQL, switch your mindset from "writing a fancy view"
to "writing a stored procedure" -- that's a closer match to how Progress
4GL works. Temporary tables, variables, if commands, etc.

Brian.

-----Original Message-----
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of John Driggers
Sent: Thursday, September 06, 2012 11:58 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] progress help

I'm not sure that will work in 4gl.

Loop through your TT table once to get your sum and store it in a
variable.
Use that in a second query.

On Thu, Sep 6, 2012 at 10:46 AM, STEPHEN <subuloye@...
<mailto:subuloye%40yahoo.com> > wrote:

> **
>
>
> Hi all,
> I know that I might sound silly but I am fairly new to this Progress
> thing but well skilled at sql.
>
> I have a query that I would like to put in a BPM but am having an
> issue as I do not know how to write the sub query in the following
statement.
> I have substituted it with the sql version. Help
>
> for each ttdropshipdtl
> where (ttdropshipdtl.RowMod = 'U' or ttdropshipdtl.RowMod = 'A') each
> OrderHed each OrderDtl where OrderHed.ponum =ttdropshipdtl.ponum and
> OrderDtl.ordernum = ttdropshipdtl.ordernum and OrderDtl.company =
> ttdropshipdtl.company and and OrderDtl.OrderLine =
> ttdropshipdtl.OrderLine and (OrderDtl.sellingquantity - (select
> sum(ourqty) from dropshipdtl where ordernum = ttdropshipdtl.ordernum
> and company = ttdropshipdtl.company and orderline =
> ttdropshipdtl.orderline)) < ttdropshiphead.OurQty
>
> any thanks
>
>
>

[Non-text portions of this message have been removed]

------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links



No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2197 / Virus Database: 2437/5252 - Release Date: 09/06/12




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2197 / Virus Database: 2437/5252 - Release Date: 09/06/12

[Non-text portions of this message have been removed]
2
Hi all,
I know that I might sound silly but I am fairly new to this Progress thing but well skilled at sql.

I have a query that I would like to put in a BPM but am having an issue as I do not know how to write the sub query in the following statement.
I have substituted it with the sql version. Help


for each ttdropshipdtl
where (ttdropshipdtl.RowMod = 'U' or ttdropshipdtl.RowMod = 'A')
each OrderHed
each OrderDtl
where OrderHed.ponum =ttdropshipdtl.ponum and OrderDtl.ordernum = ttdropshipdtl.ordernum and OrderDtl.company = ttdropshipdtl.company and
and OrderDtl.OrderLine = ttdropshipdtl.OrderLine and
(OrderDtl.sellingquantity - (select sum(ourqty) from dropshipdtl
where ordernum = ttdropshipdtl.ordernum
and company = ttdropshipdtl.company
and orderline = ttdropshipdtl.orderline)) < ttdropshiphead.OurQty



any thanks
3
I'm not sure that will work in 4gl.

Loop through your TT table once to get your sum and store it in a variable.
Use that in a second query.

On Thu, Sep 6, 2012 at 10:46 AM, STEPHEN <subuloye@...> wrote:

> **
>
>
> Hi all,
> I know that I might sound silly but I am fairly new to this Progress thing
> but well skilled at sql.
>
> I have a query that I would like to put in a BPM but am having an issue as
> I do not know how to write the sub query in the following statement.
> I have substituted it with the sql version. Help
>
> for each ttdropshipdtl
> where (ttdropshipdtl.RowMod = 'U' or ttdropshipdtl.RowMod = 'A')
> each OrderHed
> each OrderDtl
> where OrderHed.ponum =ttdropshipdtl.ponum and OrderDtl.ordernum =
> ttdropshipdtl.ordernum and OrderDtl.company = ttdropshipdtl.company and
> and OrderDtl.OrderLine = ttdropshipdtl.OrderLine and
> (OrderDtl.sellingquantity - (select sum(ourqty) from dropshipdtl
> where ordernum = ttdropshipdtl.ordernum
> and company = ttdropshipdtl.company
> and orderline = ttdropshipdtl.orderline)) < ttdropshiphead.OurQty
>
> any thanks
>
>
>


[Non-text portions of this message have been removed]
4
Progress does not have an equivalent of SQL sub queries at all. Since
you're coming from SQL, switch your mindset from "writing a fancy view"
to "writing a stored procedure" -- that's a closer match to how Progress
4GL works. Temporary tables, variables, if commands, etc.

Brian.

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of John Driggers
Sent: Thursday, September 06, 2012 11:58 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] progress help

I'm not sure that will work in 4gl.

Loop through your TT table once to get your sum and store it in a
variable.
Use that in a second query.

On Thu, Sep 6, 2012 at 10:46 AM, STEPHEN <subuloye@...> wrote:

> **
>
>
> Hi all,
> I know that I might sound silly but I am fairly new to this Progress
> thing but well skilled at sql.
>
> I have a query that I would like to put in a BPM but am having an
> issue as I do not know how to write the sub query in the following
statement.
> I have substituted it with the sql version. Help
>
> for each ttdropshipdtl
> where (ttdropshipdtl.RowMod = 'U' or ttdropshipdtl.RowMod = 'A') each
> OrderHed each OrderDtl where OrderHed.ponum =ttdropshipdtl.ponum and
> OrderDtl.ordernum = ttdropshipdtl.ordernum and OrderDtl.company =
> ttdropshipdtl.company and and OrderDtl.OrderLine =
> ttdropshipdtl.OrderLine and (OrderDtl.sellingquantity - (select
> sum(ourqty) from dropshipdtl where ordernum = ttdropshipdtl.ordernum
> and company = ttdropshipdtl.company and orderline =
> ttdropshipdtl.orderline)) < ttdropshiphead.OurQty
>
> any thanks
>
>
>


[Non-text portions of this message have been removed]



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links