Home » SQL & PL/SQL » SQL & PL/SQL » self join oracle
self join oracle [message #244705] Wed, 13 June 2007 18:51 Go to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
I have two queries which I want to make one using a self join.

SELECT CCMSECTYPE, DECODE('U','U','UPGRADE','D','DOWNGRADE') AS "DIRECTION", SUM(MARKETVALUE), SUM(STATVALUE) , SUM(GAAPVALUE),
SUM(PAR), COUNT(*)
FROM NAIC_UP_DOWN_VIEW
where
RECORD_DATE =
AND
(DECODE ('U','U',1,0)=1 AND ORG_NAIC>CURRENT_NAIC)
OR (DECODE('U', 'D' , 1, 0) = 1 AND ORG_NAIC < CURRENT_NAIC) GROUP BY CCMSECTYPE ORDER BY CCMSECTYPE

SELECT RECORD_DATE, DECODE('U','U','UPGRADE','D','DOWNGRADE') AS "DIRECTIONMON", AVG(TOTAL_MV), AVG(TOTAL_SV),
AVG(TOTAL_GV) , AVG(TOTAL_PAR), COUNT(*)
FROM NAIC_UP_DOWN_VIEW
WHERE ((DECODE('U', 'U', 1, 0) = 1 AND ORG_NAIC > CURRENT_NAIC) OR (DECODE('U', 'D', 1, 0) = 1
AND ORG_NAIC < CURRENT_NAIC)) GROUP BY RECORD_DATE ORDER BY RECORD_DATE


Could somebody help me with this

[Updated on: Wed, 13 June 2007 18:52]

Report message to a moderator

Re: self join oracle [message #244706 is a reply to message #244705] Wed, 13 June 2007 18:54 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I have two queries which I want to make one using a self join.
You don't need our permission to proceed. Have at it.

Please follow ALL posting guidelines as enumerated in the STICKY posts at the top of this forum.
Re: self join oracle [message #244707 is a reply to message #244705] Wed, 13 June 2007 19:16 Go to previous messageGo to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
just to add that I have tried to construct that as follows:-

SELECT a.CCMSECTYPE AS ccm, DECODE('U','U','UPGRADE','D','DOWNGRADE') AS "DIRECTION",
SUM(a.MARKETVALUE) AS market, SUM(a.STATVALUE) AS stat,
SUM(a.GAAPVALUE) AS gaapVal,
SUM(a.PAR) AS Par, COUNT(*) AS Count,
b.RECORD_DATE AS monrecdate
, DECODE('U','U','UPGRADE','D','DOWNGRADE') AS "DIRECTIONMON"
, AVG(b.TOTAL_MV) AS monmv
, AVG(b.TOTAL_SV) AS montotsv
,AVG(b.TOTAL_GV) AS montotgv
--, AVG(b.TOTAL_PAR) AS montotpar
FROM NAIC_UP_DOWN_VIEW a
where
RECORD_DATE IN (
SELECT RECORD_DATE
--, DECODE('U','U','UPGRADE','D','DOWNGRADE') AS "DIRECTIONMON", AVG(TOTAL_MV), AVG(TOTAL_SV),
--AVG(TOTAL_GV) , AVG(TOTAL_PAR), COUNT(*)
FROM NAIC_UP_DOWN_VIEW b
WHERE ((DECODE('U', 'U', 1, 0) = 1 AND ORG_NAIC > CURRENT_NAIC) OR (DECODE('U', 'D', 1, 0) = 1
AND ORG_NAIC < CURRENT_NAIC))
) AND
(DECODE ('U','U',1,0)=1 AND a.ORG_NAIC>a.CURRENT_NAIC)
OR (DECODE('U', 'D' , 1, 0) = 1 AND a.ORG_NAIC < a.CURRENT_NAIC)
GROUP BY a.CCMSECTYPE
ORDER BY a.CCMSECTYPE


but I am getting ora 00904 error
Re: self join oracle [message #244708 is a reply to message #244705] Wed, 13 June 2007 19:20 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Please follow ALL posting guidelines as enumerated in the STICKY posts at the top of this forum.

Since you are unwilling or incapable of following the request above, You're On Your Own (YOYO)!
Re: self join oracle [message #244760 is a reply to message #244705] Thu, 14 June 2007 01:17 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/mv/msg/83512/244759/102589/#msg_244759

Regards
Michel
Previous Topic: Trigger Related Questions
Next Topic: optimizer hints
Goto Forum:
  


Current Time: Wed Dec 07 12:38:29 CST 2016

Total time taken to generate the page: 0.11189 seconds