we have multiple location inv what am i joining th...
# suiteqls
Sim Greenbaum
03/16/2023, 2:54 PMwe have multiple location inv what am i joining the item table with to get the inv info
s
Stephanie Hughes
03/16/2023, 2:57 PMWhich info? Location qty available?
s
Sim Greenbaum
03/16/2023, 2:57 PMLocation qty available yes
s
Stephanie Hughes
03/16/2023, 2:58 PMinventoryItemLocations
s
Sim Greenbaum
03/16/2023, 3:20 PM Copy code
inventoryItemLocations.itemid = item.idSim Greenbaum
03/16/2023, 3:21 PM??
s
Stephanie Hughes
03/16/2023, 3:28 PMinventoryItemLocations.item = item.id
s
Sim Greenbaum
03/16/2023, 3:31 PMnot sure why it is not working
Copy code
SELECT item.*
FROM item
INNER JOIN inventoryItemLocations ON item
inventoryItemLocations.item = item.id
WHERE item.ROWNUM = 1s
Stephanie Hughes
03/16/2023, 3:35 PMYou have an extra word in there (3rd line, 'item' shouldn't exist), and also rownum isn't a field in the item table. The way this is written, it will return all the item fields as many times as you have locations. So if you have 10 locations you are going to see the same item, 10 times. Is that what you're aiming for?
s
Sim Greenbaum
03/16/2023, 3:47 PMim trying to get the inv from one location for a list of items
s
Stephanie Hughes
03/16/2023, 3:54 PMOK you'll want something that filters to the specific location. Get the internal ID of the location.
Copy code
SELECT
Item.displayname,
inventoryItemLocations.quantityavailable
FROM
Item
INNER JOIN inventoryItemLocations ON Item.ID = inventoryItemLocations.item AND inventoryItemLocations.location = 1s
Sim Greenbaum
03/21/2023, 4:01 PMhow can I join the assembly items which is called a member item /component
s
Stephanie Hughes
03/21/2023, 4:20 PMTim Dietrich has a blog article on that. https://timdietrich.me/blog/netsuite-suiteql-assembly-items/
a
Alan Fitch
03/21/2023, 4:47 PMTims solution works if you aren't using advanced bills of materials.
s
Sim Greenbaum
03/21/2023, 7:04 PMhow can i add a colmun that is division of two
a
Alan Fitch
03/21/2023, 7:05 PMcolumnname / 2s
Sim Greenbaum
03/21/2023, 7:06 PMi mean columnnameA/columnnameB
s
Stephanie Hughes
03/21/2023, 7:07 PMJust like that. columnnameA/columnnameB
s
Sim Greenbaum
03/21/2023, 7:08 PM Copy code
qty/MemberItemQtySim Greenbaum
03/21/2023, 7:08 PM Copy code
Search error occurred: Invalid or unsupported searchSim Greenbaum
03/21/2023, 7:09 PMi see it only works on the columname no alias
4 Views