Quick Relational Query in SELECT

[Andrew Cole]

I've always wondered how to do this and I just ran across a query where it wanted to do this. Basically what I want is:

SELECT INVENTORY IF VENDOR "VEND.NUM" AND ( IF QOH GT "0" OR IF QDF GT "0" )

Both on mvBase and Jbase it fails due to the parenthesis. What (if any...) character or operator can accomplish this? I know () work in SQL and in most programming languages including BASIC.

[Mvreply]

Andrew,

Does this query work any better:

 

SELECT INVENTORY WITH VENDOR "VEND.NUM" AND WITH (QOH GT "0" OR QDF GT "0")

 

Mike

--
You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com
To unsubscribe, email to: mvdbms+un...@googlegroups.com
For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/a588c79c-022c-4df7-bc24-ce36cae1c3b2%40googlegroups.com.

[Andrew Cole]

Ahh success! Just the location of my parenthesis. Thanks Mike!

On Monday, May 13, 2019 at 12:49:11 PM UTC-4, Zumasys Reply wrote:

Andrew,

Does this query work any better:

 

SELECT INVENTORY WITH VENDOR "VEND.NUM" AND WITH (QOH GT "0" OR QDF GT "0")

 

Mike

 

From: mvd...@googlegroups.com <mvd...@googlegroups.com> On Behalf Of Andrew Cole
Sent: 13 May 2019 17:32
To: Pick and MultiValue Databases <mvd...@googlegroups.com>
Subject: [mvdbms] Quick Relational Query in SELECT

 

I've always wondered how to do this and I just ran across a query where it wanted to do this. Basically what I want is:

 

SELECT INVENTORY IF VENDOR "VEND.NUM" AND ( IF QOH GT "0" OR IF QDF GT "0" )

 

 

Both on mvBase and Jbase it fails due to the parenthesis. What (if any...) character or operator can accomplish this? I know () work in SQL and in most programming languages including BASIC.

--
You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com

To unsubscribe, email to: mvd...@googlegroups.com

For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mvd...@googlegroups.com.

[Brian Speirs]

You could also split this into two select statements:

  SELECT INVENTORY WITH VENDOR EQ "VEND.NUM"

  SELECT INVENTORY WITH QOH GT "0" OR WITH QDF GT "0"

  Now do something with the select list.

Cheers,

Brian

[Steve Trimble]

bravo Brian - keep it simple!

Computerized Data Mgmt Inc
Steve Trimble
(501) 615-8674 office

(501) 772-3450 cell / text

cdm...@gmail.com

To unsubscribe, email to: mvdbms+un...@googlegroups.com

For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/264dd176-7dda-4295-8e52-2b1600d36aba%40googlegroups.com.

[Andrew Cole]

Right and I've done that for years. But there are lots of times I'd like to do a simple one line query with () like I'm used to in SQL. I also have some applications that use Jbase to drive our ecommerce site where doing two queries aren't possible while single queries are.

[Scott Ballinger]

Hello,

D3 does not support parentheses, but you can still do it in one shot. Just remember that an "or" starts a new logical rule set, so...

select inventory with vendor eq "xxx" and with qoh gt "0" or with vendor eq "xxx" and with qdf gt "0"  (t

/Scott Ballinger

Pareto Corporation

Edmonds WA USA

[Ian Harper]

Wow that's good to know! I've been using multiple select statements to implement this kind of query.

[Tony Gravagno]

There are times when a single query simply can't cut it. So it's helpful to be aware of the tools in the kit.

With a single query we need to traverse the entire file with multiple criteria. That's great to do when we can.

With successive queries, we're not reading every record, so the pain with each query is reduced.

The groups and items may still be in memory, so the pain of re-reading may be further reduced.

It's easier to debug multiple queries with smaller sets of criteria.

With mostly static datasets it may help to save common lists. For example, with the query in question here:

select inventory with vendor eq "xxx" and with qoh gt "0" or with vendor eq "xxx" and with qdf gt "0"

(With xxx in both clauses that query can be reduced. I'll assume we want xxx and yyy).

Consider this:

get-list inventory-vendor-xxx

select inventory with qoh gt "0"

save-list iv1

get-list inventory-vendor-yyy

select inventory with qdf gt "0"

save-list iv2

compare-list iv1 + iv2 iv3

That may or may not run faster than a single query. It depends on how big the inventory file is and how many pieces we get from these vendors. If there are a million parts but we only get a few from each of these vendors, then this will fly compared to a single query that parses through the entire file.

And then ... consider indexes...

All of this depends on the application. If are working with big data, take some time to analyze common queries to determine if there is a performance issue. What is an "issue"? That also depends ... a report that runs a long time overnight may not be an "issue". A query for web clients that takes more than a couple seconds is an "issue".

The rule of thumb - there are no absolutes.

T

[Tony Gravagno]

Not the same topic but this thread reminds me of another feature that's not built-in to most (any?) MV plaforms : the ability to compare file data using two dict refs.

Examples:

  select inventory with QOH < QDF

  select orders with BILLTO.ZIP # SHIPTO.ZIP

  select customers with OPEN.AR.BAL > MAX.CREDIT

Sure, we can do that with EVAL, but explain that to a user, and reporting tools might not support the funky syntax of EVAL+FMT.

So I published a solution for this called ComparativeAQL.

https://bitbucket.org/foss4mv/nebulaware/wiki/ComparativeAQL

I thought it was kinda cool but somehow it never got traction. Meh, no surprise... :)

T