11 Jan

Creating a UNION statement in Cyberquery

I’m going to venture into something that is a small bit of risk, only because I no longer have access to Cyberquery documentation and that is how to perform a UNION using Cyberquery.  The reason for this is that, well, unless it’s been added, there is no such thing in Cyberquery.

In case you don’t know a UNION done to combine the results of two queries.  For instance, you had monthly historical sales data that you had broken off from the main table and now you needed to combine 2005 and 2006 data for some reason.  In MySQL the syntax for a join is the following:

<pre>
mysql> SELECT 1, 2, 3 UNION SELECT 1, 2, 3;
+—+—+—+
| 1 | 2 | 3 |
+—+—+—+
| 1 | 2 | 3 |
+—+—+—+
1 row in set (0.00 sec)

mysql> SELECT 1, 2, 3 UNION ALL SELECT 1, 2, 3;
+—+—+—+
| 1 | 2 | 3 |
+—+—+—+
| 1 | 2 | 3 |
| 1 | 2 | 3 |
+—+—+—+
2 rows in set (0.00 sec)
</pre>

The first example uses the default syntax (you can also use the keyword DISTINCT after the UNION keyword) to produce the same result.  The ALL keyword combines all of the results and omitting it, or using the DISTINCT keyword, returns only unique results.  Also, to confuse the issue a tiny bit, Oracle has additional keywords like INTERSECT, MINUS and you can even do a MERGE instead of a UNION.

So, back to Cyberquery and creating a UNION in that environment.  Here is how I would go about creating the same queries in Cyberquery.  This example is based on a UNIX environment.  If you using it on a different platform the concepts will be the same but the commands will be different.

I would write 2 Cyberqueries:

var1 = 1;
var2 = 2;
var3 = 3;

List/hold=”query1″
var1
var2
var3

var1 = 1;
var2 = 2;
var3 = 3;

List/hold=”query2″
var1
var2
var3

This will create 2 files called query1.hf and query2.hf in, well, wherever your environment puts them.

To produce a UNION ALL from the 2 hold files.

mv query1.hf query1.bak
cat query2.hf >> query1.hf

This simply appends the data from query2 to query1 and any Cyberquery written against query1 would contain both results.

To produce a UNION DISTINCT.

mv query1.hf query1.bak
cat query2.hf >> query1.hf
uniq query1.hf > query3.hf
mv query3.hf query1.hf

In this case you are appending the duplicate data to the first file, then using the uniq command to create a new file with unique data in it, and then overwriting the first file with the uniq file.  Assuming the permissions are set correctly you shouldn’t have any problems doing this.  Oh, and the first mv command creates a backup in case you screw something up.

And, in case you are wondering, all a hold file is in the Cyberquery environment is a flat file with a definition file (hfd).  The definition file provides variable type and positional information so that a Cyberquery can read it and know what the data at column 7 means.

One last point.  Do not do this with different file layouts because the .hfd file won’t understand the positioning and you’ll get something akin to garbage when you try to run the Cyberquery.  The line lengths, and the data in each line, needs to be identical as far as positioning and type are concerned.  It will allow you to write the query, probably, based on the .hfd file but the results will be, at best, interesting.

Hope this helps the few remaining Cyberquery users out there.

One thought on “Creating a UNION statement in Cyberquery

  1. Hi,
    This is really good. It is so difficlut to find anything on CyberQuery
    I have an issue though, the cat command is overwriting query 3 only has query2 data.
    What could be causing this? i really that thispost is really old . but any help is appreciated.
    regards,
    Rinsy

Leave a Reply

Your email address will not be published. Required fields are marked *