Creating Search Queries using indents

[DaveC426913]

TL;DR

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 [[ )

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
]

 

[hmmm27]

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]

 

[hmmm27]

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.

 

[jedishrfu]

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.

 

[DaveC426913]

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.

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!

 

[hmmm27]

You appear to be just tossing brackets randomly. Each of those "ranging" condition-pairs should be enclosed in their own brackets.

 

[hmmm27]

Query :
    MS = enrolled
AND
    (Age range)
AND (  (   (Q142 range)
       AND (  (SOB range)
           OR (FOBT range)
           )
       )
    OR (FIT range)
    OR (SIG range)
    )

 

[DaveC426913]

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).

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.

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.

 

[hmmm27]

Hah. No. Not sure why you'd say that since your solution actually has two more sets (9) than mine does (7).

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.

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.