how to combine declare and select statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to combine declare and select statements

Author

Topic

sanramcp
Starting Member

1 Post

Posted - 2008-07-25 : 06:09:45

Hi guys......i am new to this forum......i have a question regarding the following code....could anyone tell me if it is possible to have Delcare, select statements in one code like the below.....i tried to run this so many times and i got the errors, please help me.......

DECLARE A DATE;
B DATE;
GP VARCHAR2(20);
HOS VARCHAR2(20);
WD VARCHAR2(20);
NO VARCHAR2(10);
ST DATE;
ED DATE;
LOS NUMBER;
BEGIN
A:= TO_DATE('07-21-2007','MM-DD-YYYY');
B:= TO_DATE('06-23-2007', 'MM-DD-YYYY');

SELECT gp.org_pas_code,hos.org_short_name,koi.koi_sepe_short_name,koi.koi_ccs_pid,koi.koi_star
te,koi.koi_end_date,sum(least(trunc
(nvl(koi.koi_end_date,'23-JUL-2008')),'23-JUL-2008')
- greatest(trunc
(koi.koi_start_date ),'21-JUL-2008')) INTO GP,HOS,WD,NO,ST,ED,LOS

from organisations gp
,organisations hos
,people per
,gp_registrations gpr
,addresses adr
,address_usages adu
,korner_intervals koi
where koi.koi_ccs_specialty in ('5','21','31')
and gpr.gpr_status(+)!= 'X'
and adu.adu_status(+) != 'X'
and koi.koi_status in ('C','O')
and hos.org_id = koi.koi_Sepe_located_at
and gpr.gpr_gp_org_id = gp.org_id(+)
and adu.adu_address_type(+) = 'HOME'
and adu.adu_start_date(+) < nvl(koi.koi_end_date,sysdate)
and NVL(adu.adu_end_date(+),sysdate) >= trunc(nvl(koi.koi_end_date,sysdate))
and gpr.gpr_registration_type(+) = 'NORMAL'
and gpr.gpr_start_date(+) < nvl(koi.koi_end_date,sysdate)
and NVL(gpr.gpr_end_date(+),sysdate) >= trunc(nvl(koi.koi_end_date,sysdate))
AND adu.adu_add_id = adr.add_id(+)
AND koi.koi_ccs_per = adu.adu_per_id(+)
and koi.koi_ccs_per = gpr.gpr_per_id(+)
AND adu.adu_add_id = adr.add_id(+)
and gpr.gpr_per_id(+) = koi.koi_ccs_per
and koi.koi_ccs_per = per.per_id
and trunc(nvl(koi.koi_end_date,'23-JUL-2008'))
>= '21-JUL-2008'
and trunc(koi.koi_start_date)< '23-JUL-2008'
and koi.koi_sepe_short_name = 'WD';
END;
/
DECLARE A DATE;
*
ERROR at line 1:
ORA-00937: not a single-group group function
ORA-06512: at line 14

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-25 : 06:15:22

This is a Microsoft SQL Server forum. Please post your question on oracle in orafaq.com or dbforums.com

KH
[spoiler]Time is always against us[/spoiler]