Working with Oracle

Question

Working with Oracle

chakrapanishroff

Grasshopper

Points: 15
More actions
October 19, 2010 at 3:02 am

#227990

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

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

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.

gints.plivna Ten Centuries Points: 1256 More actions · Answer 2

TOP also is SQL Server specific construction and not allowed in Oracle.

Gints Plivna
http://www.gplivna.eu

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

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.

feersum_endjinn SSCommitted Points: 1966 More actions · Answer 4

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'

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

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.

Working with Oracle

Cookies on SQLServerCentral