Chuyển tôi câu lệnh sql từ distinst sang sử dụng group by:
SELECT
DISTINCT pfc.pfc_app_status,
pfc.pfc_record_id,
case
when (select
count()
from
pfc_main_detail
where
pfcd_record_id=pfc.pfc_record_id
and pfcd_pctab='P'
and pfcd_container_type=pfcd.pfcd_container_type) > 0 then 'P'
else '-'
end as fp,
case
when (select
count()
from
pfc_main_detail
where
pfcd_record_id=pfc.pfc_record_id
and pfcd_pctab='C'
and pfcd_container_type=pfcd.pfcd_container_type) > 0 then 'C'
else '-'
end as fc,
pfc.pfc_freight_party_id,
pfcd.pfcd_container_type,
pfc.pfc_container_status,
pfc.pfc_place_of_receipt_code,
pfc.pfc_load_port_code,
pfc.pfc_discharge_port_code,
pfc.pfc_place_of_delivery_code,
pfc.pfc_final_destination_code,
pfc.pfc_port_of_transshipment1_code,
pfc.pfc_effective_from,
pfc.pfc_effective_to,
pfc.pfc_receipt_type_code,
pfc.pfc_delivery_type_code,
pfc.pfc_dg_class,
pfc.pfc_vessel_id,
pfc.pfc_voyage,
pfc.pfc_port_of_transshipment2_code,
pfc.pfc_port_of_transshipment3_code,
pfc.pfc_shipment_type,
(case
when pfc.pfc_detention = 'R' then pfc.pfc_detention || ' ' || pfc.pfc_detention_request
else pfc.pfc_detention
end) as detention,
(case
when pfc.pfc_demurrage = 'R' then pfc.pfc_demurrage || ' ' || pfc.pfc_demurrage_request
else pfc.pfc_demurrage
end) as demurrage,
pfc.pfc_commodity_id,
pfc.pfc_is_commission,
pfc.pfc_user_requested,
pfc.pfc_date_requested,
pfc.pfc_user_approved,
pfc.pfc_date_approved,
pfc.pfc_user_modified,
pfc.pfc_date_modified,
pfc.pfc_is_valid,
pfc.pfc_user_invalid,
pfc.pfc_date_invalid,
pfc.pfc_is_deleted,
pfc.pfc_user_deleted,
pfc.pfc_date_deleted,
pfc.pfc_reason_deleted,
coalesce(fp.customer_name,
'') as pfcFreightPartyName,
coalesce(por.location_name,
'') as pfcPlaceOfReceiptName,
coalesce(pol.location_name,
'') as pfcPortOfLoadingName,
coalesce(pod.location_name,
'') as pfcPortOfDischargeName,
coalesce(podl.location_name,
'') as pfcPlaceOfDeliveryName,
coalesce(fdest.location_name,
'') as pfcFinalDestinationName,
coalesce(pot.location_name,
'') as pfcPortOfTransshipment1Name,
coalesce(pot2.location_name,
'') as pfcPortOfTransshipment2Name,
coalesce(pot3.location_name,
'') as pfcPortOfTransshipment3Name,
coalesce(comm.commodity_name,
'') as pfcCommodityName,
coalesce(mv.vessel_name,
'') as pfcVesselName,
pfc.pfc_booking_party_id,
pfc.pfc_shipper_id,
coalesce(bp.customer_name,
'') as pfcBookingPartyName,
coalesce(sp.customer_name,
'') as pfcShipperName,
(case
when pfc.pfc_detention_pol = 'R' then pfc.pfc_detention_pol || ' ' || pfc.pfc_detention_request_pol
else pfc.pfc_detention_pol
end) as detention_pol,
(case
when pfc.pfc_demurrage_pol = 'R' then pfc.pfc_demurrage_pol || ' ' || pfc.pfc_demurrage_request_pol
else pfc.pfc_demurrage_pol
end) as demurrage_pol
FROM
pfc_main_header pfc
LEFT JOIN
pfc_main_detail pfcd
ON pfcd.pfcd_record_id=pfc.pfc_record_id
LEFT JOIN
master_customers_ft fp
ON fp.customer_id= pfc.pfc_freight_party_id
LEFT JOIN
master_locations_ft por
ON por.location_code=pfc.pfc_place_of_receipt_code
LEFT JOIN
master_locations_ft pol
ON pol.location_code=pfc.pfc_load_port_code
LEFT JOIN
master_locations_ft pod
ON pod.location_code=pfc.pfc_discharge_port_code
LEFT JOIN
master_locations_ft podl
ON podl.location_code=pfc.pfc_place_of_delivery_code
LEFT JOIN
master_locations_ft fdest
ON fdest.location_code=pfc.pfc_final_destination_code
LEFT JOIN
master_locations_ft pot
ON pot.location_code=pfc.pfc_port_of_transshipment1_code
LEFT JOIN
master_locations_ft pot2
ON pot2.location_code=pfc.pfc_port_of_transshipment2_code
LEFT JOIN
master_locations_ft pot3
ON pot3.location_code=pfc.pfc_port_of_transshipment3_code
LEFT JOIN
master_commodities_ft comm
ON comm.commodity_id=pfc.pfc_commodity_id
LEFT JOIN
master_vessels_ft mv
ON mv.vessel_id=pfc.pfc_vessel_id
LEFT JOIN
master_customers_ft bp
ON bp.customer_id=pfc.pfc_booking_party_id
LEFT JOIN
master_customers_ft sp
ON sp.customer_id=pfc.pfc_shipper_id
WHERE
1=1
AND pfc.pfc_app_status = 'C'
AND pfc.pfc_receipt_type_code='CY'
AND pfc.pfc_delivery_type_code='CY'
AND pfc.pfc_is_deleted='N'
ORDER BY
pfc_date_modified DESC OFFSET 0 LIMIT 10