ASP SQL Join for Progress

Yahoo Archive
1
Randy,

Sorry, I assumed you had the query down and just needed help with the
Progress driver syntax ("PUB_" vs "Pub.").


have fun,
john





----- Original message -----
From: "Randy Weber" <rweber@...>
To: vantage@yahoogroups.com
Date: Thu, 27 Aug 2009 13:23:38 -0500
Subject: RE: [Vantage] ASP SQL Join for Progress

John,



Yes, but that would return every order line and order release for all
sales orders. I need to figure out the Join between OrderHed, OrderDtl,
and OrderRel.



Randy










.
2
I am struggling with creating an ASP page that joins OrderHed, OrderDtl, and OrderRel. I am familiar with MS-SQL but there seems to be a slight difference with Porgress. Can someone reply with a query that joins the three tables such as:

SELECT PUB.OrderHed.OrderNum, PUB.OrderDtl.OrderLine,PUB.OrderRel.OrderRelNum From... [you fill in the rest]
...Where PUB.OrderHed.OpenOrder = True (or 1) or -1 [which is it anyway for True]?

If I can get this, I can do the rest :)

Thanks in advance

Randy Weber
3
Randy,

SELECT PUB_OrderHed.OrderNum, PUB_OrderDtl.OrderLine,
PUB_OrderRel.OrderRelNum
FROM ...
WHERE PUB_OrderHed.OpenOrder=Yes

Works for me using the Merant v3.60 Progress SQL driver (Vantage 6.1, of
course).


have fun,
john





----- Original message -----
From: "randyweb" <rweber@...>
To: vantage@yahoogroups.com
Date: Thu, 27 Aug 2009 17:46:37 -0000
Subject: [Vantage] ASP SQL Join for Progress

I am struggling with creating an ASP page that joins OrderHed, OrderDtl,
and OrderRel. I am familiar with MS-SQL but there seems to be a slight
difference with Porgress. Can someone reply with a query that joins the
three tables such as:

SELECT PUB.OrderHed.OrderNum,
PUB.OrderDtl.OrderLine,PUB.OrderRel.OrderRelNum From... [you fill in the
rest]
...Where PUB.OrderHed.OpenOrder = True (or 1) or -1 [which is it anyway
for True]?

If I can get this, I can do the rest :)

Thanks in advance

Randy Weber









.
4
John,



Yes, but that would return every order line and order release for all
sales orders. I need to figure out the Join between OrderHed, OrderDtl,
and OrderRel.



Randy







From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of John Sage
Sent: Thursday, August 27, 2009 1:15 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] ASP SQL Join for Progress





Randy,

SELECT PUB_OrderHed.OrderNum, PUB_OrderDtl.OrderLine,
PUB_OrderRel.OrderRelNum
FROM ...
WHERE PUB_OrderHed.OpenOrder=Yes

Works for me using the Merant v3.60 Progress SQL driver (Vantage 6.1, of
course).

have fun,
john

----- Original message -----
From: "randyweb" <rweber@...
<mailto:rweber%40tlcelectronics.com> >
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Date: Thu, 27 Aug 2009 17:46:37 -0000
Subject: [Vantage] ASP SQL Join for Progress

I am struggling with creating an ASP page that joins OrderHed, OrderDtl,
and OrderRel. I am familiar with MS-SQL but there seems to be a slight
difference with Porgress. Can someone reply with a query that joins the
three tables such as:

SELECT PUB.OrderHed.OrderNum,
PUB.OrderDtl.OrderLine,PUB.OrderRel.OrderRelNum From... [you fill in the
rest]
...Where PUB.OrderHed.OpenOrder = True (or 1) or -1 [which is it anyway
for True]?

If I can get this, I can do the rest :)

Thanks in advance

Randy Weber

.





[Non-text portions of this message have been removed]
5
This is an example of using Progress code:

Find first company no-lock no-error.

For each orderhed no-lock where orderhed.company = company.company
And orderhed.openorder = true,
Each orderdtl no-lock where orderdtl.company =
orderhed.company
And orderdtl.ordernum = orderhed.ordernum
And orderdtl.openline = true,
Each orderrel no-lock where orderrel.company = orderdtl.company
And orderrel.ordernum =
orderdtl.ordernum
And orderrel.orderline =
orderdtl.orderline
And orderrel.openrelease = true.

Display orderhed.orderdate orderhed.ordernum orderdtl.orderline
orderrel.orderrel.

End.

Help?

Carl Peterson

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Randy Weber
Sent: Thursday, August 27, 2009 2:24 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] ASP SQL Join for Progress


John,

Yes, but that would return every order line and order release for all
sales orders. I need to figure out the Join between OrderHed, OrderDtl,
and OrderRel.

Randy

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of John Sage
Sent: Thursday, August 27, 2009 1:15 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] ASP SQL Join for Progress

Randy,

SELECT PUB_OrderHed.OrderNum, PUB_OrderDtl.OrderLine,
PUB_OrderRel.OrderRelNum
FROM ...
WHERE PUB_OrderHed.OpenOrder=Yes

Works for me using the Merant v3.60 Progress SQL driver (Vantage 6.1, of
course).

have fun,
john

----- Original message -----
From: "randyweb" <rweber@...
<mailto:rweber%40tlcelectronics.com>
<mailto:rweber%40tlcelectronics.com> >
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Date: Thu, 27 Aug 2009 17:46:37 -0000
Subject: [Vantage] ASP SQL Join for Progress

I am struggling with creating an ASP page that joins OrderHed, OrderDtl,
and OrderRel. I am familiar with MS-SQL but there seems to be a slight
difference with Porgress. Can someone reply with a query that joins the
three tables such as:

SELECT PUB.OrderHed.OrderNum,
PUB.OrderDtl.OrderLine,PUB.OrderRel.OrderRelNum From... [you fill in the
rest]
...Where PUB.OrderHed.OpenOrder = True (or 1) or -1 [which is it anyway
for True]?

If I can get this, I can do the rest :)

Thanks in advance

Randy Weber

.

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



[Non-text portions of this message have been removed]
6
Thanks, but I really need the ASP syntax from someone who uses ASP with
an ODBC connection. I should have clarified that.



Plus, I just figured it out - I think:



SELECT PUB.OrderHed.OrderNum, PUB.OrderDtl.OrderLine,
PUB.OrderRel.OrderRelNum FROM PUB.OrderHed

INNER JOIN PUB.OrderDtl ON PUB.OrderHed.OrderNum = PUB.OrderDtl.OrderNum


INNER JOIN PUB.OrderRel ON PUB.OrderRel.OrderNum = PUB.OrderDtl.OrderNum
AND PUB.OrderRel.OrderLine = PUB.OrderDtl.OrderLine

WHERE PUB.OrderHed.OrderNum = 82530



Randy Weber





From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Carl Peterson
Sent: Thursday, August 27, 2009 1:37 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] ASP SQL Join for Progress





This is an example of using Progress code:

Find first company no-lock no-error.

For each orderhed no-lock where orderhed.company = company.company
And orderhed.openorder = true,
Each orderdtl no-lock where orderdtl.company =
orderhed.company
And orderdtl.ordernum = orderhed.ordernum
And orderdtl.openline = true,
Each orderrel no-lock where orderrel.company = orderdtl.company
And orderrel.ordernum =
orderdtl.ordernum
And orderrel.orderline =
orderdtl.orderline
And orderrel.openrelease = true.

Display orderhed.orderdate orderhed.ordernum orderdtl.orderline
orderrel.orderrel.

End.

Help?

Carl Peterson

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of
Randy Weber
Sent: Thursday, August 27, 2009 2:24 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] ASP SQL Join for Progress


John,

Yes, but that would return every order line and order release for all
sales orders. I need to figure out the Join between OrderHed, OrderDtl,
and OrderRel.

Randy

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On
Behalf
Of John Sage
Sent: Thursday, August 27, 2009 1:15 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] ASP SQL Join for Progress

Randy,

SELECT PUB_OrderHed.OrderNum, PUB_OrderDtl.OrderLine,
PUB_OrderRel.OrderRelNum
FROM ...
WHERE PUB_OrderHed.OpenOrder=Yes

Works for me using the Merant v3.60 Progress SQL driver (Vantage 6.1, of
course).

have fun,
john

----- Original message -----
From: "randyweb" <rweber@...
<mailto:rweber%40tlcelectronics.com>
<mailto:rweber%40tlcelectronics.com>
<mailto:rweber%40tlcelectronics.com> >
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Date: Thu, 27 Aug 2009 17:46:37 -0000
Subject: [Vantage] ASP SQL Join for Progress

I am struggling with creating an ASP page that joins OrderHed, OrderDtl,
and OrderRel. I am familiar with MS-SQL but there seems to be a slight
difference with Porgress. Can someone reply with a query that joins the
three tables such as:

SELECT PUB.OrderHed.OrderNum,
PUB.OrderDtl.OrderLine,PUB.OrderRel.OrderRelNum From... [you fill in the
rest]
...Where PUB.OrderHed.OpenOrder = True (or 1) or -1 [which is it anyway
for True]?

If I can get this, I can do the rest :)

Thanks in advance

Randy Weber

.

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

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





[Non-text portions of this message have been removed]
7
And in SQL92 language - as generated by MSQuery

SELECT OrderHed.OpenOrder, OrderDtl.PartNum, OrderRel.OrderNum, OrderRel.OrderLine, OrderRel.OrderRelNum
FROM MFGSYS.PUB.OrderDtl, MFGSYS.PUB.OrderHed, MFGSYS.PUB.OrderRel
WHERE OrderDtl.OrderNum = OrderRel.OrderNum AND OrderDtl.OrderNum = OrderHed.OrderNum AND OrderDtl.Company = OrderHed.Company AND OrderDtl.Company = OrderRel.Company AND OrderDtl.OrderLine = OrderRel.OrderLine AND OrderHed.OrderNum = OrderRel.OrderNum AND OrderHed.Company = OrderRel.Company AND ((OrderHed.OpenOrder=1) AND (OrderRel.OrderNum=16937))

--- In vantage@yahoogroups.com, "Carl Peterson" <cpeterson@...> wrote:
>
> This is an example of using Progress code:
>
> Find first company no-lock no-error.
>
> For each orderhed no-lock where orderhed.company = company.company
> And orderhed.openorder = true,
> Each orderdtl no-lock where orderdtl.company =
> orderhed.company
> And orderdtl.ordernum = orderhed.ordernum
> And orderdtl.openline = true,
> Each orderrel no-lock where orderrel.company = orderdtl.company
> And orderrel.ordernum =
> orderdtl.ordernum
> And orderrel.orderline =
> orderdtl.orderline
> And orderrel.openrelease = true.
>
> Display orderhed.orderdate orderhed.ordernum orderdtl.orderline
> orderrel.orderrel.
>
> End.
>
> Help?
>
> Carl Peterson
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
> Randy Weber
> Sent: Thursday, August 27, 2009 2:24 PM
> To: vantage@yahoogroups.com
> Subject: RE: [Vantage] ASP SQL Join for Progress
>
>
> John,
>
> Yes, but that would return every order line and order release for all
> sales orders. I need to figure out the Join between OrderHed, OrderDtl,
> and OrderRel.
>
> Randy
>
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
> Behalf
> Of John Sage
> Sent: Thursday, August 27, 2009 1:15 PM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: Re: [Vantage] ASP SQL Join for Progress
>
> Randy,
>
> SELECT PUB_OrderHed.OrderNum, PUB_OrderDtl.OrderLine,
> PUB_OrderRel.OrderRelNum
> FROM ...
> WHERE PUB_OrderHed.OpenOrder=Yes
>
> Works for me using the Merant v3.60 Progress SQL driver (Vantage 6.1, of
> course).
>
> have fun,
> john
>
> ----- Original message -----
> From: "randyweb" <rweber@...
> <mailto:rweber%40tlcelectronics.com>
> <mailto:rweber%40tlcelectronics.com> >
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> <mailto:vantage%40yahoogroups.com>
> Date: Thu, 27 Aug 2009 17:46:37 -0000
> Subject: [Vantage] ASP SQL Join for Progress
>
> I am struggling with creating an ASP page that joins OrderHed, OrderDtl,
> and OrderRel. I am familiar with MS-SQL but there seems to be a slight
> difference with Porgress. Can someone reply with a query that joins the
> three tables such as:
>
> SELECT PUB.OrderHed.OrderNum,
> PUB.OrderDtl.OrderLine,PUB.OrderRel.OrderRelNum From... [you fill in the
> rest]
> ...Where PUB.OrderHed.OpenOrder = True (or 1) or -1 [which is it anyway
> for True]?
>
> If I can get this, I can do the rest :)
>
> Thanks in advance
>
> Randy Weber
>
> .
>
> [Non-text portions of this message have been removed]
>
>
>
> [Non-text portions of this message have been removed]
>