Creating a table view in Oracle Apex-CodePudding

'Missing Expression' for Select Distinct Query the error sign

CREATE OR REPLACE FORCE EDITIONABLE VIEW "KIR_V_KAS_MASUK_KET" ("...") as
    SELECT
KMH.KODE_KAS,
KA.NIS,
KA.NAMA,
KA.KELAS,
KA.JURUSAN,
        SELECT DISTINCT
        (SELECT PERIODE FROM KIR_KAS_MASUK_HEAD WHERE KODE_KAS=:P16_KODE_KAS) PERIODE,
        TO_CHAR((SELECT SUM(PEMBAYARAN)  FROM KIR_KAS_MASUK_DETAIL WHERE KODE_KAS = :P16_KODE_KAS), '999,999,999') TOTAL_PEMBAYARAN,
         TO_CHAR(WAJIB_BAYAR * (SELECT COUNT(*) FROM KIR_ANGGOTA WHERE STATUS != 'TIDAK AKTIF'), '999,999,999') TOTAL_WAJIB_BAYAR,
KMD.KETERANGAN
FROM
KIR_ANGGOTA KA,
KIR_KAS_MASUK_HEAD KMH,
KIR_KAS_MASUK_DETAIL KMD
WHERE
KMD.KODE_KAS=KMH.KODE_KAS
AND
KMD.NIS=KA.NIS
/

i wanted to make a view table with 9 column that 3 of those column are something that i think is the problem, because select distinct in a select query which is wrong but i really don't know the solution because i just add those 'select distinct' query without knowing anything that makes it wrong. I hope someone would correct me so that i can make the result i wanted to create using the view table query in my app

CodePudding user response：

What is obvious, is that syntax is wrong. Should be something like this (simplified):

create view ... as
select DISTINCT kmh.kode_kas, ...              --> DISTINCT goes here
  (select periode from ... ) periode,
  (select to_char(sum(pembayaran, '999,999,999)) from ...) total_pembayaran
from kir_anggota ka, ...
where ...

Note that all subqueries (for periode, total_pembayaran, ...) must return a single value. Their where clause have to enforce that.

CodePudding user response：

There are a couple of issues:

In the select part of a select statement, each column you are select needs to return a scalar value. It is not possible to select 3 columns with a single select statement
It's not possible to reference bind variables (the page item P16_KODE_KAS) in a database object. That doesn't compile because at compilation time, the apex item values cannot be known. Workaround is to use the v() function.
Use ansi joins instead of the old oracle syntax. It's easier to read and harder to do it wrong.

Here is the "fixed" version. There might still be errors since I have no access to your environment nor table definitions and sample data.

CREATE OR REPLACE FORCE EDITIONABLE VIEW "KIR_V_KAS_MASUK_KET" ("...") as
    SELECT
KMH.KODE_KAS,
KA.NIS,
KA.NAMA,
KA.KELAS,
KA.JURUSAN,
(SELECT DISTINCT PERIODE FROM KIR_KAS_MASUK_HEAD WHERE KODE_KAS=v('P16_KODE_KAS')) PERIODE,
(SELECT DISTINCT TO_CHAR((SELECT SUM(PEMBAYARAN)  FROM KIR_KAS_MASUK_DETAIL WHERE KODE_KAS = v('P16_KODE_KAS')), '999,999,999') TOTAL_PEMBAYARAN,
(SELECT DISTINCT TO_CHAR(WAJIB_BAYAR * (SELECT COUNT(*) FROM KIR_ANGGOTA WHERE STATUS != 'TIDAK AKTIF'), '999,999,999') TOTAL_WAJIB_BAYAR,
KMD.KETERANGAN
FROM
KIR_ANGGOTA KA
  JOIN KIR_KAS_MASUK_DETAIL KDM ON KMD.NIS=KA.NIS
  JOIN KIR_KAS_MASUK_HEAD KMH ON KMD.KODE_KAS=KMH.KODE_KAS
/

This being said, it looks like this is only going to be used in a single page. I would not create a view in that case. It looks like overkill.