Enform user-defined variables

[Shiva]

Hi all,

I have an Enform where I declare a user variable and use it in the LIST to display records. This works fine. But if I use the user-variable in WHERE clause, I always get zero records no matter what. I looked up in the Enform user guide and I found the following in page 62.

OPEN order;
DECLARE user-var;
SET user-var TO 10;
LIST ordernum,
user-var :=(ordernum + user-var)
WHERE user-var > 10;

It was mentioned that the above enform would return zero records because of SET and I understand that part. But I don't use a SET statement in my enform.

OPEN order;
DECLARE user-var INTERNAL F8.2;
LIST key,
var1,
user-var = key - var1
WHERE user-var > 0;

My assumption is that (since the first time the user-var calculation occurred) the value came as zero (my first records in 'oder' file are zero - I believed that it returned zero records for all the WHERE condition though if I remove the WHERE clause I get all the records, including those where user-var > 0 and others too.

Where am I going wrong? Or I cannot use any user-variables in WHERE clause? I don't see such examples in the Enform User Guide. (I haven't looked deep, but from what I saw there was no user-variable used in WHERE clause)

Any help is appreciated.

[Keith Dick]

You can use user variables in the WHERE clause, but they don't work the way it appears that you think they should.

When a LIST statement begins executing, the current value of any user variable referenced within the statement establishes the initial value for that user variable.

That initial value is the value used when the user variable is referenced in the WHERE clause, regardless of any changes made to the user variable within the target-list.

When evaluating the target-list, the user variable is reset to that initial value at the beginning of the processing of the target-list for every record selected. Any changes to the value of the user variable within the target-list are used in subsequent references to the user variable in the target-list. Those changes do not affect the value of the user variable used in the WHERE clause, and do not affect the initial value used when processing the target-list for the next record selected.

When a LIST statement completes, the value of the user variable retains the last value assigned to it while processing the target-list for the last record selected. If the value of the user variable is not changed by any intervening statement, and the user variable is used in a subsequent LIST statement, that last value assigned is the initial value of the user variable for that next LIST statement.

I don't know what value is used for a user variable if it is referenced in other places, such as title, footer, etc. I don't know whether user variables even can be used in such places.

It has been a long time since I've used ENFORM, the above is what I recall, after reminding myself of some details by looking at the manual. I might not have every last detail correct, but I'm pretty clear about the main points: user variables do not retain changes from one record to the next, and the value of a user variable used in a WHERE clause does not change during the execution of the LIST statement.

So you probably should not use a user variable in a WHERE clause when that user variable is given a value within the target-list of the LIST within which the WHERE clause appears. It is allowed, but the way it works is not the way most people would expect. If you want to declare and use a user variable to give a name to a constant used within a WHERE clause, that works fine, but don't try to use a user variable to change the WHERE clause for each record of the file. If you want to select the records that satisfy some condition computed from the fields of the record and include the value of that computed result in the target-list, I don't know of any way within the ENFORM language to avoid repeating the expression. If you are willing to use some TACL code to feed commands to ENFORM, you can easily use the same expression in two or more places without repeating it.

Well, there is, at least in some cases, a way to do something like that in ENFORM, but it isn't very efficient. You could first use a FIND to create a new file that includes an additional field whose value in each record is set by the computation you would do for the user variable, then you would use a LIST from that file (and a new DDL RECORD for it) to include that new field in the target-list and test it in the WHERE clause. FIND only produces unstructured files, so plan accordingly if you use this approach. However, the fact that it requires writing an intermediate file raises the overhead, so for very large files, you probably would not want to do that, but for modest-sized files, it can sometimes be a useful technique. The intermediate file does not have to include all the records of the input file(s), since a WHERE clause can select which records go into the file FIND produces. But it still probably will be slower than an approach that repeats the expression in t
he target-list and the WHERE clause and does not produce an intermediate file.

[Shiva]

Hmm, that's bad. I thought ENFORM was really good. But it is more of a report making tool rather than an efficient query language. At least that's what it looks like from the outside and I may be wrong. Let's say I convert the database into a table, now would I be able to do the same thing in SQL at least? I'm not too well versed in SQL to be sure.

[Shiva]

And by the way, thanks for your detailed explanation - Keith. You were right on most counts, even if by memory you speak! :)

I'll just write the whole expression up again on ENFORM. I was so sure that I could use a WHERE clause for a user-variable that way. Very disappointed! Ha ha, sometimes you just expect too much out of HP NS because it is so good otherwise.

[Shiva]

Also another ENFORM query - I am trying to take four values out of a file. Say A, B, C, D.

I'm trying to LIST these four fields from the file and I have only one requirement. I want the list to have unique values of B. That should mean that A, C and D would be a sample space for each unique values of B.

I did this:

LIST UNIQUE B
BY
A, C, D;

I'm not sure whether it was the keyword UNIQUE or anything - but my integrity system took more than one hour and still didn't provide me an output. Is my query right?

Any advice appreciated.

[Keith Dick]

Concerning your earlier question, yes, ENFORM is a report writing tool, not a general-purpose database processing language. SQL isn't any better for the particular problem you were asking about, at least I cannot think of a way to avoid duplicating the expression within the SQL language.

As for your new question, the UNIQUE keyword does not work the way I believe you think it does. It eliminates records from the input that are the same in ALL columns, not just eliminating records that have the same value for a particular column. I do have to say that I'm not sure exactly what you want the command to do, so I'm not sure you misunderstood what DISTICT does.

I imagine the reason the command ran so long is that it had to do one or more sorts, and if the file is large, that could take a long time. I'm not sure, but I imagine ENFORM would do one sort on the full record to find any records that are identical and drop all but one of them. Then I imagine it would do a second sort to order the records using the key A,C,D to get the records in the order your LIST asks them to be. If the file happens to have its primary key or an alternate key that is composed of the fields A,C,D, then in the absence of the UNIQUE, it would not have to sort, but I think the results of the sort for UNIQUE will not have any keys, so two sorts would be needed. I supppose if ENFORM were really clever, it could do one sort on A,C,D,B and drop the duplicate records as it was doing the report, but I doubt it is that clever.

As to how you should write the LIST, I'm not sure. I don't know what you mean by "A, C and D would be a sample space for each unique values of B". If you wrote

LIST BY B, A, C, D;

ENFORM would sort the records by B if B were not a key and it would produce a report like:

B1 Ax1 Cx1 Dx1
Ax2 Cx2 Dx2
Ax3 Cx3 Dx3
B2 Ay1 Cy1 Dy1
B3 Az1 Cz1 Dz1
Az2 Cz2 Dz2

That is, the report would sort the records by B and produce a list that showed B only when it changed, but each record would be listed. The lines that have a blank where the value of B would appear really have the value of B that was last displayed above. The order of the records that have the same value for B would not be determined -- the sort key would be only B. I think the way the sort works, the records with a given value of B would appear in the order they appeared in the original file, but I believe that is not guaranteed.

If you wrote

LIST BY B, BY A, BY C, BY D;

that would sort the file with the key B,A,C,D and produce a listing similar to the above, except the values of A for a given value of B would be in order and if the value would duplicate the value above it, it would be replaced by blanks. And for a given value of B and A, the values of C would be in order and the value would be blank if it was the same as the value above it.

If you don't want a value to be blanked when it matches the value just above it, you can do that this way:

LIST BY B, BY A NOPRINT, BY C NOPRINT, BY D NOPRINT, A, C, D;

But, still, I feel that isn't what you want, since you seem to want to see only one record in the report for each distinct value of B, and neither of the above does that -- they only avoid printing the value of B when it matches the value in the previous record after sorting by B.

When you say you want the list to have unique values of B, I think that means that you want only one line to appear for each distinct value of B in the file. If that is correct, what do you want to see to the right of B in the report? Do you want to see something like the sum of the values in A for records that all have the same value of B? (It does not seem like that.) Do you somehow want to select just one of the records for each value of B, print the values of B, A, C, D from it and ignore the other records that have the same value of B? (If there is a way to do that, I don't know what it is.) Something else?

It might be that ENFORM can't do what you want to do and you would need to write a program to do it, but since I don't understand what you want, I can't say for sure that is the case.

[Shiva]

Really sorry, Keith. I see that I still haven't learnt the art of asking questions with every detail required for your people to answer the query. But yes, you did figure out that the following is what I want.

Do you somehow want to select just one of the records for each value of B, print the values of B, A, C, D from it and ignore the other records that have the same value of B?

I haven't worked much with any query language to be sure whether any of those languages support the above set of requirements in just one line of a query. But like you have said that ENFORM doesn't support that - it comes back to my original assumption (how so ever shocking it had been to me) that Enform is not the best of query languages. I had thought otherwise, initially I thought that ENFORM was a query language that is good at producing reports too. Because that obviously seemed to me, to be the way HP would operate.

But of course, I don't really know if other query languages can do that yet. A friend of mine tells me that in SQL you'd just do a SELECT DISTINCT B, A, C, D. Where the DISTINCT is for B alone.

I can't validate that either. Let me know if there's still a way in ENFORM to do that. Thanks in advance. :)

[Keith Dick]

I'm pretty sure your friend who says DISTINCT in SQL works on individual columns is wrong, unless the SQL he or she uses has an extension to the SQL standard for that. As far as I know, standard SQL specifies that DISTINCT removes duplicate rows from the query result, it does not decide on the basis of an individual column. I'm not sure how an extension that appllied DISTINCT to an individual column would be very usual, since I think it would provide no way to say which values of the other columns you would want for the one value of the DISTINCT columne. Such a non-deterministic function wouldn't seem very useful to me.

Just suppse you had a table:

A B
1 a
1 b
1 c
2 x
2 y
3 k
3 l
3 m

If a statement such as SELECT DISTINCT(A), B; were possible, there are many possible results:

1 a
2 x
3 k
or
1 a
2 y
3 m

and many more combinations. Which is the "right" answer? I don't know that there is an answer that would be useful to a wide range of people, so I imagine that is why SQL did not define something like that.

But back to your problem. If you really want an ENFORM report that shows just one row for each unique value of B, and it doesn't matter which of the rows is picked, I believe the following will do so:

LIST BY B NOPRINT AFTER CHANGE ON B PRINT B, " ", A, " ", C, " ", D;

You need to put in spaces because a print list puts each item immediately after the other. This won't give you any column headings. You might be able to use TITLE or SUBTITLE to print column headings. This has the same logical problem as I described above for SQL -- the values of A, C, and D for each B are arbitrary, so I'm not sure why you would find such output useful.

You have to have BY B, since the AFTER CHANGE ON has to name a BY field.

Now if the A, C, and D fields were numeric, and you wanted to see just the totals for each distinct B, I think this would do it:

LIST BY B, SUM(A) OVER B, SUM(C) OVER B, SUM(D) OVER B;

You could get average, max, or min using the appropriate function names instead of SUM. I think you could even get the range for each value of B:

LIST BY B, MAX(A) OVER B, MIN(A) OVER B, MAX(C) OVER B, MIN(C) OVER B, MAX(D) OVER B, MIN(D) OVER B;

I can see where either of those reports might be useful in certain situations. I just don't see how a report that selects a random row for each value of B would be useful, but if you have a good use for it, I think using the AFTER CHANGE ON will do it.

[Shiva]

Yes, Keith. You're right for the millionth time. DISTINCT is row wise. Then I can't find an SQL query for that one. I'll check whether this ENFORM works and let you know. I haven't tried it yet, but looks like it should work.

Hmm, pretty cool. In a second, you changed my mind about ENFORM. If this works - then SQL < ENFORM. Just kidding. This is good.

And about my requirement. Say you have a table with Name, PhoneNumber, things, and price. And you are in a supermarket. And the manager asks you to get details of customer names (Name and PhoneNumber) for each product. And he needs just one customer details for each product. May be to call them and ask how the product was. Or just something like that. Why won't somebody have a sample space requirement for anything! Of course you can get the same through other queries like say all details for all products or so on. But what if there are duplicates. Disabling duplicates by column is something that is part of MicroSoft Office Excel even. I'm just making this example up so that my requirement makes sense. Ha ha, please don't make fun of me if this example is not so good either. Just that I had a similar requirement where there are too many duplicates on one column and I just needed to check one of those values on other cells.

I will try that ENFORM out and let you know, but great work. Thanks!

[Keith Dick]

Shiva wrote:
> Yes, Keith. You're right for the millionth time. DISTINCT is row wise. Then I can't find an SQL query for that one. I'll check whether this ENFORM works and let you know. I haven't tried it yet, but looks like it should work.
>
> Hmm, pretty cool. In a second, you changed my mind about ENFORM. If this works - then SQL < ENFORM. Just kidding. This is good.

Actually, SQL/MP includes report writer capability that should allow you to do the same thing with SQL/MP.

SQL/MX does not include the report writer capability. The idea was that reporting tools are available on personal computers and SQL/MX data can easily be accessed from personal computers, so reports would be done that way.

>
> And about my requirement. Say you have a table with Name, PhoneNumber, things, and price. And you are in a supermarket. And the manager asks you to get details of customer names (Name and PhoneNumber) for each product. And he needs just one customer details for each product. May be to call them and ask how the product was. Or just something like that. Why won't somebody have a sample space requirement for anything! Of course you can get the same through other queries like say all details for all products or so on. But what if there are duplicates. Disabling duplicates by column is something that is part of MicroSoft Office Excel even. I'm just making this example up so that my requirement makes sense. Ha ha, please don't make fun of me if this example is not so good either. Just that I had a similar requirement where there are too many duplicates on one column and I just needed to check one of those values on other cells.

Oh, now I see what you mean by a sample. That does make what you asked to do seem a bit more reasonable. I think it would be a lot more common to want more than a single sample per value, and there isn't any way to extend the approach I described to print more than a single sample per value. I guess the simplest way I can think of to do that would be to send the output of a regular report that used BY B (and no NOPRINT or AFTER CHANGE) to a program that copied the input lines to its output, but skipped any lines with blanks in column B after it had printed the desired number of lines for the current value of B. That isn't a very hard program to write. The only tricky part would be to make sure that title and header lines were not mistaken for values of B.

[Shiva]

I was actually comparing ENFORM with the SQL of the outside world. But anyways, pretty good. :)

And yes, you're right. The idea is right and the code should be straightforward and pretty easy. And thanks a lot for all your explanations and advice. As always, you've been greatly insightful! Thanks a lot. :)