GAT專案新需求：車輛基本資訊查詢增加勾選不包含下級
程式碼：
SQL語句：
superuser登入，預設查全部，包含下級：
--[936, 2000, 0, 936, 2000, 0, -1, 2000, 0]
select *
from (select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(936, 2000, 0)) f
where h.appsysid = f.appsysid
and h.appdeptid = f.appdeptid)
union
select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(936, 2000, 0)) g
where g.appsysid = e.owner_appsysid
and g.appdeptid = e.owner_appdeptid)) t
where exists (select appsysid, appdeptid
from table(get_subdept(-1, 2000, 0)) g
where g.appsysid = t.appsysid
and g.appdeptid = t.appdeptid)
order by t.updatetimeorder desc, t.hostno
--[936, 2000, 0, 936, 2000, 0, 2000, 0]
select *
from (select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(936, 2000, 0)) f
where h.appsysid = f.appsysid
and h.appdeptid = f.appdeptid)
union
select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(936, 2000, 0)) g
where g.appsysid = e.owner_appsysid
and g.appdeptid = e.owner_appdeptid)) t
where t.appsysid = 2000
and t.appdeptid = 0
order by t.updatetimeorder desc, t.hostno
人事處登入，人事處已經沒有下級了，勾選不勾選，結果都一樣，只是sql語句不一樣而已
預設查全部，包含下級（其實沒有下級了）：
--[8196, 23551, 3, 8196, 23551, 3, -1, 23551, 3]
select *
from (select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(8196, 23551, 3)) f
where h.appsysid = f.appsysid
and h.appdeptid = f.appdeptid)
union
select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(8196, 23551, 3)) g
where g.appsysid = e.owner_appsysid
and g.appdeptid = e.owner_appdeptid)) t
where exists (select appsysid, appdeptid
from table(get_subdept(-1, 23551, 3)) g
where g.appsysid = t.appsysid
and g.appdeptid = t.appdeptid)
order by t.updatetimeorder desc, t.hostno
--[8196, 23551, 3, 8196, 23551, 3, 23551, 3]
--bao1人事處登入
select *
from (select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(8196, 23551, 3)) f
where h.appsysid = f.appsysid
and h.appdeptid = f.appdeptid)
union
select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(8196, 23551, 3)) g
where g.appsysid = e.owner_appsysid
and g.appdeptid = e.owner_appdeptid)) t
where t.appsysid = 23551
and t.appdeptid = 3
order by t.updatetimeorder desc, t.hostno
相關文章
- GAT專案新需求：ETC管理增加查詢統計
- GAT專案新需求：保險管理增加查詢統計（續）
- GAT專案新需求：保險管理增加查詢統計-傳日期引數問題
- GAT專案新需求：保險管理修改
- GAT-解決車輛基本資訊中，刪除未返場的車輛時，錯誤提示資訊Bug
- GAT專案新需求：加油管理修改
- 車輛資訊查詢 - 高效快捷地獲取車輛相關資訊的利器
- 根據關鍵字查詢車輛資訊
- 惠安惠達專案新需求：首頁增加待命車明細
- 車輛資訊快速查詢API：輕鬆查詢車牌號對應車輛的詳細資料API
- 解決GAT專案Bug：車裝車飾費用管理中的裝飾費統計（車輛）統計資料不對
- 解決GAT專案Bug：軌跡分析查詢不到資料
- Oracle-ORA-01722:invalid number-解決GAT專案中車輛維修BugOracle
- ORA-01722:invalid number-解決GAT專案中車輛維修Bug(續)
- 解決GAT輸入車牌號未帶出車輛品牌Bug
- 免費線上，查詢名下企業資訊新選擇
- 提供車輛出險報告查詢功能的API介面API
- JN專案-風采展示增加下拉查詢條件
- GAT-解決用車審批中，如果排程車輛是通過Excel匯入的車輛，會有錯誤提示BugExcel
- Api介面：線上查車輛vin資訊（發動機號）詳情API
- Golang仿雲盤專案-2.2 檔案查詢資訊介面Golang
- 車險承保理賠資訊網路查詢
- easyui的treegrid的級聯勾選子節點,或者級聯勾選父節點UI
- 收集更有效的專案需求資訊 （轉）
- 列車車次查詢-餘票查詢-Api介面API
- GWC和GAT專案搭建過程
- GAT專案前臺到後臺
- MySQL查詢索引的選擇性、索引欄位、註釋等基本資訊的SQLMySql索引
- MySQL 查詢索引的選擇性、索引欄位、註釋等基本資訊的SQLMySql索引
- oracle 基本查詢Oracle
- postgresql 基本查詢SQL
- 怎樣應對“需求不確定型專案”？
- 增加子查詢表條件篩選提高效能
- 資訊化專案的選題
- 小組專案----使用者需求調查
- 專案需求討論— ButterKnife初級小結
- Winform 工具欄 ToolStripMenuItem下拉選擇項選中對勾不居中ORMUI
- Oracle基本資訊檢查Oracle