ADD another where clause?

Question

ADD another where clause?

AlexSQLForums

SSChampion

Points: 14277
More actions
June 26, 2006 at 9:48 pm

#118516

Hi all how can i add another where clause to this sql statement?
i need to add a second set of columns with this where clause
AND (B.ACCOUNT = '13400' OR SUBSTR(B.ACCOUNT,1,2) = '74')
so the result set would look like:
DESCR REQS UNAPPROVEDREQS REQS UNAPPROVEDREQS
BOOK1 34 1 32 4
BOOO2 37 6 21 2
BOOK3 54 4 16 3
The problem is the where clause in the subquery.
I added the where clause and it gave the wrong resultset
i tried writing another exact statement and then union them both, but i only get two columns reqs and unapprovedreqs.
I need to get another set of the same 2 columns but with above where clause
here is the sql:
SELECT B.DESCR,
SUM(CASE WHEN TO_CHAR(A.REQ_DT, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY') THEN 1 ELSE 0 END) Reqs,
SUM(CASE WHEN TO_CHAR(A.REQ_DT, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY') THEN
(CASE WHEN A.REQ_STATUS IN ('A', 'O') THEN 0 ELSE 1 END) ELSE 0 END) UnapprovedReqs, 'ABCD' AS Type
FROM PS_REQ_HDR A, PS_BUS_UNIT_TBL_FS B
WHERE
A.REQ_ID NOT LIKE 'CNV%'
AND A.REQ_STATUS NOT IN ('X')
AND EXISTS (SELECT 'X' FROM PS_REQ_LN_DISTRIB B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.REQ_ID = B.REQ_ID
AND (B.ACCOUNT = '17100' OR SUBSTR(B.ACCOUNT,1,2) = '33'))
AND A.BUSINESS_UNIT = B.BUSINESS_UNIT
GROUP BY B.DESCR
THANKS FOR ALL YOUR HELP
Alex S

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

SELECT B.DESCR,

SUM(CASE WHEN TO_CHAR(A.REQ_DT, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY') THEN 1 ELSE 0 END) Reqs, SUM(CASE WHEN TO_CHAR(A.REQ_DT, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY') THEN

(CASE WHEN A.REQ_STATUS IN ('A', 'O') THEN 0 ELSE 1 END) ELSE 0 END) UnapprovedReqs, 'ABCD' AS Type

FROM PS_REQ_HDR A, PS_BUS_UNIT_TBL_FS B

WHERE

A.REQ_ID NOT LIKE 'CNV%'

AND A.REQ_STATUS NOT IN ('X')

AND EXISTS (SELECT 'X' FROM PS_REQ_LN_DISTRIB B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.REQ_ID = B.REQ_ID

AND (B.ACCOUNT = '17100' OR SUBSTR(B.ACCOUNT,1,2) = '33'))

AND A.BUSINESS_UNIT = B.BUSINESS_UNIT

AND (B.ACCOUNT = '13400' OR SUBSTR(B.ACCOUNT,1,2) = '74')

GROUP BY B.DESCR

N 56°04'39.16"
E 12°55'05.25"

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Just some advice.......that looks like ORACLE syntax (TO_CHAR, SYSDATE, SUBSTR), not SQL Server syntax. You might do better by posting ORACLE queries in an ORACLE site. While some of us might have experience with ORACLE, most likely it's going to be limited and you will probably get answers using SQL Server syntax.

-SQLBill

AlexSQLForums SSChampion Points: 14277 More actions · Answer 3

SQLBill yes that's oracle syntax.

I won't post anymore oracle questions.

I'm a sqldba but have been covering oracle stuff for a few weeks until my company finds another oracledba.

I've actually figured it out.

created a view with union between 2 statements with a dummy id field.

and then used this to select what i needed.

select

a.descr,a.reqs,a.unapprovedreqs, b.reqs,b.unapprovedreqs from (select * from temp1 where id=1) a, (select * from temp1 where id=2) b where a.descr=b.descr

Thanks

Alex S

SQLBill SSC Guru Points: 51440 More actions · Answer 4

It's not that you shouldn't post Oracle questions. You'll see others do it since some people have to link Oracle and SQL Server databases. However, you should let everyone know it's Oracle, or you will get solutions using TSQL not PL/SQL.

-SQLBill