|
View previous topic :: View next topic
|
| Author |
Message |
rahuindo
New User
Joined: 09 Apr 2008
Posts: 83
Location: Chennai
|
|
|
|
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 |
|
 |
aishwarya_20
New User
Joined: 19 Nov 2008
Posts: 57
Location: pune
|
|
|
|
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 |
|
|
rahuindo
New User
Joined: 09 Apr 2008
Posts: 83
Location: Chennai
|
|
|
|
| Thanks a lot!! I completely forgot about using this query while juggling my head with "CASE" and "IF" statements!! |
|
| Back to top |
|
|
bhushan.shete
New User
Joined: 14 Nov 2006
Posts: 17
|
|
|
|
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 |
|
|
aishwarya_20
New User
Joined: 19 Nov 2008
Posts: 57
Location: pune
|
|
|
|
| SQLCODE could be checked for duplicate rows after the query. For duplicate rows SQLCODE is -811 |
|
| Back to top |
|
|
bhushan.shete
New User
Joined: 14 Nov 2006
Posts: 17
|
|
|
|
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 |
|
|
aishwarya_20
New User
Joined: 19 Nov 2008
Posts: 57
Location: pune
|
|
|
|
| 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 |
|
|
rahuindo
New User
Joined: 09 Apr 2008
Posts: 83
Location: Chennai
|
|
|
|
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 |
|
|
aishwarya_20
New User
Joined: 19 Nov 2008
Posts: 57
Location: pune
|
|
|
|
| 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 |
|
|
bhushan.shete
New User
Joined: 14 Nov 2006
Posts: 17
|
|
|
|
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 |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
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 |
|
|
aishwarya_20
New User
Joined: 19 Nov 2008
Posts: 57
Location: pune
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
aishwarya_20
New User
Joined: 19 Nov 2008
Posts: 57
Location: pune
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Thanks for the clarification 
d |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
rahuindo
New User
Joined: 09 Apr 2008
Posts: 83
Location: Chennai
|
|
|
|
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 |
|
|
aishwarya_20
New User
Joined: 19 Nov 2008
Posts: 57
Location: pune
|
|
|
|
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 |
|
|
rahuindo
New User
Joined: 09 Apr 2008
Posts: 83
Location: Chennai
|
|
|
|
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 |
|
|
aishwarya_20
New User
Joined: 19 Nov 2008
Posts: 57
Location: pune
|
|
|
|
Please check your existing programs how host variables are used.
For help you can read IBM manuals for DB2 Application programming. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| as well as there have been several threads/topics in the last couple of months on 'how to populate a host variable when using wildcards'. |
|
| Back to top |
|
|
rahuindo
New User
Joined: 09 Apr 2008
Posts: 83
Location: Chennai
|
|
|
|
| Thanks dino and Aishwarya |
|
| Back to top |
|
|
|
|
