Selecting Multivalues

#1

Hello Folks

We all know that "MultiValues" are meant to be one of the great
differentiators of Pick from other system - but it has always troubled
me that the SELECT statement (rather than LIST or SORT) does handle
them well at all.

This is something that has been niggling me for a while - particularly
since many of us no longer rely on the LIST statement for producing
reports - but rather use SELECT to grab data and then feed it into
another display mechanism.

This "niggle" has now turned into an irritation since one of my clients
has a concrete situation that actually needs me to find a way to select
on multivalued data.

The main problem is that associations (between multivalued fields) are
only respected at output time. For example, the WHEN clause in the
Retrieve query language works only on output (e.g. LIST) statements -
since it is effectively a filter at display time rather than at
selection time. That is, WHEN does not apply value restriction to
SELECT statements.

As a concrete example, imagine I have the following:

id salary bonus
001 100 10
200 250
002 300 20
400 450
500 550
003 600 40
700 650

Salary and bonus are defined in an association.

I can do a LIST salary bonus WHEN salary < bonus

which gives me something like the following:

id salary bonus
001 200 250
002 400 450
500 550

What I actually want to do is a SELECT returning just the unique item
ids of any
item for which at least one bonus is greater than its associated
salary. Thus,
in this case I would like a select list containing two entries: 001 and
002.

Is this possible? If so, please can you let me know how - because I
have never been able to figure out a way to do it and I would really be
deeply grateful for any insight you can
give.

Thanks
Anthony
p.s. I can't switch them over to an existing reporting tool that can
handle such things - since this data ultimately needs feeding into a
BASIC subroutine.
p.p.s. The file in question has around 100 000 records, so writing a
BASIC program to do the selection would be a non-starter in terms of
performance relative to using the query language

#2

"Anthony Lauder" <anthony.lauder (AT) gmail (DOT) com> wrote

Quote:

Hello Folks

We all know that "MultiValues" are meant to be one of the great
differentiators of Pick from other system - but it has always troubled
me that the SELECT statement (rather than LIST or SORT) does handle
them well at all.

This is something that has been niggling me for a while - particularly
since many of us no longer rely on the LIST statement for producing
reports - but rather use SELECT to grab data and then feed it into
another display mechanism.

This "niggle" has now turned into an irritation since one of my clients
has a concrete situation that actually needs me to find a way to select
on multivalued data.

The main problem is that associations (between multivalued fields) are
only respected at output time. For example, the WHEN clause in the
Retrieve query language works only on output (e.g. LIST) statements -
since it is effectively a filter at display time rather than at
selection time. That is, WHEN does not apply value restriction to
SELECT statements.

As a concrete example, imagine I have the following:

id salary bonus
001 100 10
200 250
002 300 20
400 450
500 550
003 600 40
700 650

Salary and bonus are defined in an association.

I can do a LIST salary bonus WHEN salary < bonus

which gives me something like the following:

id salary bonus
001 200 250
002 400 450
500 550

What I actually want to do is a SELECT returning just the unique item
ids of any
item for which at least one bonus is greater than its associated
salary. Thus,
in this case I would like a select list containing two entries: 001 and
002.

Is this possible? If so, please can you let me know how - because I
have never been able to figure out a way to do it and I would really be
deeply grateful for any insight you can
give.

Thanks
Anthony
p.s. I can't switch them over to an existing reporting tool that can
handle such things - since this data ultimately needs feeding into a
BASIC subroutine.
p.p.s. The file in question has around 100 000 records, so writing a
BASIC program to do the selection would be a non-starter in terms of
performance relative to using the query language

What platform are you operating on?

On D3, I'd make a dictionary item with a correlative like:

AIF 2 > 1 THEN "1" ELSE "0"

and then do the select like:

select file with BON_GT_SAL = "1"

On R83 based clones, maybe

F;2;1;> (is that right?)

Just something you can LIST that shows 1 if the condition you're looking for
is true.

That way, at least on D3, you could build an index and significantly reduce
your select time.

Mark Brown

#3

Quote:

On D3, I'd make a dictionary item with a correlative like:

AIF 2 > 1 THEN "1" ELSE "0"

and then do the select like:

select file with BON_GT_SAL = "1"

Hello Mark

This is my worry - that I have to make a dictionary. The problem with
this approach is that it means I have to make a new dictionary for
every type of multi-valued comparison the customer wants to do.

Thus, if tomorrow they wanted everybody with a bonus > 1000 and a
salary < 2000 (still respecting multivalued associations) I would have
to make another dictionary specific to that query.

What I was hoping for is a more general way of querying multivalued
associated data, and it seems there is no way to do it - other that
creating dictionaries in every case.

I am using Universe, which does have the advantage that I can at least
create "virtual dictionaries" on the fly via clever use of the EVAL
clause. Not what I hoped for - but maybe it is the best I can achieve.

Strange that there is no simple way to do it. I really cannot
understand why WHEN is only applied on output - it seems like a very
limiting decision when Pick is meant to have strong support for
multivalued data

Thanks for you advice
Anthony

#4

"Anthony Lauder" <anthony.lauder (AT) gmail (DOT) com> wrote

Quote:

On D3, I'd make a dictionary item with a correlative like:

AIF 2 > 1 THEN "1" ELSE "0"

and then do the select like:

select file with BON_GT_SAL = "1"

Hello Mark

This is my worry - that I have to make a dictionary. The problem with
this approach is that it means I have to make a new dictionary for
every type of multi-valued comparison the customer wants to do.

Thus, if tomorrow they wanted everybody with a bonus > 1000 and a
salary < 2000 (still respecting multivalued associations) I would have
to make another dictionary specific to that query.

What I was hoping for is a more general way of querying multivalued
associated data, and it seems there is no way to do it - other that
creating dictionaries in every case.

I am using Universe, which does have the advantage that I can at least
create "virtual dictionaries" on the fly via clever use of the EVAL
clause. Not what I hoped for - but maybe it is the best I can achieve.

Strange that there is no simple way to do it. I really cannot
understand why WHEN is only applied on output - it seems like a very
limiting decision when Pick is meant to have strong support for
multivalued data

Thanks for you advice
Anthony

Does Universe have an ANY clause.

In Pick, AQL allows you to say if ANY field = something or if EVERY field =
something

and it decides if the record fits the criteria.

Sorry I can't be any further help.

Mark

#5

Mark Brown wrote:

Quote:

Does Universe have an ANY clause.

It does have an ANY clause, however it is just "filler" in the query
used (according to the manual) to make the query more "english like"
but is ignored by the query processor :-(

#6

Anthony,

You might be very surprised to discover that an "internal" select (one done
inside a BASIC subroutine) can be and often is much faster than an
"external" (AQL) select. I've done a fair amount of investigation into
complex external selects vs. internal, and only in one case was the external
select faster. This was on a Universe/AIX box. Give it a try.

Also, you are right about "any" being filler. It's a throwaway connective,
added only for readability. AQL defaults to any, overridden only by "every."

Ed

"Anthony Lauder" <anthony.lauder (AT) gmail (DOT) com> wrote

Quote:

Mark Brown wrote:

Does Universe have an ANY clause.

It does have an ANY clause, however it is just "filler" in the query
used (according to the manual) to make the query more "english like"
but is ignored by the query processor :-(

#7

"Anthony Lauder" <anthony.lauder (AT) gmail (DOT) com> wrote

Quote:

On D3, I'd make a dictionary item with a correlative like:

AIF 2 > 1 THEN "1" ELSE "0"

and then do the select like:

select file with BON_GT_SAL = "1"

Hello Mark

This is my worry - that I have to make a dictionary. The problem with
this approach is that it means I have to make a new dictionary for
every type of multi-valued comparison the customer wants to do.

Don't get the concern. Are you talking ad-hoc selects? If not, there msut be
a program involved somewhere, which could just write a temporary Dict to do
the work, no? We do this all the time because the EVAL in Uv is not reliable
(Neither is the external Select when you do a lot of 'em from EXECUTE, but
that's another story).

Chandru

Quote:

Thus, if tomorrow they wanted everybody with a bonus > 1000 and a
salary < 2000 (still respecting multivalued associations) I would have
to make another dictionary specific to that query.

What I was hoping for is a more general way of querying multivalued
associated data, and it seems there is no way to do it - other that
creating dictionaries in every case.

I am using Universe, which does have the advantage that I can at least
create "virtual dictionaries" on the fly via clever use of the EVAL
clause. Not what I hoped for - but maybe it is the best I can achieve.

Strange that there is no simple way to do it. I really cannot
understand why WHEN is only applied on output - it seems like a very
limiting decision when Pick is meant to have strong support for
multivalued data

Thanks for you advice
Anthony

#8

Anthony,

I don't have a solution, but I just want to add my voice to this beef.
You would think MV databases would have better ad hoc tools for
handling multi-values (and sub-values too!).

Regards,
Danny

Anthony Lauder wrote:

Quote:

Hello Folks

We all know that "MultiValues" are meant to be one of the great
differentiators of Pick from other system - but it has always troubled
me that the SELECT statement (rather than LIST or SORT) does handle
them well at all.
snip

#9

LIST SALARY.FILE BY.EXP SALARY WITH SALARY LT BONUS or something like
that. It might work. I did a quick test and it seemed to do the right thing.

I believe exploding the MVs will treat them like seperate records so the
comparison will work. You might have to add SAVING UNIQUE @ID either in
the same select or a subsequent one. I'm far to lazy to test for myself.

Anthony Lauder wrote:

Quote:

Hello Folks

We all know that "MultiValues" are meant to be one of the great
differentiators of Pick from other system - but it has always troubled
me that the SELECT statement (rather than LIST or SORT) does handle
them well at all.

This is something that has been niggling me for a while - particularly
since many of us no longer rely on the LIST statement for producing
reports - but rather use SELECT to grab data and then feed it into
another display mechanism.

This "niggle" has now turned into an irritation since one of my clients
has a concrete situation that actually needs me to find a way to select
on multivalued data.

The main problem is that associations (between multivalued fields) are
only respected at output time. For example, the WHEN clause in the
Retrieve query language works only on output (e.g. LIST) statements -
since it is effectively a filter at display time rather than at
selection time. That is, WHEN does not apply value restriction to
SELECT statements.

As a concrete example, imagine I have the following:

id salary bonus
001 100 10
200 250
002 300 20
400 450
500 550
003 600 40
700 650

Salary and bonus are defined in an association.

I can do a LIST salary bonus WHEN salary < bonus

which gives me something like the following:

id salary bonus
001 200 250
002 400 450
500 550

What I actually want to do is a SELECT returning just the unique item
ids of any
item for which at least one bonus is greater than its associated
salary. Thus,
in this case I would like a select list containing two entries: 001 and
002.

Is this possible? If so, please can you let me know how - because I
have never been able to figure out a way to do it and I would really be
deeply grateful for any insight you can
give.

Thanks
Anthony
p.s. I can't switch them over to an existing reporting tool that can
handle such things - since this data ultimately needs feeding into a
BASIC subroutine.
p.p.s. The file in question has around 100 000 records, so writing a
BASIC program to do the selection would be a non-starter in terms of
performance relative to using the query language

#10

I'm not sure what's wrong with:

SELECT SALARYFILE WITH SALARY < BONUS

This works fine on UV PE v10.1.

Bill

"Anthony Lauder" <anthony.lauder (AT) gmail (DOT) com> wrote

Quote:

Hello Folks

We all know that "MultiValues" are meant to be one of the great
differentiators of Pick from other system - but it has always troubled
me that the SELECT statement (rather than LIST or SORT) does handle
them well at all.

This is something that has been niggling me for a while - particularly
since many of us no longer rely on the LIST statement for producing
reports - but rather use SELECT to grab data and then feed it into
another display mechanism.

This "niggle" has now turned into an irritation since one of my clients
has a concrete situation that actually needs me to find a way to select
on multivalued data.

The main problem is that associations (between multivalued fields) are
only respected at output time. For example, the WHEN clause in the
Retrieve query language works only on output (e.g. LIST) statements -
since it is effectively a filter at display time rather than at
selection time. That is, WHEN does not apply value restriction to
SELECT statements.

As a concrete example, imagine I have the following:

id salary bonus
001 100 10
200 250
002 300 20
400 450
500 550
003 600 40
700 650

Salary and bonus are defined in an association.

I can do a LIST salary bonus WHEN salary < bonus

which gives me something like the following:

id salary bonus
001 200 250
002 400 450
500 550

What I actually want to do is a SELECT returning just the unique item
ids of any
item for which at least one bonus is greater than its associated
salary. Thus,
in this case I would like a select list containing two entries: 001 and
002.

Is this possible? If so, please can you let me know how - because I
have never been able to figure out a way to do it and I would really be
deeply grateful for any insight you can
give.

Thanks
Anthony
p.s. I can't switch them over to an existing reporting tool that can
handle such things - since this data ultimately needs feeding into a
BASIC subroutine.
p.p.s. The file in question has around 100 000 records, so writing a
BASIC program to do the selection would be a non-starter in terms of
performance relative to using the query language