November 9, 2010 at 5:38 am
Hi Guys,
I have a query and i am getting frustrated not being able to understand where the issue is.
i am writing a SSIS Package to pick data from a ORACLE OLE DB Source my query is as below:
Select
trunc(sysdate) as refresh,
empd.GSP_GROUP_ID as gpid,
empd.PROFILE_CLASS_ID as clsid,
empd.ENERGISATION_STATUS as status,
count(emp.METER_POINT_ADMIN_NUMBER) as count_mpan
from
E_MARKET_PARTICIPANT_MTR_POINT emp,
E_METER_POINT_DATA empd,
(Select
METER_POINT_ADMIN_NUMBER as MPAN, max(trunc(CREATED_DATE)) as maxdate
from
E_METER_POINT_DATA
where
DATA_STATUS = 'A'
and GSP_GROUP_ID is NOT NULL
and PROFILE_CLASS_ID is NOT NULL
and ENERGISATION_STATUS ='E'
Group by
METER_POINT_ADMIN_NUMBER
) as Latest
WHERE
trunc(emp.EFFECTIVE_FROM_DATE) <= trunc(sysdate)
and (trunc(emp.EFFECTIVE_TO_DATE) >= trunc(sysdate) or emp.EFFECTIVE_TO_DATE IS NULL)
and emp.market_participant_code = 'XXX'
and emp.MARKET_PARTICIPANT_ROLE_CODE = 'YYY'
and emp.METER_POINT_ADMIN_NUMBER = empd.METER_POINT_ADMIN_NUMBER
and trunc(empd.CREATED_DATE) = latest.maxdate
and empd.METER_POINT_ADMIN_NUMBER = latest.MPAN
group by
trunc(sysdate),
empd.GSP_GROUP_ID,
empd.PROFILE_CLASS_ID,
empd.ENERGISATION_STATUS
;
I am getting error message as :ora-00933 sql command not properly ended. I am not sure where the issue is - PLEASE ADVISE.
November 9, 2010 at 6:04 am
Oracle doesn't allow inline subqueries that way.
Try this instead:
SELECT trunc(sysdate) AS refresh,
empd.GSP_GROUP_ID AS gpid,
empd.PROFILE_CLASS_ID AS clsid,
empd.ENERGISATION_STATUS AS status,
COUNT(emp.METER_POINT_ADMIN_NUMBER) AS count_mpan
FROM E_MARKET_PARTICIPANT_MTR_POINT emp,
E_METER_POINT_DATA empd
WHERE trunc(emp.EFFECTIVE_FROM_DATE) <= trunc(sysdate)
AND (trunc(emp.EFFECTIVE_TO_DATE) >= trunc(sysdate)
OR emp.EFFECTIVE_TO_DATE IS NULL
)
AND emp.market_participant_code = 'XXX'
AND emp.MARKET_PARTICIPANT_ROLE_CODE = 'YYY'
AND emp.METER_POINT_ADMIN_NUMBER = empd.METER_POINT_ADMIN_NUMBER
AND trunc(empd.CREATED_DATE) = (SELECT MAX(trunc(CREATED_DATE)) AS maxdate
FROM E_METER_POINT_DATA
WHERE DATA_STATUS = 'A'
AND GSP_GROUP_ID IS NOT NULL
AND PROFILE_CLASS_ID IS NOT NULL
AND ENERGISATION_STATUS = 'E'
AND METER_POINT_ADMIN_NUMBER = empd.METER_POINT_ADMIN_NUMBER
)
GROUP BY trunc(sysdate),
empd.GSP_GROUP_ID,
empd.PROFILE_CLASS_ID,
empd.ENERGISATION_STATUS ;
-- Gianluca Sartori
November 9, 2010 at 8:22 am
Perfect this works - thanks Gianluca Sartori! - This is a great forum.
August 7, 2011 at 9:44 am
I guess same issue :
TableA
PID, Amount1
1, 10
1, 20
2, 10
2, 10
3, 30
TableB
PID, Amount2
1, 20
1, 50
2, 30
2, 20
What I want the output is
PID, Amount1, Amount2
1, 30, 70
2, 20, 50
3, 30,
I tried this but getting error ORA: 00933 sql command not properly ended:
SELECT one.pid
, one.Amount1OfSum
, two.Amount2OfSum
FROM ( SELECT pid
, SUM(Amount1) AS Amount1OfSum
FROM TableA
GROUP
BY pid ) AS one
LEFT OUTER
JOIN ( SELECT pid
, SUM(Amount2) AS Amount2OfSum
FROM TableB
GROUP
BY pid ) AS two
ON two.pid = one.pid
Cud you plz tell me where I went wrong ?
August 18, 2011 at 9:21 am
Have you added ; to the end of the statement?
SELECT one.pid
, one.Amount1OfSum
, two.Amount2OfSum
FROM ( SELECT pid
, SUM (Amount1) AS Amount1OfSum
FROM TableA
GROUP
BY pid ) AS one
LEFT OUTER
JOIN ( SELECT pid
, SUM(Amount2) AS Amount2OfSum
FROM TableB
GROUP
BY pid ) AS two
ON two.pid = one.pid;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply