OraFAQ Forum: SQL & PL/SQL » ALL condition

Home » SQL & PL/SQL » SQL & PL/SQL » ALL condition (Oracle 8.1.7.0.0, Windows 2004)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ALL condition [message #609257]

Tue, 04 March 2014 21:44

kumarvk
Messages: 214
Registered: July 2004

Senior Member

Hi Experts,

I have a HDR & JOBS tables. In jobs table there is a column status. There will repeating of RONO in jobs but the job code is unique. The status for the job code varies from R,V,F,O.

Now I want a sql in which I want the RONO,NAME,AMOUNT to be displayed only if all the job codes in ROJOBS is either V nor F only. If one V, and one O for the RONO in the job codes I don't want to be displayed.

RONO is the index key for ROHDR and ROJOBS.

I have attached the Table with create and insert for ROHDR and ROJOBS.

Can anyone help?

Report message to a moderator

Re: ALL condition [message #609258 is a reply to message #609257]

Tue, 04 March 2014 21:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have attached the Table with create and insert for ROHDR and ROJOBS.
I disagree.

> (Oracle 8.1.7.0.0, Windows 2004)
nice unsupported combination.
Why is it OK to upgrade OS but not OK to upgrade Oracle?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: ALL condition [message #609260 is a reply to message #609258]

Tue, 04 March 2014 22:12

kumarvk
Messages: 214
Registered: July 2004

Senior Member

I have formatted the attached sql. Is it OK? I want to upgrade but Headoffice if not doing so need there approval.

Report message to a moderator

Re: ALL condition [message #609262 is a reply to message #609260]

Tue, 04 March 2014 22:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have formatted the attached sql.
attached where?

why can't you just post the SQL directly here?

Report message to a moderator

Re: ALL condition [message #609341 is a reply to message #609262]

Wed, 05 March 2014 20:21

kumarvk
Messages: 214
Registered: July 2004

Senior Member

Hi,

As requested posted the create table scripts and insert scripts for both the tables.

CREATE TABLE MSS.ROHDR
(
   RONO     VARCHAR2 (10),
   NAME     VARCHAR2 (10),
   RODT     DATE,
   AMOUNT   NUMBER
)
TABLESPACE MSSU
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (INITIAL 1 M
         NEXT 1 M
         MINEXTENTS 1
         MAXEXTENTS UNLIMITED
         PCTINCREASE 0
         FREELISTS 1
         FREELIST GROUPS 1
         BUFFER_POOL DEFAULT)
LOGGING
NOCACHE
NOPARALLEL;

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148850',
            'SAM',
            NULL,
            500);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148851',
            'SAM',
            NULL,
            100);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148852',
            'JEFF',
            NULL,
            150);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148853',
            'NOEL',
            NULL,
            200);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148854',
            'NOEL',
            NULL,
            350);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148855',
            'NOEL',
            NULL,
            500);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148856',
            'JEFF',
            NULL,
            400);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148857',
            'SAM',
            NULL,
            250);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148858',
            'SAM',
            NULL,
            250);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148859',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148860',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148861',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148862',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148863',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148864',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148865',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148866',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148867',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148868',
            'JEFF',
            NULL,
            1000);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148869',
            'SAM',
            NULL,
            1500);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148870',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148871',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148872',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148873',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148874',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148875',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148876',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148877',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148878',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148879',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148880',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148881',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148882',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148883',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148884',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148885',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148886',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148887',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148888',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148889',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148890',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148891',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148892',
            NULL,
            NULL,
            NULL);

INSERT INTO ROHDR (RONO,
                   NAME,
                   RODT,
                   AMOUNT)
  VALUES   ('RA0148893',
            NULL,
            NULL,
            NULL);

COMMIT;

CREATE TABLE MSS.ROJOB
(
   RONO     VARCHAR2 (10),
   JTYPE    VARCHAR2 (2),
   JOB      VARCHAR2 (10),
   STATUS   VARCHAR2 (1)
)
TABLESPACE MSSU
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (INITIAL 1 M
         NEXT 1 M
         MINEXTENTS 1
         MAXEXTENTS UNLIMITED
         PCTINCREASE 0
         FREELISTS 1
         FREELIST GROUPS 1
         BUFFER_POOL DEFAULT)
LOGGING
NOCACHE
NOPARALLEL;

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148850',
            'CA',
            'T0102',
            'O');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148850',
            'IN',
            'T0103',
            'V');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148851',
            'IN',
            'T0104',
            'V');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148852',
            'IN',
            'T0103',
            'V');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148853',
            'WA',
            'T0106',
            'F');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148854',
            'IN',
            'T0102',
            'V');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148854',
            'WA',
            'T0103',
            'V');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148855',
            'WA',
            'T0102',
            'R');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148856',
            'CR',
            'T0105',
            'R');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148857',
            'CA',
            'T0108',
            'F');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148857',
            'WA',
            'T0107',
            'F');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148858',
            'IN',
            'T0106',
            'R');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148859',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148860',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148861',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148862',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148863',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148864',
            'CR',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148865',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148866',
            'CA',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148867',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148868',
            'CR',
            'T0105',
            'R');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148868',
            'IN',
            'T0101',
            'O');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148868',
            'WA',
            'T0102',
            'F');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148869',
            'CA',
            'T0101',
            'V');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148869',
            'IN',
            'T0102',
            'F');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148869',
            'WA',
            'T0103',
            'V');

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148870',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148871',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148872',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148873',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148874',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148875',
            'CA',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148876',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148877',
            'CA',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148878',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148879',
            'CA',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148880',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148881',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148882',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148883',
            'CA',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148884',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148885',
            'CA',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148886',
            'CA',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148886',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148886',
            'WA',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148887',
            'CA',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148888',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148889',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148890',
            'CA',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148891',
            'WA',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148892',
            'CA',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148893',
            'IN',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148893',
            'WA',
            NULL,
            NULL);

INSERT INTO ROJOB (RONO,
                   JTYPE,
                   JOB,
                   STATUS)
  VALUES   ('RA0148894',
            'IN',
            NULL,
            NULL);

COMMIT;

Report message to a moderator

Re: ALL condition [message #609344 is a reply to message #609341]

Wed, 05 March 2014 20:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

the CREATE TABLE should look like below

CREATE TABLE ROHDR
(
   RONO     VARCHAR2 (10),
   NAME     VARCHAR2 (10),
   RODT     DATE,
   AMOUNT   NUMBER
);

because we may not have your schema or tablespaces.

Report message to a moderator

Re: ALL condition [message #609345 is a reply to message #609344]

Wed, 05 March 2014 20:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I was able to make your tables & load the sample data.

Now all we need from you is for you to explain & show what the expected & desired result set should be.

Report message to a moderator

Re: ALL condition [message #609348 is a reply to message #609345]

Wed, 05 March 2014 20:56

kumarvk
Messages: 214
Registered: July 2004

Senior Member

Hi,

My current sql

SELECT   a.name, a.rono, a.amount
  FROM   rohdr a, rojob b
 WHERE   a.rono = b.rono AND b.status IN ('V', 'F');

Current result:

NAME RONO AMOUNT
SAM RA0148850 500
SAM RA0148851 100
JEFF RA0148852 150
NOEL RA0148853 200
NOEL RA0148854 350
NOEL RA0148854 350
SAM RA0148857 250
SAM RA0148857 250
JEFF RA0148868 1000
SAM RA0148869 1500
SAM RA0148869 1500
SAM RA0148869 1500

I need the result like this no duplicates rows and total amount;

NAME RONO AMOUNT
SAM RA0148850 500.00
SAM RA0148851 100.00
JEFF RA0148852 150.00
NOEL RA0148853 200.00
NOEL RA0148854 700.00
SAM RA0148857 500.00
JEFF RA0148868 1,000.00
SAM RA0148869 4,500.00

Report message to a moderator

Re: ALL condition [message #609349 is a reply to message #609348]

Wed, 05 March 2014 21:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

  1  SELECT   a.name, a.rono, sum(a.amount)
  2    FROM   rohdr a, rojob b
  3   WHERE   a.rono = b.rono AND b.status IN ('V', 'F')
  4  GROUP BY A.NAME, A.RONO
  5* ORDER BY  2
SQL> /

NAME       RONO       SUM(A.AMOUNT)
---------- ---------- -------------
SAM        RA0148850            500
SAM        RA0148851            100
JEFF       RA0148852            150
NOEL       RA0148853            200
NOEL       RA0148854            700
SAM        RA0148857            500
JEFF       RA0148868           1000
SAM        RA0148869           4500

8 rows selected.

Report message to a moderator

Re: ALL condition [message #609350 is a reply to message #609349]

Wed, 05 March 2014 21:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/174502/

please read URL above & then comply on all future posts

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: ALL condition [message #609351 is a reply to message #609349]

Wed, 05 March 2014 21:19

kumarvk
Messages: 214
Registered: July 2004

Senior Member

Hi,

Thanks for your reply. But you see if the job codes in rojobs has a mix of O,F,V or O,R,V I don't want that RONO to be displayed. Only if all the jobs codes has status F,V for that RONO need to be displayed.

Report message to a moderator

Re: ALL condition [message #609363 is a reply to message #609351]

Thu, 06 March 2014 00:37

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So what should be the result from the data you gave?

Report message to a moderator

Re: ALL condition [message #609364 is a reply to message #609363]

Thu, 06 March 2014 01:00

kumarvk
Messages: 214
Registered: July 2004

Senior Member

For example

RA0148850 has got status O and V this should not come.

RA0148868 has got status O,R and V this should not come.

I think now you can understand that the job codes for which the Status is only V or F should be displayed. If there is a mix like the above should not be displayed.

Report message to a moderator

Re: ALL condition [message #609366 is a reply to message #609364]

Thu, 06 March 2014 01:17

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can start with BlackSwan query:

SELECT   a.name, a.rono, sum(a.amount)
FROM   rohdr a, rojob b
WHERE   a.rono = b.rono AND b.status IN ('V', 'F')
  and not exists (select null from rojob c where a.rono = c.rono and c.status not in ('V','F'))
GROUP BY A.NAME, A.RONO
ORDER BY  2
/

[Updated on: Thu, 06 March 2014 01:20]

Report message to a moderator

Re: ALL condition [message #609368 is a reply to message #609366]

Thu, 06 March 2014 01:23

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Or

SELECT   a.name, a.rono, sum(a.amount)
FROM   rohdr a, rojob b
WHERE   a.rono = b.rono 
GROUP BY A.NAME, A.RONO
having sum(decode(b.status, 'V',0, 'F',0, 1)) = 0
ORDER BY  2
/

[Updated on: Thu, 06 March 2014 02:07]

Report message to a moderator

Re: ALL condition [message #609370 is a reply to message #609368]

Thu, 06 March 2014 01:55

kumarvk
Messages: 214
Registered: July 2004

Senior Member

Hi,

Thank you so much both scripts works.

Report message to a moderator

Previous Topic:	PlSql open read cell in excel file!!
Next Topic:	Sending UTL_SMTP mail with jpg picture as header inside an e-mail

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 21:59:26 CDT 2024