August 29, 2006 at 3:47 pm
I have a table that has two columns: ItemNmbr, ListPrce. The data in the table is like this:
A2320C, 100.00
A2320C Core, 10.00
C15310C, 100.00
C15310C Core, 10.00
K2640150.00
K2641175.00
I need to do a select query to get the results like this:
ItemNmbr, ListPrce, CorePrce
A2320C, 100.00, 10.00
C15310C, 100.00, 10.00
K2640, 150.00, Null
K2641, 175.00, Null
I've tried the following query, but it doesn't return items that don't have "Core" after it:
Select i.ItemNmbr, i.ListPrce, i2.ListPrce
FROM IV00105 AS i
INNER JOIN IV00105 AS i2 on i.itemNmbr = LEFT(i2.ItemNmbr, LEN(i2.ItemNmbr) - 5)
WHERE i2.ItemNmbr like '%Core' and i.ItemNmbr not like '%Core'
I've tried the following query, but it returns Null for every CorePrce:
SELECT i.ItemNmbr, i.ListPrce,
(SELECT i1.ListPrce
FROM IV00105 AS i1
INNER JOIN IV00105 as i2 on i1.ItemNmbr = i2.ItemNmbr
WHERE i.ItemNmbr = i1.ItemNmbr AND i2.ItemNmbr like '%Core') AS CorePrice
FROM IV00105 AS i
WHERE i.ItemNmbr NOT LIKE '%Core'
I've tried this query, but I get an error "Invalid length parameter passed to the SUBSTRING function"
SELECT i.ItemNmbr, i.ListPrce,
(SELECT i1.ListPrce
FROM IV00105 AS i1
WHERE i.ItemNmbr = LEFT(i1.ItemNmbr, LEN(i1.ItemNmbr)-5)) AS CorePrice
FROM IV00105 AS i
And I've tried many more queries, but can't seem to get it right. Can anyone help me with this?
Thanks,
Wendy Schuman
Wendy Schuman
August 29, 2006 at 4:14 pm
try this
SELECT i.ItemNmbr, i.ListPrce,i1.ListPrce
FROM IV00105 AS i left join (SELECT ItemNmbr, ListPrce FROM IV00105 AS i1 WHERE i1.ItemNmbr like '%Core') AS i1
on i.ItemNmbr=left(i1.ItemNmbr,len(i1.ItemNmbr)-5)
WHERE i.ItemNmbr NOT LIKE '%Core'
August 29, 2006 at 4:24 pm
That did it. Thanks so much.
Wendy Schuman
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply