Post

3 followers Follow
0
Avatar

How to set up a where condition

Hi, 

I need to get the data between two dates. How can i do that using API query - I would need to use both greater than and less than conditions.

curl " https://api2.clarizen.com/v2.0/services/data/EntityQuery -d "{
typeName: 'Data',
fields: ['CreatedBy','CreatedOn',
'Description','EntityType','ExternalID','LastUpdatedBy','LastUpdatedOn','Name','SourceObject','LastUpdatedBySystemOn','DataObjectType'
],
where: {
_type: 'Compare',
leftExpression: {fieldName: 'CreatedOn'},
operator: 'GreaterThan',
rightExpression: {value: '2017-04-13T00:00:00.0000000'}
},
paging: {"hasMore": false,"limit": 100000},
}" 

How to add the Less Than part to the query above. Also, is there any documentation on using where conditions, group by and order by in such API queries ?

Sahil Batra Answered

Official comment

Avatar

Hi Sahil,

Please try to use the POST method and set the CZQL in the body of the request.

 

I hope this helps,

Elad

Elad Franklin
Comment actions Permalink

Please sign in to leave a comment.

6 comments

0
Avatar

Did you read the REST API documentation?

https://success.clarizen.com/hc/en-us/articles/205711828-16-13-REST-API-Guide-Version-2?search=CZQL

In particular, there's a comment 

//Perform the above query using CZQL

curl https://../services/data/query?q=SELECT createdOn,title FROM Issue WHERE createdOn>2014-04-03

If you look at the CZQL documentation, there are a lot of examples, including examples with multiple conditions.

The CZQL documentation: https://api.clarizen.com/V2.0/services/data/Query

With the comment above and the CZQL documentation, you should be able to build complex queries.

Hope this helps,

 

Armand BOLMONT 0 votes
Comment actions Permalink
0
Avatar

I tried using CZSQL with curl but it doesn't look like that CZSQL is supported in curl.

When i ran the query, curl -H "Authorization: Session awxyz" https://api2.clarizen.com/v2.0/services/data/query?q=SELECT C_ChangedinRequest,C_ChangedinRequestID,C_ChangedObject,CreatedOn FROM Data

I get the following error -

{"errorCode":"General","message":"Invalid Query: Invalid CZQL (1:6)","referenceId":"1G93Gyhmnwo94oyu9K94oA"}

Any help ??

 

Sahil Batra 0 votes
Comment actions Permalink
0
Avatar

In addition, the query below works perfectly fine -

curl -H "Authorization: Session awxyz" https://api2.clarizen.com/v2.0/services/data/EntityQuery -d "{
typeName: 'Data',
fields: ['C_ActionParent','C_ActionProject','C_ActionType','C_AdditionalInformation','CreatedBy','CreatedOn'],
paging: {'hasMore': false,'limit': 100000},
where: {_type: 'Compare',
leftExpression:{ 'fieldName': 'CreatedOn'},
operator: 'GreaterThan',
rightExpression: { 'value': '2017-01-01T00:00:00.0000000' }
},
}"

Its only when i add the AND condition that i get the error as per below mentioned query -

curl  -H "Authorization: Session awxyz" https://api2.clarizen.com/v2.0/services/data/EntityQuery -d "{
typeName: 'Data',
fields: ['C_ActionParent','C_ActionProject','C_ActionType','C_AdditionalInformation','CreatedBy','CreatedOn'],
where: {
"and":[
{
_type: 'Compare',
"leftExpression":{ "fieldName": "CreatedOn"},
"operator": "GreaterThan",
"rightExpression": { "value": "2017-01-01T00:00:00.0000000" }
},
{
_type: 'Compare',
"leftExpression":{ "fieldName": "CreatedOn"},
"operator": "LessThan",
"rightExpression": { "value": "2017-03-01T00:00:00.0000000" }
}
]
},
paging: {"hasMore": false,"limit": 100000},
}"

Error Message -

{
"errorCode": "Internal",
"message": "An internal error has occurred, the error was logged and will be examined.",
"referenceId": "26JcwYx9Rdaebktm46Gqly"
}

 

Let me know if there might be some syntax issue i might have to fix ?

 

Sahil Batra 0 votes
Comment actions Permalink
0
Avatar

Hello Sahil,

 

Could you share how you fixed your problem? Was it a syntax problem in your query?

By the way, the upper limit for the "limit" parameter is 1000. 100000 isn't taken into account.

Armand BOLMONT 0 votes
Comment actions Permalink