Convert progress query to Transact SQL

Yahoo Archive
1
Thanks for you advice.

I have tried your second option to obtain the last partOper.
Main and sub query run on their own but when joined together it runs forever. I think my test virtual machine 1GB RAM can't handle it so I will upgarde it and try again.

I will also try your first contribution.Really apprecaited.
Once again thank you

Michael

--- In vantage@yahoogroups.com, Mark Wonsil <mark_wonsil@...> wrote:
>
> > I am running Vantage 803.409C and due recent increase in data database is unable to handle the volume. I am now using SQL server to run the BIG reports but I am now stuck with a BAQ where I anm unable to implement FIRST or LAST in TSQL as shown below in dotted line
> > The first part of the BAQ run perfect and am now looking for how to
> > join the last bit. Please help. Thanks
> >
> > Below is the BAQ:
> > for each JobHead no-lock , each JobAsmbl where (JobHead.Company = JobAsmbl.Company and JobHead.JobNum = JobAsmbl.JobNum) no-lock , each LaborDtl outer-join where (JobAsmbl.Company = LaborDtl.Company and JobAsmbl.JobNum = LaborDtl.JobNum and JobAsmbl.AssemblySeq = LaborDtl.AssemblySeq) no-lock , each Part where (JobHead.Company =
> > Part.Company and JobHead.PartNum = Part.PartNum) no-lock ,
> > ----------------------------------------------------------
> > LAST PartOpr where (Part.Company = PartOpr.Company and Part.PartNum = PartOpr.PartNum) no-lock by JobAsmbl.JobNum by LaborDtl.OprSeq by LaborDtl.ClockInDate.
> >
>
> I'm not an T-SQL expert but the way I remember people dealing with
> this is with a sub-query:
>
> (SELECT TOP 1 PartOpe from blah blah where blah blah order by blah
> blah DESC) as LastOper
>
> The trick is to select the top record by sorting in reverse order. The
> other way is to use the MAX operator if you know the data will allow
> it but here you're looking for an operation number and not a date.
>
> Mark W.
>
2
I am running Vantage 803.409C and due recent increase in data database is unable to handle the volume. I am now using SQL server to run the BIG reports but I am now stuck with a BAQ where I anm unable to implement FIRST or LAST in TSQL as shown below in dotted line
The first part of the BAQ run perfect and am now looking for how to
join the last bit. Please help. Thanks

Below is the BAQ:
for each JobHead no-lock , each JobAsmbl where (JobHead.Company = JobAsmbl.Company and JobHead.JobNum = JobAsmbl.JobNum) no-lock , each LaborDtl outer-join where (JobAsmbl.Company = LaborDtl.Company and JobAsmbl.JobNum = LaborDtl.JobNum and JobAsmbl.AssemblySeq = LaborDtl.AssemblySeq) no-lock , each Part where (JobHead.Company =
Part.Company and JobHead.PartNum = Part.PartNum) no-lock ,
----------------------------------------------------------------------
LAST PartOpr where (Part.Company = PartOpr.Company and Part.PartNum = PartOpr.PartNum) no-lock by JobAsmbl.JobNum by LaborDtl.OprSeq by LaborDtl.ClockInDate.
3
> I am running Vantage 803.409C and due recent increase in data database is unable to handle the volume. I am now using SQL server to run the BIG reports but I am now stuck with a BAQ where I anm unable to implement FIRST or LAST in TSQL as shown below in dotted line
> The first part of the BAQ run perfect and am now looking for how to
> join the last bit. Please help. Thanks
>
> Below is the BAQ:
> for each JobHead no-lock , each JobAsmbl where (JobHead.Company = JobAsmbl.Company and JobHead.JobNum = JobAsmbl.JobNum) no-lock , each LaborDtl outer-join where (JobAsmbl.Company = LaborDtl.Company and JobAsmbl.JobNum = LaborDtl.JobNum and JobAsmbl.AssemblySeq = LaborDtl.AssemblySeq) no-lock , each Part where (JobHead.Company =
> Part.Company and JobHead.PartNum = Part.PartNum) no-lock ,
> ----------------------------------------------------------
> LAST PartOpr where (Part.Company = PartOpr.Company and Part.PartNum = PartOpr.PartNum) no-lock by JobAsmbl.JobNum by LaborDtl.OprSeq by LaborDtl.ClockInDate.
>

I'm not an T-SQL expert but the way I remember people dealing with
this is with a sub-query:

(SELECT TOP 1 PartOpe from blah blah where blah blah order by blah
blah DESC) as LastOper

The trick is to select the top record by sorting in reverse order. The
other way is to use the MAX operator if you know the data will allow
it but here you're looking for an operation number and not a date.

Mark W.
4
Good Day Michael:

I do not know what I am saying, BUT:

I was once told to click "First" or "Last" on screen below and the
system would run it differently.
Not sure of this, but i was told that it would run Progress code
instead of HTML - I could be wrong.

I was told to do this as a way fix the BAQ from not filtering on a
Calculated field that had a Dash "-" in its name.

Maybe somehow this answers your question.


len.hartka@...



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of michaelogyekye
Sent: Friday, March 04, 2011 4:19 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Convert progress query to Transact SQL




I am running Vantage 803.409C and due recent increase in data database
is unable to handle the volume. I am now using SQL server to run the BIG
reports but I am now stuck with a BAQ where I anm unable to implement
FIRST or LAST in TSQL as shown below in dotted line
The first part of the BAQ run perfect and am now looking for how to
join the last bit. Please help. Thanks

Below is the BAQ:
for each JobHead no-lock , each JobAsmbl where (JobHead.Company =
JobAsmbl.Company and JobHead.JobNum = JobAsmbl.JobNum) no-lock , each
LaborDtl outer-join where (JobAsmbl.Company = LaborDtl.Company and
JobAsmbl.JobNum = LaborDtl.JobNum and JobAsmbl.AssemblySeq =
LaborDtl.AssemblySeq) no-lock , each Part where (JobHead.Company =
Part.Company and JobHead.PartNum = Part.PartNum) no-lock ,
----------------------------------------------------------
LAST PartOpr where (Part.Company = PartOpr.Company and Part.PartNum =
PartOpr.PartNum) no-lock by JobAsmbl.JobNum by LaborDtl.OprSeq by
LaborDtl.ClockInDate.






This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.


[Non-text portions of this message have been removed]
5
Good Day Michael:

I see the Screeen shot was filtered out so..

In the BAQ design screen >> Phrase build >> add a file >> Bottom of
Screen -- Table-List Tab >> Qualifier >> Drop down


len.hartka@...

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Len Hartka
Sent: Friday, March 04, 2011 9:31 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Convert progress query to Transact SQL




Good Day Michael:

I do not know what I am saying, BUT:

I was once told to click "First" or "Last" on screen below and the
system would run it differently.
Not sure of this, but i was told that it would run Progress code
instead of HTML - I could be wrong.

I was told to do this as a way fix the BAQ from not filtering on a
Calculated field that had a Dash "-" in its name.

Maybe somehow this answers your question.


len.hartka@... <mailto:len.hartka%40sunautomation.com>



________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of michaelogyekye
Sent: Friday, March 04, 2011 4:19 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Convert progress query to Transact SQL

I am running Vantage 803.409C and due recent increase in data database
is unable to handle the volume. I am now using SQL server to run the BIG
reports but I am now stuck with a BAQ where I anm unable to implement
FIRST or LAST in TSQL as shown below in dotted line
The first part of the BAQ run perfect and am now looking for how to
join the last bit. Please help. Thanks

Below is the BAQ:
for each JobHead no-lock , each JobAsmbl where (JobHead.Company =
JobAsmbl.Company and JobHead.JobNum = JobAsmbl.JobNum) no-lock , each
LaborDtl outer-join where (JobAsmbl.Company = LaborDtl.Company and
JobAsmbl.JobNum = LaborDtl.JobNum and JobAsmbl.AssemblySeq =
LaborDtl.AssemblySeq) no-lock , each Part where (JobHead.Company =
Part.Company and JobHead.PartNum = Part.PartNum) no-lock ,
----------------------------------------------------------
LAST PartOpr where (Part.Company = PartOpr.Company and Part.PartNum =
PartOpr.PartNum) no-lock by JobAsmbl.JobNum by LaborDtl.OprSeq by
LaborDtl.ClockInDate.

This e-mail and any attachments may contain proprietary and/or
confidential information. If you are not the intended recipient, please
notify the sender immediately by reply e-mail or at 410-472-2900 and
then delete the message without using, disseminating, or copying this
message or any portion thereof. With e-mail communications you are urged
to protect against viruses.

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






This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.


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