CSPro Users Forum

Dear All,

I want to refer ID items & record items in sqlquery function.

My Current sqlquery function looks like this, which works fine. This refers to records level:
string sqlQry1=
"SELECT TRIM(DISTRICT), SUM(PATIENTS_REGISTERED), SUM(PATIENTS_ATTENDED) FROM MMDP_REC "
"GROUP BY DISTRICT;";

Problem no. 1. when I add ID items "CAMP_TYPE" to this query, CSPRO says "No such column : CAMP_TYPE".
How to refer to the ID items or Join two record levels. I have attached the dictionary structure for better understanding.

Problem no. 2. the current sqlquery returns all data (deleted + non-deleted). How do I filter only the non-deleted records in the query.

Please help.

Thanks.

Parth Saha

SqlQuery is definitely an advanced feature. Is there any reason you don't want to use CSPro logic functions to compute your sums?

In any case, the description of the file is described here: https://www.csprousers.org/help/CSPro/c ... ormat.html

You'll want to start your query on the `cases` table (where you can filter on `deleted`=0 to get non-deleted records), and then join on `level-1`, where you'll get `camp_type`, and then join on `mmdp_rec`, where you'll get your other variables.

Let us know if you have trouble with this.

Hi Martin,

Many thanks for the your reply & the link! I was exactly looking for this only....

My queries are now working.

Coming to your question: Why I'm using sqlquery and not other logic functions.
It is because of the reasons:
1. I'm more comfortable in using sql queries
2. sql queries will allow me to group records by columns
3. I don't know much of logic functions which will help me do these grouping and other stuff.
4. And I guess, those logic functions will computation intensive.

Now, as you are an expert, please suggest the best way to show reports and if logic functions are best, then please suggest the appropriate logic functions to use for grouping, etc.

Thanks.

Parth Saha

If you're comfortable with SQL queries, then that's definitely a good approach. As you note, CSPro logic will definitely be more computationally intensive because all parts of each case will have to processed rather than just the items that you are interested in processing.

If you were to use CSPro, you could do something like this:
Your SQL is probably easier.