Embeded SQL

[venkat2003]

Hi,

I want to know how to write the embeded sql in cobol language for this below requirement.

Req. is Suppose there are 3 fileds in the screen. User can enter either one filed or two filed or three fields.
For example Customer table is exists the fileds are customer id, name, address.

Suppose in customer enquiry screen user can enter customer id or customer id and name. For this how to write embeded sql in cobol? Please let me know.

Thanks
Venkat

 

[gregsimpson]

Something like the following should work for you.

EXEC SQL
DECLARE CSR1 CURSOR FOR
SELECT
CUST_ID
,CUST_NME
,CUST_ADR
FROM TABLE
WHERE
(CUST_ID BETWEEN
CLTABLE-LOW.CUST-ID AND CLTABLE-HIGH.CUST-ID)
AND
(CUST_NME BETWEEN
CLTABLE-LOW.CUST-NME AND CLTABLE-HIGH.CUST-NME)
AND
(CUST_ADR BETWEEN
CLTABLE-LOW.CUST-ADR AND CLTABLE-HIGH.CUST-ADR)
FOR FETCH ONLY;

You will need to include 2 copybooks/DCLGENS one for the low range and one for the high range. Populate these accordingly.

Move zeroes to CUST-ID of DCLTABLE-LOW.
Move 999999 to CUST-ID of DCLTABLE-HIGH.

Move HIGH-VALUES to CUST-NAME of DCLTABLE-HIGH
Move HIGH-VALUES to CUST-ADR of DCLTABLE-HIGH
Move LOW-VALUES to CUST-NAME of DCLTABLE-LOW
Move LOW-VALUES to CUST-ADR of DCLTABLE-LOW

Whichever field(s) are present overwrite from the input.

Pseudo code

IF INPUT-CUST-ID NOT = ZEROES
CUST-ID of DCLTABLE-LOW = INPUT-CUST-ID
CUST-ID of DCLTABLE-HIGH = INPUT-CUST-ID
END-IF

IF INPUT-CUST-ADR NOT = SPACES
CUST-ADR of DCLTABLE-LOW = INPUT-CUST-ADR
CUST-ADR of DCLTABLE-HIGH = INPUT-CUST-ADR
etc ....
etc ....

This should cope with any comination of the 3 being input, in only 1 Cursor definition. You may however wish to think about doing it differently and breaking up the query for performance reasons.

Certainly using the BETWEEN predicates will do what you require in only 1 CURSOR.

Cheers
Greg

 

[Triad]

Research dynamic SQL--this is exactly what it can be used for.

I've run the query the previous poster shows in a production environment (millions of rows) and it was far too slow. It did a full table scan and then looked at each row to reject it.... The bound SQL had to guess what was in the low and high values.

 

[gregsimpson]

Triad,

I'm not in the least surprised by your findings. All I did was attempt to answer the posting and I did put on my disclaimer about doing it differently for "performance reasons". I didn't think the original post was 100% accurate in its design as clearly there would be no need to enter someones name to search upon if you had also entered their customer id. Surely customer id would be sufficient!

There are a whole host of techniques to speed things up and stop tablespace scans such as "index overcrowding" and the Optimize clause etc, did you include these in your tests? Whenever any solution is considered volumes, TPS and response times etc all need to be considered, along with an appropriate tuning exercise before deciding on a solution. I wasn't really attempting that.

Perhaps you could provide the dynamic sql for Venkat and show him how to include it in a COBOL program?

Cheers
Greg

 

[Gregory21]

I am an RPG programmer, but I had this type of problem in my applications to. I used different technique.
1. Alter your input value with wild characters (in my case
it is '%' and all blank values are replaced with it)
From Screen User entered Name = ' ABC '
Variable for SQL statement Name = '%%%ABC%%'

2. Your SQL Statement will look like:

Declare Table_Cursor as Dynamic Scroll Cursor for
Select t.FName, t.LName, ... from Customer as t Where
t.CisID like :ID, t.LName like :NAME
for Read Only

Open Table_Cursor

Fetch from Table_Cursor into :FLD1, :FLD2

.... load your selection to the screen

Close your Cursor after you done with retrieving records for this selection scenario