HELP - Can someone review this query and tell me where my SQL is wrong?

Toad Data Point
1

SELECT SERVICE_CENTER,

PRODUCT_CODE,

COMPANY_CODE,

REVENUE_SORT_CODE,

CONTROL_TYPE,

HISTORY_DATE,

STATUS_CODE

　

FROM CLIENT_HISTORY

　

WHERE (HISTORY_DATE = TO_DATE (‘2011-05-01 00:00:00’, ‘yyyy/mm/dd hh24:mi:ss’))

AND STATUS_CODE = ‘A’

AND CONTROL_TYPE NOT IN (‘X’,‘W’,‘O’)

AND SERVICE_CENTER IN (‘0010’,‘0060’,‘0056’,‘0040’,‘0020’,‘0055’,‘0052’,‘0032’,‘0030’,‘0036’,‘0034’,‘0062’,‘0069’,‘0070’,‘0073’,‘0075’,‘0076’)

SELECT SERVICE_CENTER,

PRODUCT_CODE,

COMPANY_CODE,

REVENUE_SORT_CODE,

CONTROL_TYPE,

HISTORY_DATE,

STATUS_CODE

　

FROM CLIENT_HISTORY

　

WHERE (HISTORY_DATE = TO_DATE (‘2011-05-01 00:00:00’, ‘yyyy/mm/dd hh24:mi:ss’))

AND STATUS_CODE = ‘A’

AND CONTROL_TYPE NOT IN (‘X’,‘W’,‘O’)

AND ((SERVICE_CENTER IN (‘0056’,‘0040’,‘0020’,‘0055’,‘0052’,‘0032’,‘0030’,‘0036’,‘0034’,‘0062’,‘0069’,‘0070’,‘0073’,‘0075’,‘0076’))

OR (SERVICE_CENTER IN(‘0060’,‘0010’) AND (REVENUE_SORT_CODE IS NULL OR REVENUE_SORT_CODE <> ‘8’ )))

AND ((PRODUCT_CODE NOT IN(‘3E’,‘3F’,‘42’,‘2R’,‘87’))

OR (PRODUCT_CODE IN (‘46’,‘47’,‘8Y’,‘8Z’) AND (REVENUE_SORT_CODE IS NULL OR REVENUE_SORT_CODE <> ‘==’)))

Again, I think my errors boil down to improper placement of text and/or commas, etc. Any assistance would be greatly appreciated. Thanks.

The following query runs and basically overselects by about 19,000 records - I can then bring the results into Excel or a db tool and delete down to the desired results. For some reason, Toad is not recognizing the code and not excluding things I want to exclude. Here is the query that runs fine, but overselects a bit:

AND PRODUCT_CODE NOT IN(‘3E’,‘3F’,‘42’,‘2R’,‘87’)

In addition to these criteria, I also want to add these two criteria which makes the whole thing break down:

(PRODUCT_CODE IN(46,47,8Y,8Z) AND REVENUE_SORT_CODE <> ‘==’)

(SERVICE_CENTER IN(‘0010’,‘0060’) AND REVENUE_SORT_CODE <> ‘8’)

HELP!!!

Below is the query that runs but yields crazy, incorrect results:

2

I assume this is a connection to Oracle. Are you using an OCI client, Direct Connect or ODBC to connect?

Also, it would be helpful if you could attached or email the DDL of the table and a small set of sample data. A screenshot of the table definition from the table editor and an export of some of the data to csv would suffice.

Debbie

3

Yes, this is an Oracle connection using a DataDirect ODBC for Oracle driver ( Oracle Wire Protocol). Unfortunately, my company would flip if I release ANY actual data so that’s not an option at this point. I’ve noticed Toad is very fickle about the way the parantheses are used, how many, etc. I’m guessing this is going to be my issue. Also, would using the sub-query function in Toad possible solve my issue? I’ve played around with it before but could not get it to run in this case. I may not know enough about that feature in particular to actually use it.

Thanks.

4

Can you atleast provide the data types of the columns in the table? For me to look into this I have to recreate the table and data and if a datatype of a column is part of the issue (which that can be the case) then I may miss finding the issue.

Debbie

5

Absolutely, here are the fields/types:

FIELD
TYPE
SERVICE_CENTER
VARCHAR2(4)
PRODUCT_CODE
VARCHAR2(2)
COMPANY_CODE
VARCHAR2(6)
HISTORY_DATE
DATE
CONTROL_TYPE
VARCHAR2(1)
REVENUE_SORT_CODE
VARCHAR2(2)
STATUS_CODE
VARCHAR2(1)
Thanks.

6

Sorry about that, copy and paste didn’t seem to work:

service_center varchar2(4)
product_code varchar2(2)
company_code varchar2(6)
history_date date
control_type varchar2(1)
revenue_sort_code varchar2(2)
status_code varchar2(1)

thanks

7

WHERE (HISTORY_DATE > TO_DATE (‘2011-05-01’, ‘yyyy/mm/dd’) and

Debbie

I created a table and inserted some data to fit your criteria and the SQL returns data as expected. But that is only true is the date is an exact match on minutes and seconds.

I suspect that the dates are the issue here. If you need all enteries on a specific day you need to give a range. Try this below and see if that clears up the issue.

HISTORY_DATE < TO_DATE (‘2011-05-02’, ‘yyyy/mm/dd’))

8

Thanks for pointing that out Debbie, but unfortunately that’ not the case in this instance. Every entry has a timestamp of 12:00:00 AM tacked on to the back of the date given, rendering the timestamp portion of the records essentially useless and irrelevant. Ultimately I think my problem is that I’m simpy not writing the subqueries correctly regarding the additional service center and product code criteria. In English this is what I’m trying to add to my working query below:

SELECT SERVICE_CENTER,
PRODUCT_CODE,
COMPANY_CODE,
REVENUE_SORT_CODE,
CONTROL_TYPE,
HISTORY_DATE,
STATUS_CODE

FROM CLIENT_HISTORY

WHERE (HISTORY_DATE = TO_DATE (‘2011-05-01 00:00:00’, ‘yyyy/mm/dd hh24:mi:ss’))

AND STATUS_CODE = ‘A’

AND CONTROL_TYPE NOT IN (‘X’,‘W’,‘O’)

AND SERVICE_CENTER IN (‘0056’,‘0040’,‘0020’,‘0055’,‘0052’,‘0032’,‘0030’,‘0036’,‘0034’,‘0062’,‘0069’,‘0070’,‘0073’,‘0075’,‘0076’)

AND PRODUCT_CODE NOT IN(‘3E’,‘3F’,‘42’,‘2R’,‘87’)

This query runs perfectly by overselects by about 19k records. The result I’m needing to get in this case is around 200k records, instead I’m getting approx 219k.

What I’m trying to add to this query, in English, are these two components:

1)Include service centers 0060 and 0010 as well, but only when the rev sort code is NOT EQUAL to 8.

2)Exclude all records when product codes 46,47,8Y,8Z have a rev sort code equal to the weird symbol of ‘==’ (46,47,8y,8z are already included in my results but i need to filter them further, removing them where the rev sort cd is ‘==’)

Hopefully I explained this correctly. Thanks for all of your help thus far. If I can get this query to run successfully, I will be able to use Toad going further…instead of Crystal!!

9

Sounds like you need to play with the parenthesis on the items you listed.

Or, you could try created separate queries with your where criteris and union then together.

Debbie

10

I’ve played with the parenthesis so much it’s driving me insane! I’ve tried every combination possible. It gets to the point where Toad won’t even read the rest of the code or totally ignores it.

The only thing I haven’t tried is a union. I’ll give that a shot. Thanks for pointing that out.