Definition Query to Python script on Oracle 9i and ArcGIS 9.3.1

Question

SUBTYPECD=1 AND SERIESNAME NOT LIKE '%-%' AND OPERATIONALSTATUS IS NULL OR SUBTYPECD=1 AND SERIESNAME NOT LIKE '%-%' AND OPERATIONALSTATUS = 976

The above is my def query in ArcMap against ArcSDE 9.3.1 on oracle 9i with python 2.5. I want to be using the gp.makefeaturelayer with the sql parameter filled in with a rendition of the above.

ps I started to ask this question because it seemed daunting and I am in a crunch..then figured it out so I thought I'd finish the post for those to come.

• Justin: and I welcome any critic of my sql..thanks community!
• Brad Nesom: SUBTYPECD=1 AND SERIESNAME NOT LIKE '%-%' AND OPERATIONALSTATUS IN (NULL, 976)
• Justin: @Brad, this didn't seem to work..I think..because, as I found out today, querying for NULL requires an IS operator and not and = which I assume is being replicated in the IN operator. This may be because oracle. Thanks anyways..

Answer 1

Python allows you to use either single or double quotes for strings, so you can embed one within the other:

"embedding a 'single quote' in double quotes"
'embedding a "double quote" in single quotes'

So you could have gotten away with skipping all those concatenations, just using:

"SERIESNAME NOT LIKE '%-%' AND SUBTYPECD=1 AND OPERATIONALSTATUS IS NULL OR SUBTYPECD=1 AND SERIESNAME NOT LIKE '%-%' AND OPERATIONALSTATUS=976"

Answer 2

"SERIESNAME NOT LIKE" + "'" + "%-%" + "'" + "AND SUBTYPECD=1 AND OPERATIONALSTATUS IS NULL OR SUBTYPECD=1 AND SERIESNAME NOT LIKE" + "'" + "%-%" + "'" + " AND OPERATIONALSTATUS=976"

Answer 3

I can't confirm this for ArcMap 9.3 (the documentation makes no mention of it), but in ArcMap 10, you can embed an entire argument in triple quotes to avoid issues with concatenation and escape characters.

An example from the Make Feature Layer Tool:

Or you could enclose the entire string in triple quotes without escaping: 
""" "CITY_NAME" = 'Chicago' """

• jburka: That's actually just another Python convention. Triple-quoted strings can span newlines and can contain both single and double quotes without escaping them.
• Nathanus: I've never seen it used in Python aside from documentation, though. It makes me curious as to whether it's lack of mention in the 9.3 help indicates it will not function correctly.