Filter Stage Query

[DS_MJ]

Hello:

Am trying to get the following query in the filter stage.

(COL_NAME <> 'J' AND COL_NAME <> 'E') AND
(
(COL_NAME IN ('Y', 'BRZ', 'KC', 'T', 'TK')) AND
(SUBSTRING(ID,1,1) BETWEEN '0' AND '9') AND
(SUBSTRING(ID,2,1) BETWEEN 'A' AND 'Z') AND
(SUBSTRING(ID,12,6) NOT IN ('ZZZZZ', 'WWWWW', 'YYYYY')) AND
(SUBSTRING(ID,6,1) >= '7')
) OR
(
(COL_NAME IN ('CXL', 'PR')) AND
COL_NAME_2 = 'M' AND
(COL_NAME_3 = '2000000' OR COL_NAME_3 = '8310000') AND
(SUBSTRING(ID,7,2) >= '07')
) OR
(
(COL_NAME IN ('KPT')) AND
COL_NAME_2 = 'M' AND
(COL_NAME_3 = '2000000') AND
(SUBSTRING(ID,7,2) >= '07')
) OR
(
(COL_NAME IN ('KC')) AND
COL_NAME_2 = 'M' AND
(COL_NAME_3 = '2000000') AND
(SUBSTRING(ID,7,2) >= '07')
) OR
(
(COL_NAME IN ('B')) AND
(SUBSTRING(ID,1,1) BETWEEN 'A' AND 'Z') AND
(SUBSTRING(ID,2,1) BETWEEN 'A' AND 'Z') AND
(SUBSTRING(ID,3,1) BETWEEN '0' AND '9') AND
(SUBSTRING(ID,12,6) NOT IN ('ZZZZZ', 'WWWWW', 'YYYYY')) AND
(SUBSTRING(ID,6,1) >= '7')
)


But the job aborts with the following error:

Filter_52: Parse error: Expected comparison, 'between', or 'like' operator, got: "IN"
Parse error: Expected boolean operator (and, or) got: "("
Parse error: Expected comparison, 'between', or 'like' operator, got: "("
Parse error: Expected boolean operator (and, or) got: "ITMID"
Parse error: Expected 'is' statement ('is true', 'is false') or boolean operator (and, or) got: "BETWEEN"
Parse error: Expected comparison operator ('>', '<', ...) after literal, got: ")"
Parse error: Expected comparison, 'between', or 'like' operator, got: "("
Parse error: Expected boolean operator (and, or) got: "ITMID"
Parse error: Expected 'is' statement ('is true', 'is false') or boolean operator (and, or) got: "BETWEEN"
Parse error: Expected comparison operator ('>', '<', ...) after literal, got: ")"
Parse error: Expected comparison, 'between', or 'like' operator, got: "("
Parse error: Expected boolean operator (and, or) got: "ITMID"
Parse error: Expected 'is' statement ('is true', 'is false') or boolean operator (and, or) got: "NOT".

main_program: Creation of a step finished with status = FAILED.

Any guidence is deeply appreciated

Thanks in advance.

Best Regards

[ray.wurlod]

Looks like it doesn't like IN (in spite of what might be in the manual).

Try rewriting the IN clauses as multiple "=" operations joined by OR.

[DS_MJ]

Looks like it doesn't like IN (in spite of what might be in the manual).

Try rewriting the IN clauses as multiple "=" operations joined by OR. ...

Thanks ray.wurlod, appreciate it.

I broke the query and just tried the following in the Filter stage:

COL_NAME = 'Y'
OR COL_NAME = 'BRZ'
OR COL_NAME = 'KC'
OR COL_NAME = 'T'
OR COL_NAME = 'TK'
AND SUBSTRING(ID,1,1) BETWEEN 0 AND 9 AND
SUBSTRING(ID,2,1) BETWEEN 'A' AND 'Z'
AND SUBSTRING(ID,12,6) NOT = 'ZZZZZ'
AND SUBSTRING(ID,12,6) NOT = 'WWWWW'
AND SUBSTRING(ID,12,6) NOT = 'YYYYY'
AND SUBSTRING(ID,6,1) >= 7

and now it does not complain about the 'IN' but still the job aborts with the following error:

Filter_52: Parse error: Expected comparison, 'between', or 'like' operator, got: "("
Parse error: Expected boolean operator (and, or) got: "ITMID".
main_program: Creation of a step finished with status = FAILED.

I dont understand what it means..?

[DS_MJ]

Oooppps please ignore the above query:

Wrote the follwoing query in the filter stage without the 'IN"

COL_NAME = 'Y'
OR COL_NAME = 'BRZ'
OR COL_NAME = 'KC'
OR COL_NAME = 'T'
OR COL_NAME = 'TK'
AND SUBSTRING(ID,1,1) BETWEEN 0 AND 9 AND
SUBSTRING(ID,2,1) BETWEEN 'A' AND 'Z'
AND NOT SUBSTRING(ID,12,6) = 'ZZZZZ'
AND NOT SUBSTRING(ID,12,6) = 'WWWWW'
AND NOT SUBSTRING(ID,12,6) = 'YYYYY'
AND SUBSTRING(ID,6,1) >= 7

and now it does not complain about the 'IN' but still the job aborts with the following error:

Filter_52: Parse error: Expected comparison, 'between', or 'like' operator, got: "("
Parse error: Expected boolean operator (and, or) got: "ITMID".
main_program: Creation of a step finished with status = FAILED.

[us1aslam1us]

Hi DS

Don't you think, you forgot to seperate the conditions with brackets.

Thanks
Sam

[kumar_s]

Hi DS

Don't you think, you forgot to seperate the conditions with brackets.

Thanks
Sam

Do you mean t say like this

COL_NAME = 'Y'
OR COL_NAME = 'BRZ'
OR COL_NAME = 'KC'
OR COL_NAME = 'T'
OR COL_NAME = 'TK'
AND (SUBSTRING(ID,1,1) BETWEEN 0 AND 9) AND
(SUBSTRING(ID,2,1) BETWEEN 'A' AND 'Z')
AND NOT (SUBSTRING(ID,12,6) = 'ZZZZZ')
AND NOT (SUBSTRING(ID,12,6) = 'WWWWW')
AND NOT (SUBSTRING(ID,12,6) = 'YYYYY')
AND (SUBSTRING(ID,6,1) >= 7)

[ray.wurlod]

Oooppps please ignore the above query:

Wrote the follwoing query in the filter stage without the 'IN"

COL_NAME = 'Y'
OR COL_NAME = 'BRZ'
OR COL_NAME = 'KC'
OR COL_NAME = 'T'
OR COL_NAME = 'TK'
AND SUBSTRING(ID,1,1) BETWEEN 0 AND 9 AND
SUBSTRING(ID,2,1) BETWEEN 'A' AND 'Z'
AND NOT SUBSTRING(ID,12,6) = 'ZZZZZ'
AND NOT SUBSTRING(ID,12,6) = 'WWWWW'
AND NOT SUBSTRING(ID,12,6) = 'YYYYY'
AND SUBSTRING(ID,6,1) >= 7

and now it does not complain about the 'IN' but still the job aborts with the following error:

Filter_52: Parse error: Expected comparison, 'between', or 'like' operator, got: "("
Parse error: Expected boolean operator (and, or) got: "ITMID".
main_program: Creation of a step finished with status = FAILED.

Are you sure you're checking on the same job you're designing in? There is no column or any other form of identifier called ITMID in the SQL that you've posted. Maybe you better post the entire SQL!

[rwierdsm]

Yes, there are more inconsistencies, e.g.

In the first message, the code is

Code: Select all

(SUBSTRING(ID,1,1) BETWEEN '0' AND '9') AND 

later it is

Code: Select all

SUBSTRING(ID,1,1) BETWEEN 0 AND 9 AND 

We need to see the exact code that is generating the error.

Rob