Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Db2 Conditional Statement
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rahuindo

New User


Joined: 09 Apr 2008
Posts: 83
Location: Chennai

PostPosted: Thu Apr 30, 2009 1:23 pm    Post subject: Db2 Conditional Statement
Reply with quote

Hi,
I have the following situation in which i can have input for the following three fields:
1. last Name (mandatory)
2. First Name(optional)
3. Employee ID (optional).
Now there can be 4 scenarios:
1. Only Last Name is valued
2. Last Name and First name are valued.
3. last Name and Employee ID can be valued.
4. All three are valued.

One approach will be to write separate queries as given below and handle these scenarios:
SELECT *
FROM UAD_EMPLOYEE
WHERE
Last_Name like ‘<Input last Name>%’
AND First_Name LIKE ‘<Input First Name>%’
AND EMployee_ID like '<Input Employee ID>'

The above query can handle only the last scenario (4). We can write similar queries for the other three scenarios.
Can we modify this query to handle all the 4 scenarios through 1 query??
Please let me know if it is possible.
Back to top
View user's profile Send private message

aishwarya_20

New User


Joined: 19 Nov 2008
Posts: 57
Location: pune

PostPosted: Thu Apr 30, 2009 1:51 pm    Post subject:
Reply with quote

This is the query:
SELECT *
FROM UAD_EMPLOYEE
WHERE
(Last_Name like ‘<Input last Name>%’ OR

(Last_Name like ‘<Input last Name>%’ AND
First_Name LIKE ‘<Input First Name>%’) OR

(Last_Name like ‘<Input last Name>%’ AND
EMployee_ID like '<Input Employee ID>') OR

(Last_Name like ‘<Input last Name>%’ AND
First_Name LIKE ‘<Input First Name>%’ AND
EMployee_ID like '<Input Employee ID>'))
Back to top
View user's profile Send private message
rahuindo

New User


Joined: 09 Apr 2008
Posts: 83
Location: Chennai

PostPosted: Thu Apr 30, 2009 2:43 pm    Post subject:
Reply with quote

Thanks a lot!! I completely forgot about using this query while juggling my head with "CASE" and "IF" statements!!
Back to top
View user's profile Send private message
bhushan.shete

New User


Joined: 14 Nov 2006
Posts: 17

PostPosted: Thu Apr 30, 2009 4:04 pm    Post subject:
Reply with quote

Hi rahuindo

Does query given by aishwarya_20 completely satisfy your requirement.

I have a test case, for 2nd scenario
if there are two records in database as
    Rec no. Last_name First_name
    Rec1 Sharma Rohit
    Rec2 Sharma Sachin

and input is Last_name= Sharma & First_name= Rohit
Then it will fetch both rows, but according to the requirement it should fetch only one row. Please let me know if i am wrong.

For your requirement i have two solutions.
Solution 1
This solutuion is widely used in my client's shop.
Request you to ignore syntax and check for logic in below code
Code:
If WS-first_name is valued
/*(Note: here u can check according to your "valued" definition.)
      move WS-first-name to Input First Name1, Input First Name2
else
      move all spaces to Input First Name1
      move all '9' to Input First Name2
/*Note: Do same for employee id. if numeric move all 9 and not '9' 
end-if
/**QUERY
SELECT *
FROM UAD_EMPLOYEE
WHERE
Last_Name = ‘<Input last Name>%’
AND First_Name    >= ‘<Input First Name1>%’
AND First_Name    <= ‘<Input First Name2>%’
AND EMployee_ID >= '<Input Employee ID1>'
AND EMployee_ID <= '<Input Employee ID2>'


Solution 2
I have developed this solution which i think is faster than earlier one.
Code:
If WS-first_name is valued
/*(Note: here u can check according to your "valued" definition.)
      move 0 to WS-Flag-first-name
      move WS-first-name to Input First Name
else
      move 1 to WS-Flag-first-name     
end-if
/*Note: Do same for employee id
/**QUERY
SELECT *
FROM UAD_EMPLOYEE
WHERE
Last_Name = ‘<Input last Name>%’
AND (  WS-Flag-first-name = 1
          OR First_Name    <= ‘<Input First Name>%’  )
AND (  WS-Flag-Employee-id = 1
          OR Employee_id    <= ‘<Input Employee-id>%’  )



Thanks
Bhushan
"Together we will grow"
Back to top
View user's profile Send private message
aishwarya_20

New User


Joined: 19 Nov 2008
Posts: 57
Location: pune

PostPosted: Thu Apr 30, 2009 4:07 pm    Post subject:
Reply with quote

SQLCODE could be checked for duplicate rows after the query. For duplicate rows SQLCODE is -811
Back to top
View user's profile Send private message
bhushan.shete

New User


Joined: 14 Nov 2006
Posts: 17

PostPosted: Thu Apr 30, 2009 4:43 pm    Post subject:
Reply with quote

Hi aishwarya_20

Ya, surely -811 can be used to check for more than 1 row. But think about rahuindo's requirement. is it fulfilling?

If you are using cursor then u wont't get -811.

Regards
Bhushan
Back to top
View user's profile Send private message
aishwarya_20

New User


Joined: 19 Nov 2008
Posts: 57
Location: pune

PostPosted: Thu Apr 30, 2009 4:46 pm    Post subject:
Reply with quote

Here, Rahuindo asked for the query and not for any duplicate handling. This he can handle in his code i guess. Let him reply on this. Otherwise its ok. It is not related to me.
Back to top
View user's profile Send private message
rahuindo

New User


Joined: 09 Apr 2008
Posts: 83
Location: Chennai

PostPosted: Tue May 05, 2009 1:24 pm    Post subject:
Reply with quote

Hi,
Sorry for the late reply as i was on vacation!!
Bhushan, you are write about the 2nd scenario which will actually fetch wrong records. I had written a similar query as provided by you in your 1st solution.
As we are developing a DB2 Stored Procedure, i am not able to check the the 2nd solution provided by you. I will let you know how does the 2nd solution works.
Let me know if i am missing something.
Back to top
View user's profile Send private message
aishwarya_20

New User


Joined: 19 Nov 2008
Posts: 57
Location: pune

PostPosted: Tue May 05, 2009 1:31 pm    Post subject:
Reply with quote

Hi Bhushan, In case of cursor also we can get -811 sqlcode and this will be the better idea to check for SQLCODE after the query.
Back to top
View user's profile Send private message
bhushan.shete

New User


Joined: 14 Nov 2006
Posts: 17

PostPosted: Tue May 05, 2009 2:06 pm    Post subject: Reply to: Db2 Conditional Statement
Reply with quote

Hi rahuindo
Quote:
you are write about the 2nd scenario
is this write or right?

Quote:

As we are developing a DB2 Stored Procedure, i am not able to check the the 2nd solution provided by you

There should not be any problem with stored procedure also. Please share the problem with us.

aishwarya_20,
Quote:
In case of cursor also we can get -811

I request Seniors to verify above quoted statement.

Regards
Bhushan
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Tue May 05, 2009 11:42 pm    Post subject: Reply to: Db2 Conditional Statement
Reply with quote

Hi Bhushan.sethe

You are correct. For a cursor there is no question of getting SQLCODE
of -811. See the explanation on DB2 manual about SQLCODE -811.

Quote:
Explanation: Execution of an embedded SELECT statement or a subselect in the SET clause of an UPDATE statement has resulted in a result table that contains more than one row.
Alternatively, a subquery contained in a basic predicate has produced more than one value.


When dealing with CURSOR ( DECLARE, OPEN, FETCH,CLOSE) none of the above mentioned statements are using.

Regards
Raghu
Back to top
View user's profile Send private message
aishwarya_20

New User


Joined: 19 Nov 2008
Posts: 57
Location: pune

PostPosted: Wed May 06, 2009 7:48 am    Post subject:
Reply with quote

Raghu/ Bhushan,

Generally we declare cursor for not getting more than one row at a time and process each row one at a time. But it may be the case where for one cursor processing more than one row is selected and -811 can come.... it is my experience. You can also try this. further i don't want to discuss as it depends on hands on experience.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed May 06, 2009 8:08 am    Post subject:
Reply with quote

Hello,

I'm not sure if the breakdown here is technical understanding or a language misinterpretation. . . Possibly i'm mistaken. . .

Quote:
Generally we declare cursor for not getting more than one row at a time and process each row one at a time.
We typically declare a cursor in order to be able to process more than one row.

Quote:
But it may be the case where for one cursor processing more than one row is selected and -811 can come....
Most cursors that process multiple rows do not cause an -811.
Back to top
View user's profile Send private message
aishwarya_20

New User


Joined: 19 Nov 2008
Posts: 57
Location: pune

PostPosted: Wed May 06, 2009 8:16 am    Post subject:
Reply with quote

Hi Dick,

My only purpose of telling this is, we can get -811 sqlcode even if we use cursors. That's all, Nothing else. Please do not take it in other way.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed May 06, 2009 8:18 am    Post subject: Reply to: Db2 Conditional Statement
Reply with quote

Hello,

Thanks for the clarification icon_smile.gif

d
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Wed May 06, 2009 11:28 pm    Post subject: Reply to: Db2 Conditional Statement
Reply with quote

Hi aishwarya_20

Thanks for providing this valuable information that while using cursor there is a chance of getting SQLCODE -811.

Can you please post the sql statement used in cursor that returns SQLCODE -811.
Your response is very much appreciated.

Regards
Raghu
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed May 06, 2009 11:47 pm    Post subject:
Reply with quote

Raghu,

since it is apparent that you can not do your own research:

looking up -811 error code:
Quote:

Execution of an embedded SELECT statement has resulted in a result table containing more than one row. Alternatively, a subquery contained in a basic predicate has produced more than one value.
Back to top
View user's profile Send private message
rahuindo

New User


Joined: 09 Apr 2008
Posts: 83
Location: Chennai

PostPosted: Thu May 07, 2009 1:37 pm    Post subject: Reply to: Db2 Conditional Statement
Reply with quote

Hi,
In between the solution provided by both Aishwarya and Bhushan will not work for the following reasons:
Aishwarya's Solution:

In Aishwarya's query, the first condition will always pass (as the Last name is mandatory) and the other conditions will never be checked.

Bhushan:
The Solution 1 will not work because the following conditions:
Code:

AND First_Name    >= ‘<Input First Name1>%’
AND First_Name    <= ‘<Input First Name2>%’
AND EMployee_ID >= '<Input Employee ID1>'
AND EMployee_ID <= '<Input Employee ID2>'

Consider the situation where-in only the First few letters of the First Name are given like "ROB*"..in this case we cannot use the above conditions because the query will check for :
AND First_Name >= ‘ROB%'.

Hence this is not feasible. Correct me if i am wrong.

Solution 2:
This query is not possible because we are using a non-DB2 variable and hence the query can never be executed.
Hence both the solutions provided do not work.

Is there any other way out apart from splitting the query and writing cursors based on the different scenarios?
Back to top
View user's profile Send private message
aishwarya_20

New User


Joined: 19 Nov 2008
Posts: 57
Location: pune

PostPosted: Thu May 07, 2009 1:49 pm    Post subject: Reply to: Db2 Conditional Statement
Reply with quote

Hi,

You can put query like this:-

SELECT *
FROM UAD_EMPLOYEE
WHERE

((Last_Name like ‘<Input last Name>%’ AND
First_Name LIKE ‘<Input First Name>%’) OR

(Last_Name like ‘<Input last Name>%’ AND
EMployee_ID like '<Input Employee ID>') OR

(Last_Name like ‘<Input last Name>%’ AND
First_Name LIKE ‘<Input First Name>%’ AND
EMployee_ID like '<Input Employee ID>'))
Back to top
View user's profile Send private message
rahuindo

New User


Joined: 09 Apr 2008
Posts: 83
Location: Chennai

PostPosted: Thu May 07, 2009 3:55 pm    Post subject:
Reply with quote

Hi Aishwarya,
Thanks for the solution. But i have a question here. I will be getting the names in a variable. How do i use the "LIKE" statement with the "%" when i have the names in variables?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm
This topic is locked: you cannot edit posts or make replies. Updating value of key using rewrite s... ParminderKumar COBOL Programming 4 Thu Aug 04, 2016 12:46 am
No new posts Inspect statement spoorni COBOL Programming 5 Mon Jun 06, 2016 9:53 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us