Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Search functinality is not working in this SQL

Posted on 2014-01-15
9
Medium Priority
?
332 Views
Last Modified: 2014-01-16
Hello experts,

I have this SQL where I am not getting the expected result.

For example I have to search for global search on COUNTRY , and I typed 'USA' then I get the result with "USA" along with "India" .

But the expected result is to get the "USA" result.  
 

SQL :
SELECT 
     FACILITY_ID,
     facility_name,
     city,
     STATE_PROVINCE,
     COUNTRY,
     VISIT_ID,
     START_DATE,
     FILTER_STATUS,
     VISIT_STATUS,
     LINK
FROM
  (SELECT f.Facility_id,
    f.facility_name,
    f.city,
    F.STATE_PROVINCE,
    F.COUNTRY,
    v.visit_id,
    TRUNC(v.start_date) start_date,
    DECODE(V.VISIT_STATUS, '1', 'Pending', '2','Pending', '3', 'Pending','4' ,'Active', '5','Active', '6','Active', 'D','Cancelled','8','Completed','9','Completed','10','Completed','11','Completed','12','Completed','13','Completed','14','Completed' ) AS filter_status,
    MS_APPS_UTILITIES.GET_DISPLAY_VALUE(100000,'MS SRA VISIT STATUS',V.VISIT_STATUS) AS VISIT_STATUS ,
    MS_SRA_VISIT_SETUP_HOOK_PKG.ms_sra_get_link(v.dd_process_instance_id,'2109760788',v.visit_status,v.visit_id) LINK
  FROM MS_SRA_VISIT_SETUP V,
    (SELECT VISIT_ID ,
      FACILITY_ID,
      facility_name,
      city,
      STATE_PROVINCE,
      COUNTRY
    FROM MS_SRA_FACILITY_INFO p
    WHERE DD_OBJECT_TYPE='MS_SRA_VISIT_SETUP'
    ) F
  WHERE F.VISIT_ID = V.VISIT_ID
  ORDER BY F.VISIT_ID DESC
  )
WHERE (:status                                              IS NULL
OR filter_status                                             = :status)
AND ((TRIM( :global_status)                                 IS NULL
OR (facility_name                                           IS NULL
OR visit_id                                                 IS NULL
OR CITY                                                     IS NULL
OR STATE_PROVINCE                                           IS NULL
OR start_date                                               IS NULL
OR country                                                  IS NULL))
OR INSTR(LOWER(facility_name), LOWER( :global_status))       > 0
OR INSTR(LOWER(visit_id), LOWER( :global_status))            > 0
OR INSTR(LOWER(CITY), LOWER( :global_status))                > 0
OR INSTR(LOWER(STATE_PROVINCE), LOWER( :global_status))      > 0
OR INSTR(LOWER(TO_CHAR(START_DATE)), LOWER( :global_status)) > 0
OR INSTR(LOWER(COUNTRY), LOWER( :global_status))             > 0) ;

Open in new window



Experts ....

If you need the table structure or sample data then I can provide.

Let me know if you need any details.

Thanks,
Thomos
0
Comment
Question by:deve_thomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39783022
Check out all the OR's you have.

It reads like:
A is null or B is null or INSTR ...

If ANY of those return true, the entire section (everything in the parenthesis) is true.

Check the data.  The row with India must be meeting one of the other OR'd clauses.

For example:  Maybe It's CITY is NULL?
0
 

Author Comment

by:deve_thomos
ID: 39783060
Hi Experts,
That is not my requirement. i  if i am searching  , suppose country is  usa . it will show all the records belongs USA. but i am getting the record which is  not related   to usa. some other countries  records are showing.

Or if i search a city then I should get only list of  cities which i have input . But there were I am facing issues.  

Thanks
Thomos
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39783084
>>suppose country is  usa

Based on the SQL you posted, how do you specify the term to search is ONLY in the Country field?

That said, check your OR's.

For example:
drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));
insert into tab1 values('a','b');
insert into tab1 values('c',null);
commit;

Open in new window


I only want col1 that is an 'a'.

Your query is similar to this:
select col1 from tab1 where (col1 is null or col2 is null or col1='a');

Both rows above are returned.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:deve_thomos
ID: 39783130
HI  expert,
I am not getting from your answer. can you   please give an example with query.
the way i have given to you. please i am not able to understand.


Thanks
Thomos
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39783190
My example above is trying to simplify your query and show you the issue you are likely having.

You said you want COUNTRY='USA' but I do not see anything in your query where you specifically search COUNTRY.

You are searching many fields for the same INSTR value.

The other problem you have is all the NULL checks with an OR between them.

Back to my example above:  Where you want country='USA', I search for col1='A'.

Look at my query:  The OR with the null checks cause both rows to be returned even though I have col1='a' because "col2 is null" in the other row.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 39783213
See if this one helps.

It uses the same column names and most of your existing where clause.

I insert two rows.  One with country of USA and one with India.

I then issue a select after setting :global_status='USA'.

It returns both rows because in the second one, facility_name is null.

You can run the complete test case using sqlplus.

drop table tab1 purge;
create table tab1(
	facility_name char(1),
	visit_id char(1),
	city char(1),
	state_province char(1),
	start_date char(1),
	country varchar2(10)
);

insert into tab1 values('a','a','a','a','a','USA');
insert into tab1 values(null,'a','a','a','a','India');
commit


var global_status varchar2(10)

exec :global_status := 'USA'

select country from tab1
WHERE 
((TRIM( :global_status)                                 IS NULL
OR (facility_name                                           IS NULL
OR visit_id                                                 IS NULL
OR CITY                                                     IS NULL
OR STATE_PROVINCE                                           IS NULL
OR start_date                                               IS NULL
OR country                                                  IS NULL))
OR INSTR(LOWER(facility_name), LOWER( :global_status))       > 0
OR INSTR(LOWER(visit_id), LOWER( :global_status))            > 0
OR INSTR(LOWER(CITY), LOWER( :global_status))                > 0
OR INSTR(LOWER(STATE_PROVINCE), LOWER( :global_status))      > 0
OR INSTR(LOWER(TO_CHAR(START_DATE)), LOWER( :global_status)) > 0
OR INSTR(LOWER(COUNTRY), LOWER( :global_status))             > 0) ;

Open in new window

0
 

Author Comment

by:deve_thomos
ID: 39784489
@slightwv: I undertand but my issue is like I can put 'USA' or 'usa' or null .

If I pass USA then I should get all the records only for USA and this should be case insensitive too.

And if I pass null then I should get all the records .
0
 

Author Closing Comment

by:deve_thomos
ID: 39784493
Thanks a lot...
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39785218
>>If I pass USA then I should get all the records only for USA and this should be case insensitive too.

Then you need to change your logic.

Another issue you have is if any of the other columns would have USA in it somewhere.  For example facility_name has 'Usability Facility name', even if the country is India, it would be returned since it has 'Usa' in it.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question