July 28, 2009 at 3:43 pm
I have a query below in which I am trying to limit the records being returned from the p21_view_po_line_schedule table to only the first returned record and am having a bit of trouble getting the syntax correct. Anyone able to offer suggestions on this?
Thanks
SELECT inv_mast.item_id, inv_mast.item_desc,
inv_loc.stockable, inv_loc.qty_on_hand,
inv_loc.qty_backordered, inv_loc.location_id, p21_view_po_line_schedule.release_date, p21_view_po_line_schedule.release_qty,
p21_view_po_line_schedule.qty_received
FROM inv_loc INNER JOIN
inv_mast ON inv_mast.inv_mast_uid = inv_loc.inv_mast_uid INNER JOIN
p21_view_po_line ON inv_loc.inv_mast_uid = p21_view_po_line.inv_mast_uid INNER JOIN
p21_view_po_line_schedule ON p21_view_po_line.po_line_uid = p21_view_po_line_schedule.po_line_uid and p21_view_po_line_schedule.row_status_flag = 704 INNER JOIN
p21_view_po_hdr ON p21_view_po_line.po_no = p21_view_po_hdr.po_no AND 920 = p21_view_po_hdr.location_id
WHERE (inv_loc.location_id IN (920)) AND (inv_loc.stockable = 'Y') AND (inv_loc.purchase_discount_group = '1410')
July 28, 2009 at 3:59 pm
Maybe you need to use TOP clause?
Something like this...
select TOP 1 your_collumns...
from ....
where ....
July 28, 2009 at 5:12 pm
How do you define "First" from the 21_view_po_line_schedule table?
This might help guide you..?
SELECT
inv_mast.item_id,
inv_mast.item_desc,
inv_loc.stockable,
inv_loc.qty_on_hand,
inv_loc.qty_backordered,
inv_loc.location_id,
p21_view_po_line_schedule.release_date,
p21_view_po_line_schedule.release_qty,
p21_view_po_line_schedule.qty_received
FROM
inv_loc
INNER JOIN inv_mast
ON inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
INNER JOIN p21_view_po_line
ON inv_loc.inv_mast_uid = p21_view_po_line.inv_mast_uid
INNER JOIN
(
SELECT TOP 1
release_date,
release_qty,
qty_received,
po_line_uid
FROM
p21_view_po_line_schedule
WHERE
p21_view_po_line_schedule.row_status_flag = 704
ORDER BY
-- ????
) AS p21_view_po_line_schedule
ON p21_view_po_line.po_line_uid = p21_view_po_line_schedule.po_line_uid
INNER JOIN p21_view_po_hdr
ON p21_view_po_line.po_no = p21_view_po_hdr.po_no
AND 920 = p21_view_po_hdr.location_id
WHERE
inv_loc.location_id IN(920)
AND inv_loc.stockable = 'Y'
AND inv_loc.purchase_discount_group = '1410'
July 29, 2009 at 8:10 am
Lamprey, Thanks for the help on the subquery, that looks like it should be what I need, however it doesn't return any records. When I remove the top and order by statements it brings back all of the result set as before so the subquery syntax appears to be correct, just not sure why the top 1 is not bringing back any records.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply