Refer id items & record items in sqlquery function

Discussions about creating CAPI applications to run on Android devices
Forum rules
New release: CSPro 8.0
Post Reply
parthsaha
Posts: 15
Joined: March 8th, 2021, 11:16 am

Refer id items & record items in sqlquery function

Post by parthsaha »

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
Attachments
dict struct.png
dict struct.png (36.44 KiB) Viewed 2305 times
Gregory Martin
Posts: 1777
Joined: December 5th, 2011, 11:27 pm
Location: Washington, DC

Re: Refer id items & record items in sqlquery function

Post by Gregory Martin »

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.
parthsaha
Posts: 15
Joined: March 8th, 2021, 11:16 am

Re: Refer id items & record items in sqlquery function

Post by parthsaha »

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
Gregory Martin
Posts: 1777
Joined: December 5th, 2011, 11:27 pm
Location: Washington, DC

Re: Refer id items & record items in sqlquery function

Post by Gregory Martin »

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:
hashmap sums_by_district(numeric, string) default(0);
list district_keys;

forcase MMDP_DICT do
    inc
(sums_by_district(DISTRICT, "REGISTERED"), PATIENTS_REGISTERED);
   
inc(sums_by_district(DISTRICT, "ATTENDED"), PATIENTS_ATTENDED);
endfor;

sums_by_district.getKeys(district_keys);

do numeric counter = 1 while counter <= district_keys.length()

   
errmsg("District: %d, Registered: %d, Attended: %d",
        district_keys(counter),
        sums_by_district(district_keys(counter),
"REGISTERED"),
        sums_by_district(district_keys(counter),
"ATTENDED"));

enddo;
Your SQL is probably easier. :D
Post Reply