Trouble in executing query

Shweta12j · New User Joined: 10 May 2010 Posts: 32 Location: Mumbai

Hello All ,

I am having a specific requirement wherein I need to fetch data from a table as :

Select Department , Salary , EmpNo , Project from Employee ;

Now my requirment is out of the above 4 columns ( mentioned in select clause ) any one of the field may have a valid value and another may have spaces for eg.

Department = D01
Empno = Spaces
Project = ABC
Salary = Spaces

My requirement is to fetch only those records for which Department is D01 and Proeject is ABC.

This data may keep on varying. For next record I may have Empno = ABC123 and Project = ABN and other fields as spaces ..this way various combinations is possible.

Now I am trying to use below query :

Select Department , Salary , EmpNo , Project from Employee
where
(Department = 'Valid value ' or Department > Spaces ) and
(Salary = 'Valid Value ' or Salary > Spaces ) and
(Empno ='Valid Value ' or Empno > spaces) and
(Project =' Valid Value ' or Project > spaces) ;

Above query is fetching all the records instead of fetching records on specific data present in host variables.

Can anyone please suggest a way to fullfill this requirement.

Regards,
Shweta.

Gnanas N · Posted: Thu Mar 21, 2013 11:56 am

Simply you can try with a Cursor to select all the rows (possibly, multi row-fetch) and fetch the rows & apply your space-checking logic inside the program.

Shweta12j · New User Joined: 10 May 2010 Posts: 32 Location: Mumbai

Hi Gnanas N ,

I used the same query mentioned in my above post in cursor however I am not getting desired results as per the values mentioned in host variable.

I also examined an option of fetching all the rows in working storage variable and then comparing each..but then this won't work as a table would have load of data.

Regards,
Shweta.

Gnanas N · Posted: Thu Mar 21, 2013 12:31 pm

Please post some sample input values and the result of the query.

GuyC · Posted: Thu Mar 21, 2013 1:59 pm

it should be :