Try and outer join
For Each OrderDtl Where OrderDtl.Company = cur-comp no-lock,
Each Part outer-join no-lock where part.company = orderdtl.company
and Part.PartNum = OrderDtl.PartNum,
This will give you both records in the part file and with out.
Then try adding a filter to skip records in the part file.
and Part.PartClass = ?
That's the best I can do.
Patrick Winter
________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Lee Hansen
Sent: Wednesday, November 08, 2006 11:52 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: 4GL Query
Thanks, Chris. Unfortunately, we've found that you "...cannot use
the 'can-find' function in a 'where' clause. Doing so will generate
a compiler error." This is according to Progress' website help.
The specific error message we get is "CAN-FIND is invalid within an
OPEN QUERY. (3541)".
Any additional suggestions?
Lee Hansen
lhansen@... <mailto:lhansen%40netcominc.com>
--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Chris Robisch" <bluewine@...> wrote:
privileged information. If you are not the intended recipient,
please notify the sender immediately by return e-mail, delete this
e-mail and destroy any copies. Any dissemination or use of this
information by a person other than the intended recipient is
unauthorized and may be illegal.
[Non-text portions of this message have been removed]
For Each OrderDtl Where OrderDtl.Company = cur-comp no-lock,
Each Part outer-join no-lock where part.company = orderdtl.company
and Part.PartNum = OrderDtl.PartNum,
This will give you both records in the part file and with out.
Then try adding a filter to skip records in the part file.
and Part.PartClass = ?
That's the best I can do.
Patrick Winter
________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Lee Hansen
Sent: Wednesday, November 08, 2006 11:52 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: 4GL Query
Thanks, Chris. Unfortunately, we've found that you "...cannot use
the 'can-find' function in a 'where' clause. Doing so will generate
a compiler error." This is according to Progress' website help.
The specific error message we get is "CAN-FIND is invalid within an
OPEN QUERY. (3541)".
Any additional suggestions?
Lee Hansen
lhansen@... <mailto:lhansen%40netcominc.com>
--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Chris Robisch" <bluewine@...> wrote:
>"Lee Hansen" <lhansen@> wrote:
> for each orderdtl where orderdtl.company eq "your-company"
> and not can-find(part where part.company eq "your-company"
> and part.partnum eq orderdtl.partnum) no-lock.
> end.
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
> >which
> > We're on 6.1.530 and use an SQL database.
> >
> > I'd like to create a Business Activity Query that would show
> > orders were entered with a part number that doesn't exist in thedatabase
> Part
> > Table.
> >
> > Vantage doesn't flag an entered part as not being in the
> > when creating an order. The only way you know this conditiona
> exists
> > is that the remaining fields don't automatically populate. Our
> > problem is that we may make the same part at multiple locations,
> > differentiated by a letter suffix. If an order is created with
> > part number that is in our database and then later edited byjust
> > changing the suffix to a part number that is NOT in ourdatabase,
> thein
> > remaining fields stay populated with the original data. The user
> > isn't given any warning that the new part number doesn't exist
> thehas
> > database.
> >
> > To find these discrepancies our orders, it's a very simple SQL
> > query, which I've listed below. The problem is that none of us
> > had to learn 4GL, which is what the BAQ needs. Is there anyonewho
> > knows both SQL and 4GL that could assist me with an equivalentBAQ
> > query?This e-mail and any attachments may contain confidential and
> >
> > SQL Query:
> > select ordernum, partnum, linedesc, orderqty
> > from orderdtl where openline = 1 and voidline = 0
> > and partnum NOT IN (select partnum from part)
> > order by ordernum, partnum
> >
> > Thanks for your assistance.
> >
>
privileged information. If you are not the intended recipient,
please notify the sender immediately by return e-mail, delete this
e-mail and destroy any copies. Any dissemination or use of this
information by a person other than the intended recipient is
unauthorized and may be illegal.
[Non-text portions of this message have been removed]