topic Re: select query doubt in Application Development and Automation Discussions

select query doubt

Former Member — Mon, 10 Sep 2007 17:44:43 GMT

Hi I have a situation where in a table has following fields

username

status

userdetails

and the id, user name, status fields are key fields.

in the select query even if I pass a single key(not all three) I should get the user details how can i write the query?

(ie sometimes status will be unique and table will have only a single entry with that status) Do i need to use AND in the where clause to achieve this?

SELECT SINGLE * from table where id = id

and username = username

and status = status.

my question is if table has an enry id=1000 username = 'anne' status = 'manager'

and is i pass only the status to query it should retrieve the record..

Will this query work

thanks in advance.

Re: select query doubt

Former Member — Mon, 10 Sep 2007 17:47:44 GMT

Since u have 3 key fields

the table will allow more than one entry with 'manager'.

So if you have more than one entry you might get any one of them in this select.

Use AND if u want 'ANNE' for sure.

Re: select query doubt

Former Member — Mon, 10 Sep 2007 17:51:02 GMT

Nehal,

yes i agree the table will allow more entries...My question is if i pass only status 'manager' to query and leave the other two key fields empty will it retrieve the record?

Larry

Re: select query doubt

Former Member — Mon, 10 Sep 2007 17:55:43 GMT

are us saying the where caluse will havwe

and username = space

and id = space?...

Then no. it will not return the result

It will look for rows with manager and id and username as space

which it might not find

Re: select query doubt

Former Member — Mon, 10 Sep 2007 17:57:42 GMT

Instead this is what u do..

use wild cards

replace the = with like in the where section

append ur variable with %var_value%

if there is no value then it should be %%.

This will ´return all with manager

Re: select query doubt

Former Member — Mon, 10 Sep 2007 17:58:07 GMT

Then how can I achieve that...?In some case i will be passing all the keys and in some case i will apss only status...Is it possible to achieve that with a single query...?

Please help!

larry

Re: select query doubt

Former Member — Mon, 10 Sep 2007 17:59:28 GMT

read my above note, another way to do this is to use ranges

define a range (like a select option)

and use IN in the where clause.

if the range is empty. it will act as awild card

Re: select query doubt

Former Member — Mon, 10 Sep 2007 18:00:13 GMT

Hi,

Try to using the below code :

Report ZXxx.

Tables -

Internal tables :

data : begin of itab occurs 0,

id like tablename-field,

user like tablename-field,

status like tablename-field,

end of itab.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

PARAMETERS : sp_id like table-field

sp_user LIKE table-user,

sp_status LIKE table-status.

start-of-selection.

select * from <table> into table itab where id = sp_id

and user = sp_user

and status = sp_status.

Thanks,

Sri:-)