I've created a winforms application that allows users to set their criteria, query ibm 400 system, view results, then mail merge selected results. As an extra feature, I'm trying to set a label that will tell users upon criteria selection (searching via code, not them clicking Search) how many records have been printed out of total results for given criteria.
Example: 30 of 35 printed.
This means 30 documents have been marked in tracking table as printed out of 35 total.
Currently I am working on the total record count of that string, not the "how many printed" portion. So currently, my code is setting the label as "# of" + RecCount + " printed.".
I'm running into some issues though. Users have their choice of 3 data sources: System1, System2, and BOTH.
The following details my queries and the results:
CRITERIA: 20121209, System1, ALL RECORDS, OLD ADDR
QUERY: SELECT COUNT(*) AS RecCount FROM Library2.Table1 a, Library2.Table2 b WHERE a.memno = b.memno and b.groupid = 'N2' and b.type = 'B' and b.datec = 20131209 AND (a.addr1 <> b.addr1 or a.addr2 <> b.addr2 or a.city <> b.city or a. state <> b.state or a.zip <> b.zip)
RETURNED CNT: 24
CRITERIA: 20121209, System2, ALL RECORDS, OLD ADDR
QUERY: SELECT COUNT(*) AS RecCount FROM psrslib.pai000pf a, psrslib.pch010pf b WHERE a.memno = b.memno and b.groupid = 'PAI0002' and b.type = 'B' and b.datec = 20131209 AND (a.addr1 <> b.addr1 or a.addr2 <> b.addr2 or a.city <> b.city or a. state <> b.state or a.zip <> b.zip)
RETURNED CNT: 14
These 2 above seem to be functioning appropriately. But when I come to the BOTH datasource:
CRITERIA: 20121209, BOTH, ALL RECORDS, OLD ADDR
QUERY: SELECT COUNT() AS RecCount FROM psrslib.pai000pf a, psrslib.pch010pf b WHERE a.memno = b.memno and b.groupid = 'PAI0002' and b.type = 'B' and b.datec = 20131209 AND (a.addr1 <> b.addr1 or a.addr2 <> b.addr2 or a.city <> b.city or a. state <> b.state or a.zip <> b.zip) UNION SELECT COUNT() AS RecCount FROM ntrslib.nai000pf a, ntrslib.nch010pf b WHERE a.memno = b.memno and b.groupid = 'NAI0002' and b.type = 'B' and b.datec = 20131209 AND (a.addr1 <> b.addr1 or a.addr2 <> b.addr2 or a.city <> b.city or a. state <> b.state or a.zip <> b.zip)
RETURNED CNT: 24 -- // This should be 38
Upon inspecting my code and using server explorer I found the following:
My original Search Query before string manipulation to only receive the Count:
SELECT a.memno,
a.name,
a.addr1,
a.addr2,
a.city,
a.state,
a.zip,
a.sex,
a.lname,
a.ssan,
b.addr1 as old_addr1,
b.addr2 as old_addr2,
b.city as old_city,
b.state as old_state,
b.zip as old_zip,
b.timec
FROM Library1.Table1 a, Libary1.Table2 b
WHERE a.memno = b.memno and
b.groupid = 'P2' and
b.type = 'B' and
b.datec = 20131209
AND (a.addr1 <> b.addr1 or a.addr2 <> b.addr2 or a.city <> b.city or a. state <> b.state or a.zip <> b.zip)
UNION SELECT a.memno,
a.name,
a.addr1,
a.addr2,
a.city,
a.state,
a.zip,
a.sex,
a.lname,
a.ssan,
b.addr1 as old_addr1,
b.addr2 as old_addr2,
b.city as old_city,
b.state as old_state,
b.zip as old_zip,
b.timec
FROM Library2.Table1 a, Library2.Table2 b
WHERE a.memno = b.memno and
b.groupid = 'N2' and
b.type = 'B' and
b.datec = 20131209
AND (a.addr1 <> b.addr1 or a.addr2 <> b.addr2 or a.city <> b.city or a. state <> b.state or a.zip <> b.zip)
ORDER BY timec desc
My Code:
private void getNumPrinted()
{
string qryCmd = "";
OdbcDataReader dr;
if (cnt > 1)
{
switch (cmbLetterType.SelectedIndex)
{
case 0:
docType = "oldAddr";
qryCmd = buildSearchQuery(docType);
break;
case 1:
docType = "newAddr";
qryCmd = buildSearchQuery(docType);
break;
case 2:
docType = "nameChg";
qryCmd = buildSearchQuery(docType);
break;
}
string query = "";
if (qryCmd.Contains("INNER JOIN"))
{
var newQry = qryCmd.Replace(qryCmd.Substring(0, qryCmd.IndexOf("FROM TableC c") - 1), "SELECT COUNT(a.memno) AS RecCount");
var orderByIndex = newQry.IndexOf("ORDER BY");
newQry = newQry.Replace(newQry.Substring(orderByIndex, newQry.Length - orderByIndex), "");
query = newQry;
}
else
{
var newQry = qryCmd.Replace(qryCmd.Substring(0, qryCmd.IndexOf("FROM") - 1), "SELECT COUNT(*) AS RecCount");
var orderByIndex = newQry.IndexOf("ORDER BY");
newQry = newQry.Replace(newQry.Substring(orderByIndex, newQry.Length - orderByIndex), "");
query = newQry;
}
dr = mdl.GetData(query);
while (dr.Read())
{
lblNumPrinted.Text = "# out of " + dr["RecCount"].ToString() + " printed";
}
// NEED TO PERFORM ANOTHER QUERY COMBINED WITH TableC to see how many records have not been printed.
mdl.closeConn();
lblNumPrinted.Visible = true;
}
cnt++;
}
After String Manipulation:
SELECT COUNT(*) AS RecCount
FROM Library1.Table1 a, Library1.Table2 b
WHERE a.memno = b.memno and
b.groupid = 'P2' and
b.type = 'B' and
b.datec = 20131209
AND (a.addr1 <> b.addr1 or a.addr2 <> b.addr2 or a.city <> b.city or a. state <> b.state or a.zip <> b.zip)
UNION SELECT COUNT(*) AS RecCount
FROM Library2.Table1 a, Library2.Table2 b
WHERE a.memno = b.memno and
b.groupid = 'N2' and
b.type = 'B' and
b.datec = 20131209
AND (a.addr1 <> b.addr1 or a.addr2 <> b.addr2 or a.city <> b.city or a. state <> b.state or a.zip <> b.zip)
I could not figure out as mentioned why my above query after string manipulation was setting the label to a value of "# of 24 printed."
When I ran my query in Server Explorer however, I saw the issue. In code I am trying to receive a single COUNT, and my replace code line is changing both select clauses in the UNION to the new "SELECT COUNT(*) AS RecCount".
Server Explorer Results:
[RecCount]
[14]
[24]
Can someone show me what modification I need to do to my code/query that uses the BOTH datasource so that I receive a single RecCount of 38?
