recursive query?

[shivalee]

i have this query:

SELECT agr_1251.AGR_NAME
FROM agr_1251
WHERE (((agr_1251.OBJECT)="s_tcode") AND ((agr_1251.FIELD)="tcd") AND ((agr_1251.LOW)="f110"))
OR
(((agr_1251.OBJECT)="f_regu_buk") AND ((agr_1251.FIELD)="fbtch") AND ((agr_1251.LOW)="2" And (agr_1251.LOW)="3" And (agr_1251.LOW)="11" And (agr_1251.LOW)="21" And (agr_1251.LOW)="25"))
OR
(((agr_1251.FIELD)="bukrs") AND ((agr_1251.LOW)="")) OR
(((agr_1251.OBJECT)="f_regu_koa") AND ((agr_1251.FIELD)="fbtch") AND ((agr_1251.LOW)="2" And (agr_1251.LOW)="3" And (agr_1251.LOW)="11" And (agr_1251.LOW)="21" And (agr_1251.LOW)="25"))
OR
(((agr_1251.FIELD)="koart") AND ((agr_1251.LOW)=""));

it is just a simple query which shows a name depending on all the 3 grouped
values listed.
if there are 10 records in a file, and this query is for the first record,
can i attach this query to the first record? so i can run this query
recursively for the rest of the 9 records?

please help.
shivalee

 

[mresann]

Before you can even consider recursivity, you should be thinking of ensuring you won't get zero records returned. Looking into your query example, I see snippets such as the following:

"...((agr_1251.LOW)="2" And (agr_1251.LOW)="3" And ..."

Since the field cannot be equal to two different values simultaneously, the statement above will negate any record return in that statement. Other OR statements contain similar examples.

 

[Pat Hartman]

and this query is for the first record

What do you mean by this? Do you mean that the criteria is designed to select one specific record from the table?

 

[shivalee]

thanks for replying.
this query of mine is working and working perfectly. as internally it is working as "And" such as values of low and then with outer it is working as "or" it returns me 5 rows, which i have rechecked in the database and they are returning me the correct answer.
secondly what i mean by "and this query is for the first record " is that i have a file. it has 1500 records which are grouped on id's, so after grouping i have 215 ids. the query i have made is for the 1st id. i need to run this query recursively for all the 215 ids. all the groups are not same which means they have different data and different fields and values.
it is very complex, i know, but if i could have solved it on my own, why would i have posted my question here!

 

[Pat Hartman]

this query of mine is working and working perfectly

You are mistaken. The "OR" clauses in red will NEVER select anything so they could be removed without impacting the recordset. I reformatted the query to remove all extraneous parentheses since they make the true structure too hard to understand.

SELECT agr_1251.AGR_NAME
FROM agr_1251
WHERE (agr_1251.OBJECT = "s_tcode" AND agr_1251.FIELD = "tcd" AND agr_1251.LOW = "f110")
OR
(agr_1251.OBJECT = "f_regu_buk" AND agr_1251.FIELD = "fbtch" AND agr_1251.LOW = "2" And agr_1251.LOW = "3" And agr_1251.LOW = "11" And agr_1251.LOW = "21" And agr_1251.LOW = "25")
OR
(agr_1251.FIELD = "bukrs" AND agr_1251.LOW = "")
OR
(agr_1251.OBJECT = "f_regu_koa" AND agr_1251.FIELD = "fbtch" AND agr_1251.LOW = "2" And agr_1251.LOW = "3" And agr_1251.LOW = "11" And agr_1251.LOW = "21" And agr_1251.LOW= "25")
OR
(agr_1251.FIELD = "koart" AND agr_1251.LOW = "");

SELECT agr_1251.AGR_NAME
FROM agr_1251
WHERE (agr_1251.OBJECT = "s_tcode" AND agr_1251.FIELD = "tcd" AND agr_1251.LOW = "f110")
OR
(agr_1251.OBJECT = "f_regu_buk" AND agr_1251.FIELD = "fbtch" AND (agr_1251.LOW = "2" OR agr_1251.LOW = "3" OR agr_1251.LOW = "11" OR agr_1251.LOW = "21" OR agr_1251.LOW = "25"))
OR
(agr_1251.FIELD = "bukrs" AND agr_1251.LOW = "")
OR
(agr_1251.OBJECT = "f_regu_koa" AND agr_1251.FIELD = "fbtch" AND (agr_1251.LOW = "2" OR agr_1251.LOW = "3" OR agr_1251.LOW = "11" OR agr_1251.LOW = "21" OR agr_1251.LOW= "25"))
OR
(agr_1251.FIELD = "koart" AND agr_1251.LOW = "");

Once the query has been corrected, post what you think will be the queries to extract records 2 and 3 and we'll tell you what needs to change.

 

[shivalee]

if this is not the way i should run the complex query, then i do not have any idea how it would be done otherwise. please suggest the query.
please suggest the change in the query.
thanks a ton for ur help

 

[Pat Hartman]

Isn't that exactly what I did? Please read the entire post. Then if you want help with subsequent queries, answer the question.

 

[shivalee]

thanks for replying. sorry for not understanding it earlier. I have tried your concept. it had only one problem, it was giving a lot of duplicate rows, so i added "distinct". now it is giving the same 5 records it is giving via my query. i also have the same query written another way:

SELECT agr_1251.AGR_NAME
FROM agr_1251
WHERE (((agr_1251.OBJECT)="s_tcode") AND ((agr_1251.FIELD)="tcd") AND ((agr_1251.LOW)="f110")) OR (((agr_1251.OBJECT)="f_regu_buk") AND ((agr_1251.FIELD)="fbtch") AND ((agr_1251.LOW)="2" And (agr_1251.LOW)="3" And (agr_1251.LOW)="11" And (agr_1251.LOW)="21" And (agr_1251.LOW)="25")) OR (((agr_1251.FIELD)="bukrs") AND ((agr_1251.LOW)="")) OR (((agr_1251.OBJECT)="f_regu_koa") AND ((agr_1251.FIELD)="fbtch") AND ((agr_1251.LOW)="2" And (agr_1251.LOW)="3" And (agr_1251.LOW)="11" And (agr_1251.LOW)="21" And (agr_1251.LOW)="25")) OR (((agr_1251.FIELD)="koart") AND ((agr_1251.LOW)=""));

the earlier one was:
SELECT agr_1251.AGR_NAME
FROM agr_1251
WHERE (((agr_1251.OBJECT)="s_tcode") AND ((agr_1251.FIELD)="tcd") AND ((agr_1251.LOW)="f110")) OR (((agr_1251.OBJECT)="f_regu_buk") AND ((agr_1251.FIELD)="fbtch" And (agr_1251.FIELD)="bukrs") AND ((agr_1251.LOW)="2" And (agr_1251.LOW)="3" And (agr_1251.LOW)="11" And (agr_1251.LOW)="21" And (agr_1251.LOW)="25" And (agr_1251.LOW)="")) OR (((agr_1251.OBJECT)="f_regu_koa") AND ((agr_1251.FIELD)="fbtch" And (agr_1251.FIELD)="koart") AND ((agr_1251.LOW)="2" And (agr_1251.LOW)="3" And (agr_1251.LOW)="11" And (agr_1251.LOW)="21" And (agr_1251.LOW)="25" And (agr_1251.LOW)=""));

yours said :
SELECT DISTINCT agr_1251.AGR_NAME
FROM agr_1251
WHERE (((agr_1251.OBJECT)="s_tcode") AND ((agr_1251.FIELD)="tcd") AND ((agr_1251.LOW)="f110")) OR (((agr_1251.OBJECT)="f_regu_buk") AND ((agr_1251.FIELD)="fbtch") AND ((agr_1251.LOW)="2" Or (agr_1251.LOW)="3" Or (agr_1251.LOW)="11" Or (agr_1251.LOW)="21" Or (agr_1251.LOW)="25")) OR (((agr_1251.FIELD)="bukrs") AND ((agr_1251.LOW)="")) OR (((agr_1251.OBJECT)="f_regu_koa") AND ((agr_1251.FIELD)="fbtch") AND ((agr_1251.LOW)="2" Or (agr_1251.LOW)="3" Or (agr_1251.LOW)="11" Or (agr_1251.LOW)="21" Or (agr_1251.LOW)="25")) OR (((agr_1251.FIELD)="koart") AND ((agr_1251.LOW)=""));


all 3 are giving me exactly the same result-5 rows. my only question, can you be kind enough and explain me which one is correct and which one should i use and why?
thanks ever so much for the great help...i am highly obliged.
regards,
shivalee

 

[shivalee]

I have studied all queries harder...yours and all mine. I tried running them individually and looking at the results...all queries are wrong. or as I may put it...not giving me the results I want. the moment all the 3 queries encounter an "or" , it does not run that part at all. and that is not what I want. I want an "and" in all the situations and that is not coming. please comment and also help!!!!

 

[Pat Hartman]

As I read the query, it has 5 separate COMPOUND conditions. In the original query, 2 of these conditions were not selecting any rows. I corrected that problem by changing the AND's to OR's and fixing the parentheses. You said the changed query selected the same rows as the original. Now you're saying that's wrong. What are the values of the three fields in the record you think should have been selected?

You are the person who knows your business rules. You should know what you are trying to select. I simply pointed out that your query had two clauses that would NEVER UNDER ANY CONDITIONS cause a record to be selected. You could remove them and the query would return exactly the same rows as it did with them. The fact that changing the query to be logically correct did not change the selected records simply means that there is no data in the table that satisfies the conditions that were fixed.

Examine the following and think about it:

OR (agr_1251.OBJECT="f_regu_buk" AND agr_1251.FIELD="fbtch" AND (agr_1251.LOW="2" And agr_1251.LOW="3" And agr_1251.LOW="11" And agr_1251.LOW="21" And agr_1251.LOW="25"))

How is is possible for one field - agr_1251.LOW to be equal to "2" AND "3" AND "11" AND "21" AND "25" AT THE SAME TIME?????? It cannot. That is why I changed the inner clause to OR's. It could have been changed to:
OR (agr_1251.OBJECT="f_regu_buk" AND agr_1251.FIELD="fbtch" AND agr_1251.LOW In("2", 3", "11", "21", "25") Using the IN() operator is more concise since the field name isn't repeated 5 times and it also prevents the problems of confusing AND with OR and with using parentheses improperly when both AND and OR operands are included in the same query.

 

[shivalee]

I totally 100% agree with you, you are absolutelt right and correct. but that is where my problem starts. I know what i want and that is why I wrote that query. I know my business rules as well as my output. But what I dont know is HOW? Even if we leave aside for a second the point what you are saying, we can consider then the first and big issue, which is:
WHERE (((agr_1251.OBJECT)="s_tcode") AND ((agr_1251.FIELD)="tcd") AND ((agr_1251.LOW)="f110")) OR
The moment the query encounters an "OR" it does not execute the rest of the query. I tried running the 5 compounds of the query individually. and the query does not return the required results.
so we have 2 issues here:
1) the "Or" situation
2) and the low having more than 1 values.
can you now suggest what can one do?
thanks for helping & replying,
shivalee

 

[Pat Hartman]

The only time you would have trouble with the OR's connecting the 5 compound conditions is if there were no parentheses surrounding each of the 5 conditions. I'm sure that the query builder put all those parens in because no human would do that. I removed all the extraneous parens so that you could see the real structure of the query. If you save the query in SQL view and do not switch back to QBE view, Access will leave the parens alone and not go crazy adding them back. Essentially you have:

Where (condition 1) OR (condition 2) OR (condition 3) OR (condition 4) OR Condition 5);

Each of the conditions contains a subset of conditions that must be true for the main condition to evaluate to true. So (condition 1) really equals
(cond A AND cond B AND cond C). That means that all three things need to be true for condition 1 to be true. Once one of the OR conditions tests true, the others are irrelevant. They can be true or false, it doesn't matter. One of the 5 conditions being true will cause the row to be selected.

You didn't answer my question. WHAT ARE THE VALUES IN THE THREE FIELDS IN QUESTION FOR THE RECORD THAT IS NOT BEING SELECTED THAT YOU THINK SHOULD BE?

 

[shivalee]

As you are saying, I removed the parentheses. but in this:

Where (condition 1) OR (condition 2) OR (condition 3) OR (condition 4) OR Condition 5);
my where (condition 1) becomes true. and the query stops after that. my conditions 2 or 3 or 4 or 5 are never evaluated at all. The subset of each query although are being executed(if I am trying to execute them individually).

I believe your question : "You didn't answer my question. WHAT ARE THE VALUES IN THE THREE FIELDS IN QUESTION FOR THE RECORD THAT IS NOT BEING SELECTED THAT YOU THINK SHOULD BE?"
this is what your are meaning:

if I am executing each condition individually, then,
(agr_1251.OBJECT = "s_tcode" AND agr_1251.FIELD = "tcd" AND agr_1251.LOW = "f110")
returns me with 14 rows
(agr_1251.OBJECT = "f_regu_buk" AND agr_1251.FIELD = "fbtch" AND (agr_1251.LOW = "2" OR agr_1251.LOW = "3" OR agr_1251.LOW = "11" OR agr_1251.LOW = "21" OR agr_1251.LOW = "25"))
OR
returns me with 112 rows and so on.
out of these 14 + 112 and all the output rows together, there are 12 such rows which are compliant with my condition, as i have worked out on paper.

Please do not get angry. I am also trying my best here.
Thanks alot for delving deeper into my subject. your time and effort are very highly appreciated.
shivalee

 

[Pat Hartman]

The where conditions are evaluated against each record. If ANY of them is true for the current record, it is selected. Put another way, if ONE of the 5 conditions is true, then the other 4 are false for any given record. The conditions you have described are mutually exclusive. So cond5 might be true for rec1, cond3 might be true for rec2, no condition may be true for rec3, etc. So, rec1 and rec2 are output by the query, rec3 is NOT.

go back to:

the moment all the 3 queries encounter an "or" , it does not run that part at all

The answer to the question:

WHAT ARE THE VALUES IN THE THREE FIELDS IN QUESTION FOR THE RECORD THAT IS NOT BEING SELECTED THAT YOU THINK SHOULD BE?

is:
agr_1251.OBJECT = "x"
agr_1251.FIELD = "y"
agr_1251.LOW = "z"
Why does condition n not select this row?

 

[shivalee]

I think the problem is that you are not understanding my question. I shall try and explain again. Lets not take the query and try to solve it, for a moment. lets take the basic question and then try to solve it.
i have a file called agr_1251. the first 12 rows correspond to the one single sodid i.e. 1. then i have like 8 rows corresponding to the second sodid i.e. 2, and so on I have 1500 rows corresponding to 215 sodid's. The structure I am looking at looks like this:
sodid object field1 value1 field2 value2.
1 S_TCODE TCD F110
1 F_REGU_BUK FBTCH 02 BUKRS
1 F_REGU_KOA FBTCH 02 KOART
1 F_REGU_BUK FBTCH 03 BUKRS
1 F_REGU_KOA FBTCH 03 KOART
1 F_REGU_BUK FBTCH 11 BUKRS
1 F_REGU_KOA FBTCH 11 KOART
1 F_REGU_BUK FBTCH 21 BUKRS
1 F_REGU_KOA FBTCH 21 KOART
1 F_REGU_BUK FBTCH 25 BUKRS
1 F_REGU_KOA FBTCH 25 KOART

(As in this case, if you see, value2 is null for all rows, but it is not so for all sodid's.)
Now if you refer to my query and your question, which I am still not able to understand, you will undertand. Still I shall put it in words,
I need a code which will :
search a name which has
object=s_tcode and field1=tcd and value1=f110
AND
object=f_regu_koa and field1=fbtch and value1=02 and
field2=bukrs and value2=null
AND
object=f_regu_buk and field1=fbtch and value1=02 and
field2=koart and value2=null
AND on and on and on till the sodid is equal to 1.
I have run each query individually and they are returning me rows which are not returned once all the queries are run together. I need the rows which are common to all the queries, which amounts to at least 10 rows(I have checked it out manully).
Am I able to answer your question or else can you re-phrase it?
Thanks,
shivalee

 

[Pat Hartman]

I think the problem is that you are not understanding my question.

No - you are not understanding the answer. I am trying to tell you how boolean logic works. It is imperative that you have some basic understanding of how compound conditions are evaluated. You also need to have some concept of a set. A set is 1 or more rows of data that have 1 or more things in common. You keep using AND when you need to use OR. In English we may say, "I want the blue ones and I want the red ones." That would translate to Where Color = "red" OR "blue". The reason that the AND becomes an OR is because as the criteria is applied to each individual row - select this row if the color is red OR select this row if the color is blue. A single row CANNOT be both red and blue. It can only be one OR the other. We want it if it is EITHER.

I need a code which will :
search a name which has
object=s_tcode and field1=tcd and value1=f110
AND
object=f_regu_koa and field1=fbtch and value1=02 and
field2=bukrs and value2=null
AND
object=f_regu_buk and field1=fbtch and value1=02 and
field2=koart and value2=null

object CANNOT be = s_tcode AND f_regu_koa AND f_regu_buk at the same time so this condition would select ZERO! records.

You still haven't identified what set of data you want. Your example simply creates a new condition for each row of data. If you want every record, you don't use any criteria at all.

I have run each query individually and they are returning me rows which are not returned once all the queries are run together.

- Then you have done something wrong. Post ALL the queries.

the first 12 rows correspond to the one single sodid i.e. 1. then i have like 8 rows corresponding to the second sodid i.e. 2, and so on I have 1500 rows corresponding to 215 sodid's.

- If you have a field - sodid - that identifies a group, why are you not using that as criteria?
Where sodid = 1

 

[shivalee]

Hello pat.
I am sending you my sod file as an attchment with this mail. it has 10 sodids. although there actually are 215. Can you please see it for me. Then you can help me out with my query as you are aware of my requirement. then we will discuss our query once again. lets not talk about the query i have made. lets talk about the question and a way to solve it. because you are right and i am not. so maybe if we are on the same platform, then maybe my question can be solved.
thanks a ton,
shivalee

 

[Pat Hartman]

Then you can help me out with my query as you are aware of my requirement.

I haven't a clue what your requirement is. You have not been able to define what "set" of data you want to select. I will ask you again -

- If you have a field - sodid - that identifies a group, why are you not using that as criteria?

Why are you using complicated compound conditions when the table contains what looks to me like a single field that identifies a set.

 

[WayneRyan]

Shivalee,

I looked at your spreadsheet. I also have no idea what your
selection criteria is, or where/how your initial where clause
resides.

But, this is probably closer to what you want. Depending on
whether this is in VBA, or the Query Grid, punctuation is
critical!

Where (object = s_tcode    And 
       field1 = tcd        And 
       value1 = f110)           Or
      (object = f_regu_koa And 
       field1 = fbtch      And 
       value1 = 02         And
       field2 = bukrs      And 
       value2 Is Null)          Or
      (object = f_regu_buk And 
       field1 = fbtch      And 
       value1 = 02         And
       field2 = koart      And 
       value2 Is Null)

Wayne

 

[shivalee]

pat and wayne, you are both trying your level best to help me out. so am I. The code which wayne has sent, I have already tried it. Please can you read the whole post, I think I have explained my question and all the types of queries I have written.
MY SET OF DATA IS BASED ON THE QUERY I AM WRITING, AND THAT I AM TRYING TO EXPLAIN. if you see my mail 2-10-2005, it only explains my question.
Pat, can you suggest how to include SODID for my set of query?
shivalee