| Select only One Case results [message #648680] |
Mon, 29 February 2016 08:53  |
 |
vradhak7
Messages: 1
Registered: February 2016
|
Junior Member |
|
|
Hello Oralcle SQL users,
I am trying to select the individual entries of a query which has two cases. But I want it to display only the results of ONE CASE. I want the query to pull data for only the CASE which produces "Incorrect" results. The query actually Sums the individual results into Category. But I need piece level information(m.trackingnumber) for the CASE "Incorrect". Your expertise would be greatly appreciated! Thanks!
Below is the query:
SELECT
m.trackingnumber
SUM (
CASE
WHEN (
CASE
WHEN(m.address1 IS NOT NULL
AND m.city IS NOT NULL
AND m.state IS NOT NULL
AND m.zip IS NOT NULL)
THEN 1
ELSE 0
END)>0
THEN 1
ELSE (
CASE
WHEN(m.zip IS NOT NULL
AND m.zip4 IS NOT NULL
AND m.dp IS NOT NULL)
THEN 1
ELSE 0
END)
END) AS "Correct",
COUNT(*)- (SUM (
CASE
WHEN (
CASE
WHEN(m.address1 IS NOT NULL
AND m.city IS NOT NULL
AND m.state IS NOT NULL
AND m.zip IS NOT NULL)
THEN 1
ELSE 0
END)>0
THEN 1
ELSE (
CASE
WHEN(m.zip IS NOT NULL
AND m.zip4 IS NOT NULL
AND m.dp IS NOT NULL)
THEN 1
ELSE 0
END
END)) AS "Incorrect"
FROM ops_owner.track_mail_item m
WHERE m.receivedate between to_date('01-31-2016 00:00:00','MM/DD/YYYY HH24:MI:SS') and to_date('02-06-2016 23:59:00','MM/DD/YYYY HH24:MI:SS')
ORDER BY 1;
[Edit MC: query with code tags:]
SELECT
m.trackingnumber
SUM (
CASE
WHEN (
CASE
WHEN(m.address1 IS NOT NULL
AND m.city IS NOT NULL
AND m.state IS NOT NULL
AND m.zip IS NOT NULL)
THEN 1
ELSE 0
END)>0
THEN 1
ELSE (
CASE
WHEN(m.zip IS NOT NULL
AND m.zip4 IS NOT NULL
AND m.dp IS NOT NULL)
THEN 1
ELSE 0
END)
END) AS "Correct",
COUNT(*)- (SUM (
CASE
WHEN (
CASE
WHEN(m.address1 IS NOT NULL
AND m.city IS NOT NULL
AND m.state IS NOT NULL
AND m.zip IS NOT NULL)
THEN 1
ELSE 0
END)>0
THEN 1
ELSE (
CASE
WHEN(m.zip IS NOT NULL
AND m.zip4 IS NOT NULL
AND m.dp IS NOT NULL)
THEN 1
ELSE 0
END
END)) AS "Incorrect"
FROM ops_owner.track_mail_item m
WHERE m.receivedate between to_date('01-31-2016 00:00:00','MM/DD/YYYY HH24:MI:SS') and to_date('02-06-2016 23:59:00','MM/DD/YYYY HH24:MI:SS')
ORDER BY 1;
[Updated on: Mon, 29 February 2016 09:01] by Moderator Report message to a moderator |
|
|
|
| Re: Select only One Case results [message #648681 is a reply to message #648680] |
Mon, 29 February 2016 08:58  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Quote: I want the query to pull data for only the CASE which produces "Incorrect" results.
So add this condition in the WHERE or HAVING clause.
[Updated on: Mon, 29 February 2016 09:00] Report message to a moderator |
|
|
|
| Re: Select only One Case results [message #648682 is a reply to message #648680] |
Mon, 29 February 2016 09:05 |
|
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Compare your query with and without code tags, which one is easier to read and understand?
Just add the clause (if I correctly understand what you want):
HAVING <expression for "Incorrect"> > 0
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
