Auto Routines - SQL - Tables  [SOLVED]

Discuss your general Proclaim related queries here.
revellbikes
Posts: 488
Joined: Fri Jun 15, 2012 12:44 pm
Has thanked: 16 times
Been thanked: 51 times

Auto Routines - SQL - Tables

Post by revellbikes »

Morning all,

I'm just wondering if anyone has found a way to use Proclaim SQL Maintenance to query a table without getting errors or getting bombed out of Procalim all together!?

I need an auto routine to run and find any dates within a tabe that are previous to todays date.

I'm falling at the first hurdle and getting crashed out if I try and query a table date field.

Thanks!

steve
Posts: 470
Joined: Wed Nov 30, 2011 10:20 pm
Been thanked: 120 times

Re: Auto Routines - SQL - Tables

Post by steve »

there may be another way but this works for me:
SQL Maint: SQL tab:
"Primary Table" dropdown change to the name of your table
Criteria tab:
tablefield.date is less than or equal to the constant date "Yesterday"
Columns tab:
case.key, plus anything else you want for debugging (it's an autoroutine right?)

the result of the run is one record per table row that matches the criteria, so your autoroutine may run multiple times on one case if that case has multiple rows that match. You can cater for this in your autoroutine linked action.

HTH

revellbikes
Posts: 488
Joined: Fri Jun 15, 2012 12:44 pm
Has thanked: 16 times
Been thanked: 51 times

Re: Auto Routines - SQL - Tables  [SOLVED]

Post by revellbikes »

Thanks Steve and for such a prompt response!

You're right about the Primary Table drop down, I forgot to mention I had selected that (admittedy I forgot it on the first round of testing!)

I've resolved the crashing issue now, I had a dodgy date in the table data which was causing the crashes. Spotted it when doing a query on one of the other columns, deleted the dodgy row and now my SQL is working.

Happy days!