October 19, 2010 at 3:02 am
SELECT SalesProductKeyID,ActualUnitPrice,BillDateKeyID,ProductKeyID
,Qty,Profit,DD.dDate, (SELECT TOP 1 ISNULL(S.Qty,0) FROM dbo.Stock S , dbo.DateDimension DD1 WHERE S.DateKeyID = DD1.DateKeyID AND S.ProductKeyID = SF.ProductKeyID AND DD1.dDate <= DD.dDate
ORDER BY DD1.Date DESC) as StockQty, (SELECT ISNULL(SUM(Qty),0) from salesfact SF2,DATEDIMENSION DD2 WHERE BillDateKeyID=DD2.DatekeyID AND SF2.ProductKeyID=SF.ProductKeyid
AND DD2.dDate > DD.dDate AND DD2.dDATE=DD.dDate) as TotalQtySold
FROM dbo.SalesFact SF INNER JOIN DATEDIMENSION DD
ON SF.billdatekeyid =DD.Datekeyid WHERE IsProfitCalculated = 1 AND
IsSalesFactSuppPopulated =0 AND Qty > 0 AND SF.SALESPRODUCTKEYID='15996220'
Order by Clause is not working in Oracle 10g
October 19, 2010 at 8:38 am
chakrapanishroff (10/19/2010)
SELECT SalesProductKeyID,ActualUnitPrice,BillDateKeyID,ProductKeyID,Qty,Profit,DD.dDate, (SELECT TOP 1 ISNULL(S.Qty,0) FROM dbo.Stock S , dbo.DateDimension DD1 WHERE S.DateKeyID = DD1.DateKeyID AND S.ProductKeyID = SF.ProductKeyID AND DD1.dDate <= DD.dDate
ORDER BY DD1.Date DESC) as StockQty, (SELECT ISNULL(SUM(Qty),0) from salesfact SF2,DATEDIMENSION DD2 WHERE BillDateKeyID=DD2.DatekeyID AND SF2.ProductKeyID=SF.ProductKeyid
AND DD2.dDate > DD.dDate AND DD2.dDATE=DD.dDate) as TotalQtySold
FROM dbo.SalesFact SF INNER JOIN DATEDIMENSION DD
ON SF.billdatekeyid =DD.Datekeyid WHERE IsProfitCalculated = 1 AND
IsSalesFactSuppPopulated =0 AND Qty > 0 AND SF.SALESPRODUCTKEYID='15996220'
Order by Clause is not working in Oracle 10g
Believe me, "order by" does works in Ora10g 🙂
I think that line is flagged because of ISNULL() function... try NVL() function in Oracle syntax.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 20, 2010 at 6:47 am
TOP also is SQL Server specific construction and not allowed in Oracle.
Gints Plivna
http://www.gplivna.eu
October 20, 2010 at 7:56 am
gints.plivna (10/20/2010)
TOP also is SQL Server specific construction and not allowed in Oracle.
Good catch.
Poster also wants to replace "dbo" table prefix by whatever schema-name owns the affected tables.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 28, 2010 at 5:22 am
I think this is what you need:
SELECT salesproductkeyid,
actualunitprice,
billdatekeyid,
productkeyid,
qty,
profit,
dd.ddate,
(SELECT * -- ADDED OUTER SELECT
FROM (SELECT NVL(s.qty, 0) -- ADDED NVL()
FROM stock s,
datedimension dd1
WHERE s.datekeyid = dd1.datekeyid
AND s.productkeyid = sf.productkeyid
AND dd1.ddate <= dd.ddate
ORDER BY dd1.DATE DESC)
WHERE ROWNUM = 1) stockqty, -- REMOVE 'AS', ADDED ROWNUM
(SELECT NVL(SUM(qty), 0) -- ADDED NVL()
FROM salesfact sf2,
datedimension dd2
WHERE billdatekeyid = dd2.datekeyid
AND sf2.productkeyid = sf.productkeyid
AND dd2.ddate > dd.ddate
AND dd2.ddate = dd.ddate) totalqtysold -- REMOVE 'AS'
FROM salesfact sf
inner join datedimension dd
ON sf.billdatekeyid = dd.datekeyid
WHERE isprofitcalculated = 1
AND issalesfactsupppopulated = 0
AND qty > 0
AND sf.salesproductkeyid = '15996220'
October 29, 2010 at 7:05 am
feersum_endjinn (10/28/2010)
I think this is what you need:
I would probably do...
SELECT SUM(NVL(qty,0))
rather than...
SELECT NVL(SUM(qty), 0)
...so to ensure individual Null values are summarized as zeros.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply