Help Joining 2 tables

Yahoo Archive
1
Just taking another tack,
Can you build the query as a BAQ and take a look at the code that gets created. This might help you work out the correct syntax.

Cheers
Simon
--- In vantage@yahoogroups.com, Ephraim Feldman <effgroups@...> wrote:
>
> As a side point I think you have to join them on the partnum too. If not
> you will end up with a enormous number of records as each record will be
> multiplied by the number of records in the other table
> On 2013-06-05 2:31 PM, "Ephraim Feldman" <effgroups@...> wrote:
>
> > The no lock has to be placed after the whole statement and the format is
> > no-lock
> > On 2013-06-05 1:37 PM, "clederer@..." <clederer@...>
> > wrote:
> >
> >> **
> >>
> >>
> >> I'm trying to Join 2 tables ( Partplant & partbininfo) on a common field
> >> (company).
> >>
> >> In the query I'm also trying to have two other WHERE statements
> >> 1. partplant.plant = "Colorado"
> >> 2. partbininfo.BinNum = ""
> >>
> >> I've used SQL before, but am having a hard time with 4GL. This is the
> >> query I came up with, but it's reporting an error message:
> >>
> >> FOR EACH partbininfo no lock, each partplant no lock Where (partbininfo.
> >> company = partplant.Company)
> >> AND partplant.plant = "Colorado"
> >> and partbininfo.BinNum = ""
> >>
> >> ERROR MESSAGE: "Unable to understand after-- "no lock'. (247) PREPARE
> >> syntax is {FOR/PRESELECT} EACH OF.. WHERE.. etc". (7324)
> >>
> >>
> >>
> >
>
>
> [Non-text portions of this message have been removed]
>
2
I'm trying to Join 2 tables ( Partplant & partbininfo) on a common field (company).

In the query I'm also trying to have two other WHERE statements
1. partplant.plant = "Colorado"
2. partbininfo.BinNum = ""

I've used SQL before, but am having a hard time with 4GL. This is the query I came up with, but it's reporting an error message:

FOR EACH partbininfo no lock, each partplant no lock Where (partbininfo. company = partplant.Company)
AND partplant.plant = "Colorado"
and partbininfo.BinNum = ""

ERROR MESSAGE: "Unable to understand after-- "no lock'. (247) PREPARE syntax is {FOR/PRESELECT} EACH OF.. WHERE.. etc". (7324)
3
Where are you trying to execute this query? From within what interface?
I'm assuming this isn't a BAQ question and that you may be using an external
query engine?



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
clederer@...
Sent: Wednesday, June 05, 2013 1:37 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Help Joining 2 tables





I'm trying to Join 2 tables ( Partplant & partbininfo) on a common field
(company).

In the query I'm also trying to have two other WHERE statements
1. partplant.plant = "Colorado"
2. partbininfo.BinNum = ""

I've used SQL before, but am having a hard time with 4GL. This is the query
I came up with, but it's reporting an error message:

FOR EACH partbininfo no lock, each partplant no lock Where (partbininfo.
company = partplant.Company)
AND partplant.plant = "Colorado"
and partbininfo.BinNum = ""

ERROR MESSAGE: "Unable to understand after-- "no lock'. (247) PREPARE syntax
is {FOR/PRESELECT} EACH OF.. WHERE.. etc". (7324)





[Non-text portions of this message have been removed]
4
The no lock has to be placed after the whole statement and the format is
no-lock
On 2013-06-05 1:37 PM, "clederer@..." <clederer@...>
wrote:

> **
>
>
> I'm trying to Join 2 tables ( Partplant & partbininfo) on a common field
> (company).
>
> In the query I'm also trying to have two other WHERE statements
> 1. partplant.plant = "Colorado"
> 2. partbininfo.BinNum = ""
>
> I've used SQL before, but am having a hard time with 4GL. This is the
> query I came up with, but it's reporting an error message:
>
> FOR EACH partbininfo no lock, each partplant no lock Where (partbininfo.
> company = partplant.Company)
> AND partplant.plant = "Colorado"
> and partbininfo.BinNum = ""
>
> ERROR MESSAGE: "Unable to understand after-- "no lock'. (247) PREPARE
> syntax is {FOR/PRESELECT} EACH OF.. WHERE.. etc". (7324)
>
>
>


[Non-text portions of this message have been removed]
5
As a side point I think you have to join them on the partnum too. If not
you will end up with a enormous number of records as each record will be
multiplied by the number of records in the other table
On 2013-06-05 2:31 PM, "Ephraim Feldman" <effgroups@...> wrote:

> The no lock has to be placed after the whole statement and the format is
> no-lock
> On 2013-06-05 1:37 PM, "clederer@..." <clederer@...>
> wrote:
>
>> **
>>
>>
>> I'm trying to Join 2 tables ( Partplant & partbininfo) on a common field
>> (company).
>>
>> In the query I'm also trying to have two other WHERE statements
>> 1. partplant.plant = "Colorado"
>> 2. partbininfo.BinNum = ""
>>
>> I've used SQL before, but am having a hard time with 4GL. This is the
>> query I came up with, but it's reporting an error message:
>>
>> FOR EACH partbininfo no lock, each partplant no lock Where (partbininfo.
>> company = partplant.Company)
>> AND partplant.plant = "Colorado"
>> and partbininfo.BinNum = ""
>>
>> ERROR MESSAGE: "Unable to understand after-- "no lock'. (247) PREPARE
>> syntax is {FOR/PRESELECT} EACH OF.. WHERE.. etc". (7324)
>>
>>
>>
>


[Non-text portions of this message have been removed]
6
"no lock" should be "no-lock", the no-lock can go where it is located right after the table name.

Jim Kinneman
Encompass Solutions, Inc

--- In vantage@yahoogroups.com, Ephraim Feldman <effgroups@...> wrote:
>
> The no lock has to be placed after the whole statement and the format is
> no-lock
> On 2013-06-05 1:37 PM, "clederer@..." <clederer@...>
> wrote:
>
> > **
> >
> >
> > I'm trying to Join 2 tables ( Partplant & partbininfo) on a common field
> > (company).
> >
> > In the query I'm also trying to have two other WHERE statements
> > 1. partplant.plant = "Colorado"
> > 2. partbininfo.BinNum = ""
> >
> > I've used SQL before, but am having a hard time with 4GL. This is the
> > query I came up with, but it's reporting an error message:
> >
> > FOR EACH partbininfo no lock, each partplant no lock Where (partbininfo.
> > company = partplant.Company)
> > AND partplant.plant = "Colorado"
> > and partbininfo.BinNum = ""
> >
> > ERROR MESSAGE: "Unable to understand after-- "no lock'. (247) PREPARE
> > syntax is {FOR/PRESELECT} EACH OF.. WHERE.. etc". (7324)
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>