Individual column search not working with SSP
Individual column search not working with SSP
bbrindza
Posts: 316Questions: 73Answers: 1
I am using a DataTables SSP script with a Where clause that is preventing accurate individual column searching.
When I remove the Where clause, column filtering works as designed.
Any thoughts?
<?php
/*
* DataTables server-side processing custom script.
*/
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
date_default_timezone_set('America/Chicago'); // CDT
$today = date("Y-m-d");
$archiveDate = date('Ymd', strtotime('-1 year'));
include('i5db2connect.php');
// DB table to use
$table = 'NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC';
//Used for optional WHERE clauses that will be appended to the SQL string
$extraWhere = "FCSTAT not in
('Closed-Deducted',
'Closed-Overpaid',
'Closed-Paid',
'Closed-PWO',
'Closed-Rejected',
'Closed-WO',
'Closed-WO/Ded mix',
'Declined',
'Rejected',
'Removed')
or
FCSTAT in
( 'Deduction Pending',
'Partial Pending',
'Reinstated',
'Pending-WO taken' )
and FCMCRD >= '" . $archiveDate . "'";
// Table's primary key
$primaryKey = 'FCCLMN';
$columns = array(
array( 'db' => 'FCCLMN', 'dt' => 'claim_number' ),
array( 'db' => 'FCCLMN', 'dt' => 'claim_number_link',
'formatter' => function($d, $row){
return ' <a id="myLink" class="link" onclick="editFreightClaim('.$d.');return false;">' . $d . '</a>';
}
),
array( 'db' => 'LOCDES', 'dt' => 'location',
'formatter' => function( $d, $row ) {
return substr( $d, 4, strlen($d));
}
),
array( 'db' => 'FCDNAM', 'dt' => 'department_name' ),
array( 'db' => 'FCCVNM', 'dt' => 'vendor_customer' ),
array( 'db' => 'FCMPRO', 'dt' => 'pro_number' ),
array( 'db' => 'FCMNAM', 'dt' => 'carrier_name' ),
array( 'db' => 'FCPAMT', 'dt' => 'claim_amount' ),
array( 'db' => 'FCFACL', 'dt' => 'freight_claim_amount' ),
array( 'db' => 'FCTOTL', 'dt' => 'total_claim_amount'),
array( 'db' => 'FCMPAM', 'dt' => 'amount_paid'),
array( 'db' => 'FCSTAT', 'dt' => 'status' ),
array( 'db' => 'FCMSHP', 'dt' => 'ship_date',
'formatter' => function( $d, $row ) {
if ($d > 0)
{
$dt = DateTime::createFromFormat('Ymd', $d);
return $dt->format('m/d/Y');
}
else
{
return '';
}
}
),
array( 'db' => 'FCMREC', 'dt' => 'received_date',
'formatter' => function( $d, $row ) {
if ($d > 0)
{
$dt = DateTime::createFromFormat('Ymd', $d);
return $dt->format('m/d/Y');
}
else
{
return '';
}
}
),
array( 'db' => 'FCLMTP', 'dt' => 'claim_type',
'formatter' => function( $d, $row ) {
if (trim($d) == 'I') {
return "Inbound";
} else {
return "Outbound";
}
}
),
array( 'db' => 'FCMCRD', 'dt' => 'created_age',
'formatter' => function( $d, $row ) {
if ($d > 0 ){
$createdOn = substr($d,0,4).'-'.substr($d,4,2).'-'.substr($d,6,2);
$createdOn = date_create($createdOn);
$today = date_create($today);
$diff=date_diff($createdOn,$today);
if($diff->format('%y') !== '0'){
$created_age = $diff->format('%y Year %m Month %d Day');
}else if($diff->format('%m') !== '0'){
$created_age = $diff->format('%m Month %d Day');
}else{
$created_age = $diff->format('%d Day');
}
return $created_age;
} else {
return '';
}
}
),
array( 'db' => 'FCMCLM', 'dt' => 'filed_age',
'formatter' => function( $d, $row ) {
if ($d > 0 && trim($row[FCSTAT]) == 'Filed'){
$createdOn = substr($d,0,4).'-'.substr($d,4,2).'-'.substr($d,6,2);
$createdOn = date_create($createdOn);
$today = date_create($today);
$diff=date_diff($createdOn,$today);
if($diff->format('%y') !== '0'){
$filed_age = $diff->format('%y Year %m Month %d Day');
}else if($diff->format('%m') !== '0'){
$filed_age = $diff->format('%m Month %d Day');
}else{
$filed_age = $diff->format('%d Day');
}
return $filed_age;
} else {
return '';
}
}
),
array( 'db' => 'FCMPAM', 'dt' => 'amount_due',
'formatter' => function( $d, $row ) {
$amountDue = $row[FCTOTL] - $row[FCMPAM];
$amountDue = number_format((float)$amountDue, 2, '.', '');
return $amountDue;
}
),
);
require( 'ssp.class_Template.php' );
//var_dump($connection);
echo json_encode(
SSP::simple( $_GET, $connection, $select, $table, $primaryKey, $columns, $extraWhere)
);
This question has an accepted answers - jump to answer
Answers
It might actually be as simple as wrapping the extra condition in parenthesis:
Allan
Allan ,
That did not work. Has the column search api been tested using where clause in the ssp class?
Yes, it should work no problem. That said, it looks like this is a customised version of the SSP class since the one we provide doesn't have a parameter after
$columnsfor thesimplemethod.I'd suggest echoing out the SQL that is being generated as the starting point for debugging this.
Allan
Here is the the Network Preview showing the echoed SQL from the ssp.class.php.
SELECT FCCLMN, FCCLMN, LOCDES, FCDNAM, FCCVNM, FCMPRO, FCMNAM, FCPAMT, FCFACL, FCTOTL, FCMPAM, FCSTAT, FCMSHP, FCMREC, FCLMTP, FCMCRD, FCMCLM, FCMPAM FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC WHERE upper(FCCLMN) LIKE ? AND FCSTAT not in ('Closed-Deducted', 'Closed-Overpaid', 'Closed-Paid', 'Closed-PWO', 'Closed-Rejected', 'Closed-WO', 'Closed-WO/Ded mix', 'Declined', 'Rejected', 'Removed') or FCSTAT in ( 'Deduction Pending', 'Partial Pending', 'Reinstated', 'Pending-WO taken' ) and FCMCRD >= '20200625' ORDER BY FCCLMN DESC LIMIT 0, 15SELECT COUNT(FCCLMN) as RCDCNT FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC WHERE upper(FCCLMN) LIKE ? AND FCSTAT not in ('Closed-Deducted', 'Closed-Overpaid', 'Closed-Paid', 'Closed-PWO', 'Closed-Rejected', 'Closed-WO', 'Closed-WO/Ded mix', 'Declined', 'Rejected', 'Removed') or FCSTAT in ( 'Deduction Pending', 'Partial Pending', 'Reinstated', 'Pending-WO taken' ) and FCMCRD >= '20200625'SELECT COUNT(FCCLMN) as RCDCNT FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC{"draw":10,"recordsTotal":18219,"recordsFiltered":52,"data":[{"claim_number":"19307","claim_number_link":" 19307<\/a>","location":"MEMPHIS ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"NEWLY WEDS FOODS, INC-WAT ","pro_number":" ","carrier_name":"XPO VOL (G!)QTE#5797041639646 ","claim_amount":"27.99","freight_claim_amount":"14.45","total_claim_amount":"42.44","amount_paid":".00","status":"Reinstated ","ship_date":"05\/24\/2021","received_date":"","claim_type":"Outbound","created_age":"21 Day","filed_age":"","amount_due":"42.44"},{"claim_number":"19258","claim_number_link":" 19258<\/a>","location":"SPRINGDALE ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"OKLAHOMA CITY MEAT COMPAN ","pro_number":"707242082 ","carrier_name":"XPO B ","claim_amount":"830.00","freight_claim_amount":".00","total_claim_amount":"830.00","amount_paid":"207.50","status":"Partial Pending ","ship_date":"05\/26\/2021","received_date":"05\/28\/2021","claim_type":"Outbound","created_age":"23 Day","filed_age":"","amount_due":"622.50"},{"claim_number":"19251","claim_number_link":" 19251<\/a>","location":"MODESTO ","department_name":"OFFICE - A\/P ","vendor_customer":"OXIDRY\/WORLD TECH INGRE ","pro_number":"089243533 ","carrier_name":"ABF FREIGHT SYSTEM, INC. ","claim_amount":"235.90","freight_claim_amount":"16.55","total_claim_amount":"252.45","amount_paid":"156.55","status":"Partial Pending ","ship_date":"05\/13\/2021","received_date":"05\/24\/2021","claim_type":"Inbound","created_age":"23 Day","filed_age":"","amount_due":"95.90"},{"claim_number":"19231","claim_number_link":" 19231<\/a>","location":"BETHLEHEM ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"NEWLY WEDS FOODS YRK ","pro_number":"773780191156 ","carrier_name":"FEDEX GROUND 871620 ","claim_amount":"264.76","freight_claim_amount":".00","total_claim_amount":"264.76","amount_paid":"211.26","status":"Partial Pending ","ship_date":"05\/20\/2021","received_date":"05\/24\/2021","claim_type":"Outbound","created_age":"28 Day","filed_age":"","amount_due":"53.50"},{"claim_number":"19229","claim_number_link":" 19229<\/a>","location":"BETHLEHEM ","department_name":"OFFICE - A\/P ","vendor_customer":"INGREDION ","pro_number":"402609815 ","carrier_name":"xpo ","claim_amount":"249.05","freight_claim_amount":"77.76","total_claim_amount":"326.81","amount_paid":"269.05","status":"Partial Pending ","ship_date":"04\/23\/2021","received_date":"04\/27\/2021","claim_type":"Inbound","created_age":"28 Day","filed_age":"","amount_due":"57.76"},{"claim_number":"19208","claim_number_link":" 19208<\/a>","location":"GERALD ","department_name":"CUSTOMER SERVICE - S\/C ","vendor_customer":"NEWLY WEDS FOODS,INC. MTP ","pro_number":"688302101 ","carrier_name":"XPO-B ","claim_amount":"857.80","freight_claim_amount":".00","total_claim_amount":"857.80","amount_paid":"514.68","status":"Partial Pending ","ship_date":"05\/20\/2021","received_date":"05\/25\/2021","claim_type":"Outbound","created_age":"29 Day","filed_age":"","amount_due":"343.12"},{"claim_number":"19180","claim_number_link":" 19180<\/a>","location":"MODESTO ","department_name":"OFFICE - A\/P ","vendor_customer":"INGREDION ","pro_number":"050284045 ","carrier_name":"ABF FREIGHT SYSTEM, INC. ","claim_amount":"235.13","freight_claim_amount":"25.65","total_claim_amount":"260.78","amount_paid":"229.25","status":"Partial Pending ","ship_date":"05\/10\/2021","received_date":"05\/18\/2021","claim_type":"Inbound","created_age":"1 Month 0 Day","filed_age":"","amount_due":"31.53"},{"claim_number":"19090","claim_number_link":" 19090<\/a>","location":"BETHLEHEM ","department_name":"OFFICE - A\/R ","vendor_customer":"CITRUS&ALLIED ESSENCES LT ","pro_number":"8766087341 ","carrier_name":"FEDERAL EXPRESS A ","claim_amount":"4242.13","freight_claim_amount":".00","total_claim_amount":"4242.13","amount_paid":"1257.08","status":"Partial Pending ","ship_date":"05\/12\/2021","received_date":"05\/14\/2021","claim_type":"Inbound","created_age":"1 Month 6 Day","filed_age":"","amount_due":"2985.05"},{"claim_number":"19087","claim_number_link":" 19087<\/a>","location":"YORKVILLE ","department_name":"OFFICE - A\/P ","vendor_customer":"DSM FOOD SPECIALTIES ","pro_number":"223-793076 ","carrier_name":"XPO B ","claim_amount":"381.62","freight_claim_amount":"5.24","total_claim_amount":"386.86","amount_paid":".00","status":"Reinstated ","ship_date":"04\/12\/2021","received_date":"04\/15\/2021","claim_type":"Inbound","created_age":"1 Month 6 Day","filed_age":"","amount_due":"386.86"},{"claim_number":"19037","claim_number_link":" 19037<\/a>","location":"SPRINGDALE ","department_name":"OFFICE - A\/P ","vendor_customer":"KERRY ","pro_number":"7772099930 7772099974 ","carrier_name":"FEDEX FREIGHT (FXA) ","claim_amount":"747.42","freight_claim_amount":"14.51","total_claim_amount":"761.93","amount_paid":"380.97","status":"Partial Pending ","ship_date":"05\/07\/2021","received_date":"05\/13\/2021","claim_type":"Inbound","created_age":"1 Month 8 Day","filed_age":"","amount_due":"380.96"},{"claim_number":"18938","claim_number_link":" 18938<\/a>","location":"SPRINGDALE ","department_name":"OFFICE - A\/P ","vendor_customer":"BIOSPRINGER NORTH AMERICA ","pro_number":"721-822850 ","carrier_name":"XPO A ","claim_amount":"83.60","freight_claim_amount":"3.25","total_claim_amount":"86.85","amount_paid":"83.60","status":"Partial Pending ","ship_date":"05\/03\/2021","received_date":"05\/05\/2021","claim_type":"Inbound","created_age":"1 Month 18 Day","filed_age":"","amount_due":"3.25"},{"claim_number":"18889","claim_number_link":" 18889<\/a>","location":"ERLANGER ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"HIGH LINER FOODS, INC. ","pro_number":"567391156 ","carrier_name":"XPO 4\/28 B ","claim_amount":"74.70","freight_claim_amount":"3.54","total_claim_amount":"78.24","amount_paid":"74.70","status":"Partial Pending ","ship_date":"04\/28\/2021","received_date":"04\/30\/2021","claim_type":"Outbound","created_age":"1 Month 22 Day","filed_age":"","amount_due":"3.54"},{"claim_number":"18876","claim_number_link":" 18876<\/a>","location":"BETHLEHEM ","department_name":"OFFICE - A\/P ","vendor_customer":"PACIFIC SPICE COMPANY INC ","pro_number":"595003883 ","carrier_name":"XPO LOGISTICS, INC. A ","claim_amount":"84.50","freight_claim_amount":"16.48","total_claim_amount":"100.98","amount_paid":"84.50","status":"Partial Pending ","ship_date":"04\/22\/2021","received_date":"04\/29\/2021","claim_type":"Inbound","created_age":"1 Month 22 Day","filed_age":"","amount_due":"16.48"},{"claim_number":"18702","claim_number_link":" 18702<\/a>","location":"MODESTO ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"HYDE & HYDE ","pro_number":"7575936771 ","carrier_name":"FEDEX FREIGHT PRIORITY ","claim_amount":"945.00","freight_claim_amount":"29.37","total_claim_amount":"974.37","amount_paid":"557.47","status":"Partial Pending ","ship_date":"04\/14\/2021","received_date":"04\/16\/2021","claim_type":"Outbound","created_age":"2 Month 6 Day","filed_age":"","amount_due":"416.90"},{"claim_number":"18497","claim_number_link":" 18497<\/a>","location":"MEMPHIS ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"NEWLY WEDS FOODS, INC-SOU ","pro_number":"6418318255 ","carrier_name":"FED EX FRT PRIORITY ","claim_amount":"147.80","freight_claim_amount":".00","total_claim_amount":"147.80","amount_paid":"129.34","status":"Partial Pending ","ship_date":"03\/30\/2021","received_date":"04\/01\/2021","claim_type":"Outbound","created_age":"2 Month 23 Day","filed_age":"","amount_due":"18.46"}]}
Here is the the Network Response from the ssp.class.php. **NOTE: Not sure why the SQL from is repeated in the response output
SELECT FCCLMN, FCCLMN, LOCDES, FCDNAM, FCCVNM, FCMPRO, FCMNAM, FCPAMT, FCFACL, FCTOTL, FCMPAM, FCSTAT, FCMSHP, FCMREC, FCLMTP, FCMCRD, FCMCLM, FCMPAM
FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC
WHERE upper(FCCLMN) LIKE ? AND FCSTAT not in
('Closed-Deducted',
'Closed-Overpaid',
'Closed-Paid',
'Closed-PWO',
'Closed-Rejected',
'Closed-WO',
'Closed-WO/Ded mix',
'Declined',
'Rejected',
'Removed')
or
FCSTAT in
( 'Deduction Pending',
'Partial Pending',
'Reinstated',
'Pending-WO taken' )
and FCMCRD >= '20200625'
ORDER BY FCCLMN DESC
LIMIT 0, 15SELECT COUNT(FCCLMN) as RCDCNT
FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC
WHERE upper(FCCLMN) LIKE ? AND FCSTAT not in
('Closed-Deducted',
'Closed-Overpaid',
'Closed-Paid',
'Closed-PWO',
'Closed-Rejected',
'Closed-WO',
'Closed-WO/Ded mix',
'Declined',
'Rejected',
'Removed')
or
FCSTAT in
( 'Deduction Pending',
'Partial Pending',
'Reinstated',
'Pending-WO taken' )
and FCMCRD >= '20200625'SELECT COUNT(FCCLMN) as RCDCNT
FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC{"draw":10,"recordsTotal":18219,"recordsFiltered":52,"data":[{"claim_number":"19307","claim_number_link":" <a id=\"myLink\" class=\"link\" onclick=\"editFreightClaim(19307);return false;\">19307<\/a>","location":"MEMPHIS ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"NEWLY WEDS FOODS, INC-WAT ","pro_number":" ","carrier_name":"XPO VOL (G!)QTE#5797041639646 ","claim_amount":"27.99","freight_claim_amount":"14.45","total_claim_amount":"42.44","amount_paid":".00","status":"Reinstated ","ship_date":"05\/24\/2021","received_date":"","claim_type":"Outbound","created_age":"21 Day","filed_age":"","amount_due":"42.44"},........................................]}
Thanks.
Formatting the SQL I think helps to focus down on the issue.
Which column in the above is it you are doing the individual column filtering on? I'd expect to see a
LIKE %?%for it, but it isn't there, so I wonder if your modifications have removed that part?This is the part that should be doing it normally.
It would be worth your checking that that part of the code is actually running in your modified version.
Allan
I was filtering the FCCLMN that is in the tables as a link.
I don't believe I removed any code from the original ssp.class.php.
I would like to share this script with you , however the forum will not allow me to post a message that large.
I could send it to you via email.
Hi,
Many thanks for your e-mail. It looks like the column filtering condition is being added:
Which makes me suspect the grouping of the conditions that are being applied again. Can you show me the SQL output when you add the parenthesis around the
$extraWhereplease?Allan
Allan,
I added the parenthesis around the $extraWhere, but when I did, it only returned 88 rows. I should see a little over 3000.
Anyway, here is my $extraWhere
Here is the echoed SQL
SELECT FCCLMN, FCCLMN, LOCDES, FCDNAM, FCCVNM, FCMPRO, FCMNAM, FCPAMT, FCFACL, FCTOTL, FCMPAM, FCSTAT, FCMSHP, FCMREC, FCLMTP, FCMCRD, FCMCLM, FCMPAM FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC WHERE FCSTAT in ('Closed-Deducted', 'Closed-Overpaid', 'Closed-Paid', 'Closed-PWO', 'Closed-Rejected', 'Closed-WO', 'Closed-WO/Ded mix', 'Declined', 'Rejected', 'Removed') and FCMCRD >= '20200628' or FCSTAT in ('Deduction Pending', 'Partial Pending', 'Reinstated', 'Pending-WO taken' ) ORDER BY FCCLMN DESC LIMIT 0, 15SELECT COUNT(FCCLMN) as RCDCNT FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC WHERE FCSTAT in ('Closed-Deducted', 'Closed-Overpaid', 'Closed-Paid', 'Closed-PWO', 'Closed-Rejected', 'Closed-WO', 'Closed-WO/Ded mix', 'Declined', 'Rejected', 'Removed') and FCMCRD >= '20200628' or FCSTAT in ('Deduction Pending', 'Partial Pending', 'Reinstated', 'Pending-WO taken' ) SELECT COUNT(FCCLMN) as RCDCNT FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC{"draw":1,"recordsTotal":18247,"recordsFiltered":3595,"data":[{"claim_number":"19505","claim_number_link":" 19505<\/a>","location":"BETHLEHEM ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"KAYEM ","pro_number":"7095982784 ","carrier_name":"FED EX FRT PRIORITY ","claim_amount":"12.48","freight_claim_amount":"1.29","total_claim_amount":"13.77","amount_paid":".00","status":"Rejected ","ship_date":"06\/01\/2021","received_date":"06\/07\/2021","claim_type":"Outbound","created_age":"5 Day","filed_age":"","amount_due":"13.77"},{"claim_number":"19493","claim_number_link":" 19493<\/a>","location":"WATERTOWN ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"NESTLE USA ","pro_number":"792440641-6 ","carrier_name":"FEDEX FREIGHT PRIORITY ","claim_amount":"14.05","freight_claim_amount":"4.35","total_claim_amount":"18.40","amount_paid":".00","status":"Rejected ","ship_date":"06\/16\/2021","received_date":"06\/18\/2021","claim_type":"Outbound","created_age":"6 Day","filed_age":"","amount_due":"18.40"},{"claim_number":"19488","claim_number_link":" 19488<\/a>","location":"BETHLEHEM ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"AGRI-MARK, INC. ","pro_number":"193 201 831 BAE ","carrier_name":"PICK UP 6\/15 ","claim_amount":"319.00","freight_claim_amount":".00","total_claim_amount":"319.00","amount_paid":".00","status":"Removed ","ship_date":"06\/17\/2021","received_date":"","claim_type":"Outbound","created_age":"7 Day","filed_age":"","amount_due":"319.00"},{"claim_number":"19486","claim_number_link":" 19486<\/a>","location":"CHICAGO ","department_name":"OFFICE - A\/P ","vendor_customer":"SONSTEGARD FOODS CO ","pro_number":"739713148 ","carrier_name":"FEDEX FREIGHT A ","claim_amount":"8190.00","freight_claim_amount":"145.24","total_claim_amount":"8335.24","amount_paid":".00","status":"Removed ","ship_date":"05\/11\/2021","received_date":"05\/12\/2021","claim_type":"Inbound","created_age":"7 Day","filed_age":"","amount_due":"8335.24"},{"claim_number":"19482","claim_number_link":" 19482<\/a>","location":"ERLANGER ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"SYSCO FOODS SERVICES ","pro_number":"8727941552 ","carrier_name":"FED EX FREIGHT PRIORITY 6\/9 ","claim_amount":"10.35","freight_claim_amount":"4.61","total_claim_amount":"14.96","amount_paid":".00","status":"Rejected ","ship_date":"06\/10\/2021","received_date":"","claim_type":"Outbound","created_age":"7 Day","filed_age":"","amount_due":"14.96"},{"claim_number":"19475","claim_number_link":" 19475<\/a>","location":"SPRINGDALE ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"NEWLY WEDS FOODS, INC. ","pro_number":"707242491 ","carrier_name":"XPO A ","claim_amount":"17.09","freight_claim_amount":".00","total_claim_amount":"17.09","amount_paid":".00","status":"Rejected ","ship_date":"06\/11\/2021","received_date":"06\/17\/2021","claim_type":"Outbound","created_age":"10 Day","filed_age":"","amount_due":"17.09"},{"claim_number":"19466","claim_number_link":" 19466<\/a>","location":"MEMPHIS ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"THE DISTRIBUTION GROUP, ","pro_number":"009729658 ","carrier_name":"ABF WWW9794449 ","claim_amount":"11.68","freight_claim_amount":"1.08","total_claim_amount":"12.76","amount_paid":".00","status":"Rejected ","ship_date":"06\/11\/2021","received_date":"","claim_type":"Outbound","created_age":"10 Day","filed_age":"","amount_due":"12.76"},{"claim_number":"19464","claim_number_link":" 19464<\/a>","location":"YORKVILLE ","department_name":"OFFICE - A\/P ","vendor_customer":"J.M. SWANK, LLC ","pro_number":"5268726245 ","carrier_name":"FED EX B ","claim_amount":"883.80","freight_claim_amount":"344.66","total_claim_amount":"1228.46","amount_paid":".00","status":"Removed ","ship_date":"05\/28\/2021","received_date":"06\/03\/2021","claim_type":"Inbound","created_age":"10 Day","filed_age":"","amount_due":"1228.46"},{"claim_number":"19462","claim_number_link":" 19462<\/a>","location":"SPRINGDALE ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"BRAKEBUSH BROTHERS, INC. ","pro_number":"707299935 ","carrier_name":"XPO A ","claim_amount":"127.44","freight_claim_amount":"29.43","total_claim_amount":"156.87","amount_paid":"156.87","status":"Closed-Paid ","ship_date":"06\/15\/2021","received_date":"06\/18\/2021","claim_type":"Outbound","created_age":"10 Day","filed_age":"","amount_due":"0.00"},{"claim_number":"19461","claim_number_link":" 19461<\/a>","location":"CLEVELAND ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"US FOODS FL ","pro_number":"8442256631 ","carrier_name":"FEDEX PER EMAIL ","claim_amount":"13.10","freight_claim_amount":"3.62","total_claim_amount":"16.72","amount_paid":".00","status":"Rejected ","ship_date":"06\/10\/2021","received_date":"06\/16\/2021","claim_type":"Outbound","created_age":"10 Day","filed_age":"","amount_due":"16.72"},{"claim_number":"19460","claim_number_link":" 19460<\/a>","location":"CLEVELAND ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"FARMERS PRIDE, INC. ","pro_number":"CLE225796 ","carrier_name":"CH ROB PER SCOTT ","claim_amount":"25.05","freight_claim_amount":"4.32","total_claim_amount":"29.37","amount_paid":".00","status":"Rejected ","ship_date":"06\/15\/2021","received_date":"06\/17\/2021","claim_type":"Outbound","created_age":"11 Day","filed_age":"","amount_due":"29.37"},{"claim_number":"19459","claim_number_link":" 19459<\/a>","location":"GERALD ","department_name":"CUSTOMER SERVICE - S\/C ","vendor_customer":"PRECISION FOODS, INC. ","pro_number":"718382523 ","carrier_name":"XPO A #5807228237364 ","claim_amount":"229.00","freight_claim_amount":".00","total_claim_amount":"229.00","amount_paid":"229.00","status":"Closed-Paid ","ship_date":"06\/02\/2021","received_date":"06\/04\/2021","claim_type":"Outbound","created_age":"11 Day","filed_age":"","amount_due":"0.00"},{"claim_number":"19453","claim_number_link":" 19453<\/a>","location":"WATERTOWN ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"CUSTOM SEASONINGS, INC. ","pro_number":"408082883-2 ","carrier_name":"NEW PENN ****CLASS 50**** ","claim_amount":"23.95","freight_claim_amount":"2.61","total_claim_amount":"26.56","amount_paid":".00","status":"Rejected ","ship_date":"06\/10\/2021","received_date":"06\/14\/2021","claim_type":"Outbound","created_age":"11 Day","filed_age":"","amount_due":"26.56"},{"claim_number":"19452","claim_number_link":" 19452<\/a>","location":"GERALD ","department_name":"CUSTOMER SERVICE - S\/C ","vendor_customer":"NEWLY WEDS FOODS,INC. MEM ","pro_number":"352572603 ","carrier_name":"XPO B #5814933616428 ","claim_amount":"424.21","freight_claim_amount":".00","total_claim_amount":"424.21","amount_paid":"424.21","status":"Closed-Paid ","ship_date":"06\/14\/2021","received_date":"06\/15\/2021","claim_type":"Outbound","created_age":"12 Day","filed_age":"","amount_due":"0.00"},{"claim_number":"19450","claim_number_link":" 19450<\/a>","location":"BETHLEHEM ","department_name":"OFFICE - A\/R ","vendor_customer":"NU-TEK NATURAL ING ","pro_number":"7407879872 ","carrier_name":"FEDERAL EXPRESS B ","claim_amount":"21.00","freight_claim_amount":"7.32","total_claim_amount":"28.32","amount_paid":".00","status":"Rejected ","ship_date":"05\/18\/2021","received_date":"05\/21\/2021","claim_type":"Inbound","created_age":"12 Day","filed_age":"","amount_due":"28.32"}]}
Formatting the SQL it looks like this:
It doesn't look like that trace was from when column filtering was being performed as I don't see
FCCLMNin the condition at all. However, the extra where as a whole still doesn't look like it has parenthesis around it as a whole. What if you use:The point is that I think
A and (B and C) or Dis causing a program due to the order. It needs to be grouped:A and ((B and C) or D)or something like that.Allan
Here is the working solution