…if I have done anything wrong

It’s hard to tell because there isn’t enough actual program source to tell what you’re trying to do. From the small bits that you show, it looks like you’re just trying to prepare and execute a straight SELECT statement, and that is not a valid thing to do.

A straight SELECT statement doesn’t do anything. It just selects.

What are you trying to do?

Tom

I Just want to check whether a record exist in the file or not for the condition

Here’s a sample COBOL program that has two SELECT INTO statements. A SELECT INTO can be used in some cases, but it must select only a single row. Of course, if it errors out because more than one row is selected, you’d still know that a row existed.

       Identification Division.
       Program-ID.     TST1     .

       Environment Division.

       Configuration Section.
       Source-computer.    IBM-AS400.
       Object-computer.    IBM-AS400.

       Data Division.

       Working-storage Section.

           EXEC SQL
              INCLUDE SQLCA
           END-EXEC.

           77 apFound              pic s9(18) binary.

       01  HOST-VARS.
           05 iCnt                 pic s9(18) binary.
           05 iTot                 pic s9(5) comp.
           05 minFile              pic x(10).
           05 maxFile              pic x(10).

       Procedure Division.

       00-Main-driver.

           EXEC SQL
                SELECT COUNT(*), SUM(APNKYF), MIN(APFILE), MAX(APFILE)
                    INTO :HOST-VARS
                    FROM ACCPTH
           END-EXEC.

           EXEC SQL
                SELECT COUNT(*)
                    INTO :apFound
                    FROM ACCPTH
                    WHERE APFILE = :minFile
           END-EXEC.

           goback.

       00-Main-driver-exit.  exit.

The first SELECT INTO pulls summary values into a data structure. It’s guaranteed to return a single row. The second SELECT INTO is also coded to return at most a single row. It returns a COUNT() of how many rows match the APFILE value returned from the first SELECT INTO. In your case, you could test for a match against your desired MEMBNO value.

Note that any program variable name in the SQL must begin with a colon — “:”.

The ACCPTH file is one that I created with DSPFD TYPE(*ACCPTH) to an outfile for testing.

Tom

BTW, the only reason I coded two SELECT INTO statements was to have one that used a data structure and one that used an elementary variable. You would only need a single SELECT INTO.

You might also use a VALUES INTO statement.

But SQL isn’t particularly appropriate if all you’re doing is testing if a row exists. SQL is intended to be used to process sets, or groups, of related rows with single statements. A COBOL START statement might be better.

Tom

I’m using a dynamic SQL.
For Example: I have two variables(LNK-VAR1, LNK-VAR2) as input to the program.

SELECT EMPNAME, EMPDOB INTO :WS-FOUND
FROM FILENAME
WHERE EMPNAME = LNK-VAR1
AND EMPDOB = CASE WHEN LNK-VAR2 <> 0
THEN LNK-VAR2
ELSE EMPDOB
END
This is the SQL query I have. I want to convert it to a dynamic SQL query.
if LNK-VAR2 is 0 then it should not be part of where condition else it should be part of where condition

can we write an Dynamic SQL query without using dynamic cursor

can we write an Dynamic SQL query without using dynamic cursor

First, a “dynamic” SQL statement will be processed first by a PREPARE statement and then an EXECUTE statement. A “dynamic” SQL statement will not contain references to program variables. Any portion of the statement that you want to modify dynamically will be noted by a parameter marker.

After the statement is PREPAREd, you run it with an EXECUTE statement. However, the dynamic SQL statement cannot be a SELECT statement if you are going to use EXECUTE. You must declare a cursor and FETCH rows.

You haven’t been showing dynamic SQL. You’ve been showing static SQL.

AND EMPDOB = CASE WHEN LNK-VAR2 <> 0
THEN LNK-VAR2
ELSE EMPDOB
END

You don’t need CASE for that. Just use a simple test:

AND (:LNK-VAR2 = 0 OR
      EMPDOB = :LNK-VAR2)

And don’t forget that host variables must be written with a colon (“:”) to mark tham. That must be done for LNK-VAR1, too. Also, if there is more than one row returned, the statement will fail.

Tom