Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

group

948062Sep 4 2012 — edited Sep 5 2012
select approved_amount from target_aggregate where status='APPROVED' group by PA_REQUEST_ID;



SELECT req_amount from target_aggregate where level_id =(select min(level_id) from target_aggregate group by PA_REQUEST_ID);




can i use group by as i shown in above sql query

Comments

jeneesh
Why cant you try?

are you getting any error?

Anyhow, when you use GROUP BY in the subquery, you should expect multiple rows as output. So, you will have to use IN instead of "="
V prasad
Use This

SELECT req_amount FROM target_aggregate
WHERE level_id =( SELECT LEVELID FROM (select PA_REQUEST_ID, min(level_id) AS "LEVELID" from target_aggregateGROUP BY GROUP BY PA_REQUEST_ID ) )
hm
First statement is wrong, because you must use all non-aggregate columns in group by clause.

Second statements may be syntactically ok, but probably it will fail at runtime, because the "select min(level)... group by pa_request_id" will give you more than one row (one for each pa_request_id).

So I think, both are wrong.

But the main question is: What do you want to achieve? Try to explain!

Edited by: hm on 04.09.2012 04:32
DivasP
the syntax of group by statement says that u have to put all the column in the group by that are being used in the select list, use this rule and check whether u are doing right or not.

Edited by: user8578271 on Sep 4, 2012 4:37 AM
948062
hm wrote:
First statement is wrong, because you must use all non-aggregate columns in group by clause.

Second statements may be syntactically ok, but probably it will fail at runtime, because the "select min(level)..." will give you more than one row (one for each pa_request_id).

So I think, both are wrong.

But the main question is: What do you want to achieve? Try to explain!
>
First statement is wrong, because you must use all non-aggregate columns in group by clause.

Second statements may be syntactically ok, but probably it will fail at runtime, because the "select min(level)..." will give you more than one row (one for each pa_request_id).

So I think, both are wrong.

But the main question is: What do you want to achieve? Try to explain! select
TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,
TARGET_EMPLOYEE.GE_ID as requesterGEID,
TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,
TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,
TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,
TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
TARGET_ATTENDEE.COUNTRY as atcountry,
TARGET_AGGREGATION_ATTENDEE.REQUEST_TYPE as requesttype,
TARGET_PA_REQUEST.PA_REQUEST_ID as parequest,
TARGET_PA_REQUEST.EVENT_DATE as eventdate,
TARGET_PA_REQUEST.EVENT_DESCRIPTION as eventdesc,
TARGET_PA_REQUEST.CREATED_DATE as requestdate,
TARGET_PA_REQUEST.STATUS as status,
TARGET_AGGREGATION_ATTENDEE.status,
(select TARGET_AGGREGATION_ATTENDEE.be_aggregation_approved_amount from TARGET_AGGREGATION_ATTENDEE where status_1='APPROVED' group by parequest) as approved_amount,
TARGET_AGGREGATION_ATTENDEE.level_id,(select
TARGET_AGGREGATION_ATTENDEE.be_aggregation_pend_amount where level_id=(select min(level_id) from target_aggregation_attendee group by parequest)as req_amount
from TARGET_ATTENDEE
TARGET_EMPLOYEE,
TARGET_PA_REQUEST,
TARGET_AGGREGATION_ATTENDEE
where TARGET_ATTENDEE.ATTENDEE_ID=TARGET_AGGREGATION_ATTENDEE.ATTENDEE_ID
and TARGET_EMPLOYEE.GE_ID=TARGET_AGGREGATION_ATTENDEE.REQUESTOR_ID
and TARGET_AGGREGATION_ATTENDEE.PA_REQUEST_ID=TARGET_PA_REQUEST.PA_REQUEST_ID
and to_char(TARGET_AGGREGATION_ATTENDEE.CREATED_DATE,'YYYY')=to_char(sysdate,'YYYY')





The table is

TARGET_AGGREGATION_ATTENDEE
columns:
PA_REQUEST_ID STATUS APPROVED_AMOUNT LEVEL_ID REQ_AMOUNT
APAC1031 CAN 0 0.61336

APAC1031 CAN 0 8.17212
APAC1031 CAN 30 0
APAC1031 CAN 30 0

APAC1033 30
APAC1033 30
APAC1033 20
APAC1033 20
APAC1053 0
APAC1053 0 45
APAC1053 0
APAC1053 0 45
APAC1059 0
APAC1059 0
APAC1059 0 34
APAC1060 30
APAC1060 30
APAC1060 0
APAC1060 30
APAC1060 0
APAC1060 0 34
APAC1061 0 56
APAC1067 EXP 0 45
APAC1067 EXP 0 89
APAC1067 EXP 0
APAC1067 EXP 0
APAC1071 0
APAC1072 0
APAC1074 0 123
APAC1089 0 11
APAC1090 0 123
APAC1092 0 11
APAC1093 0 11
APAC1094 0 11
APAC1095 0
APAC1095 0
APAC1095 0 12354
APAC1097 0 12354
APAC1097 0
APAC1097 0
APAC1099 0
APAC1099 0
APAC1099 0 12354
APAC1100 0 12354
APAC1100 0
APAC1100 0
APAC1141 70
APAC1141 0
APAC1141 60
APAC1141 30
APAC1143 0
APAC1144 CAN 0
APAC1145 0 500
APAC1156 0
APAC1156 0 34
APAC1156 0
APAC1163 CAN 23 0 0
APAC1167 0
APAC1167 30
APAC1170 0
APAC1171 0
APAC1174 CAN 0
APAC1189 0
APAC1204 0
APAC1205 0 100
APAC1207 CAN 0
APAC1208 0
APAC1212 CAN 0
APAC1213 0
APAC1215 CAN 0
APAC1218 0
APAC1218 0
APAC1218 0 123
APAC1224 0
APAC1224 0
APAC1226 CAN 0
APAC1226 CAN 0
APAC1227 0
APAC1227 0
APAC1227 0
APAC1227 0
APAC1233 0
APAC1233 0
APAC1233 0
APAC1233 0 12
APAC1233 0 123
APAC1233 0
APAC1235 CAN 0
APAC1235 CAN 0
APAC1237 0
APAC1237 0
APAC1238 CAN 2 0 0
APAC1238 CAN 2 0 0
APAC1239 0 340
APAC1244 CAN 0
APAC1244 CAN 0
APAC1245 CAN 0
APAC1245 CAN 0
APAC1247 0
APAC1247 0
APAC1247 0 256
APAC1247 0 345
APAC1263 0 123
APAC1263 0
APAC1267 CAN 0
APAC1267 CAN 0
APAC1268 0
APAC1268 0
APAC1268 0
APAC1268 0 100
APAC1268 0 123
APAC1268 0
APAC1269 0
APAC1269 0
APAC1269 0 230
APAC1269 0 230
APAC1269 0
APAC1269 0
APAC1276 0
APAC1280 0
APAC1283 0
APAC1283 0 55
APAC1283 0
APAC1285 20
APAC1285 20 0
APAC1285 20 0
APAC1285 20 0
APAC1285 20
APAC1285 20
APAC1285 20
APAC1285 20
APAC1285 20
APAC1285 0
APAC1285 0
APAC1285 0
APAC1285 0
APAC1285 0
APAC1285 0
APAC1285 0 55
APAC1285 0 66
APAC1285 0 66
APAC1293 CAN 0
APAC1295 CAN 0
APAC1296 0
APAC1297 CAN 25 0 0
APAC1298 0 25
APAC1303 0
APAC1303 0 55
APAC1303 0 55
APAC1303 0
APAC1304 0
APAC1304 0
APAC1304 0 44
APAC1304 0 44
APAC1311 0 10
APAC1311 0
APAC1311 0
APAC1311 0 12
APAC1312 0 12
APAC1312 0 12
APAC1312 0
APAC1312 0
APAC1345 CAN 33 0 0
APAC1347 CAN 0
APAC1352 0
APAC1352 30
APAC1353 0
APAC1354 0
APAC1356 0 816.9795
APAC1356 30 0
APAC1371 0
APAC1372 0
APAC1381 0
APAC1383 0
APAC1386 CAN 0
APAC1387 0
APAC1387 0 3.6
APAC1389 30
APAC1389 0
APAC1391 0
APAC1392 0 102.204
APAC1393 0 10.206
APAC1394 0 10.206
APAC1396 0 3.6
APAC1398 CAN 0
APAC1399 0
APAC1399 0
APAC1399 0 41.814
APAC1399 0 41.814
APAC1399 0
APAC1399 0
APAC1402 0
APAC1403 0 9.828
APAC1405 30
APAC1405 0
APAC1407 0 8.208
APAC1410 0 102.15
APAC1428 Approved 0
APAC1433 0
APAC1433 0 0.036
APAC1433 0
APAC1440 CAN 0.612 0 0
APAC1445 0 58.212
APAC1445 0
APAC1447 Rejected 0
APAC1449 0 0
APAC1449 0
APAC1449 0
APAC1452 0 0.81
APAC1452 0
APAC1452 0
APAC1462 0 0
APAC1462 0
APAC1462 0
APAC1463 0 0.162
APAC1463 0 0.162
APAC1463 0
APAC1463 0
APAC1469 0 8.208
APAC1470 30
APAC1470 0
APAC1471 0
APAC1471 30
APAC1481 CAN 0.414 0 0
APAC1484 0
APAC1484 0
APAC1486 0
APAC1486 0
APAC1486 0
APAC1486 0 0.018
APAC1486 0 0.018
APAC1486 0 0.036
APAC1486 0
APAC1486 0
APAC1487 0
APAC1488 0
APAC1488 0 4.212
APAC1488 30
APAC1488 30 0
APAC1489 0
APAC1489 30 0
APAC1489 30
APAC1489 0 2.214
APAC1501 0
APAC1502 0
APAC1507 CAN 0
APAC1508 0
APAC1531 0 0.162
APAC1531 0
APAC1531 0
APAC1531 30
APAC1531 30
APAC1534 0 5.4
APAC1534 0
APAC1538 0
APAC1540 0
APAC1561 CAN 0
APAC1564 Approved 0
APAC1569 0 0
APAC1569 30 0
APAC1570 0 4.49196
APAC1570 20
APAC1570 0
APAC1573 20
APAC1573 0 4.49196
APAC1573 0
APAC1573 Approved 50
APAC1573 30
APAC1574 0
APAC1574 0
APAC1574 0
APAC1574 0
APAC1591 0 5.39396
APAC1591 0 5.39396
APAC1592 0 5.39396
APAC1593 0
APAC1595 0 5.39396
APAC1596 0 5.39396
APAC1597 0
APAC1598 0 252.56
APAC1601 0
APAC1613 CAN 0.1804 0 0
APAC1618 0
APAC1618 0
APAC1618 0
APAC1618 0
APAC1618 0
APAC1619 0
APAC1619 0
APAC1619 0
APAC1619 0
APAC1628 0
APAC1628 0
APAC1629 0 0.10824
APAC1629 0
APAC1630 0
APAC1632 0
APAC1636 0 0.01804
APAC1637 0 783.7478
APAC1637 0
APAC1637 0 783.7478
APAC1637 0
APAC1638 0
APAC1639 0
APAC1640 0
APAC1640 0
APAC1641 0
APAC1641 0 0.16236
APAC1641 0
APAC1646 0
APAC1649 CAN 0
APAC1651 0
APAC1651 20
APAC1654 0
APAC1655 0 250.756
APAC1658 CAN 61.76896 0 0
APAC1659 0
APAC1659 80
APAC1662 CAN 7.79328 0 0
APAC1662 CAN 7.63092 0 0
APAC1670 CAN 7.82936 0 0
APAC1672 0 8.22023E+16
APAC1676 0 252.56
APAC1681 0 0.1804
APAC1681 0 0.0902
APAC1681 0 0.0902
APAC1684 Approved 50
APAC1684 30
APAC1684 30
APAC1684 20
APAC1684 20
APAC1684 0
APAC1684 0
APAC1684 Approved 50
APAC1688 0 1803999.982
APAC1689 0
APAC1689 0 8.19016
APAC1694 0
APAC1694 0
APAC1695 0
APAC1695 0
APAC1695 0
APAC1695 0 0.61336
APAC1695 0
APAC1695 0
APAC1695 0
APAC1695 0 0.61336
APAC1695 0 0.61336
APAC1696 0
APAC1696 0
APAC1696 0
APAC1696 0
APAC1696 0 0.03608
APAC1696 0
APAC1696 0 0.03608
APAC1696 0 0.03608
APAC1696 0
APAC1709 0 0.03608
APAC1709 0
APAC1709 0
APAC1711 0
APAC1711 0 0.03608
APAC1711 0
APAC1712 0
APAC1714 CAN 0
APAC1717 0 0.21648
APAC1717 0
APAC1717 0
APAC1717 0
APAC1717 0
APAC1717 0 0.21648
APAC1723 0
APAC1723 0
APAC1726 0
APAC1728 Approved 2.1648 0 0
APAC1729 Approved 2.1648 0 0
APAC1730 CAN 70
APAC1730 CAN 80
APAC1730 CAN 60
APAC1730 CAN 30
APAC1730 CAN 0
APAC1731 20
APAC1731 0
APAC1734 0
APAC1734 0
APAC1734 0 384.63084
APAC1736 0
APAC1736 0
APAC1736 0 0
APAC1737 0
APAC1739 0 36.08
APAC1739 0
APAC1739 0
APAC1739 0 54.12
APAC1742 0
APAC1743 0 22.26136
APAC1746 0 270.6
APAC1748 0 0.07216
APAC1748 0
APAC1748 0
APAC1748 0 0.05412
APAC1749 0
APAC1749 0
APAC1752 0
APAC1753 CAN 90.2 0 0
APAC1754 Approved 0 30 0
APAC1754 0 617.76176
APAC1754 0
APAC1754 20 0
APAC1755 0
APAC1755 0 1.01024
APAC1756 0
APAC1756 0
APAC1756 0
APAC1756 0
APAC1758 0 623.57064
APAC1758 20 0
APAC1758 0
APAC1758 Approved 0 30 0

I need requested amount column data from target aggregate table whose level id is minimum
that corresponding value for particular request id.

and approvel amount column data from target Aggregate table such that status should Approved for that particular requst id.
948062
can u suggest for first query how to get group by request id and status=approved
for Approved amount column from table TARGET_AGGREGATION_ATTENDEE

i use this one:
select approved_amount from target_aggregate where status='APPROVED' group by PA_REQUEST_ID;
Venkadesh Raja
Same mistake what you did yesterday !

2437105
948062
yes but my requirement is like:

i'am not able to paste the table in correct format frm excel.
948062
i will give u clear details

my table is
TARGET_AGGREGATION_ATTENDEE

query i used is:

select
TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,
TARGET_EMPLOYEE.GE_ID as requesterGEID,
TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,
TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,
TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,
TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
TARGET_ATTENDEE.COUNTRY as atcountry,
TARGET_AGGREGATION_ATTENDEE.REQUEST_TYPE as requesttype,
TARGET_PA_REQUEST.PA_REQUEST_ID as parequest,
TARGET_PA_REQUEST.EVENT_DATE as eventdate,
TARGET_PA_REQUEST.EVENT_DESCRIPTION as eventdesc,
TARGET_PA_REQUEST.CREATED_DATE as requestdate,
TARGET_PA_REQUEST.STATUS as status,
TARGET_AGGREGATION_ATTENDEE.status,
TARGET_AGGREGATION_ATTENDEE.be_aggregation_approved_amount as approved_amount,
TARGET_AGGREGATION_ATTENDEE.level_id,
TARGET_AGGREGATION_ATTENDEE.be_aggregation_pend_amount as req_amount
from TARGET_ATTENDEE,
TARGET_EMPLOYEE,
TARGET_PA_REQUEST,
TARGET_AGGREGATION_ATTENDEE
where TARGET_ATTENDEE.ATTENDEE_ID=TARGET_AGGREGATION_ATTENDEE.ATTENDEE_ID
and TARGET_EMPLOYEE.GE_ID=TARGET_AGGREGATION_ATTENDEE.REQUESTOR_ID
and TARGET_AGGREGATION_ATTENDEE.PA_REQUEST_ID=TARGET_PA_REQUEST.PA_REQUEST_ID
and to_char(TARGET_AGGREGATION_ATTENDEE.CREATED_DATE,'YYYY')=to_char(sysdate,'YYYY')
and TARGET_AGGREGATION_ATTENDEE.pa_request_id='NAM1421';

for one particular request id is: 'NAM1421';

i got output from my query is:
(in rows i got this output unable to separate it so making it in a column

PAREQUEST
NAM1421
NAM1421
NAM1421

STATUS_1
NULL
Approved
NULL

APPROVED_AMOUNT
Null
3
Null

LEVEL_ID
15
70
20




REQ_AMOUNT
3
0
3




i need output like:(in a single row here i'am showing in column)

PAREQUEST
NAM1421


APPROVED_AMOUNT
3


STATUS_1
Approved


LEVEL_ID
15



REQ_AMOUNT
3



the logic behind is
requested amount should print the value whose levelid is minimum for that enter request id. that corresponding value.

Approved Amount status should be approved tht data should print for that enter requestid.
Venkadesh Raja
i'm not sure.

try this !
WITH t AS 
(
select
target_employee.first_name||' ' ||target_employee.last_name as requestername, 
target_employee.ge_id as requestergeid, 
target_attendee.first_name||' ' ||target_attendee.last_name as attendeename, 
target_attendee.attendee_type_flag as attendeetyflg, 
target_attendee.us_go_attendee_flag as usgoflg, 
target_attendee.comp_govt_agency_dept as atcomp, 
target_attendee.country as atcountry, 
target_aggregation_attendee.request_type as requesttype, 
target_pa_request.pa_request_id as parequest, 
target_pa_request.event_date as eventdate, 
target_pa_request.event_description as eventdesc, 
target_pa_request.created_date as requestdate, 
target_pa_request.status as status, 
target_aggregation_attendee.status,
target_aggregation_attendee.be_aggregation_approved_amount as approved_amount, 
target_aggregation_attendee.level_id,
target_aggregation_attendee.be_aggregation_pend_amount as req_amount 
from target_attendee, 
target_employee, 
target_pa_request, 
target_aggregation_attendee 
where target_attendee.attendee_id=target_aggregation_attendee.attendee_id
and target_employee.ge_id=target_aggregation_attendee.requestor_id
and target_aggregation_attendee.pa_request_id=target_pa_request.pa_request_id
and to_char(target_aggregation_attendee.created_date,'yyyy')=to_char(sysdate,'yyyy')
and target_aggregation_attendee.pa_request_id='nam1421'
)
SELECT PAREQUEST,STATUS_1,SUM(approved_amount) approved_amount,LEVEL_ID,AVG(req_amount) req_amount
FROM t
GROUP BY PAREQUEST,LEVEL_ID,STATUS_1;
i know this code does not produce expected results..But i think GROUP BY will help you.
948062
hi prasad,

can u please suggest me how can i use for getting Aproved_amount .
for particular request id,.....

select approved_amount from target_aggregate where status='APPROVED' group by PA_REQUEST_ID;
BrendanP
Use CASE to select out the approved record and KEEP to get the first requested amount, and aggregate by pa_request_id (untested) 
SELECT pa_request_id, Max (req_amount) KEEP (DENSE_RANK FIRST ORDER BY level_id) req_amount,
Max (CASE WHEN status = 'APPROVED' THEN approved_amount END) approved_amount
  FROM target_aggregate 
 GROUP BY pa_request_id
Edited by: BrendanP on 04-Sep-2012 07:00
Added pa_request_id to select
Venkadesh Raja 
select approved_amount from target_aggregate where status='APPROVED' AND PA_REQUEST_ID=&your_input;
948062
hi ,


can u suggest me from my query
i don't have to pass a value

select
TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,
TARGET_EMPLOYEE.GE_ID as requesterGEID,
TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,
TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,
TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,
TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
TARGET_ATTENDEE.COUNTRY as atcountry,
TARGET_AGGREGATION_ATTENDEE.REQUEST_TYPE as requesttype,
TARGET_PA_REQUEST.PA_REQUEST_ID as parequest,
TARGET_PA_REQUEST.EVENT_DATE as eventdate,
TARGET_PA_REQUEST.EVENT_DESCRIPTION as eventdesc,
TARGET_PA_REQUEST.CREATED_DATE as requestdate,
TARGET_PA_REQUEST.STATUS as status,
TARGET_AGGREGATION_ATTENDEE.status,
TARGET_AGGREGATION_ATTENDEE.be_aggregation_approved_amount as approved_amount,
TARGET_AGGREGATION_ATTENDEE.level_id,
TARGET_AGGREGATION_ATTENDEE.be_aggregation_pend_amount as req_amount
from TARGET_ATTENDEE,
TARGET_EMPLOYEE,
TARGET_PA_REQUEST,
TARGET_AGGREGATION_ATTENDEE
where TARGET_ATTENDEE.ATTENDEE_ID=TARGET_AGGREGATION_ATTENDEE.ATTENDEE_ID
and TARGET_EMPLOYEE.GE_ID=TARGET_AGGREGATION_ATTENDEE.REQUESTOR_ID
and TARGET_AGGREGATION_ATTENDEE.PA_REQUEST_ID=TARGET_PA_REQUEST.PA_REQUEST_ID
and to_char(TARGET_AGGREGATION_ATTENDEE.CREATED_DATE,'YYYY')=to_char(sysdate,'YYYY')
and TARGET_AGGREGATION_ATTENDEE.pa_request_id='NAM1421';
948062
hi,

can please suggest me from my query:

select
TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,
TARGET_EMPLOYEE.GE_ID as requesterGEID,
TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,
TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,
TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,
TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
TARGET_ATTENDEE.COUNTRY as atcountry,
TARGET_AGGREGATION_ATTENDEE.REQUEST_TYPE as requesttype,
TARGET_PA_REQUEST.PA_REQUEST_ID as parequest,
TARGET_PA_REQUEST.EVENT_DATE as eventdate,
TARGET_PA_REQUEST.EVENT_DESCRIPTION as eventdesc,
TARGET_PA_REQUEST.CREATED_DATE as requestdate,
TARGET_PA_REQUEST.STATUS as status,
TARGET_AGGREGATION_ATTENDEE.status,
TARGET_AGGREGATION_ATTENDEE.be_aggregation_approved_amount as approved_amount,
TARGET_AGGREGATION_ATTENDEE.level_id,
TARGET_AGGREGATION_ATTENDEE.be_aggregation_pend_amount as req_amount
from TARGET_ATTENDEE,
TARGET_EMPLOYEE,
TARGET_PA_REQUEST,
TARGET_AGGREGATION_ATTENDEE
where TARGET_ATTENDEE.ATTENDEE_ID=TARGET_AGGREGATION_ATTENDEE.ATTENDEE_ID
and TARGET_EMPLOYEE.GE_ID=TARGET_AGGREGATION_ATTENDEE.REQUESTOR_ID
and TARGET_AGGREGATION_ATTENDEE.PA_REQUEST_ID=TARGET_PA_REQUEST.PA_REQUEST_ID
and to_char(TARGET_AGGREGATION_ATTENDEE.CREATED_DATE,'YYYY')=to_char(sysdate,'YYYY')
and TARGET_AGGREGATION_ATTENDEE.pa_request_id='NAM1421';
sb92075
How do I ask a question on the forums?
2176552
SELECT target_employee.first_name 
       ||' ' 
       ||target_employee.last_name                                AS 
       Requestername, 
       target_employee.ge_id                                      AS 
       requesterGEID, 
       target_attendee.first_name 
       ||' ' 
       ||target_attendee.last_name                                AS 
       AttendeeName, 
       target_attendee.attendee_type_flag                         AS 
       Attendeetyflg, 
       target_attendee.us_go_attendee_flag                        AS usgoflg, 
       target_attendee.comp_govt_agency_dept                      AS Atcomp, 
       target_attendee.country                                    AS atcountry, 
       target_aggregation_attendee.request_type                   AS requesttype 
       , 
       target_pa_request.pa_request_id                            AS 
       parequest, 
       target_pa_request.event_date                               AS eventdate, 
       target_pa_request.event_description                        AS eventdesc, 
       target_pa_request.created_date                             AS requestdate 
       , 
       target_pa_request.status                                   AS 
       status, 
       target_aggregation_attendee.status, 
       target_aggregation_attendee.be_aggregation_approved_amount AS 
       approved_amount, 
       target_aggregation_attendee.level_id, 
       target_aggregation_attendee.be_aggregation_pend_amount     AS req_amount 
FROM   target_attendee, 
       target_employee, 
       target_pa_request, 
       target_aggregation_attendee 
WHERE  target_attendee.attendee_id = target_aggregation_attendee.attendee_id 
       AND target_employee.ge_id = target_aggregation_attendee.requestor_id 
       AND target_aggregation_attendee.pa_request_id = 
           target_pa_request.pa_request_id 
       AND To_char(target_aggregation_attendee.created_date, 'YYYY') = 
           To_char(SYSDATE, 'YYYY') 
       AND target_aggregation_attendee.pa_request_id = 'NAM1421';
BrendanP
Not off the top of my head, no, as I don't know your data structures or full requirement. My query allows you to group by each request and get the approved and requested amounts as I understood the requirement. You should be able to work it out from there.
948062
BrendanP wrote:
Not off the top of my head, no, as I don't know your data structures or full requirement. My query allows you to group by each request and get the approved and requested amounts as I understood the requirement. You should be able to work it out from there.
>
Not off the top of my head, no, as I don't know your data structures or full requirement. My query allows you to group by each request and get the approved and requested amounts as I understood the requirement. You should be able to work it out from there.
> Not off the top of my head, no, as I don't know your data structures or full requirement. My query allows you to group by each request and get the approved and requested amounts as I understood the requirement. You should be able to work it out from there. hi brendan,

Just help me out i have simple requirement.
thanks for ur previous suggestions.
table is:



select
TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,
TARGET_EMPLOYEE.GE_ID as requesterGEID,
TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,
TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,
TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,
TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
TARGET_ATTENDEE.COUNTRY as atcountry,
TARGET_AGGREGATION_ATTENDEE.REQUEST_TYPE as requesttype,
TARGET_PA_REQUEST.PA_REQUEST_ID as parequest,
TARGET_PA_REQUEST.EVENT_DATE as eventdate,
TARGET_PA_REQUEST.EVENT_DESCRIPTION as eventdesc,
TARGET_PA_REQUEST.CREATED_DATE as requestdate,
TARGET_PA_REQUEST.STATUS as status,
TARGET_AGGREGATION_ATTENDEE.status,
TARGET_AGGREGATION_ATTENDEE.be_aggregation_approved_amount as approved_amount,
TARGET_AGGREGATION_ATTENDEE.l*level_id*,
TARGET_AGGREGATION_ATTENDEE.be_aggregation_pend_amount as req_amount
from TARGET_ATTENDEE,
TARGET_EMPLOYEE,
TARGET_PA_REQUEST,
TARGET_AGGREGATION_ATTENDEE
where TARGET_ATTENDEE.ATTENDEE_ID=TARGET_AGGREGATION_ATTENDEE.ATTENDEE_ID
and TARGET_EMPLOYEE.GE_ID=TARGET_AGGREGATION_ATTENDEE.REQUESTOR_ID
and TARGET_AGGREGATION_ATTENDEE.PA_REQUEST_ID=TARGET_PA_REQUEST.PA_REQUEST_ID
and to_char(TARGET_AGGREGATION_ATTENDEE.CREATED_DATE,'YYYY')=to_char(sysdate,'YYYY')
and TARGET_AGGREGATION_ATTENDEE.pa_request_id='NAM1421';



my requirement is only that i have to select req_amount data condition for that is (i have to select min(level id) corresponding data from table for request amount for a particular request id).
and for approved_amount conditiion is tht i have to select where status_1='APPROVED' for entered group of requested id.



i framed my query like tht:
correct me.

select

TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,

TARGET_EMPLOYEE.GE_ID as requesterGEID,

TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,

TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,

TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,

TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,

TARGET_ATTENDEE.COUNTRY as atcountry,

TARGET_AGGREGATION_ATTENDEE.REQUEST_TYPE as requesttype,

TARGET_PA_REQUEST.PA_REQUEST_ID as parequest,

TARGET_PA_REQUEST.EVENT_DATE as eventdate,

TARGET_PA_REQUEST.EVENT_DESCRIPTION as eventdesc,

TARGET_PA_REQUEST.CREATED_DATE as requestdate,

TARGET_PA_REQUEST.STATUS as status,

TARGET_AGGREGATION_ATTENDEE.status,

(select TARGET_AGGREGATION_ATTENDEE.be_aggregation_approved_amount from TARGET_AGGREGATION_ATTENDEE where status_1='APPROVED' group by parequest) as approved_amount,

TARGET_AGGREGATION_ATTENDEE.level_id,
(select
TARGET_AGGREGATION_ATTENDEE.be_aggregation_pend_amount where level_id=(select min(level_id) from target_aggregation_attendee group by parequest)as req_amount

from TARGET_ATTENDEE,

TARGET_EMPLOYEE,

TARGET_PA_REQUEST,

TARGET_AGGREGATION_ATTENDEE

where TARGET_ATTENDEE.ATTENDEE_ID=TARGET_AGGREGATION_ATTENDEE.ATTENDEE_ID

and TARGET_EMPLOYEE.GE_ID=TARGET_AGGREGATION_ATTENDEE.REQUESTOR_ID

and TARGET_AGGREGATION_ATTENDEE.PA_REQUEST_ID=TARGET_PA_REQUEST.PA_REQUEST_ID

and to_char(TARGET_AGGREGATION_ATTENDEE.CREATED_DATE,'YYYY')=to_char(sysdate,'YYYY')

and TARGET_AGGREGATION_ATTENDEE.pa_request_id='NAM1421';


i think you can suggest me better.
BrendanP
Well, all the fields that are the same for a given request_id should just be added to the select list and group by. The two you mentioned that vary, you can do as I suggested, or you can do other things too such as subqueries but they're likely to be slower. If there are any others that vary you would need to say so and explain what you want to do with them. I understand you to want a request_id level aggregation.
948062
i will give u clear details

my table is
TARGET_AGGREGATION_ATTENDEE

query i used is:

select
TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,
TARGET_EMPLOYEE.GE_ID as requesterGEID,
TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,
TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,
TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,
TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
TARGET_ATTENDEE.COUNTRY as atcountry,
TARGET_AGGREGATION_ATTENDEE.REQUEST_TYPE as requesttype,
TARGET_PA_REQUEST.PA_REQUEST_ID as parequest,
TARGET_PA_REQUEST.EVENT_DATE as eventdate,
TARGET_PA_REQUEST.EVENT_DESCRIPTION as eventdesc,
TARGET_PA_REQUEST.CREATED_DATE as requestdate,
TARGET_PA_REQUEST.STATUS as status,
TARGET_AGGREGATION_ATTENDEE.status,
TARGET_AGGREGATION_ATTENDEE.be_aggregation_approved_amount as approved_amount,
TARGET_AGGREGATION_ATTENDEE.level_id,
TARGET_AGGREGATION_ATTENDEE.be_aggregation_pend_amount as req_amount
from TARGET_ATTENDEE,
TARGET_EMPLOYEE,
TARGET_PA_REQUEST,
TARGET_AGGREGATION_ATTENDEE
where TARGET_ATTENDEE.ATTENDEE_ID=TARGET_AGGREGATION_ATTENDEE.ATTENDEE_ID
and TARGET_EMPLOYEE.GE_ID=TARGET_AGGREGATION_ATTENDEE.REQUESTOR_ID
and TARGET_AGGREGATION_ATTENDEE.PA_REQUEST_ID=TARGET_PA_REQUEST.PA_REQUEST_ID
and to_char(TARGET_AGGREGATION_ATTENDEE.CREATED_DATE,'YYYY')=to_char(sysdate,'YYYY')
and TARGET_AGGREGATION_ATTENDEE.pa_request_id='NAM1421';

for one particular request id is: 'NAM1421';

i got output from my query is:
(in rows i got this output unable to separate it so making it in a column

PAREQUEST
NAM1421
NAM1421
NAM1421

STATUS_1
NULL
Approved
NULL

APPROVED_AMOUNT
Null
3
Null

LEVEL_ID
15
70
20
REQ_AMOUNT
3
0
3
i need output like:(in a single row here i'am showing in column)

PAREQUEST
NAM1421
APPROVED_AMOUNT
3
STATUS_1
Approved
LEVEL_ID
15

REQ_AMOUNT
3

the logic behind is
requested amount should print the value whose levelid is minimum for that enter request id. that corresponding value.

Approved Amount status should be approved tht data should print for that enter requestid.
sb92075
How do I ask a question on the forums?
2176552
BrendanP
Ok, you want to aggregate the level_id as well, that's easy: 
SELECT pa_request_id, 
    Max (req_amount) KEEP (DENSE_RANK FIRST ORDER BY level_id) req_amount,
    Min (level_id) level_id,
    Max (CASE WHEN status = 'APPROVED' THEN approved_amount END) approved_amount
  FROM target_aggregate 
 GROUP BY pa_request_id
948062
hi
select
TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,
TARGET_EMPLOYEE.GE_ID as requesterGEID,
TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,
TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,
TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,
TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
TARGET_ATTENDEE.COUNTRY as atcountry,
TARGET_AGGREGATION_ATTENDEE.REQUEST_TYPE as requesttype,
TARGET_PA_REQUEST.PA_REQUEST_ID as parequest,
TARGET_PA_REQUEST.EVENT_DATE as eventdate,
TARGET_PA_REQUEST.EVENT_DESCRIPTION as eventdesc,
TARGET_PA_REQUEST.CREATED_DATE as requestdate,
TARGET_PA_REQUEST.STATUS as status,
TARGET_AGGREGATION_ATTENDEE.status,
TARGET_AGGREGATION_ATTENDEE.be_aggregation_approved_amount as approved_amount,
TARGET_AGGREGATION_ATTENDEE.level_id,
TARGET_AGGREGATION_ATTENDEE.be_aggregation_pend_amount as req_amount
from TARGET_ATTENDEE,
TARGET_EMPLOYEE,
TARGET_PA_REQUEST,
TARGET_AGGREGATION_ATTENDEE
where TARGET_ATTENDEE.ATTENDEE_ID=TARGET_AGGREGATION_ATTENDEE.ATTENDEE_ID
and TARGET_EMPLOYEE.GE_ID=TARGET_AGGREGATION_ATTENDEE.REQUESTOR_ID
and TARGET_AGGREGATION_ATTENDEE.PA_REQUEST_ID=TARGET_PA_REQUEST.PA_REQUEST_ID
and to_char(TARGET_AGGREGATION_ATTENDEE.CREATED_DATE,'YYYY')=to_char(sysdate,'YYYY')
and TARGET_AGGREGATION_ATTENDEE.pa_request_id='NAM1421';

for one particular request id is: 'NAM1421';

i got output from my query is:
(in rows i got this output unable to separate it so making it in a column

PAREQUEST
NAM1421
NAM1421
NAM1421

STATUS_1
NULL
Approved
NULL

APPROVED_AMOUNT
Null
*3*
Null

LEVEL_ID
*15*
*70*
*20*
REQ_AMOUNT
*3*
*0*
*3*
i need output like:(in a single row here i'am showing in column)

PAREQUEST
NAM1421

APPROVED_AMOUNT
3

STATUS_1
Approved

LEVEL_ID
15

REQ_AMOUNT
3



FOR GETTING REQ_AMOUNT=3 IN OUTPUT I HAVE TO APPLY LOGIC(SELECT MIN(LEVEL_ID) I.E. 15 CORRESPONDING VALUE IN ABOVE IS 3 SO IT'S 3)

FOR GETTING APPROVED_AMOUNT=3 IN OUTPUT I HAVE TO APPLY LOGIC (SELECT APPROVED_AMOUNT WHERE STATUS='APPROVED' I.E FOR REUQUEST_ID=*NAM1421*

HERE IN THIS WHERE STATUS_1=APPROVED APPROVED_AMOUNT=3


SO HERE IF WE ARE FETCHING THE DATA
FOR ONE REUEST ID, MNY REST ID ARE WITH SAME ID..SO WE NEED GROUP BY.

SUGGEST ME I THINK YOU ARE NEARBY.
Biju Das
>
SUGGEST ME I THINK YOU ARE NEARBY.
>

Please Do not use caps!
And...you are too lazy to format your code! Bad! Very bad!

Regards
Biju
948062
i framed my query like tht:
correct me.

select

TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,

TARGET_EMPLOYEE.GE_ID as requesterGEID,

TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,

TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,
TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,
TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
TARGET_ATTENDEE.COUNTRY as atcountry,
TARGET_AGGREGATION_ATTENDEE.REQUEST_TYPE as requesttype,
TARGET_PA_REQUEST.PA_REQUEST_ID as parequest,

TARGET_PA_REQUEST.EVENT_DATE as eventdate,
TARGET_PA_REQUEST.EVENT_DESCRIPTION as eventdesc,
TARGET_PA_REQUEST.CREATED_DATE as requestdate,
TARGET_PA_REQUEST.STATUS as status,
TARGET_AGGREGATION_ATTENDEE.status,

*(select TARGET_AGGREGATION_ATTENDEE.be_aggregation_approved_amount from TARGET_AGGREGATION_ATTENDEE where status_1='APPROVED' group by parequest) as approved_amount,*
TARGET_AGGREGATION_ATTENDEE.level_id,
*(select*
TARGET_AGGREGATION_ATTENDEE.be_aggregation_pend_amount where level_id=(select min(level_id) from target_aggregation_attendee group by parequest)as req_amount

from TARGET_ATTENDEE,

TARGET_EMPLOYEE,

TARGET_PA_REQUEST,

TARGET_AGGREGATION_ATTENDEE

where TARGET_ATTENDEE.ATTENDEE_ID=TARGET_AGGREGATION_ATTENDEE.ATTENDEE_ID

and TARGET_EMPLOYEE.GE_ID=TARGET_AGGREGATION_ATTENDEE.REQUESTOR_ID

and TARGET_AGGREGATION_ATTENDEE.PA_REQUEST_ID=TARGET_PA_REQUEST.PA_REQUEST_ID

and to_char(TARGET_AGGREGATION_ATTENDEE.CREATED_DATE,'YYYY')=to_char(sysdate,'YYYY')

and TARGET_AGGREGATION_ATTENDEE.pa_request_id='NAM1421';
6363
945059 wrote:
i framed my query like tht:
correct me.
Forum FAQ

{thread:id=2174552}

Read it.

{message:id=9360002}

Read it.

This is now the fifth time you have been asked to read this and format your code in your two threads on this question.

There is no point in anyone responding to your posts until you give some indication that you are able to read them.
948062
suggest me how to do.
6363
What? Tell you how to read those links?
948062
i have to use in this query.

for given request id:
Requested amount sholud select min(level_id) corresponding value from table target aggregate attendee.
and Approved amount should select where status='Approved' for given request id.
select

TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,

TARGET_EMPLOYEE.GE_ID as requesterGEID,

TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,

TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,
TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,
TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
TARGET_ATTENDEE.COUNTRY as atcountry,
TARGET_AGGREGATION_ATTENDEE.REQUEST_TYPE as requesttype,
TARGET_PA_REQUEST.PA_REQUEST_ID as parequest,

TARGET_PA_REQUEST.EVENT_DATE as eventdate,
TARGET_PA_REQUEST.EVENT_DESCRIPTION as eventdesc,
TARGET_PA_REQUEST.CREATED_DATE as requestdate,
TARGET_PA_REQUEST.STATUS as status,
TARGET_AGGREGATION_ATTENDEE.status,

(select TARGET_AGGREGATION_ATTENDEE.be_aggregation_approved_amount from TARGET_AGGREGATION_ATTENDEE where status_1='APPROVED' group by parequest) as approved_amount,
TARGET_AGGREGATION_ATTENDEE.level_id,
(select
TARGET_AGGREGATION_ATTENDEE.be_aggregation_pend_amount where level_id=(select min(level_id) from target_aggregation_attendee group by parequest)as req_amount

from TARGET_ATTENDEE,

TARGET_EMPLOYEE,

TARGET_PA_REQUEST,

TARGET_AGGREGATION_ATTENDEE

where TARGET_ATTENDEE.ATTENDEE_ID=TARGET_AGGREGATION_ATTENDEE.ATTENDEE_ID

and TARGET_EMPLOYEE.GE_ID=TARGET_AGGREGATION_ATTENDEE.REQUESTOR_ID

and TARGET_AGGREGATION_ATTENDEE.PA_REQUEST_ID=TARGET_PA_REQUEST.PA_REQUEST_ID

and to_char(TARGET_AGGREGATION_ATTENDEE.CREATED_DATE,'YYYY')=to_char(sysdate,'YYYY')

and TARGET_AGGREGATION_ATTENDEE.pa_request_id='NAM1421';
Paul Horth
3360 wrote:
What? Tell you how to read those links?
From his latest response: yes you have to tell him how to read those links :-)
1 - 30
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 3 2012
Added on Sep 4 2012
#spatial
30 comments
287 views