4GL Query Blank Title 52978

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:
>
> 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> ,
"Lee Hansen" <lhansen@> wrote:
> >
> > We're on 6.1.530 and use an SQL database.
> >
> > I'd like to create a Business Activity Query that would show
which
> > orders were entered with a part number that doesn't exist in the
> Part
> > Table.
> >
> > Vantage doesn't flag an entered part as not being in the
database
> > when creating an order. The only way you know this condition
> 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
a
> > part number that is in our database and then later edited by
just
> > changing the suffix to a part number that is NOT in our
database,
> the
> > remaining fields stay populated with the original data. The user
> > isn't given any warning that the new part number doesn't exist
in
> the
> > 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
has
> > had to learn 4GL, which is what the BAQ needs. Is there anyone
who
> > knows both SQL and 4GL that could assist me with an equivalent
BAQ
> > query?
> >
> > 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.
> >
>






This e-mail and any attachments may contain confidential and
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]
We're on 6.1.530 and use an SQL database.

I'd like to create a Business Activity Query that would show which
orders were entered with a part number that doesn't exist in the Part
Table.

Vantage doesn't flag an entered part as not being in the database
when creating an order. The only way you know this condition 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 a
part number that is in our database and then later edited by just
changing the suffix to a part number that is NOT in our database, the
remaining fields stay populated with the original data. The user
isn't given any warning that the new part number doesn't exist in the
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 has
had to learn 4GL, which is what the BAQ needs. Is there anyone who
knows both SQL and 4GL that could assist me with an equivalent BAQ
query?

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.
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, "Lee Hansen" <lhansen@...> wrote:
>
> We're on 6.1.530 and use an SQL database.
>
> I'd like to create a Business Activity Query that would show which
> orders were entered with a part number that doesn't exist in the
Part
> Table.
>
> Vantage doesn't flag an entered part as not being in the database
> when creating an order. The only way you know this condition
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 a
> part number that is in our database and then later edited by just
> changing the suffix to a part number that is NOT in our database,
the
> remaining fields stay populated with the original data. The user
> isn't given any warning that the new part number doesn't exist in
the
> 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 has
> had to learn 4GL, which is what the BAQ needs. Is there anyone who
> knows both SQL and 4GL that could assist me with an equivalent BAQ
> query?
>
> 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.
>
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@...

--- In vantage@yahoogroups.com, "Chris Robisch" <bluewine@...> 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, "Lee Hansen" <lhansen@> wrote:
> >
> > We're on 6.1.530 and use an SQL database.
> >
> > I'd like to create a Business Activity Query that would show
which
> > orders were entered with a part number that doesn't exist in the
> Part
> > Table.
> >
> > Vantage doesn't flag an entered part as not being in the
database
> > when creating an order. The only way you know this condition
> 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
a
> > part number that is in our database and then later edited by
just
> > changing the suffix to a part number that is NOT in our
database,
> the
> > remaining fields stay populated with the original data. The user
> > isn't given any warning that the new part number doesn't exist
in
> the
> > 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
has
> > had to learn 4GL, which is what the BAQ needs. Is there anyone
who
> > knows both SQL and 4GL that could assist me with an equivalent
BAQ
> > query?
> >
> > 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.
> >
>