[RESOLVED] Impossible SQL statement?

[Ms.Longstocking]

I have to come up with a convoluted SQL statement.

Here are the tables:

cfpurchases(purchaseid, storeid, sectionid, itemid, cost)
stores(storeid, sale)
sections(sectionid, olditemid, newitemid)
purchaseindex(account, purchaseid)

This is the output I need:
account, purchaseid, cost(if old), cost(if new)

I'm having trouble with the cost portions of the output.
I have to come up with a way to check the itemid vs. the olditemid. If they match, then the cost(old) column will hold a value and the cost(new) column will not (= NULL). And vice-versa.

Totally convoluted, I know. But I'm stuck with this problem.

Regards,
MizPippz

[riteshjain1982]

i think you need to use Case statement with Inline query or better make one function and pass itemid to it and this will return the Cost.something like this

CREATE FUNCTION UDF_Get_COST
(
	@ItemId int,
        @IsOld bit
)
returns  decimal
AS
BEGIN
       DECLARE @SectionID int
	
       if (@IsOld = 1) 
       SELECT @SectionID = sectionid from section where olditemid  = @ItemId
       else
       SELECT @SectionID = sectionid from section where newitemid  = @ItemId       

       if  @SectionID IS NULL
       RETURN 0
       ELSE
       RETURN (select cost from cfpurchases where  sectionid = @SectionID)
END

GO

SELECT account, purchaseid, dbo.UDF_Get_COST(itemid,1),dbo.UDF_Get_COST(itemid,0)

[si_the_geek]

You haven't told us which database system you are using, so what we are posting could be wrong for you.

Here is an alternative to ritesh's version (which will be easier to convert for use with a different DBMS):

SELECT p.account, c.purchaseid, 
       CASE WHEN s.sectionid Is Not Null THEN c.cost ELSE Null END as OldCost, 
       CASE WHEN s.sectionid Is Null     THEN c.cost ELSE Null END as NewCost

FROM       cfpurchases c 
INNER JOIN purchaseindex p  ON (c.purchaseid = p.purchaseid)
LEFT  JOIN section s        ON (s.sectionid = c.sectionid AND s.olditemid = s.newitemid)

[Ms.Longstocking]

My bad.
SQL Server 2005

[riteshjain1982]

have you tried si's query?

[Ms.Longstocking]

After some fiddling to tweak Si's query to fit my needs exact... we have success. I'm using the query pretty much as is.

No UDF function required. Perfect solution.

Many thanks,
MizPippz

[Ms.Longstocking]

At present, my output is as follows(each row):

account, purchaseid, oldCost, newCost

So, any single purchase can output as multiple lines.

I'd like to be able (don't ask me why) to write a query that delivers the entire purchase as one row.

e.g. - account, purchaseid, oldCost1, newCost1, oldCost2, newCost2, oldCost3, newCost3 ...etc

Possible?

Or should I just make the necessary changes in code-behind by creating a second datatable and making the necessary manipulations there?

[si_the_geek]

I'm not aware of a way to do that, and don't think I would ever want to do it either - to me that seems very hard to read, and I would go for a different output option instead (like perhaps keeping multiple rows, but hiding the first two columns when repeated).

[Ms.Longstocking]

ok. thanks.