How to create a single SELECT with multiple conditions

I am trying to write a single SQL statement that will return to me a list of
Timesheet Charge codes that match the following criteria.  There are about 100,000 rows to choose from and at most I should get back

* The status (CHARGE.CHR_STATUS) must be = 'Opened'
* The charge description CHARGE.CHR_DESC cannot contain the letters '_WP_' or '_WM_'
* In the case where the project name TCPROJ.PRJ_NAME begins with 'ABC' then I only want the charge codes
  that are assigned to me as per the employee doing the timesheet.
 
Although this was written for SQL Server, it must work in SQL Server, Oracle and MySql

Tables

TCRES (Resources)
Resource Unique Key            TCRES.RES_KEY NUMERIC(11)

EMPLOYEE (Employees)
Employee Unique Key            EMPLOYEE.EMP_KEY NUMERIC(11)
Employee Resource Key      EMPLOYEE.EMP_RES NUMERIC(11)  foreign key to TCRES.RES_KEY

TSHEADER (Timesheet Header) [This table is the active table at the time the SQL is run so we always append 'AND TSH_KEY=12345' to the WHERE clause so we get the right Employee Key
Timsheet Unique Key            TSHEADER.TSH_KEY NUMERIC(11)
Timesheet Employee Key      TSHEADER.TSH_EMP NUMERIC(11)   foreign key to EMPLOYEE.EMP_KEY

TCPROJ  (Projects)
Project Unique Key            TCPROJ.PRJ_KEY NUMERIC(11)
Project Name                  TCPROJ.PRJ_NAME VARCHAR(30)

CHARGE  (Charge Codes)
Charge Unique Key            CHARGE.CHR_KEY NUMERIC(11)
Charge Code                   CHARGE.CHR_CODE VARCHAR(100)
Charge Description            CHARGE.CHR_DESC VARCHAR(50)
Charge Status                  CHARGE.CHR_STATUS VARCHAR(8)
Charge Project Key            CHARGE.CHR_PRJ NUMERIC(11)   foreign key to TCPROJ.PRJ_KEY

CHRRES  (Assigned charge codes)
Assignment Unique Key      CHRRES.CRS_KEY NUMERIC(11)
Assignment Charge Key      CHRRES.CRS_CHR NUMERIC(11)   foreign key to CHARGE.CHR_KEY
Assignment Resource Key      CHRRES.CRS_RES NUMERIC(11)   foreign key to TCRES.RES_KEY


SQL to SELECT out CHR_KEY so
SELECT CHR_KEY FROM CHARGE .....
Relationships

TSHEADER.TSH_KEY is always assigned a value so somewhere in the SELECT we must include 'AND TSH_KEY=12345'  (note the key is not staic, but assigned by the application)
The TCPROJ.PRJ_KEY is also assigned a non static value by the applicaiton depending on the project the user has previously selected.  So in the example below the key is 1.  This value is also used by the CHARGE.CHR_PRJ as that is the relationship.
LEFT JOIN TCPROJ ON TCPROJ.PRJ_KEY = CHARGE.CHR_KEY
JOIN EMPLOYEE ON EMPLOYEE.EMP_KEY = TSHEADER.TSH_EMP
LEFT JOIN TCRES ON TCRES.RES_KEY = EMPLOYEE.EMP_RES
LEFT JOIN CHRRES ON (CHRRES.CRS_CHR = CHARGE.CHR_KEY AND CHRRES.CRS_RES = TCRES.RES_KEY)

We've tried several ways and the closest we've come to is this, but still delivers more rows than we'd expect primarily the "CHR_DESC NOT LIKE '%_WM_%' AND CHR_DESC NOT LIKE '%_WP_%' " is failing
SELECT CHR_KEY, CHR_CODE, CHR_DESC, CHR_PARENT, CHR_PRJ FROM CHARGE  WHERE CHR_PRJ=1 AND CHR_KEY IN
(SELECT CHR_KEY FROM CHARGE WHERE CHR_STATUS<>'Closed' AND   ((CHR_KEY IN (SELECT CRS_CHR FROM CHRRES, EMPLOYEE, TSHEADER WHERE CRS_RES=EMP_RES AND EMP_KEY=TSH_EMP AND TSH_KEY=1) AND(
   CHR_PRJ IN (SELECT PRJ_KEY FROM TCPROJ WHERE PRJ_NAME LIKE 'ABC%'))   OR (CHR_PRJ IS NULL OR (CHR_DESC NOT LIKE '%_WM_%' AND CHR_DESC NOT LIKE '%_WP_%' AND CHR_PRJ NOT IN (SELECT PRJ_KEY FROM TCPROJ WHERE PRJ_NAME LIKE 'ABC%'))))))
hmstechsupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DavidSenior Oracle Database AdministratorCommented:
Oracle would work with the CASE statement, but I cannot speak to the other two.

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/case_statement.htm
0
Aneesh RetnakaranDatabase AdministratorCommented:
if you want to search for underscore, you should include it inside square brackets, otherwise, SQL engine will treat it as  any character

CHR_DESC NOT LIKE '%[_]WM[_]%' AND CHR_DESC NOT LIKE '%[_]WP[_]%'
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I presume your bracketing is just wrong ....

I took your specs, and tried to write it in "my style" and "my order"
SELECT CHR_KEY, CHR_CODE, CHR_DESC, CHR_PARENT, CHR_PRJ 
FROM CHARGE  
WHERE CHR_PRJ=1 
AND CHR_KEY IN ( SELECT CHR_KEY 
                   FROM CHARGE 
                  WHERE CHR_STATUS<>'Closed' 
		    AND CHR_DESC NOT LIKE '%_WM_%' 
                    AND CHR_DESC NOT LIKE '%_WP_%' 

                    AND (  CHR_PRJ NOT IN (SELECT PRJ_KEY FROM TCPROJ WHERE PRJ_NAME LIKE 'ABC%')
                        OR CHR_PRJ IS NULL 
                        OR (   CHR_PRJ IN (SELECT PRJ_KEY FROM TCPROJ WHERE PRJ_NAME LIKE 'ABC%')
                           AND CHR_KEY IN (SELECT CRS_CHR FROM CHRRES, EMPLOYEE, TSHEADER WHERE CRS_RES=EMP_RES AND EMP_KEY=TSH_EMP AND TSH_KEY=1 )
                           )
                        )
                  )

Open in new window


it should work in all 3 dbms
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alexander Eßer [Alex140181]Software DeveloperCommented:
if you want to search for underscore, you should include it inside square brackets, otherwise, SQL engine will treat it as  any character

CHR_DESC NOT LIKE '%[_]WM[_]%' AND CHR_DESC NOT LIKE '%[_]WP[_]%'

Brackets?!?
You should rather "escape" the underscore:
CHR_DESC NOT LIKE '%\_WM\_%' AND CHR_DESC NOT LIKE '%\_WP\_%' escape '\'

Open in new window

0
hmstechsupportAuthor Commented:
Exactly the help I needed to complete my solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.