Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I have a coding like this. There are two queries and I want to use Union. But I get an error "ORA-01789: query block has incorrect number of result columns". Is there any wrong with my syntax or how?
public function index()
{
$medias = DB::connection('oracle')
->table('ARCHIVE_LOG_SHEET_HDR AS a')
->selectRaw("a.MATERIAL_ID, 'SHOOT' as media_type, NULL as media_format, b.HOUSE_NO, a.LOG_DATE, b.FILM_NAME, NULL as episode_title, NULL as episode_no, NULL as last_tx, a.REMARKS, a.LOCATION_ID, c.REMARKS as REMARK_DTL")
->leftJoin('ARCHIVE_LOG_SHEET_PROG01 AS b', 'a.LOG_ID', '=', 'b.LOG_ID')
->leftJoin('ARCHIVE_LOG_SHEET_DTL AS c', 'a.LOG_ID', '=', 'c.LOG_ID');
$materials = DB::connection('oracle')
->table("STOCK_MATERIAL AS x")
->select('x.MATERIAL_ID', 'y.HOUSE_NO', 'x.TXN_DATE', 'y.PROGRAM_NAME', 'w.EPI_NO', 'x.REMARKS', 'x.LOCATION_ID', 'z.remarks',
DB::raw("(SELECT TAPET_NAME FROM MA_TAPE_TYPE WHERE TAPET_CODE = x.MATERIAL_TYPE) as media_type"),
DB::raw("(SELECT TAPEF_NAME FROM MA_TAPE_FORMAT WHERE TAPEF_CODE = x.MATERIAL_FORMAT) as media_format"),
DB::raw("(SELECT MAX (last_date) FROM run_master WHERE run_master.row_id_epi = w.row_id AND run_master.run_aired = Y) as last_tx,"),
DB::raw('CASE WHEN x.iden_flag = P THEN y.epi_title WHEN x.iden_flag = C THEN y.prod_version_name WHEN x.iden_flag = M THEN y.promo_name END as episode_title'))
->leftJoin('STOCK_MATERIAL_EPI AS y', 'x.MATERIAL_ID', '=', 'y.MATERIAL_ID')
->leftJoin('stock_material_slag AS z', 'x.MATERIAL_ID', '=', 'z.MATERIAL_ID')
->leftJoin('PUR_EPISODE_HDR AS w', 'y.ROW_ID_EPI', '=', 'w.ROW_ID')
->union($medias)
->paginate(50);
return view('ReceiveMedia.index', compact('medias', 'materials'));
}
When running the coding I get an error "Illuminate\Database\QueryException thrown with message "Error Code : 1789 Error Message : ORA-01789: query block has incorrect number of result columns Position : 0 Statement : select count() as aggregate from "STOCK_MATERIAL" x left join "STOCK_MATERIAL_EPI" y on "X"."MATERIAL_ID" = "Y"."MATERIAL_ID" left join "STOCK_MATERIAL_SLAG" z on "X"."MATERIAL_ID" = "Z"."MATERIAL_ID" left join "PUR_EPISODE_HDR" w on "Y"."ROW_ID_EPI" = "W"."ROW_ID" union select a.MATERIAL_ID, 'SHOOT' as media_type, '' as media_format, b.HOUSE_NO, a.LOG_DATE, b.FILM_NAME, '' as episode_title, '' as episode_no, '' as last_tx, a.REMARKS, a.LOCATION_ID, c.REMARKS as REMARK_DTL from "ARCHIVE_LOG_SHEET_HDR" a left join "ARCHIVE_LOG_SHEET_PROG01" b on "A"."LOG_ID" = "B"."LOG_ID" left join "ARCHIVE_LOG_SHEET_DTL" c on "A"."LOG_ID" = "C"."LOG_ID" Bindings : [] (SQL: select count() as aggregate from "STOCK_MATERIAL" x left join "STOCK_MATERIAL_EPI" y on "X"."MATERIAL_ID" = "Y"."MATERIAL_ID" left join "STOCK_MATERIAL_SLAG" z on "X"."MATERIAL_ID" = "Z"."MATERIAL_ID" left join "PUR_EPISODE_HDR" w on "Y"."ROW_ID_EPI" = "W"."ROW_ID" union select a.MATERIAL_ID, 'SHOOT' as media_type, '' as media_format, b.HOUSE_NO, a.LOG_DATE, b.FILM_NAME, '' as episode_title, '' as episode_no, '' as last_tx, a.REMARKS, a.LOCATION_ID, c.REMARKS as REMARK_DTL from "ARCHIVE_LOG_SHEET_HDR" a left join "ARCHIVE_LOG_SHEET_PROG01" b on "A"."LOG_ID" = "B"."LOG_ID" left join "ARCHIVE_LOG_SHEET_DTL" c on "A"."LOG_ID" = "C"."LOG_ID")"
@lia97 The problem here is that you select more columns in one query than in the other. You need to make sure you end up with the same number of select columns in both queries. They also need to match the same data type to make this work. So make sure both have the same kind of selects.
Source: https://www.techonthenet.com/oracle/errors/ora01789.php
@bobbybouwmann Each query generates the same number of columns as 12 columns.
@lia97 Did you read my previous answer? They also need to match the same data type. So a string should match a string and an integer should match an integer and a data should match a date.
@bobbybouwmann I think both of these queries already have the same type of data, because both of those queries I've tried to run with the UNION command in SQL developer and it worked fine. But, when I run into Laravel will still get the error
Well, it's actually an Oracle exception. The exception itself is not Laravel related.
Why don't you just use a full raw query? Since you already have a working query, it doesn't make sense to try to get it to work in Eloquent ;)
I agree with @bobbybouwmann on this.
Oracle's SQL dialect differs a bit from the one of the other databases so I think it's better to use pure SQL for those things. I don't know how the support for Oracle SQL is in the Query Builder and Eloquent.
@tray2 There is support for it, but this is a pretty complex query. Only one little wrong where statement and the query are already different with Eloquent. So a raw query is the best solution here for sure
@bobbybouwmann so true. The mixing of old and "new" join syntax makes it harder to read. Nice to hear that Oracle SQL syntax is supported by Laravel. The Oracle dialect has some nice features but it also has some complex syntax. Like limiting and sorting is not that clean of a syntax in Oracle SQL compared to MySQL.
MySQL:
SELECT *
FROM table
ORDER BY column ASC
LIMIT 10;
Oracle 11G or earlier version:
SELECT *
FROM (SELECT *
FROM table
ORDER BY column ASC)
WHERE ROWNUM <= 10;
Oracle 12C and up
SELECT *
FROM table
ORDER BY column ASC
FETCH NEXT 10 ROWS ONLY;
@lia97 Sorry for highjacking the thread.
Please or to participate in this conversation.