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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

PL/SQL & Form Builder

561207Feb 11 2007 — edited Mar 4 2007

Hey

I used Oracle 8i when i was at uni to develop databases using PL/SQL and Form Builder. I was able to purchase Oracle 9i a few years so that i could practice my developing skills. Now i do not know what has happened to the Oracle 9i i bought, probably got misplaced when i moved house.
Does anyone know where i can purchase it from or download it from?
I need PL/SQL to program and create my tables and Form Builder to have the front end to link to my tables.

Any help would be most appreciated.

Kind Regards
Zak

BluShadow

user10594152 wrote:
Please let me know any better solutions for this query..??

Why not just..

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  select count(empno) as emps
  2       , count(mgr) as mgrs
  3       , sum(case when sal > 2000 then 1 else 0 end) as sals
  4* from emp
SQL> /

      EMPS       MGRS       SALS
---------- ---------- ----------
        14         13          6

SQL>

666352

TRy this query.

REgards Salim.

SELECT COUNT (empno) empno, COUNT (mgr) mgr,
       COUNT (CASE
                 WHEN sal > 2000
                    THEN 1
              END) sal
  FROM emp;

BluShadow

.. although your requirement seems a little odd.

Are you perhaps looking to count distinct values?

SQL> ed
Wrote file afiedt.buf

  1  select count(distinct empno) as emps
  2       , count(distinct mgr) as mgrs
  3       , count(distinct case when sal > 2000 then sal else null end) as sals
  4* from emp
SQL> /

      EMPS       MGRS       SALS
---------- ---------- ----------
        14          6          5

SQL>

Frank Kulash

Hi,

There's no need to use daul: all the values you need are in emp:

SELECT	COUNT (empno)		AS empno_cnt
,	COUNT (mgr)		AS mgr_cnt
,	COUNT ( CASE
		    WHEN  sal > 2000
		    THEN  sal
		    ELSE  NULL		-- This is the default, so you don't really need this line
		END
	      )			AS sal_2000_cnt
FROM	emp;

The tricky part is counting all emps and mgrs, but counting only some sals.
A WHERE clause applies to the whole query, so we don't want that.
A CASE statement can use the same kinds of conditions as a WHERE clause, but they only apply to one column, which is exactly what we want here.

Hesh

hope this is usefull..............

select empno,mgr,
(select count(sal) from emp  where sal>2000)  as sal
from(
select count(empno) as empno,count(mgr) as mgr from emp )

Edited by: user10734616 on Apr 8, 2009 6:02 AM

user10594152

Thanks for your immediate Reply...
I have been converting procedure SQL SERVER to Oracle..
They used temporary table .. But i want to avoid TEMP table

set @invent=(select count(inventoryReport)as invent from AdDashRoleProfile where inventoryReport != 1 and roleid=@roleid)    
set @development=(select count(development)as develop from AdDashRoleProfile where development != 1 and roleid=@roleid)    
set @Execution=(select count(Execution)as Exe from AdDashRoleProfile where Execution != 1 and roleid=@roleid)    
insert into @tempRole(inventoryReport,development,Execution) values(@invent,@development,@Execution)    
select * from @tempRole

So I changed my query...

SELECT (SELECT COUNT (inventoryreport)
          FROM addashroleprofile
         WHERE inventoryreport  != 1 AND roleid = v_roleid) inventoryreport,
       (SELECT COUNT (development)
          FROM addashroleprofile
         WHERE development  != 1 AND roleid = v_roleid) development,
       (SELECT COUNT (execution)
          FROM addashroleprofile
         WHERE execution  != 1 AND roleid = v_roleid) execution
  FROM DUAL;

Your solutions are good..
How can I apply it..???

666352

TRy this code.

REgards salim.

/* Formatted on 2009/04/08 09:22 (Formatter Plus v4.8.8) */
SELECT COUNT
            (CASE
                WHEN inventoryreport != 1 AND roleid = v_roleid
                   THEN inventoryreport
             END
            ) inventoryreport,
       COUNT (CASE
                 WHEN development != 1 AND roleid = v_roleid
                    THEN development
              END
             ) development,
       COUNT (CASE
                 WHEN execution != 1 AND roleid = v_roleid
                    THEN execution
              END
             ) execution
  FROM addashroleprofile;

Edited by: Salim Chelabi on 2009-04-08 06:20

1 person found this helpful

BluShadow

Answer

user10594152 wrote:
Your solutions are good..
How can I apply it..???

Like this:

SELECT COUNT(CASE WHEN inventoryreport != 1 AND roleid = v_roleid THEN inventoryreport END) inventoryreport
      ,COUNT(CASE WHEN development != 1 AND roleid = v_roleid THEN development END) development
      ,COUNT(CASE WHEN execution != 1 AND roleid = v_roleid THEN execution END) execution
FROM addashroleprofile;

Marked as Answer by user10594152 · Sep 27 2020

user10594152

Thank you... It works.. :):)

John Spencer

Blu:

Just curious. Why not:

SELECT COUNT(CASE WHEN inventoryreport != 1 THEN inventoryreport END) inventoryreport,
       COUNT(CASE WHEN development != 1 THEN development END) development,
      COUNT(CASE WHEN execution != 1 THEN execution END) execution
FROM addashroleprofile
WHERE roleid = v_roleid;

John

666352

Hi John,

Yeah, good remark.

REgards salim.

BluShadow

John Spencer wrote:
Blu:

Just curious. Why not:

SELECT COUNT(CASE WHEN inventoryreport != 1 THEN inventoryreport END) inventoryreport,
COUNT(CASE WHEN development != 1 THEN development END) development,
COUNT(CASE WHEN execution != 1 THEN execution END) execution
FROM addashroleprofile
WHERE roleid = v_roleid;

John

Yeah, did spot that myself after, but was a little busy to come back and edit. ;)

Aketi Jyuuzou

I arranged yours B-)
We can use NullIF

select count(nullIF(inventoryreport,1)) inventoryreport
       count(nullIF(development,1)) development
       count(nullIF(execution,1)) execution
  from addashroleprofile
 WHERE roleid = v_roleid;

1 - 13

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 1 2007

Added on Feb 11 2007

4 comments

535 views

SQL & PL/SQL

PL/SQL & Form Builder

Comments

Post Details