Creating Search Queries using indents

  • Thread starter DaveC426913
  • Start date
  • Tags
    Search
In summary, the conversation is about constructing a search query in a proprietary database and the warning message that results from using "ands" and "ors" in the query. The expected results are enrolled members between ages 50 and 74 who have had at least one of the specified conditions within a given time frame. However, there is a concern about the system responding with an error message and the need for more bracketing in the query to rectify the problem. The experts suggest different ways to structure the query using logical operators and brackets to ensure correct results. The conversation ends with a successful solution and appreciation for the helpful advice.
  • #1
DaveC426913
Gold Member
22,480
6,147
TL;DR Summary
Why is this search query warning me about and/or indentation?
I'm constructing a search query in a proprietary database and I'm getting a warning about my use of ands and ors.

I've included the search as pseudocode. I have inserted square brackets whereas the actual query only uses indents (which I cannot get my head around).

What I am expecting to get is:
  • Enrolled members
  • between ages 50 and 74 (incl.)
  • who have had at least one of the following (during the specified 30 month window):
    • Q code along with SOB or FOBT
    • FIT
    • SIG

Here is my concern:
When I test this the system responds with
"You should not have an and and an or at the same indent level."

Is my search not well-formed?
Can I rectify the problem simply by indenting the entire section after the third and that contains all the ors? (That would require an initial double-indentation, i.e. and [[ )
Code:
Member Status = Enrolled
and
Age >= 50
and
Age <= 74
and
[
    Q code date is earlier than Apr 1, 2021
    and
    Q code date is later than Sep 30, 2018
    and
    [
        [
            SOB date is earlier than Apr 1, 2021
            and
            SOB date is later than Sep 30, 2018
        ]
        or
        [
            FOBT date is earlier than Apr 1, 2021
            and
            FOBT date is later than Sep 30, 2018
        ]
    ]
]
or
[
    FIT date is earlier than Apr 1, 2021
    and
    FIT date is later than Sep 30, 2018
]
or
[
    SIG date earlier than Apr 1, 2021
    and
    SIG date is later than Sep 30, 2018
]
 
Technology news on Phys.org
  • #2
You need more bracketing : the proggy is reading it as a and b and c and d or e or f or g (or something like that). [edit: *less* bracketing... let me see what I can come up with]

Apart from the obvious ranging (20<age<120 sort of thing) I can't really tell, either.

[edit: I suppose reading the whole of your post would help]

There is actually an order of precedence with logical operators, but nobody remembers what it is (thus the blanket "you can't do that" message).

You have seven conditions : MS Age Q SOB FOBT FIT SIG
Can you set them up with ands and ors and brackets, and then work on the details of each condition. [edit: or read the next post]
 
Last edited:
  • #3
MS & <Age> & [ ( <Q> & { <SOB> | <FOBT> } ) | <FIT> | <SIG> ]
Something to that effect. As you've probably surmised & | is and and or, and <>[]{}() are all in/outdents and []brackets. I'd suggest an indentation for the two age conditions for legibility.
 
Last edited:
  • #4
I would go the hacking route and try subsets of these queries taking out one condition term at a time until it works and then begin adding them back working from the outer level to the inner level.
 
  • Like
Likes sysprog
  • #5
Thanks. I experimented with the editor and found out that it does, in fact, allow double indentations, so I can "simply" add more indents where- and as-needed.
Code:
Member Status = Enrolled
and
Age >= 50
and
Age <= 74
and
[
    [
        Q142 code date is earlier than Apr 1, 2021
        and
        Q142 code date is later than Sep 30, 2018
        and
        [
            [
                SOB date is earlier than Apr 1, 2021
                and
                SOB date is later than Sep 30, 2018
            ]
        or
            [
                FOBT date is earlier than Apr 1, 2021
                and
                FOBT date is later than Sep 30, 2018
            ]
        ]
    ]
    or
    [
        FIT date is earlier than Apr 1, 2021
        and
        FIT date is later than Sep 30, 2018
    ]
    or
    [
        SIG date is earlier than Apr 1, 2021
        and
        SIG date is later than Sep 30, 2018
    ]
]
Unfortunately, my results contain some "false positives".
It seems to be returning some SOB/FOBTs without meeting the Q142 requirement.


Working just fine now. The Q142 condition was not actually spelled correctly.

Thanks all!
 
Last edited:
  • #6
You appear to be just tossing brackets randomly. Each of those "ranging" condition-pairs should be enclosed in their own brackets.
 
Last edited:
  • #7
Code:
Query :
    MS = enrolled
AND
    (Age range)
AND (  (   (Q142 range)
       AND (  (SOB range)
           OR (FOBT range)
           )
       )
    OR (FIT range)
    OR (SIG range)
    )
 
Last edited:
  • #8
hmmm27 said:
You appear to be just tossing brackets randomly.
Hah. No. Not sure why you'd say that since your solution actually has two more sets (9) than mine does (7). :wink:

And mine are virtually the same as yours, I just have them on separate lines because I didn't abbreviate the date conditional pairs like you did.

hmmm27 said:
Each of those "ranging" condition-pairs should be enclosed in their own brackets.
They all are, except the Q142. And it doesn't need its own set since it's part of a list of ANDs.

In yours, the age conditional pair doesn't need brackets.
 
Last edited:
  • #9
DaveC426913 said:
Hah. No. Not sure why you'd say that since your solution actually has two more sets (9) than mine does (7). :wink:
Yeah, but mine's prettier.

And mine are virtually the same as yours, I just have them on separate lines because I didn't abbreviate the date conditional pairs like you did.
Exactly the same, but no way was I going to write that stuff out 20 times.
They all are, except the Q142. And it doesn't need its own set since it's part of a list of ANDs.

In yours, the age conditional pair doesn't need brackets.
Makes it easier to read : compiler will just toss them, anyways.
Code:
Query :
    Member Status = enrolled
AND
    (Age >= 50 AND
     Age <= 74)
AND (  (   (Q142 date is   later than Sep 30, 2018 AND
            Q142 date is earlier than Apr  1, 2021)
       AND (  (SOB  date is   later than Sep 30, 2018 AND
               SOB  date is earlier than Apr  1, 2021)
           OR (FOBT date is   later than Sep 30, 2018 AND
               FOBT date is earlier than Apr  1, 2021)
           )
       )
    OR (FIT date is   later than Sep 30, 2018 AND
        FIT date is earlier than Apr 1,  2021)
    OR (SIG date is   later than Sep 30, 2018 AND
        SIG date is earlier than Apr 1,  2021)
    )
Still prettier.
:wink:
 
Last edited:
  • Like
Likes DaveC426913

1. What is the purpose of using indents in search queries?

Indents in search queries are used to refine and narrow down search results by adding additional parameters or conditions to the query. This allows for more specific and accurate search results.

2. How do I create a search query using indents?

To create a search query using indents, you can use special symbols or operators such as quotes, plus signs, and minus signs to specify certain criteria or exclude certain terms from your search. You can also use advanced search options provided by search engines to add indents to your query.

3. Can indents be used for all types of search queries?

Yes, indents can be used for all types of search queries on most search engines. However, some search engines may have different operators or symbols for creating indents, so it is important to check their guidelines before using them.

4. What are some common mistakes to avoid when using indents in search queries?

Some common mistakes to avoid when using indents in search queries include using too many operators or symbols, using incorrect syntax, or not understanding how the search engine's algorithm interprets indents. It is also important to use relevant and specific keywords to get the best results.

5. Are there any alternatives to using indents in search queries?

Yes, there are other ways to refine search results without using indents, such as using filters or advanced search options provided by search engines. Some search engines also offer natural language processing, which allows for more conversational search queries instead of using specific operators or symbols.

Similar threads

  • Computing and Technology
Replies
15
Views
4K
Replies
10
Views
2K
  • Computing and Technology
Replies
1
Views
3K
Replies
5
Views
3K
Back
Top