If (or Iff) Statements in Query

[LWB]

I'm not sure I can explain this, but I developed a "nested" (if that is the right word - one table but multiple lines) Query that is much faster than the TCursor approach I was using to track a line backwards (like a family tree, starting with one and going to many). The problem is that if any line ends before I have reached the number of steps my query requests, I get an empty answer file.

Is there any way to handle the holes? I thought maybe an IFF statement could work, but they don't seem to be allowed in Queries.

Lynn

 

[Langley]

Not sure I understand completely what you are doing. Are you saying that an empty answer table that appears during the course of multiple queries is causing the problem? If so that can be tested for in OPAL.

When you say one table query with multiple lines, are you talking about a single query statement with multiple lines (i.e. a modified OR) or are you talking about querying the same table over and over with different query statements with a block of code?

Maybe if you gave a brief example of the table structure and what you are trying to extract I could give a better response.


Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[LWB]

Mac,

It is a single query statement with multiple lines. A good example would be a family tree (actual code not on this computer). This is over simplified, but it is the approach.

| DN | Father | Mother |
| ~pName | _join1 | _join2 |
| _join1 as father | _join3 | _join4 |
| _join2 as mother | _join5 | _join6 |
| _join3 as pat-grandfather | _join7 | _join8 |
| _join4 as pat-grandmother | _join9 | _join10|
| _join5 as mat-grandfather | _join11| _join12|
| _join6 as mat-grandmother | _join13| _join14|
etc

The problem is that if any the Father or Mother record is "n/a" the query comes up empty. What I really want it to do is to fill in "n/a" for all following parts of the tree. My TCursor approach works fine, but this is faster - it just doesn't work if are any "n/a"s.

Lynn

 

[Langley]

Well, I've never tried something like that before so I'm shooting in the dark. It is logical that you would get a blank record on a join that failed. I don't know if you can use an inclusion operator (!) in this case, because I think you are technically linking to more than one table (even though it's really the same one). My limited query construction abilities don't offer a solution. You may be stuck with the tCursor unless you can find an old copy of Dan Erhmann's book (Paradox Queries). If anyone could query like that it would be him.

Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[footpad]

Lynn,

PMFJI, but I've been trying to wrap my mind around this one for a few minutes now and I'm not sure I'm completely clear on what you're really after. I've tried to re-create your example on some sample data I have, but your example is incomplete, so I'm having to guess at a few things.

I believe Mac's on idea regarding the inclusion operator may help, but I don't think you'll be happy the resulting performance hit, for outer joins are far more time consuming than simple matches.

Depending on what you're really trying to accomplish, I think I'd be tempted to run a series of queries instead of trying to put everything into a single query.

Also, I'm rather surprised your query is faster than your TCursor-based approach, as I've frequently seen the reverse behavior.

Finally, you can include IIF in a query, but only as part of a query expression, which Paradox parses while defining the query. Here's an example:

q = Query

   :DATA:ORDERS | Ship Date |
          Check | > today(), < today + ~( iif( dow() = 1, &quot;8&quot;, iif( dow() = 7, &quot;9&quot;, &quot;7&quot; ) ) ) |

Endquery

Now, I realize that may be a bit hard to read, but the idea is to let Paradox determine the day of the week and then to return the orders that need to be shipped by the same business day next week. (OK, that's a bit confusing, but if the query is run on Saturday or Sunday, then we return the following Monday.)

The thing to remember about query expressions, though, is that you must return values that can be coerced into string values, as that's how QBE grammer is parsed by ObjectPAL.

Hope this helps...

-- Lance