How to display the SQL statement in the queries during run-time

[khcheong]

I'm current having problem in the selecting the data base on the conditions and parameter pass in by the user. For eg. I want to select the data between the range 'A' to 'C' but the results is only 'A' and 'B' can be selected and the data 'C' is excluded. It will only included when I select the data range from 'A' to 'D'....

Does anyone got idea what's goes wrong?

I've check the parameter value by displaying out the values using message box and it is correct. is it because of the SQL statement problem? How to display the SQL statement in the queries at run-time?

my SQLstatement is like this:

SELECT ALL N_CLMS_CLMSSETUP.CLMS_CLMTYPE,
N_CLMS_CLMSSETUP.CLMS_LATEPYMTINT, N_CLMS_CLMSSETUP.CLMS_FREEINTPERIOD,
N_CLMS_CLMSSETUP.CLMS_DOCPREFIX, N_CLMS_CLMSSETUP.CLMS_DOCLASTNO
FROM N_CLMS_CLMSSETUP WHERE (MINIMUM IS NOT NULL AND :MAXIMUM IS NOT NULL AND CLMS_CLMTYPE BETWEEN :MINIMUM AND :MAXIMUM) OR MINIMUM IS NULL AND :MAXIMUM IS NULL AND CLMS_CLMTYPE = CLMS_CLMTYPE)) ORDER BY CLMS_CLMTYPE

:MINIMUM & :MAXIMUM is the parameter field


Thanks a lot...

Regards,
Dynafront

 

[Scunningham99]

I bet it is your 'OR' condition that is screwing it up. Ive come across this many a time!!

Firstly for clarity why dont you give your table an alias, such as

from N_CLMS_CLMSSETUP CLM

in your where clause why dont you code for null value such as

where CLM.CLMS_CLMTYPE between nvlMINIMUM,CLM.CLMS_CLMTYPE) AND MAXIMUM,CLM.CLMS_CLMTYPE)

this will code for null paramater and replace with join.
Note, you can replace CLMS_CLMTYPE with whatever you want!!

Failing this you could always use a decode statement.

 

[khcheong]

Thanks. I have already solved this problem