error 103 in reports6i 1

[lelo25]

hello,
i have the following sql :

---------------------------------------------------
SELECT D.BRCH_CODE,D.CACC_NUM
FROM BBSD_INTEREST_D D
WHERE INTD_DATE = '20/05/2004'
AND (SELECT MIN(INTD_DATE) FROM BBSD_INTEREST_D M
WHERE M.CACC_NUM = D.CACC_NUM
AND M.BRCH_CODE = D.BRCH_CODE)<LAST_DAY(ADD_MONTHS(D.INTD_DATE,-1))
AND (SELECT COUNT(*)
FROM BBSD_INTEREST_D I
WHERE I.CACC_NUM = D.CACC_NUM
AND I.BRCH_CODE = D.BRCH_CODE
AND I.INTD_DATE = (SELECT LAST_DAY(MIN(INTD_DATE)) FROM BBSD_INTEREST_D M
WHERE M.CACC_NUM = D.CACC_NUM
AND M.BRCH_CODE = D.BRCH_CODE))=0
---------------------------------------------------

when i run the above select statement from sqlplus, it works fine , but when i compile it from Reports6i builder
i get the error :
"
ERROR 103 at line xx, column xx
Encountered the symbol "SELECT" when expecting one of the following, etc,etc,etc
"
please note that the cursor is pointing to the SELECT statement after the AND operator.


please advise
tx in advance

 

[sem]

The syntax may depend on Oracle version (your example should work on 8i+).

On earlier try this:

SELECT  D.BRCH_CODE,D.CACC_NUM
FROM BBSD_INTEREST_D D
WHERE INTD_DATE = '20/05/2004'
AND LAST_DAY(ADD_MONTHS(D.INTD_DATE,-1))<(SELECT MIN(INTD_DATE) FROM BBSD_INTEREST_D M
WHERE M.CACC_NUM = D.CACC_NUM
AND M.BRCH_CODE = D.BRCH_CODE)
AND 0=(SELECT COUNT(*)
FROM BBSD_INTEREST_D I
WHERE I.CACC_NUM = D.CACC_NUM
AND I.BRCH_CODE = D.BRCH_CODE
AND I.INTD_DATE = (SELECT LAST_DAY(MIN(INTD_DATE)) FROM BBSD_INTEREST_D M
WHERE M.CACC_NUM = D.CACC_NUM
AND M.BRCH_CODE = D.BRCH_CODE))

You may also reformulate your query using EXISTS, e.g.

..AND EXISTS(SELECT 1 FROM <TABLE> WHERE <FIELD> < <VALUE>)

instead of current far more complex

<VALUE> > (SELECT min(<FIELD>) FROM <TABLE>)

Regards, Dima

http://seminihin.narod.ru