I believe I found a bug. Not sure where though and looking for suggestion on best way to fix it.
This is code to create sql view script:
return MessageFormat.format(
"CREATE VIEW {0} AS " +
"SELECT T.{1} AS {2}, T.{3} AS {4}, T.{5} AS {6}, " +
"T.{7} AS {8}, T.{9} AS {10}, " +
"MAX(CASE WHEN T.{11} = TS.{12} THEN TS.{13} ELSE '''' END) AS {14}, " +
"MAX(CASE WHEN T.{15} = TS.{16} THEN TS.{17} ELSE '''' END) AS {18}, " +
"MAX(CASE WHEN T.{19} = TS.{20} THEN TS.{21} ELSE 0 END) AS {22}, " +
"MAX(CASE WHEN T.{23} = TS.{24} THEN TS.{25} ELSE 0 END) AS {26}, " +
"MAX(CASE WHEN T.{27} = TS.{28} THEN TS.{29} ELSE '''' END) AS {30}, " +
"MAX(CASE WHEN T.{31} = TS.{32} THEN TS.{33} ELSE '''' END) AS {34}, " +
"MAX(CASE WHEN T.{35} = TS.{36} THEN TS.{37} ELSE 0 END) AS {38}, " +
"MAX(CASE WHEN T.{39} = TS.{40} THEN TS.{41} ELSE 0 END) AS {42}, " +
"COALESCE(MAX(ABS(TA_Weight.{43})), 0) {44}, " +
"COALESCE(MAX(ABS(TA_PayBasis.{45})), 0) {46}, " +
"SUM(COALESCE(ABS(TSA_Miles.{47}), 0)) {48} " +
"FROM {49} T " +
"INNER JOIN {50} TS ON T.{51} = TS.{52} " +
"LEFT OUTER JOIN {53} TA_Weight ON T.{54} = TA_Weight.{55} AND TA_Weight.{56} = ''H:W'' " +
"LEFT OUTER JOIN {57} TA_PayBasis ON T.{58} = TA_PayBasis.{59} AND TA_PayBasis.{60} = ''H:AGR'' " +
"LEFT OUTER JOIN {61} TSA_Miles ON TS.{62} = TSA_Miles.{63} AND TS.{64} = TSA_Miles.{65} AND TSA_Miles.{66} = ''D:DD'' " +
"GROUP BY T.{67}, T.{68}, T.{69}, T.{70}, T.{71}",
TRIP_VIEW_NAME,
TRIP_COL_TRIP_ID, TRIP_VIEW_COL_TRIP_ID, TRIP_COL_IS_VIEWED, TRIP_VIEW_COL_IS_VIEWED, TRIP_COL_IS_ACCEPTED, TRIP_VIEW_COL_IS_ACCEPTED,
TRIP_COL_IS_DECLINED, TRIP_VIEW_COL_IS_DECLINED, TRIP_COL_STATUS, TRIP_VIEW_COL_STATUS,
TRIP_COL_FIRST_STOP_NUMBER, TRIP_STOP_COL_STOP_NUMBER, TRIP_STOP_COL_MUNICIPALITY, TRIP_VIEW_COL_FROM_MUNICIPALITY,
TRIP_COL_FIRST_STOP_NUMBER, TRIP_STOP_COL_STOP_NUMBER, TRIP_STOP_COL_ADMINISTRATIVE_AREA, TRIP_VIEW_COL_FROM_ADMINISTRATIVE_AREA,
TRIP_COL_FIRST_STOP_NUMBER, TRIP_STOP_COL_STOP_NUMBER, TRIP_STOP_COL_SCHEDULED_ARRIVAL_FROM, TRIP_VIEW_COL_PICKUP_DATE,
TRIP_COL_FIRST_STOP_NUMBER, TRIP_STOP_COL_STOP_NUMBER, TRIP_STOP_COL_TIME_ZONE, TRIP_VIEW_COL_PICKUP_TIME_ZONE,
TRIP_COL_LAST_STOP_NUMBER, TRIP_STOP_COL_STOP_NUMBER, TRIP_STOP_COL_MUNICIPALITY, TRIP_VIEW_COL_TO_MUNICIPALITY,
TRIP_COL_LAST_STOP_NUMBER, TRIP_STOP_COL_STOP_NUMBER, TRIP_STOP_COL_ADMINISTRATIVE_AREA, TRIP_VIEW_COL_TO_ADMINISTRATIVE_AREA,
TRIP_COL_LAST_STOP_NUMBER, TRIP_STOP_COL_STOP_NUMBER, TRIP_STOP_COL_SCHEDULED_ARRIVAL_FROM, TRIP_VIEW_COL_DELIVERY_DATE,
TRIP_COL_FIRST_STOP_NUMBER, TRIP_STOP_COL_STOP_NUMBER, TRIP_STOP_COL_TIME_ZONE, TRIP_VIEW_COL_DELIVERY_TIME_ZONE,
TRIP_ATTRIBUTE_COL_VALUE, TRIP_VIEW_COL_WEIGHT,
TRIP_ATTRIBUTE_COL_VALUE, TRIP_VIEW_COL_PAY_BASIS,
TRIP_STOP_ATTRIBUTE_COL_VALUE, TRIP_VIEW_COL_MILES,
TRIP_TABLE_NAME,
TRIP_STOP_TABLE_NAME, TRIP_COL_TRIP_ID, TRIP_STOP_COL_TRIP_ID,
TRIP_ATTRIBUTE_TABLE_NAME, TRIP_COL_TRIP_ID, TRIP_ATTRIBUTE_COL_TRIP_ID, TRIP_ATTRIBUTE_COL_NAME,
TRIP_ATTRIBUTE_TABLE_NAME, TRIP_COL_TRIP_ID, TRIP_ATTRIBUTE_COL_TRIP_ID, TRIP_ATTRIBUTE_COL_NAME,
TRIP_STOP_ATTRIBUTE_TABLE_NAME, TRIP_STOP_COL_TRIP_ID, TRIP_STOP_ATTRIBUTE_COL_TRIP_ID, TRIP_STOP_COL_STOP_NUMBER, TRIP_STOP_ATTRIBUTE_COL_STOP_NUMBER, TRIP_STOP_ATTRIBUTE_COL_NAME,
TRIP_COL_TRIP_ID, TRIP_COL_IS_VIEWED, TRIP_COL_IS_ACCEPTED, TRIP_COL_IS_DECLINED, TRIP_COL_STATUS);
You see how I escape single tick with double tick, '''' should become '' in resulting string in my understanding.
Running this code in Android emulator I get following string:
CREATE VIEW TripView AS SELECT T.TripId AS TripId, T.IsViewed AS IsViewed, T.IsAccepted AS IsAccepted, T.IsDeclined AS IsDeclined, T.Status AS Status,
MAX(CASE WHEN T.FirstDispatchedStopNumber = TS.StopNumber THEN TS.Municipality ELSE '' END)
AS FromMunicipality, MAX(CASE WHEN T.FirstDispatchedStopNumber = TS.StopNumber THEN TS.AdministrativeArea ELSE '' END) AS FromAdministrativeArea, MAX(CASE WHEN T.FirstDispatchedStopNumber = TS.StopNumber THEN TS.ScheduledArrivalFrom ELSE 0 END) AS PickupDate, MAX(CASE WHEN T.FirstDispatchedStopNumber = TS.StopNumber THEN TS.TimeZone ELSE 0 END) AS PickupTimeZone, MAX(CASE WHEN T.LastDispatchedStopNumber = TS.StopNumber THEN TS.Municipality ELSE '' END) AS ToMunicipality, MAX(CASE WHEN T.LastDispatchedStopNumber = TS.StopNumber THEN TS.AdministrativeArea ELSE '' END) AS ToAdministrativeArea, MAX(CASE WHEN T.LastDispatchedStopNumber = TS.StopNumber THEN TS.ScheduledArrivalFrom ELSE 0 END) AS DeliveryDate, MAX(CASE WHEN T.FirstDispatchedStopNumber = TS.StopNumber THEN TS.TimeZone ELSE 0 END) AS DeliveryTimeZone, COALESCE(MAX(ABS(TA_Weight.Value)), 0) Weight, COALESCE(MAX(ABS(TA_PayBasis.Value)), 0) PayBasis, SUM(COALESCE(ABS(TSA_Miles.Value), 0)) Miles FROM Trip T INNER JOIN TripStop TS ON T.TripId = TS.TripId LEFT OUTER JOIN TripAttribute TA_Weight ON T.TripId = TA_Weight.TripId AND TA_Weight.Name = 'H:W' LEFT OUTER JOIN TripAttribute TA_PayBasis ON T.TripId = TA_PayBasis.TripId AND TA_PayBasis.Name = 'H:AGR' LEFT OUTER JOIN TripStopAttribute TSA_Miles ON TS.TripId = TSA_Miles.TripId AND TS.StopNumber = TSA_Miles.StopNumber AND TSA_Miles.Name = 'D:DD' GROUP BY T.TripId, T.IsViewed, T.IsAccepted, T.IsDeclined, T.Status
When I run on actual device I get following:
CREATE VIEW TripView AS SELECT T.TripId AS TripId, T.IsViewed AS IsViewed, T.IsAccepted AS IsAccepted, T.IsDeclined AS IsDeclined, T.Status AS Status,
MAX(CASE WHEN T.FirstDispatchedStopNumber = TS.StopNumber THEN TS.Municipality ELSE ''' END)
AS FromMunicipality, MAX(CASE WHEN T.FirstDispatchedStopNumber = TS.StopNumber THEN TS.AdministrativeArea ELSE ''' END) AS FromAdministrativeArea, MAX(CASE WHEN T.FirstDispatchedStopNumber = TS.StopNumber THEN TS.ScheduledArrivalFrom ELSE 0 END) AS PickupDate, MAX(CASE WHEN T.FirstDispatchedStopNumber = TS.StopNumber THEN TS.TimeZone ELSE 0 END) AS PickupTimeZone, MAX(CASE WHEN T.LastDispatchedStopNumber = TS.StopNumber THEN TS.Municipality ELSE ''' END) AS ToMunicipality, MAX(CASE WHEN T.LastDispatchedStopNumber = TS.StopNumber THEN TS.AdministrativeArea ELSE ''' END) AS ToAdministrativeArea, MAX(CASE WHEN T.LastDispatchedStopNumber = TS.StopNumber THEN TS.ScheduledArrivalFrom ELSE 0 END) AS DeliveryDate, MAX(CASE WHEN T.FirstDispatchedStopNumber = TS.StopNumber THEN TS.TimeZone ELSE 0 END) AS DeliveryTimeZone, COALESCE(MAX(ABS(TA_Weight.Value)), 0) Weight, COALESCE(MAX(ABS(TA_PayBasis.Value)), 0) PayBasis, SUM(COALESCE(ABS(TSA_Miles.Value), 0)) Miles FROM Trip T INNER JOIN TripStop TS ON T.TripId = TS.TripId LEFT OUTER JOIN TripAttribute TA_Weight ON T.TripId = TA_Weight.TripId AND TA_Weight.Name = 'H:W' LEFT OUTER JOIN TripAttribute TA_PayBasis ON T.TripId = TA_PayBasis.TripId AND TA_PayBasis.Name = 'H:AGR' LEFT OUTER JOIN TripStopAttribute TSA_Miles ON TS.TripId = TSA_Miles.TripId AND TS.StopNumber = TSA_Miles.StopNumber AND TSA_Miles.Name = 'D:DD' GROUP BY T.TripId, T.IsViewed, T.IsAccepted, T.IsDeclined, T.Status
Looks like a bug somewhere. Line 3 of script shows difference, '' vs '''
This certainly looks wrong according to the rules of the MessageFormat reference. Your emulator seems to be doing the correct thing, but the JRE on the device seems to be interpretting '''' incorrectly.
Another way to represent two single quotes is to escape them like this: \'\'\'\' which might give your device's JRE enough information that you want them treated separately. This may still result in the same error, but worth a try in case it works.
My guess for how it comes out with three single quotes is:
It's that middle one that would be wrong. The 2nd single quote was consumed in the first pass, so shouldn't be considered part of another pair.
It would be worth reporting this to the source of the JRE used on your device.
A simpler test case is this:
System.out.println(MessageFormat.format("Say ''''{0}''''", "hello"));
Which should give
Say ''hello''
as output, but should show three single quotes instead of two before and after 'hello' with the device's JRE.