Getting Oracle ora-00918 column ambiguously defined

[SSiwek]

I have an innerjoin on two tables.
Both tables do have a column named dtimemod.

In gridview when I click on columnheader to sort, I get ora-00918

I think that the sql build system has to put the tablename in front of every column in the order by clause... It does do so with the rest of the clauses

WITH USER_SQL AS (SELECT "SHIPCONTPRT".* FROM "SHIPCONTPRT" INNER JOIN "SHIP" ON ("SHIPCONTPRT"."ZLAVISNR" = "SHIP"."SHIPID") AND (("SHIP"."SHIPSTAT" < 95) AND ("SHIP"."SHIPTY"='PV')) WHERE ZLLBLNAME is null ORDER BY "DTIMEMOD"),
PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL)
SELECT *
FROM PAGINATION
WHERE rownum <= 20

Additional info

Q	A
Yii version	2.0.20
PHP version	7.2.20
Operating system	Unix

[yii-bot]

Thanks for posting in our issue tracker.
In order to properly assist you, we need additional information:

• When does the issue occur?
• What do you see?
• What was the expected result?
• Can you supply us with a stacktrace? (optional)
• Do you have exact code to reproduce it? Maybe a PHPUnit tests that fails? (optional)

Thanks!

This is an automated comment, triggered by adding the label status:need more info.

[SSiwek]

The error occurs everytime when I want to sort the attribut dtimemod via the gridview ( Clicking on the header of the table index view )

To reproduce the error you need two tables with same colum names.
Than do an inner join. Try to sort after one column via gridview.

As I stated before the sql build tool needs to add the tablename in front of the attribut
eg "SHIPCONTPRT.DTIMEMOD" for the order by clause

PDOException: SQLSTATE[HY000]: General error: 918 OCIStmtExecute: ORA-00918: Spalte nicht eindeutig definiert
(/builddir/build/BUILD/php-7.2.20/ext/pdo_oci/oci_statement.c:159) in /var/www/html/intranet7/vendor/yiisoft/yii2/db/Command.php:1290
Stack trace:
#0 /var/www/html/intranet7/vendor/yiisoft/yii2/db/Command.php(1290): PDOStatement->execute()
#1 /var/www/html/intranet7/vendor/yiisoft/yii2/db/Command.php(1158): yii\db\Command->internalExecute('WITH USER_SQL A...')
#2 /var/www/html/intranet7/vendor/yiisoft/yii2/db/Command.php(399): yii\db\Command->queryInternal('fetchAll', NULL)
#3 /var/www/html/intranet7/vendor/yiisoft/yii2/db/Query.php(237): yii\db\Command->queryAll()
#4 /var/www/html/intranet7/vendor/yiisoft/yii2/db/ActiveQuery.php(133): yii\db\Query->all(NULL)
#5 /var/www/html/intranet7/modules/packpruef/models/ShipcontprtQuery.php(32): yii\db\ActiveQuery->all(NULL)
#6 /var/www/html/intranet7/vendor/yiisoft/yii2/data/ActiveDataProvider.php(116): app\modules\packpruef\models\ShipcontprtQuery->all(NULL)
#7 /var/www/html/intranet7/vendor/yiisoft/yii2/data/BaseDataProvider.php(101): yii\data\ActiveDataProvider->prepareModels()
#8 /var/www/html/intranet7/vendor/yiisoft/yii2/data/BaseDataProvider.php(114): yii\data\BaseDataProvider->prepare()
#9 /var/www/html/intranet7/vendor/yiisoft/yii2/data/BaseDataProvider.php(155): yii\data\BaseDataProvider->getModels()
#10 /var/www/html/intranet7/vendor/kartik-v/yii2-grid/src/GridView.php(1441): yii\data\BaseDataProvider->getCount()
#11 /var/www/html/intranet7/vendor/yiisoft/yii2/widgets/BaseListView.php(158): kartik\grid\GridView->renderSummary()
#12 /var/www/html/intranet7/vendor/yiisoft/yii2/grid/GridView.php(326): yii\widgets\BaseListView->renderSection('{summary}')
#13 /var/www/html/intranet7/vendor/yiisoft/yii2/widgets/BaseListView.php(135): yii\grid\GridView->renderSection('{summary}')
#14 [internal function]: yii\widgets\BaseListView->yii\widgets{closure}(Array)
#15 /var/www/html/intranet7/vendor/yiisoft/yii2/widgets/BaseListView.php(138): preg_replace_callback('/{\w+}/', Object(Closure), '<div class="pan...')
#16 /var/www/html/intranet7/vendor/yiisoft/yii2/grid/GridView.php(301): yii\widgets\BaseListView->run()
#17 /var/www/html/intranet7/vendor/kartik-v/yii2-grid/src/GridView.php(1201): yii\grid\GridView->run()
#18 /var/www/html/intranet7/vendor/yiisoft/yii2/base/Widget.php(140): kartik\grid\GridView->run()
#19 /var/www/html/intranet7/modules/packpruef/views/default/index.php(22): yii\base\Widget::widget(Array)
#20 /var/www/html/intranet7/vendor/yiisoft/yii2/base/View.php(348): require('/var/www/html/i...')
#21 /var/www/html/intranet7/vendor/yiisoft/yii2/base/View.php(257): yii\base\View->renderPhpFile('/var/www/html/i...', '[<span class="s...')
#22 /var/www/html/intranet7/vendor/yiisoft/yii2/base/View.php(156): yii\base\View->renderFile('/var/www/html/i...', Array, Object(app\modules\packpruef\controllers\DefaultController))
#23 /var/www/html/intranet7/vendor/yiisoft/yii2/base/Controller.php(384): yii\base\View->render('index', Array, Object(app\modules\packpruef\controllers\DefaultController))
#24 /var/www/html/intranet7/modules/packpruef/controllers/DefaultController.php(46): yii\base\Controller->render('index', Array)
#25 [internal function]: app\modules\packpruef\controllers\DefaultController->actionIndex()
#26 /var/www/html/intranet7/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#27 /var/www/html/intranet7/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#28 /var/www/html/intranet7/vendor/yiisoft/yii2/base/Module.php(528): yii\base\Controller->runAction('index', Array)
#29 /var/www/html/intranet7/vendor/yiisoft/yii2/web/Application.php(103): yii\base\Module->runAction('packpruef/defau...', Array)
#30 /var/www/html/intranet7/vendor/yiisoft/yii2/base/Application.php(386): yii\web\Application->handleRequest(Object(yii\web\Request))
#31 /var/www/html/intranet7/index.php(19): yii\base\Application->run()
#32 {main}

Next yii\db\Exception: SQLSTATE[HY000]: General error: 918 OCIStmtExecute: ORA-00918: Spalte nicht eindeutig definiert
(/builddir/build/BUILD/php-7.2.20/ext/pdo_oci/oci_statement.c:159)
The SQL being executed was: WITH USER_SQL AS (SELECT "SHIPCONTPRT".* FROM "SHIPCONTPRT" INNER JOIN "SHIP" ON ("SHIPCONTPRT"."ZLAVISNR" = "SHIP"."SHIPID") AND (("SHIP"."SHIPSTAT" < 95) AND ("SHIP"."SHIPTY"='PV')) WHERE ZLLBLNAME is null ORDER BY "DTIMEMOD"),
PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL)
SELECT *
FROM PAGINATION
WHERE rownum <= 20 in /var/www/html/intranet7/vendor/yiisoft/yii2/db/Schema.php:664
Stack trace:
#0 /var/www/html/intranet7/vendor/yiisoft/yii2/db/Command.php(1295): yii\db\Schema->convertException(Object(PDOException), 'WITH USER_SQL A...')
#1 /var/www/html/intranet7/vendor/yiisoft/yii2/db/Command.php(1158): yii\db\Command->internalExecute('WITH USER_SQL A...')
#2 /var/www/html/intranet7/vendor/yiisoft/yii2/db/Command.php(399): yii\db\Command->queryInternal('fetchAll', NULL)
#3 /var/www/html/intranet7/vendor/yiisoft/yii2/db/Query.php(237): yii\db\Command->queryAll()
#4 /var/www/html/intranet7/vendor/yiisoft/yii2/db/ActiveQuery.php(133): yii\db\Query->all(NULL)
#5 /var/www/html/intranet7/modules/packpruef/models/ShipcontprtQuery.php(32): yii\db\ActiveQuery->all(NULL)
#6 /var/www/html/intranet7/vendor/yiisoft/yii2/data/ActiveDataProvider.php(116): app\modules\packpruef\models\ShipcontprtQuery->all(NULL)
#7 /var/www/html/intranet7/vendor/yiisoft/yii2/data/BaseDataProvider.php(101): yii\data\ActiveDataProvider->prepareModels()
#8 /var/www/html/intranet7/vendor/yiisoft/yii2/data/BaseDataProvider.php(114): yii\data\BaseDataProvider->prepare()
#9 /var/www/html/intranet7/vendor/yiisoft/yii2/data/BaseDataProvider.php(155): yii\data\BaseDataProvider->getModels()
#10 /var/www/html/intranet7/vendor/kartik-v/yii2-grid/src/GridView.php(1441): yii\data\BaseDataProvider->getCount()
#11 /var/www/html/intranet7/vendor/yiisoft/yii2/widgets/BaseListView.php(158): kartik\grid\GridView->renderSummary()
#12 /var/www/html/intranet7/vendor/yiisoft/yii2/grid/GridView.php(326): yii\widgets\BaseListView->renderSection('{summary}')
#13 /var/www/html/intranet7/vendor/yiisoft/yii2/widgets/BaseListView.php(135): yii\grid\GridView->renderSection('{summary}')
#14 [internal function]: yii\widgets\BaseListView->yii\widgets{closure}(Array)
#15 /var/www/html/intranet7/vendor/yiisoft/yii2/widgets/BaseListView.php(138): preg_replace_callback('/{\w+}/', Object(Closure), '<div class="pan...')
#16 /var/www/html/intranet7/vendor/yiisoft/yii2/grid/GridView.php(301): yii\widgets\BaseListView->run()
#17 /var/www/html/intranet7/vendor/kartik-v/yii2-grid/src/GridView.php(1201): yii\grid\GridView->run()
#18 /var/www/html/intranet7/vendor/yiisoft/yii2/base/Widget.php(140): kartik\grid\GridView->run()
#19 /var/www/html/intranet7/modules/packpruef/views/default/index.php(22): yii\base\Widget::widget(Array)
#20 /var/www/html/intranet7/vendor/yiisoft/yii2/base/View.php(348): require('/var/www/html/i...')
#21 /var/www/html/intranet7/vendor/yiisoft/yii2/base/View.php(257): yii\base\View->renderPhpFile('/var/www/html/i...', '[<span class="s...')
#22 /var/www/html/intranet7/vendor/yiisoft/yii2/base/View.php(156): yii\base\View->renderFile('/var/www/html/i...', Array, Object(app\modules\packpruef\controllers\DefaultController))
#23 /var/www/html/intranet7/vendor/yiisoft/yii2/base/Controller.php(384): yii\base\View->render('index', Array, Object(app\modules\packpruef\controllers\DefaultController))
#24 /var/www/html/intranet7/modules/packpruef/controllers/DefaultController.php(46): yii\base\Controller->render('index', Array)
#25 [internal function]: app\modules\packpruef\controllers\DefaultController->actionIndex()
#26 /var/www/html/intranet7/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#27 /var/www/html/intranet7/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#28 /var/www/html/intranet7/vendor/yiisoft/yii2/base/Module.php(528): yii\base\Controller->runAction('index', Array)
#29 /var/www/html/intranet7/vendor/yiisoft/yii2/web/Application.php(103): yii\base\Module->runAction('packpruef/defau...', Array)
#30 /var/www/html/intranet7/vendor/yiisoft/yii2/base/Application.php(386): yii\web\Application->handleRequest(Object(yii\web\Request))
#31 /var/www/html/intranet7/index.php(19): yii\base\Application->run()
#32 {main}
Additional Information:
Array
(
[0] => HY000
[1] => 918
[2] => OCIStmtExecute: ORA-00918: Spalte nicht eindeutig definiert
(/builddir/build/BUILD/php-7.2.20/ext/pdo_oci/oci_statement.c:159)
)

[dtmonterrey]

Hi, try specifying the selected columns in your query using the select() method. Had the same issue when the query send to oracle had table.* instead of having the columns one by one