SQL执行没问题，mybatis调用该执行报错“未明确定义列”

最新推荐文章于 2024-08-19 14:50:17 发布

原创最新推荐文章于 2024-08-19 14:50:17 发布 · 920 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #sql #数据库

在使用Mybatis进行SQL查询Oracle数据库时，如果SQL语句中存在重复定义的字段，会导致未明确定义列的错误。虽然在SQL工具中执行没有问题，但在Mybatis中执行该查询会出错。删除重复的字段后，问题得到解决。查询涉及到的表为HIS_STOCK_INFO，错误出现在STORE_UNIT,PHAR_UNIT,RETAIL_UNIT字段的重复定义上。

在此记录个BUG，mybatis的，SQL查询Oracle数据，SQL中重复定义查询了字段，就会导致报错未明确定义列，放到SQL工具中执行没问题，就是mybatis调用组装SQL后，再去查询数据，就要提示未明确定义列，但是又找不出是哪一个。删掉重复的就行了

 SELECT
	dsii.GOODS_ID AS goodsId,
	dsii.STORE_UNIT AS storeUnit,
	dsii.STORE_LEVEL AS storeLevel,
	dsii.STORE_SPEC AS storeSpec,
	dsii.PHAR_SPEC AS pharSpec,
	dsii.STORE_FACTOR AS storeFactor,
	dsii.PHAR_FACTOR AS pharFactor,
	dsii.PRICE_PURCHASE_PHAR AS pricePurchasePhar,
	dsii.PRICE_RETAIL_PHAR AS priceRetailPhar,
	dsii.PHAR_UNIT AS pharUnit,
	dsii.RETAIL_UNIT AS retailUnit,
	dsii.STOCK_ID AS stockId,
	dsii.AMOUNT_RETAIL + nvl(fun_get_inventory_amount(dsii.hospital_id, dsii.store_id, dsii.stock_id), 0) AS amountRetail,
	dsii.AMOUNT_ALLOC - nvl(fun_get_inventory_amount_alloc(dsii.hospital_id, dsii.store_id, dsii.stock_id), 0) AS amountAlloc,
	dsii.AMOUNT_STOP amountStop,
	dsii.PRICE_PURCHASE AS pricePurchase,
	dsii.PRICE_RETAIL AS priceRetail,
	dsii.MANUFACTURER_NAME AS manufacturerName,
	dsii.CSI_CODE AS csiCode,
	dsii.PRODUCER_NAME AS producerName,
	dsii.SUPPLIER_NAME AS supplierName,
	dsii.GOODS_NAME AS goodsName,
	dsii.MATERIEL_CATY AS materielCaty,
	dsii.GB_CODE AS gbCode,
	dsii.REGISTERED_CODE AS registeredCode,
	dsii.GOODS_CODE AS goodsCode,
	dsii.STORE_UNIT AS storeUnit, --重复的
	dsii.PHAR_UNIT AS pharUnit,  --重复的
	dsii.RETAIL_UNIT AS retailUnit,  --重复的
	nvl(dsii.MATERIEL_COMMON_NAME, dsii.GOODS_NAME) AS materielCommonName
FROM
	HIS_STOCK_INFO dsii