Need Help on Select Query to satisfy one of the Search field



IBM's flagship relational database management system

Need Help on Select Query to satisfy one of the Search field

Postby Chandra_n » Wed Jul 16, 2008 2:51 pm

Hi all

My search criteria is to select a row from the table when the user give one mandatory input field and, optionally, any other fields.

Totally 1 mandatory field And 10 optionally fields.

I have used BETWEEN clause for the optional fields but I feel performance will be very bad.

Pls find sample code below

IF OPT-FLD1 = SPACES
MOVE LOW-VALUES TO WS-OPT-FID1-FROM
MOVE HIGH-VALUES TO WS-OPT-FID1-TO
ELSE
MOVE OPT-FLD1 TO WS-OPT-FID1-FROM
WS-OPT-FID1-TO
END-IF

WHERE MAN_FLD = WS-MAN-FLD
AND
WHERE OPT_FLD1
BETWEEN :WS-OPT-FID1-FROM AND :WS-OPT-FID1-TO
AND OPT_FLD2
BETWEEN :WS-OPT-FID2-FROM AND :WS-OPT-FID2-TO
AND OPT_FLD3
BETWEEN :WS-OPT-FID3-FROM AND :WS-OPT-FID3-TO
AND OPT_FLD4
BETWEEN :WS-OPT-FID4-FROM AND :WS-OPT-FID4-TO
AND OPT_FLD5
BETWEEN :WS-OPT-FID5-FROM AND :WS-OPT-FID5-TO
.
.
.
AND OPT_FLD10
BETWEEN :WS-OPT-FID10-FROM AND :WS-OPT-FID10-TO

Please let me know if they are any other ways to handle.

Thanks
Chandra N
Chandra_n
 
Posts: 6
Joined: Wed Jul 16, 2008 2:20 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need Help on Select Query to satisfy one of the Search field

 

Re: Need Help on Select Query to satisfy one of the Search field

Postby jayind » Fri Jul 18, 2008 12:22 pm

Hi Chandra_n,

what I noticed from your code is, you are using low-values and high-values in the search fields values which may be causing the performance issues... please try running the query in the backend using any SQL tool eliminating the fields that are not used and see the difference...

I hope I have a better thought!! try this

construct the query using STRING command depending on the search fields used.. for example,

WHERE
(part 1) OPT_FLD1
           BETWEEN :WS-OPT-FID1-FROM AND :WS-OPT-FID1-TO
(part 2) AND OPT_FLD2
           BETWEEN :WS-OPT-FID2-FROM AND :WS-OPT-FID2-TO


concatenate (part 1) if OPT_FLD1 not = spaces to the main query
concatenate (part 1) if OPT_FLD2 not = spaces to the main query

Hope this helps..

Try this and let me know if you still have performance issue..

Regards,
jayind
jayind
 
Posts: 62
Joined: Wed Apr 23, 2008 1:37 pm
Location: Chennai, India
Has thanked: 0 time
Been thanked: 0 time

Re: Need Help on Select Query to satisfy one of the Search field

Postby Chandra_n » Mon Jul 21, 2008 1:32 pm

HI jayind,

Thank you very much for your reply,

Here in my program I need to use this Query in cursors. So how can concatenate the Query depending the OPT_FLD1 and OPT_FLD2…OPT_FLD10 not spaces.
i need to declare one cursor or i need to decaalre possible combination cursors...

I am to able to get u fully

Please advise.

Chandra n
Chandra_n
 
Posts: 6
Joined: Wed Jul 16, 2008 2:20 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need Help on Select Query to satisfy one of the Search field

Postby jayind » Mon Jul 21, 2008 4:17 pm

Hi Chandra_n,

Another point I noticed in your code is,

IF OPT-FLD1 = SPACES
MOVE LOW-VALUES TO WS-OPT-FID1-FROM
MOVE HIGH-VALUES TO WS-OPT-FID1-TO
ELSE
MOVE OPT-FLD1 TO WS-OPT-FID1-FROM
WS-OPT-FID1-TO
END-IF


why are you using a range of values for an option field OPT-FLD when it contains only one value entered by user?
For example, OPT-FLD1 will have only one value when entered by user, so why did you code moving FLD1 to range fields FROM and TO fields? You can use "=" when you know the value!!!

Since you said you need to use the query in the cursor, to my knowledge the best is using dynamic sql.. check the following example... and the link is

http://supportline.microfocus.com/Documentation/books/sx40sp2/spudsc.htm

Fixed-List Select Statements
A fixed-list SELECT statement is a statement that returns a predictable number of data items of a known type from an open cursor. For each of these items, a host variable is required to accommodate incoming data.

To issue a fixed-list SELECT statement dynamically:

(Optional) Test each SQL statement using Interactive SQL to see that proper results are obtained.
Load the SQL statement into a data area.
Use PREPARE to validate the statement and translate it into an executable form.
Use DECLARE to declare a cursor for the statement.
Use OPEN to open the cursor.
Use FETCH to retrieve a row into a fixed list of variables.
When the end of data is reached, use CLOSE to close the cursor.
Be sure to handle any errors.

The following example shows how to execute a fixed-list SELECT statement dynamically.

* include SQLDA copybook
     EXEC SQL
         INCLUDE SQLDA
     END-EXEC

* assign value to variable sqlcmd.
     MOVE 'select e_no, lname from employee where dept="1050"' TO sqlcmd.

* prepare SELECT command.
     EXEC SQL
         PREPARE q1 FROM :sqlcmd
     END-EXEC

* declare cursor for SELECT command.
     EXEC SQL
         DECLARE c1 CURSOR FOR q1
     END-EXEC

* open cursor.
     EXEC SQL
         OPEN c1
     END-EXEC

* fetch data into program variables EMPNO, LASTNME and FIRSTNME.
     EXEC SQL
         FETCH c1 INTO :EMPNO, :LASTNME
     END-EXEC

* close cursor.
     EXEC SQL
         CLOSE c1
     END-EXEC


I hope this helps to resolve your problem...

Regards,
jayind
jayind
 
Posts: 62
Joined: Wed Apr 23, 2008 1:37 pm
Location: Chennai, India
Has thanked: 0 time
Been thanked: 0 time

Re: Need Help on Select Query to satisfy one of the Search field

Postby Chandra_n » Mon Jul 21, 2008 5:09 pm

Jayind

According to our coding standards we should not use Dynamic cursors.

Thanks & Regards,
Chandra n
Chandra_n
 
Posts: 6
Joined: Wed Jul 16, 2008 2:20 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need Help on Select Query to satisfy one of the Search field

Postby jayind » Mon Jul 21, 2008 5:38 pm

In such case another alternative and the last I can suggest is use, ">=" (greater than or equal) for each field and move the field values to variables and use the variables in the cursor declaration

assuming all the option fields are alphanumeric fields in your example,

remove the BETWEEN and replace with

IF OPT-FLD1 = SPACES
    MOVE SPACES TO OPT-FLD1-TEMP (a working storage temporary variable)
ELSE
    MOVE OPT-FLD1 TO OPT-FLD1-TEMP
END-IF

query:
WHERE MAN_FLD = WS-MAN-FLD
AND
OPT_FLD1 >= :OPT-FLD1-TEMP
AND
OPT_FLD2  >= :OPT-FLD2-TEMP
....

This may also have poor performance but compared to earlier your code, it gives better performance..

Hope this helps..

Regards,
jayind
jayind
 
Posts: 62
Joined: Wed Apr 23, 2008 1:37 pm
Location: Chennai, India
Has thanked: 0 time
Been thanked: 0 time

Re: Need Help on Select Query to satisfy one of the Search field

Postby dick scherrer » Mon Jul 21, 2008 10:15 pm

Hello,

According to our coding standards we should not use Dynamic cursors.
It may be time to review/revise the standards. . .

In some very large systems with critical response time requirements, one way to do what you want is to have multiple sets of code that are optimized for the amount of selection data provided by the user. This means the developer must do more work up front, but the result is that far less machine resources are needed and the user gets acceptable response time rather than having to wait for long periods of time.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Need Help on Select Query to satisfy one of the Search field

Postby Chandra_n » Tue Jul 22, 2008 1:31 pm

HI jayind,

Sure i will try...Thank you very much for your suggestion,

Thanks,
Chandra n
Chandra_n
 
Posts: 6
Joined: Wed Jul 16, 2008 2:20 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need Help on Select Query to satisfy one of the Search field

Postby Chandra_n » Tue Jul 22, 2008 5:57 pm

HI jayind,

If ">=" (greater than or equal) is used for each field then all the rows are fetched ...

i.e. requriment is if field value = A then only A row should be fetched but with >= option all rows greater than A are fetching which are not requried.

Thanks,
Chandra N
Chandra_n
 
Posts: 6
Joined: Wed Jul 16, 2008 2:20 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need Help on Select Query to satisfy one of the Search field

Postby jayind » Tue Jul 22, 2008 6:27 pm

You can give EQUAL sign only for the mandatory fields but not for optional fields. You have only one mandatory field for which '=' sign has been given and for other optional fields only '>=' should be given to have all the optional fields in the query. You can't give '=' for optional fields as if user doesnt enter any value, then it will look for the records having space in that particular field which is wrong. Dynamic SQL is the best solution for this kind of requirement. Since you said you can't use dynamic SQL, this is only way to get the all the related records of the optional field values.

Obviously it will fetch the rows equal and greater than the value mentioned in the optional fields. Since you are using cursor, you can have additional 'IF' statements in the program to pickup the exact record. Check if you can code as mentioned below

1. DECLARE CURSOR
< declare the query as mentioned earlier>

2. FETCH CURSOR

< after fetching eachg record, let it go through the following validation to pickup the right record
IF (OPT-FLD1-TEMP NOT = SPACES) AND (OPT-FLD1-TEMP = host variable) etc.. you may need to code nested 'IF' to select the right record

Hope this helps..

Regards,
jayind
jayind
 
Posts: 62
Joined: Wed Apr 23, 2008 1:37 pm
Location: Chennai, India
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post