Soner Gönül Soner Gönül - 1 month ago 13
SQL Question

SQL Error when I scroll down the results

I have a SQL like this; (in Oracle SQL Developer)

SELECT A.HESAP_NO, A.TEKLIF_NO1 || '/' || A.TEKLIF_NO2 AS TEKLIF, A.MUS_K_ISIM,
B.MARKA, C.SASI_NO, C.SASI_DURUM, D.TAS_MAR, RISK_SASI(A.TEKLIF_NO1, A.TEKLIF_NO2, C.SASI_NO) AS RISK,
MV_SASI(A.TEKLIF_NO1, A.TEKLIF_NO2, C.SASI_NO, SYSDATE) AS MV
FROM S_TEKLIF A, S_URUN B, S_URUN_DETAY C, KOC_KTMAR_PR D
WHERE A.TEKLIF_NO1 || A.TEKLIF_NO2 = B.TEKLIF_NO1 || B.TEKLIF_NO2
AND A.TEKLIF_NO1 || A.TEKLIF_NO2 = C.TEKLIF_NO1 || C.TEKLIF_NO2
AND B.SIRA_NO = C.URUN_SIRA_NO
AND B.DISTRIBUTOR = D.DIST_KOD
AND B.MARKA = D.MARKA_KOD
AND B.URUN_KOD = D.TAS_KOD


And the result is;

enter image description here

By this steps, there is no error BUT when I scroll down in Results, I getting an error like this:


ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at "MARDATA.RISK_SASI", line 28

01422. 00000 - "exact fetch returns more than requested number of rows"

*Cause: The number specified in exact fetch is less than the rows returned.

*Action: Rewrite the query or change number of rows requested


How can I solve this problem?

Answer

Look at your RISK_SASI function. I do not see it but I guess that in 28-th line you will find something like this

SELECT <something> into <some variables>
FROM <somewhere>
WHERE <some condition>

When there is more than one row which fulfils "some condition", you get ORA-01422. If my guess is correct, you should correct this statement to return exactly one row.

The error appears only for some specific values of A.TEKLIF_NO1, A.TEKLIF_NO2, C.SASI_NO, that is why you don't have this error when you fetch first few rows (records are fetched as you scroll down)