I finally just decided to give up and make a new one.
When I basically redid the original steps, it produced a query that works fine.
I don't know what I did differently or not.
Looking at the Query that works vs. the one that didn't - they're identical.
You can't see it here, but the relationships were identical too.
Charles, thank you very much for taking the time to help me out, and Robert for giving me the tip about 4GL
I appreciate it!
NEW ONE THAT WORKS:
for each PartPlant where ( PartPlant.MinimumQty > 0 AND PartPlant.PrimWhse = 'ODES') no-lock ,
each PartBin outer-join where (PartPlant.PrimWhse = PartBin.WarehouseCode and PartPlant.PartNum = PartBin.PartNum) no-lock ,
each PartWhse outer-join where (PartBin.WarehouseCode = PartWhse.WarehouseCode and PartBin.PartNum = PartWhse.PartNum) no-lock by PartPlant.PartNum.
OLD ONE THAT RESULTS IN zero RECORDS:
for each PartPlant where ( PartPlant.MinimumQt y > 0 AND PartPlant.PrimWhse = 'ODES') no-lock ,
each PartBin outer-join where (PartPlant.PrimWhse = PartBin.WarehouseCo de and PartPlant.PartNum = PartBin.PartNum) no-lock ,
each PartWhse outer-join where (PartBin.WarehouseC ode =
PartWhse.WarehouseC ode and PartBin.PartNum = PartWhse.PartNum) no-lock
by PartPlant.PartNum.
----- Original Message ----
From: Charles Carden <ccarden@...>
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Sent: Tuesday, August 5, 2008 3:04:32 PM
Subject: RE: [Vantage] BAQ SQL
If you don't do an outer join to the part bin table then you will only get records for parts in stock. I'm not sure why it is taking so long to run for you. I created a test query just for grins and it took about 6 seconds to return 13,202 records and that was on my test machine which does not have near the horsepower of my live machine.
This is the query as I wrote it.
for each PartPlant where ( PartPlant.PrimWhse = '1') no-lock , each PartWhse where (PartPlant.Company = PartWhse.Company and PartPlant.PartNum = PartWhse.PartNum and PartPlant.PrimWhse = PartWhse.WarehouseC ode) no-lock , each PartBin where (PartWhse.Company = PartBin.Company and PartWhse.PartNum = PartBin.PartNum and PartWhse.WarehouseC ode = PartBin.WarehouseCo de) no-lock .
I also changed it around and wrote it using the plant to bin and bin to warehouse so it looked like this:
For each PartPlant where ( PartPlant.PrimWhse = '1') no-lock , each PartBin where (PartPlant.Company = PartBin.Company and PartPlant.PartNum = PartBin.PartNum and PartPlant.PrimWhse = PartBin.WarehouseCo de) no-lock , each PartWhse where (PartBin.Company = PartWhse.Company and PartBin.PartNum = PartWhse.PartNum and PartBin.WarehouseCo de = PartWhse.WarehouseC ode) no-lock .
I still ran in about 6 seconds for 13,202 parts.
From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf Of Tony Hughes
Sent: Tuesday, August 05, 2008 1:38 PM
To: vantage@yahoogroups .com
Subject: Re: [Vantage] BAQ SQL
Thank you Charles very much, appreciate your time.
Your query also results in zero records, but takes 5 minutes to arrive at that. But the reason for that might be the way the company was setup, not sure, as all that happened in the 2 mos. before I arrived, unfortunately.
But, the reason I use PartBin to get to PartWhse is because partWhse has a record for every part, whereas Bin will only have a record for bins with parts in it.
In other words, run through ~2,200 Bin records as opposed to 45,000 Whse records.
In SQL Server, the query is very fast and completes in quicker than I can get the "2" out of my mouth when counting seconds.
BAQ is a god-awful tool, no matter how hard I keep my frustration in check and just try to create queries.
thank you,
Tony
----- Original Message ----
From: Charles Carden <ccarden@manitex. com<mailto:ccarden% 40manitex. com>>
To: "vantage@yahoogroups .com<mailto:vantage% 40yahoogroups. com>" <vantage@yahoogroups .com<mailto:vantage% 40yahoogroups. com>>
Sent: Tuesday, August 5, 2008 12:44:45 PM
Subject: RE: [Vantage] BAQ SQL
Don't use PartBin to get to PartWhse. I would do just the opposite and use the PartWhse to get to the PartBin table. There should be no need to do an outer join to the PartWhse. I think the following should be correct but I could have made a mistake or two. Also, you should always include the company in your joins. This will also speed up the queries.
for each
PartPlant where ( PartPlant.MinimumQt y > 0 AND PartPlant.PrimWhse = 'ODES') no-lock ,
each PartWhse where (PartPlant.PrimWhse = PartWhse.WarehouseC ode) no lock,
each PartBin outer-join where (PartWhse.Warehouse Code = PartBin.WarehouseCo de and PartWhse.PartNum = PartBin.PartNum no lock by
PartPlant.PartNum
From: vantage@yahoogroups .com [mailto:vantage@ yahoogroups .com] On Behalf Of Tony Hughes
Sent: Tuesday, August 05, 2008 11:00 AM
To: vantage@yahoogroups .com
Subject: [Vantage] BAQ SQL
I'm still working out how to translate the SQL I've been using for years in to a viable query in BAQ.
Can anyone help me work through this SQL and how I make the BAQ?
There's a check for outer join, but I am not sure what is in the Key dropdown
and every time I test, I get zero records result.
Thank you for any help you can offer
Here's my SQL statement:
------------ --------- --------- --------- --------- -------
select
partplant.partnum, partplant.minimumqt y, partbin.onhandqty, partwhse.allocqty, partbin.binnum
from
partplant
left outer join partbin on partplant.primwhse = partbin.warehouseco de and partplant.partnum = partbin.partnum
left outer join partwhse on partbin.warehouseco de = partwhse.warehousec ode and partbin.partnum = partwhse.partnum
where
partplant.minimumqt y > 0 and partplant.primwhse = 'VERN'
order by
partplant.partnum
------------ --------- --------- --------- --------- --------
Here's what I made in the BAQ:
------------ --------- --------- --------- --------- --------
for each
PartPlant where ( PartPlant.MinimumQt y > 0 AND PartPlant.PrimWhse = 'ODES') no-lock ,
each PartBin outer-join where (PartPlant.PrimWhse = PartBin.WarehouseCo de and PartPlant.PartNum = PartBin.PartNum) no-lock ,
each PartWhse outer-join where (PartBin.WarehouseC ode = PartWhse.WarehouseC ode and PartBin.PartNum = PartWhse.PartNum) no-lock by PartPlant.PartNum.
------------ --------- --------- --------- --------- -------
Anthony Hughes
ERP Manager
Logan Oil Tools, Inc.
Houston, Texas
____________ _________ _________ __
DISCLAIMER:
This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately.
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
____________ _________ _________ __
DISCLAIMER:
This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately.
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
When I basically redid the original steps, it produced a query that works fine.
I don't know what I did differently or not.
Looking at the Query that works vs. the one that didn't - they're identical.
You can't see it here, but the relationships were identical too.
Charles, thank you very much for taking the time to help me out, and Robert for giving me the tip about 4GL
I appreciate it!
NEW ONE THAT WORKS:
for each PartPlant where ( PartPlant.MinimumQty > 0 AND PartPlant.PrimWhse = 'ODES') no-lock ,
each PartBin outer-join where (PartPlant.PrimWhse = PartBin.WarehouseCode and PartPlant.PartNum = PartBin.PartNum) no-lock ,
each PartWhse outer-join where (PartBin.WarehouseCode = PartWhse.WarehouseCode and PartBin.PartNum = PartWhse.PartNum) no-lock by PartPlant.PartNum.
OLD ONE THAT RESULTS IN zero RECORDS:
for each PartPlant where ( PartPlant.MinimumQt y > 0 AND PartPlant.PrimWhse = 'ODES') no-lock ,
each PartBin outer-join where (PartPlant.PrimWhse = PartBin.WarehouseCo de and PartPlant.PartNum = PartBin.PartNum) no-lock ,
each PartWhse outer-join where (PartBin.WarehouseC ode =
PartWhse.WarehouseC ode and PartBin.PartNum = PartWhse.PartNum) no-lock
by PartPlant.PartNum.
----- Original Message ----
From: Charles Carden <ccarden@...>
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Sent: Tuesday, August 5, 2008 3:04:32 PM
Subject: RE: [Vantage] BAQ SQL
If you don't do an outer join to the part bin table then you will only get records for parts in stock. I'm not sure why it is taking so long to run for you. I created a test query just for grins and it took about 6 seconds to return 13,202 records and that was on my test machine which does not have near the horsepower of my live machine.
This is the query as I wrote it.
for each PartPlant where ( PartPlant.PrimWhse = '1') no-lock , each PartWhse where (PartPlant.Company = PartWhse.Company and PartPlant.PartNum = PartWhse.PartNum and PartPlant.PrimWhse = PartWhse.WarehouseC ode) no-lock , each PartBin where (PartWhse.Company = PartBin.Company and PartWhse.PartNum = PartBin.PartNum and PartWhse.WarehouseC ode = PartBin.WarehouseCo de) no-lock .
I also changed it around and wrote it using the plant to bin and bin to warehouse so it looked like this:
For each PartPlant where ( PartPlant.PrimWhse = '1') no-lock , each PartBin where (PartPlant.Company = PartBin.Company and PartPlant.PartNum = PartBin.PartNum and PartPlant.PrimWhse = PartBin.WarehouseCo de) no-lock , each PartWhse where (PartBin.Company = PartWhse.Company and PartBin.PartNum = PartWhse.PartNum and PartBin.WarehouseCo de = PartWhse.WarehouseC ode) no-lock .
I still ran in about 6 seconds for 13,202 parts.
From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf Of Tony Hughes
Sent: Tuesday, August 05, 2008 1:38 PM
To: vantage@yahoogroups .com
Subject: Re: [Vantage] BAQ SQL
Thank you Charles very much, appreciate your time.
Your query also results in zero records, but takes 5 minutes to arrive at that. But the reason for that might be the way the company was setup, not sure, as all that happened in the 2 mos. before I arrived, unfortunately.
But, the reason I use PartBin to get to PartWhse is because partWhse has a record for every part, whereas Bin will only have a record for bins with parts in it.
In other words, run through ~2,200 Bin records as opposed to 45,000 Whse records.
In SQL Server, the query is very fast and completes in quicker than I can get the "2" out of my mouth when counting seconds.
BAQ is a god-awful tool, no matter how hard I keep my frustration in check and just try to create queries.
thank you,
Tony
----- Original Message ----
From: Charles Carden <ccarden@manitex. com<mailto:ccarden% 40manitex. com>>
To: "vantage@yahoogroups .com<mailto:vantage% 40yahoogroups. com>" <vantage@yahoogroups .com<mailto:vantage% 40yahoogroups. com>>
Sent: Tuesday, August 5, 2008 12:44:45 PM
Subject: RE: [Vantage] BAQ SQL
Don't use PartBin to get to PartWhse. I would do just the opposite and use the PartWhse to get to the PartBin table. There should be no need to do an outer join to the PartWhse. I think the following should be correct but I could have made a mistake or two. Also, you should always include the company in your joins. This will also speed up the queries.
for each
PartPlant where ( PartPlant.MinimumQt y > 0 AND PartPlant.PrimWhse = 'ODES') no-lock ,
each PartWhse where (PartPlant.PrimWhse = PartWhse.WarehouseC ode) no lock,
each PartBin outer-join where (PartWhse.Warehouse Code = PartBin.WarehouseCo de and PartWhse.PartNum = PartBin.PartNum no lock by
PartPlant.PartNum
From: vantage@yahoogroups .com [mailto:vantage@ yahoogroups .com] On Behalf Of Tony Hughes
Sent: Tuesday, August 05, 2008 11:00 AM
To: vantage@yahoogroups .com
Subject: [Vantage] BAQ SQL
I'm still working out how to translate the SQL I've been using for years in to a viable query in BAQ.
Can anyone help me work through this SQL and how I make the BAQ?
There's a check for outer join, but I am not sure what is in the Key dropdown
and every time I test, I get zero records result.
Thank you for any help you can offer
Here's my SQL statement:
------------ --------- --------- --------- --------- -------
select
partplant.partnum, partplant.minimumqt y, partbin.onhandqty, partwhse.allocqty, partbin.binnum
from
partplant
left outer join partbin on partplant.primwhse = partbin.warehouseco de and partplant.partnum = partbin.partnum
left outer join partwhse on partbin.warehouseco de = partwhse.warehousec ode and partbin.partnum = partwhse.partnum
where
partplant.minimumqt y > 0 and partplant.primwhse = 'VERN'
order by
partplant.partnum
------------ --------- --------- --------- --------- --------
Here's what I made in the BAQ:
------------ --------- --------- --------- --------- --------
for each
PartPlant where ( PartPlant.MinimumQt y > 0 AND PartPlant.PrimWhse = 'ODES') no-lock ,
each PartBin outer-join where (PartPlant.PrimWhse = PartBin.WarehouseCo de and PartPlant.PartNum = PartBin.PartNum) no-lock ,
each PartWhse outer-join where (PartBin.WarehouseC ode = PartWhse.WarehouseC ode and PartBin.PartNum = PartWhse.PartNum) no-lock by PartPlant.PartNum.
------------ --------- --------- --------- --------- -------
Anthony Hughes
ERP Manager
Logan Oil Tools, Inc.
Houston, Texas
____________ _________ _________ __
DISCLAIMER:
This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately.
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
____________ _________ _________ __
DISCLAIMER:
This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately.
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]