Home » SQL & PL/SQL » SQL & PL/SQL » One more condition in this script (Oracle,8.1.7.0.0,Windows 2003 Server)
| One more condition in this script [message #610937] |
Wed, 26 March 2014 03:19  |
kumarvk
Messages: 214
Registered: July 2004
|
Senior Member |
|
|
My current condition:
SELECT subm_id, count(car_no)
FROM CCR
WHERE pohd_cfm_date <= SYSDATE
AND pohd_cfm_date IS NOT NULL
AND (ledger_in_date IS NULL OR ledger_in_date > SYSDATE)
AND (sihd_si_date IS NULL OR sihd_si_date > SYSDATE)
GROUP BY subm_id;
I am getting the correct result.
Now I want to add onemore condition along with this
SELECT subm_id, count(car_no)
FROM CCR
WHERE pohd_cfm_date <= SYSDATE
AND pohd_cfm_date IS NOT NULL
AND (ledger_in_date IS NULL OR ledger_in_date > SYSDATE)
AND (sihd_si_date IS NULL OR sihd_si_date > SYSDATE)
and car_no is null --new condition
GROUP BY subm_id;
If I add the new condition car_no is null I am not getting the correct result. Can anyone help?
CREATE TABLE CCR
(
CAR_NO VARCHAR2(9),
SUBM_ID VARCHAR2(10),
LEDGER_IN_DATE DATE,
POHD_CFM_DATE DATE,
SIHD_SI_DATE DATE
)
PCTUSED 65
PCTFREE 20
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 16M
NEXT 16400K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
(NULL, '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
(NULL, '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
('TNM00133', '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('02/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
(NULL, '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
('TNM00128', '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('02/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
('TNM00129', '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('02/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
('TNM00130', '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('02/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
(NULL, '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
(NULL, '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
(NULL, '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
(NULL, '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
(NULL, '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
(NULL, '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
(NULL, '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
('TNM00131', '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('02/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ccr
(CAR_NO, SUBM_ID, LEDGER_IN_DATE, POHD_CFM_DATE, SIHD_SI_DATE)
Values
('TNM00132', '4732', NULL, TO_DATE('01/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('02/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
[Updated on: Wed, 26 March 2014 03:21] Report message to a moderator |
|
|
|
|
|
|
|
| Re: One more condition in this script [message #610940 is a reply to message #610939] |
Wed, 26 March 2014 03:34  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
This is my result:orclz> SELECT subm_id, count(car_no)
2 FROM CCR
3 WHERE pohd_cfm_date <= SYSDATE
4 AND pohd_cfm_date IS NOT NULL
5 AND (ledger_in_date IS NULL OR ledger_in_date > SYSDATE)
6 AND (sihd_si_date IS NULL OR sihd_si_date > SYSDATE)
7 and car_no is null --new condition
8 GROUP BY subm_id;
SUBM_ID COUNT(CAR_NO)
---------- -------------
4732 0
orclz> |
|
|
|
|
|
|
|
|
|
|
|
| Re: One more condition in this script [message #611041 is a reply to message #611027] |
Thu, 27 March 2014 03:35 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
To clarify:
COUNT(column) returns the count of non-nbull values in column.
So
SELECT COUNT(column)
FROM ....
WHERE column IS NULL
Must return 0 by definition.
And in your sample data only 6 records have a not null car_no.
Your sample data can never have given a value of 16.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 08:01:36 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
