Scripting query help

Ignition
1

I am trying to build a query using scripting.
The main problem is with the Select portion.

this works:

selectedQuarter = -1
query = “SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) -1, 0)”

rows = system.db.runQuery(query,‘historian’)

for row in rows:

startDate = row[0]
print startDate

query = “SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), -1)”

rows = system.db.runQuery(query,‘historian’)

for row in rows:

endDate = row[0]

print endDate
print selectedQuarter

this doesn’t:

selectedQuarter = int(event.source.parent.getComponent(‘Numeric Text Field’).intValue
query = “SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) [selectedQuarter], 0)”

rows = system.db.runQuery(query,‘historian’)

for row in rows:

startDate = row[0]
print startDate

query = “SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), -1)”

rows = system.db.runQuery(query,‘historian’)

for row in rows:

endDate = row[0]

print endDate
print selectedQuarter

Any thoughts?

2

My first observation is that you're missing a closing parenthesis at the end of this line.

Also... I don't think that this

query = "SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) [selectedQuarter], 0)"

is doing what you expect. It's possible I'm mistaken, but I suspect if you print that out query, you will get:

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) [selectedQuarter], 0)

The variable will not be inserted into the string. You need to use a different method to make it part of the string; check out this link.

3

Thanks Stuart, but it appears to be working, unless i typed something wrong in my original post.

image.png1285×615 56.9 KB

4

Well… In that screenshot, you’ve commented out the line missing the parenthesis, and you’re never using the selectedQuarter variable!