This query runs fine on SQL Server but errors out on WebFOCUS. Any idea how to fix this issue? The error I get is on the bottom.

 SELECT DISTINCT
S.SHIPMENTDATE HAWB_DATE,
S.SHIPMENTNUMBER HAWB_NO,
ISNULL((SELECT CONVERT(VARCHAR(3),CARRIERCODENUMERIC)+ROUTINGNUMBER
FROM ROUTING WITH (NOLOCK) WHERE ROUTING.ROUTINGID = SA.LASTROUTINGID),' ')
MAWB_NO,
ISNULL(DBO.GETREFERENCELIST(S.SHIPMENTID),' ') ORDER_NUMBER,
S.ORIGINLOCATIONCODE ORIGIN,
S.DESTINATIONLOCATIONCODE DESTINATION,
CASE WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) IN ('D','P','A') THEN
	CASE WHEN SA.PICKUPSTATUSID = 0
		THEN 'Missing'
	ELSE ' '
	END
    WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) NOT IN ('D','P','A') THEN
	CASE WHEN SA.PICKUPSTATUSID = 0
		THEN 'Missing'
	ELSE ' '
	END
END MISSING_PICKUP_DATE,
CASE WHEN ISNULL((SELECT TOP 1 SHIPPINGSTATUSID
		FROM SHIPMENTSTATUSEVENTS SS WITH (NOLOCK)
		WHERE SS.SHIPMENTID = S.SHIPMENTID
		AND SS.SHIPPINGSTATUSID IN (61,87)),0) = 0
			THEN 'Missing'
		ELSE ' '
END MISSING_TENDERED_TO_CARRIER,
CASE WHEN SA.FIRSTROUTINGFIRSTLEGSEGMENTACTUALID = 0
	THEN 'Missing'
	ELSE ' '
END MISSING_ATD,
CASE WHEN ISNULL(SA.LASTROUTINGLASTLEGSEGMENTACTUALID,0) = 0
	THEN 'Missing'
	ELSE ' '
END MISSING_ATA,
CASE WHEN LEFT(S.DELIVERYSERVICETYPECODE,1) IN ('A','P') AND RIGHT(S.DELIVERYSERVICETYPECODE,1) IN ('A','P') THEN
	CASE WHEN SA.DOCUMENTSTOBROKERSTATUSID = 0
		THEN 'Missing'
		ELSE ' '
	END
	WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) = 'D' THEN ' '
	WHEN SA.CUSTOMSRELEASESTATUSID <> 0 THEN ' '
	WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) NOT IN ('D', 'P', 'A')
		AND SA.CUSTOMSRELEASESTATUSID = 0
		AND SA.CUSTOMSRELEASESTATUSID = 0
		THEN 'Missing'
		ELSE ' '
	END MISSING_DOCS_TO_BROKER,
CASE WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) = 'D' THEN
	CASE WHEN SA.CUSTOMSRELEASESTATUSID = 0
		THEN 'Missing'
		ELSE ' '
	END
		WHEN SA.DOCUMENTSTOBROKERSTATUSID <> 0 THEN ' '
		WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) NOT IN ('D', 'P', 'A')
			AND SA.CUSTOMSRELEASESTATUSID = 0
			AND SA.DOCUMENTSTOBROKERSTATUSID = 0
		THEN 'Missing'
		ELSE ' '
END MISSING_CLEARED_CUSTOMS,
CASE WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) = 'D' THEN
	CASE WHEN ISNULL((SELECT TOP 1 SHIPPINGSTATUSID
			FROM SHIPMENTSTATUSEVENTS SS WITH (NOLOCK)
			WHERE SS.SHIPMENTID = S.SHIPMENTID
				AND SS.SHIPPINGSTATUSID IN (42,44,46)),0) = 0 				THEN 'Missing'
			ELSE ' '
	END
WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) IN ('A','P') THEN ' '
WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) NOT IN ('D', 'P', 'A')
	AND ISNULL((SELECT TOP 1 SHIPPINGSTATUSID
		FROM SHIPMENTSTATUSEVENTS SS WITH (NOLOCK)
		WHERE SS.SHIPMENTID = S.SHIPMENTID
		AND SS.SHIPPINGSTATUSID IN (42,44,46)),0) = 0
		THEN 'Missing'
		ELSE ' '
END MISSING_POD,
SPI.CONSIGNEEACCOUNTNAME CONSIGNEE_NAME,
(SELECT COUNTRYNAME
	FROM LOCATIONSCITYSTATECOUNTRY WITH (NOLOCK, NOEXPAND)
	WHERE S.DESTINATIONLOCATIONCODE = LOCATIONCODE)
DESTINATION_COUNTRY,
CASE WHEN S.PRIORITYSERVICELEVELCODE = ''
	THEN 'Missing'
	ELSE S.PRIORITYSERVICELEVELCODE
END SERVICE_LEVEL,
CASE WHEN S.DELIVERYSERVICETYPECODE = ''
	THEN 'Missing'
	ELSE S.DELIVERYSERVICETYPECODE
END DELIVERY_TERMS,
S.TOTALPIECES NUMBER_OF_PIECES,
CASE WHEN S.TOTALWEIGHTUOMID = 56
	THEN ROUND((S.TOTALWEIGHT/2.2046),2)
	ELSE S.TOTALWEIGHT
END ACTUAL_WEIGHT_KGS,
CASE WHEN S.TOTALCHARGEABLEWEIGHTUOMID = 56
	THEN ROUND((S.TOTALCHARGEABLEWEIGHT/2.2046),2)
	ELSE S.TOTALCHARGEABLEWEIGHT
END CHARGE_WEIGHT_KGS,
(SELECT PSC.BILLINGCURRENCYCODE
	FROM PROFILESHIPMENTCHARGES PSC WITH (NOLOCK)
	WHERE PSC.SHIPMENTID = S.SHIPMENTID AND PSC.PROFILEID = 6088)
CURRENCY,
(SELECT PSC.BILLINGAMOUNT
	FROM PROFILESHIPMENTCHARGES PSC WITH (NOLOCK)
	WHERE PSC.SHIPMENTID = S.SHIPMENTID AND PSC.PROFILEID = 6088)
INVOICE_AMOUNT
FROM PROFILESHIPMENTS PS WITH (NOLOCK)
	INNER JOIN SHIPMENTS S WITH (NOLOCK)
		ON PS.SHIPMENTID = S.SHIPMENTID
	INNER JOIN SHIPMENTPARTYINFO SPI WITH (NOLOCK)
		ON PS.SHIPMENTID = SPI.SHIPMENTID
	LEFT JOIN SHIPMENTACTIVITY SA WITH (NOLOCK)
		ON PS.SHIPMENTID = SA.SHIPMENTID
WHERE PS.PROFILEID = 6088
	AND PS.SHIPMENTDATE >= CONVERT(VARCHAR(12),GETDATE()-60,101)
	AND PS.SHIPMENTDATE < CONVERT(VARCHAR(12),GETDATE(),101)
	AND S.SHIPMENTTYPEID = 1 AND S.SYSTEMMODULEID = 1
	ORDER BY HAWB_Date, HAWB_NO
; 

==================================================ERROR===============================================================

--------------------------------------------------------------------------------

No HTML Output!

--------------------------------------------------------------------------------


(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN:
: (0) [ ] A processing error Incorrect syntax near the keyword 'set'.
: has occurred.
L (FOC1405) SQL PREPARE ERROR.
(FOC1517) UNRECOGNIZED COMMAND WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) NOT IN
('D','P','A') THEN
(FOC1517) UNRECOGNIZED COMMAND CASE WHEN SA.PICKUPSTATUSID = 0
(FOC1517) UNRECOGNIZED COMMAND THEN 'MISSING'
(FOC1517) UNRECOGNIZED COMMAND ELSE ' '
(FOC1517) UNRECOGNIZED COMMAND CASE WHEN ISNULL((SELECT TOP 1 SHIPPINGSTATUSID
(FOC1517) UNRECOGNIZED COMMAND FROM SHIPMENTSTATUSEVENTS SS WITH (NOLOCK)
(FOC1517) UNRECOGNIZED COMMAND WHERE SS.SHIPMENTID = S.SHIPMENTID
(FOC1517) UNRECOGNIZED COMMAND AND SS.SHIPPINGSTATUSID IN (61,87)),0) = 0
(FOC1517) UNRECOGNIZED COMMAND THEN 'MISSING'
(FOC1517) UNRECOGNIZED COMMAND ELSE ' '
(FOC1517) UNRECOGNIZED COMMAND CASE WHEN SA.FIRSTROUTINGFIRSTLEGSEGMENTACTUALID
= 0
(FOC1517) UNRECOGNIZED COMMAND THEN 'MISSING'
(FOC1517) UNRECOGNIZED COMMAND ELSE ' '
(FOC1517) UNRECOGNIZED COMMAND CASE WHEN
ISNULL(SA.LASTROUTINGLASTLEGSEGMENTACTUALID,0) = 0
(FOC1517) UNRECOGNIZED COMMAND THEN 'MISSING'
(FOC1517) UNRECOGNIZED COMMAND ELSE ' '
(FOC1517) UNRECOGNIZED COMMAND CASE WHEN LEFT(S.DELIVERYSERVICETYPECODE,1) IN
('A','P') AND RIGHT(S.DELIVERYSERVICETYPECODE,1) IN ('A','P') THEN
(FOC1517) UNRECOGNIZED COMMAND CASE WHEN SA.DOCUMENTSTOBROKERSTATUSID = 0
(FOC1517) UNRECOGNIZED COMMAND THEN 'MISSING'
(FOC1517) UNRECOGNIZED COMMAND ELSE ' '
(FOC1517) UNRECOGNIZED COMMAND WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) = 'D'
THEN ' '
(FOC1517) UNRECOGNIZED COMMAND WHEN SA.CUSTOMSRELEASESTATUSID <> 0 THEN ' '
(FOC1517) UNRECOGNIZED COMMAND WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) NOT IN
('D', 'P', 'A')
(FOC1517) UNRECOGNIZED COMMAND AND SA.CUSTOMSRELEASESTATUSID = 0
(FOC1517) UNRECOGNIZED COMMAND AND SA.CUSTOMSRELEASESTATUSID = 0
(FOC1517) UNRECOGNIZED COMMAND THEN 'MISSING'
(FOC1517) UNRECOGNIZED COMMAND ELSE ' '
(FOC1517) UNRECOGNIZED COMMAND CASE WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) =
'D' THEN
(FOC1517) UNRECOGNIZED COMMAND CASE WHEN SA.CUSTOMSRELEASESTATUSID = 0
(FOC1517) UNRECOGNIZED COMMAND THEN 'MISSING'
(FOC1517) UNRECOGNIZED COMMAND ELSE ' '
(FOC1517) UNRECOGNIZED COMMAND WHEN SA.DOCUMENTSTOBROKERSTATUSID <> 0 THEN '
'
(FOC1517) UNRECOGNIZED COMMAND WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) NOT IN
('D', 'P', 'A')
(FOC1517) UNRECOGNIZED COMMAND AND SA.CUSTOMSRELEASESTATUSID = 0
(FOC1517) UNRECOGNIZED COMMAND AND SA.DOCUMENTSTOBROKERSTATUSID = 0
(FOC1517) UNRECOGNIZED COMMAND THEN 'MISSING'
(FOC1517) UNRECOGNIZED COMMAND ELSE ' '
(FOC1517) UNRECOGNIZED COMMAND CASE WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) =
'D' THEN
(FOC1517) UNRECOGNIZED COMMAND CASE WHEN ISNULL((SELECT TOP 1 SHIPPINGSTATUSID
(FOC1517) UNRECOGNIZED COMMAND FROM SHIPMENTSTATUSEVENTS SS WITH (NOLOCK)
(FOC1517) UNRECOGNIZED COMMAND WHERE SS.SHIPMENTID = S.SHIPMENTID
(FOC1517) UNRECOGNIZED COMMAND AND SS.SHIPPINGSTATUSID IN (42,44,46)),0) =
0 THEN 'MISSING'
(FOC1517) UNRECOGNIZED COMMAND ELSE ' '
(FOC1517) UNRECOGNIZED COMMAND WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) IN
('A','P') THEN ' '
(FOC1517) UNRECOGNIZED COMMAND WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) NOT IN
('D', 'P', 'A')
(FOC1517) UNRECOGNIZED COMMAND AND ISNULL((SELECT TOP 1 SHIPPINGSTATUSID
(FOC1517) UNRECOGNIZED COMMAND FROM SHIPMENTSTATUSEVENTS SS WITH (NOLOCK)
(FOC1517) UNRECOGNIZED COMMAND WHERE SS.SHIPMENTID = S.SHIPMENTID
(FOC1517) UNRECOGNIZED COMMAND AND SS.SHIPPINGSTATUSID IN (42,44,46)),0) = 0
(FOC1517) UNRECOGNIZED COMMAND THEN 'MISSING'
(FOC1517) UNRECOGNIZED COMMAND ELSE ' '
(FOC1517) UNRECOGNIZED COMMAND SPI.CONSIGNEEACCOUNTNAME CONSIGNEE_NAME,
(FOC1517) UNRECOGNIZED COMMAND (SELECT COUNTRYNAME
(FOC1517) UNRECOGNIZED COMMAND FROM LOCATIONSCITYSTATECOUNTRY WITH (NOLOCK,
NOEXPAND)
(FOC1517) UNRECOGNIZED COMMAND WHERE S.DESTINATIONLOCATIONCODE = LOCATIONCODE)
(FOC1517) UNRECOGNIZED COMMAND DESTINATION_COUNTRY,
(FOC1517) UNRECOGNIZED COMMAND CASE WHEN S.PRIORITYSERVICELEVELCODE = ''
(FOC1517) UNRECOGNIZED COMMAND THEN 'MISSING'
(FOC1517) UNRECOGNIZED COMMAND ELSE S.PRIORITYSERVICELEVELCODE
(FOC1517) UNRECOGNIZED COMMAND CASE WHEN S.DELIVERYSERVICETYPECODE = ''
(FOC1517) UNRECOGNIZED COMMAND THEN 'MISSING'
(FOC1517) UNRECOGNIZED COMMAND ELSE S.DELIVERYSERVICETYPECODE
(FOC1517) UNRECOGNIZED COMMAND S.TOTALPIECES NUMBER_OF_PIECES,
(FOC1517) UNRECOGNIZED COMMAND CASE WHEN S.TOTALWEIGHTUOMID = 56
(FOC1517) UNRECOGNIZED COMMAND THEN ROUND((S.TOTALWEIGHT/2.2046),2)
(FOC1517) UNRECOGNIZED COMMAND ELSE S.TOTALWEIGHT
(FOC1517) UNRECOGNIZED COMMAND CASE WHEN S.TOTALCHARGEABLEWEIGHTUOMID = 56
(FOC1517) UNRECOGNIZED COMMAND THEN ROUND((S.TOTALCHARGEABLEWEIGHT/2.2046),2)
(FOC1517) UNRECOGNIZED COMMAND ELSE S.TOTALCHARGEABLEWEIGHT
(FOC1517) UNRECOGNIZED COMMAND (SELECT PSC.BILLINGCURRENCYCODE
(FOC1517) UNRECOGNIZED COMMAND FROM PROFILESHIPMENTCHARGES PSC WITH (NOLOCK)
(FOC1517) UNRECOGNIZED COMMAND WHERE PSC.SHIPMENTID = S.SHIPMENTID AND
PSC.PROFILEID = 6088)
(FOC1517) UNRECOGNIZED COMMAND CURRENCY,
(FOC1517) UNRECOGNIZED COMMAND (SELECT PSC.BILLINGAMOUNT
(FOC1517) UNRECOGNIZED COMMAND FROM PROFILESHIPMENTCHARGES PSC WITH (NOLOCK)
(FOC1517) UNRECOGNIZED COMMAND WHERE PSC.SHIPMENTID = S.SHIPMENTID AND
PSC.PROFILEID = 6088)
(FOC1517) UNRECOGNIZED COMMAND INVOICE_AMOUNT
(FOC1517) UNRECOGNIZED COMMAND FROM PROFILESHIPMENTS PS WITH (NOLOCK)
(FOC1517) UNRECOGNIZED COMMAND INNER JOIN SHIPMENTS S WITH (NOLOCK)
(FOC1517) UNRECOGNIZED COMMAND ON PS.SHIPMENTID = S.SHIPMENTID
(FOC1517) UNRECOGNIZED COMMAND INNER JOIN SHIPMENTPARTYINFO SPI WITH (NOLOCK)
(FOC1517) UNRECOGNIZED COMMAND ON PS.SHIPMENTID = SPI.SHIPMENTID
(FOC1517) UNRECOGNIZED COMMAND LEFT JOIN SHIPMENTACTIVITY SA WITH (NOLOCK)
(FOC1517) UNRECOGNIZED COMMAND ON PS.SHIPMENTID = SA.SHIPMENTID
(FOC1517) UNRECOGNIZED COMMAND WHERE PS.PROFILEID = 6088
(FOC1517) UNRECOGNIZED COMMAND AND PS.SHIPMENTDATE >=
CONVERT(VARCHAR(12),GETDATE()-60,101)
(FOC1517) UNRECOGNIZED COMMAND AND PS.SHIPMENTDATE <
CONVERT(VARCHAR(12),GETDATE(),101)
(FOC1517) UNRECOGNIZED COMMAND AND S.SHIPMENTTYPEID = 1 AND S.SYSTEMMODULEID =
1
(FOC1517) UNRECOGNIZED COMMAND ORDER BY HAWB_DATE, HAWB_NO
(FOC1517) UNRECOGNIZED COMMAND ;


Thanks,
Sayed

Open a problem with help desk. I've seen something like this before, and it had something to do with spaces and the case statements, but I cannot remember the specifics. I just remember that it was resolved by removing some very specific white space.

Thanks Dhagen. I do have a case open http://techsupport.informationbuilders.com/iro/casehistory.jsp?case=32762527

Found a solution to this issue. WebFOCUS does not seem to like CASE statements without parenthesis. The code below works just fine.

  SELECT DISTINCT
convert(varchar(12),S.SHIPMENTDATE,101) HAWB_DATE,
S.SHIPMENTNUMBER HAWB_NO,
ISNULL((SELECT CONVERT(VARCHAR(3),CARRIERCODENUMERIC)+ROUTINGNUMBER
FROM ROUTING WITH (NOLOCK) WHERE ROUTING.ROUTINGID = SA.LASTROUTINGID),' ')
MAWB_NO,
ISNULL(DBO.GETREFERENCELIST(S.SHIPMENTID),' ') ORDER_NUMBER,
S.ORIGINLOCATIONCODE ORIGIN,
S.DESTINATIONLOCATIONCODE DESTINATION,
(CASE WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) IN ('D','P','A') THEN
	(CASE WHEN SA.PICKUPSTATUSID = 0
		THEN 'Missing'
	ELSE ' '
	END)
    WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) NOT IN ('D','P','A') THEN
	(CASE WHEN SA.PICKUPSTATUSID = 0
		THEN 'Missing'
	ELSE ' '
	END)
END) MISSING_PICKUP_DATE,
(CASE WHEN ISNULL((SELECT TOP 1 SHIPPINGSTATUSID
		FROM SHIPMENTSTATUSEVENTS SS WITH (NOLOCK)
		WHERE SS.SHIPMENTID = S.SHIPMENTID
		AND SS.SHIPPINGSTATUSID IN (61,87)),0) = 0
			THEN 'Missing'
		ELSE ' '
END) MISSING_TENDERED_TO_CARRIER,
(CASE WHEN SA.FIRSTROUTINGFIRSTLEGSEGMENTACTUALID = 0
	THEN 'Missing'
	ELSE ' '
END) MISSING_ATD,
(CASE WHEN ISNULL(SA.LASTROUTINGLASTLEGSEGMENTACTUALID,0) = 0
	THEN 'Missing'
	ELSE ' '
END) MISSING_ATA,
(CASE WHEN LEFT(S.DELIVERYSERVICETYPECODE,1) IN ('A','P')
			AND RIGHT(S.DELIVERYSERVICETYPECODE,1) IN ('A','P') THEN
	(CASE WHEN SA.DOCUMENTSTOBROKERSTATUSID = 0
		THEN 'Missing'
		ELSE ' '
	END)
	WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) = 'D' THEN ' '
	WHEN SA.CUSTOMSRELEASESTATUSID <> 0 THEN ' '
	WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) NOT IN ('D', 'P', 'A')
		AND SA.CUSTOMSRELEASESTATUSID = 0
		AND SA.CUSTOMSRELEASESTATUSID = 0
		THEN 'Missing'
		ELSE ' '
	END) MISSING_DOCS_TO_BROKER,
(CASE WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) = 'D' THEN
	(CASE WHEN SA.CUSTOMSRELEASESTATUSID = 0
		THEN 'Missing'
		ELSE ' '
	END)
		WHEN SA.DOCUMENTSTOBROKERSTATUSID <> 0 THEN ' '
		WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) NOT IN ('D', 'P', 'A')
			AND SA.CUSTOMSRELEASESTATUSID = 0
			AND SA.DOCUMENTSTOBROKERSTATUSID = 0
		THEN 'Missing'
		ELSE ' '
END) MISSING_CLEARED_CUSTOMS,
(CASE WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) = 'D' THEN
	(CASE WHEN ISNULL((SELECT TOP 1 SHIPPINGSTATUSID
			FROM SHIPMENTSTATUSEVENTS SS WITH (NOLOCK)
			WHERE SS.SHIPMENTID = S.SHIPMENTID
				AND SS.SHIPPINGSTATUSID IN (42,44,46)),0) = 0 				THEN 'Missing'
			ELSE ' '
	END)
WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) IN ('A','P') THEN ' '
WHEN RIGHT(S.DELIVERYSERVICETYPECODE,1) NOT IN ('D', 'P', 'A')
	AND ISNULL((SELECT TOP 1 SHIPPINGSTATUSID
		FROM SHIPMENTSTATUSEVENTS SS WITH (NOLOCK)
		WHERE SS.SHIPMENTID = S.SHIPMENTID
		AND SS.SHIPPINGSTATUSID IN (42,44,46)),0) = 0
		THEN 'Missing'
		ELSE ' '
END) MISSING_POD,
SPI.CONSIGNEEACCOUNTNAME CONSIGNEE_NAME,
(SELECT COUNTRYNAME
	FROM LOCATIONSCITYSTATECOUNTRY WITH (NOLOCK, NOEXPAND)
	WHERE S.DESTINATIONLOCATIONCODE = LOCATIONCODE)
DESTINATION_COUNTRY,
CASE WHEN S.PRIORITYSERVICELEVELCODE = ''
	THEN 'Missing'
	ELSE S.PRIORITYSERVICELEVELCODE
END SERVICE_LEVEL,
CASE WHEN S.DELIVERYSERVICETYPECODE = ''
	THEN 'Missing'
	ELSE S.DELIVERYSERVICETYPECODE
END DELIVERY_TERMS,
S.TOTALPIECES NUMBER_OF_PIECES,
CASE WHEN S.TOTALWEIGHTUOMID = 56
	THEN ROUND((S.TOTALWEIGHT/2.2046),2)
	ELSE S.TOTALWEIGHT
END ACTUAL_WEIGHT_KGS,
CASE WHEN S.TOTALCHARGEABLEWEIGHTUOMID = 56
	THEN ROUND((S.TOTALCHARGEABLEWEIGHT/2.2046),2)
	ELSE S.TOTALCHARGEABLEWEIGHT
END CHARGE_WEIGHT_KGS,
(SELECT PSC.BILLINGCURRENCYCODE
	FROM PROFILESHIPMENTCHARGES PSC WITH (NOLOCK)
	WHERE PSC.SHIPMENTID = S.SHIPMENTID AND PSC.PROFILEID = 6088)
CURRENCY,
(SELECT PSC.BILLINGAMOUNT
	FROM PROFILESHIPMENTCHARGES PSC WITH (NOLOCK)
	WHERE PSC.SHIPMENTID = S.SHIPMENTID AND PSC.PROFILEID = 6088)
INVOICE_AMOUNT
FROM PROFILESHIPMENTS PS WITH (NOLOCK)
	INNER JOIN SHIPMENTS S WITH (NOLOCK)
		ON PS.SHIPMENTID = S.SHIPMENTID
	INNER JOIN SHIPMENTPARTYINFO SPI WITH (NOLOCK)
		ON PS.SHIPMENTID = SPI.SHIPMENTID
	LEFT JOIN SHIPMENTACTIVITY SA WITH (NOLOCK)
		ON PS.SHIPMENTID = SA.SHIPMENTID
WHERE PS.PROFILEID = 6088
	AND PS.SHIPMENTDATE >= CONVERT(VARCHAR(12),GETDATE()-60,101)
	AND PS.SHIPMENTDATE < CONVERT(VARCHAR(12),GETDATE(),101)
	AND S.SHIPMENTTYPEID = 1 AND S.SYSTEMMODULEID = 1
	ORDER BY HAWB_Date, HAWB_NO
;