[CLOSED] How to do UNION using just MRE

Hi All - Newbie here. I am an experienced SQL developer but brand new to WebFOCUS. My company is using version 7.6.11 (old version I know but I'm hoping what I'm trying to do is simple). At this time I do NOT have access to Developer Studio. I would like to create a simple UNION between 2 tables (files).
Simple example:
SQL SELECT Plant, Lineprice
FROM ORDNORTH
UNION ALL
SELECT Plant, Lineprice
FROM ORDSOUTH
But the Managed Reporting tool I am using is confusing to me (understanding I have had only limited training in this tool). I know that I need to create a "Join" Reporting Object. (Correct?) But what SYNTAX do I use for the SQL within this Join item? I'm assuming I don't use normal SQL but some special syntax?
Thanks much,
Rex

This message has been edited. Last edited by: Kerry,

WebFOCUS 7.6
Windows, All Outputs

Posts: 3 | Registered: March 08, 2012

Mary Watermann

Gold member

posted

Hide Post

Rex,

WebFOCUS has what they call "SQL Passthru" method.

This allows you to pass your SQL code and then read the output using WebFOCUS (TABLE FILE SQLOUT).

engine_name = SQLORA or DB2

ENGINE engine_name SET DEFAULT_CONNECTION connection_name
SQL engine_name PREPARE SQLOUT FOR

SELECT Plant, Lineprice
FROM ORDNORTH
UNION ALL
SELECT Plant, Lineprice
FROM ORDSOUTH
END

TABLEF FILE SQLOUT
PRINT *
ON TABLE HOLD AS TEST1
END

Is this what you are needing?

WF 7.6.10, Windows, PDF, Excel

Posts: 75 | Location: Dallas, TX | Registered: February 12, 2004

Ignored post by Mary Watermann posted

Show Post

RexB

Member

posted

Hide Post

Mary - That may be what I need. Here are my follow-up questions:

1. Is your code the actual text/code that I put in the Join/Edit dialog box (with my own custom modifications of course)?

2. For the "engine_name": Are "SQLORA" and "DB2" the only possibilities? And I would use one or the other? I will need to talk to my administrator to determine which we have, but I wanted to know all of the possibilities.

3. Is there any way for me to figure out what the connection name is on my own?

Thanks very much, Mary,
Rex

WebFOCUS 7.6
Windows, All Outputs

Posts: 3 | Registered: March 08, 2012

Ignored post by RexB posted

Show Post

rfbowley

Platinum Member

posted

Hide Post

I would suggest further research in the tech manuals under "Universal Concatenation".

Basically there are two ways I know of to achieve the same effect as a SQL UNION. In both cases, the meta data for the fields in the two(or more) tables must be EXACTLY the same. Use temporary hold files to select only those fields needed to reduce process time.

The primary one in the documentation is the MORE construct

 
TABLE FILE ORDNORTH
SUM PLANT
    LINEPRICE
MORE
FILE ORDSOUTH

END

The second posibility is a USE construct:

 
USE
  ORDNORTH AS ORDNORTH
  ORDSOUTH AS ORDNORTH
END

TABLE FILE ORDNORTH
SUM PLANT
    LINEPRICE
END

What you are trying to do does not involve a JOIN in any way. The way I learned it was JOINs add columns to a table, MORE / USE add rows, just the same as a UNION in SQL adds rows.

Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001

Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005

Ignored post by rfbowley posted

Show Post

RexB

Member

posted

Hide Post

Robert,
That is very helpful - and I understand your point that a UNION is not a Join. The reason I am using that term is that even though it is technically not a Join, it appears that the only way to enter this into the MRE is through the Join/Edit dialog box. Can you or anyone confirm whether this is true?
Question #2: I did not share the following additional information originally, as I did not want to further complicate my question, but my 2 tables (files) are NOT an exact match in column layout. Specifically, File1 has an A_Amount column and File2 has a B_Amount field. I need to simply add the amounts in both files(tables) - that is why I need a Union. ... So I imagine I will need to create a Define for each of the 2 fields and have the AS result column name be the same in the 2 files/tables. So my next question is: Robert, how would I change the code in order to accommodate simply using this one column in my output (understanding that the 2 files/tables have different structures)?
Thanks very much!
Rex

WebFOCUS 7.6
Windows, All Outputs

Posts: 3 | Registered: March 08, 2012

Ignored post by RexB posted

Show Post

rfbowley

Platinum Member

posted

Hide Post

Well, this additional info makes the task a bit easier actually.

DEFINE FILE CAR
   DLR_VALUE/D12.2 = DEALER_COST;
END

TABLE FILE CAR
SUM DLR_VALUE
BY COUNTRY
ON TABLE HOLD AS filename1 FORMAT FOCUS INDEX COUNTRY
END
-RUN

TABLE FILE CAR
SUM DLR_VALUE
BY COUNTRY
ON TABLE HOLD AS filename2 FORMAT FOCUS INDEX COUNTRY
END
-RUN

TABLE FILE filename1
SUM DLR_VALUE
BY COUNTRY
RUN
MORE
FILE filename2
END
-RUN

When you look up Universal Concatenation in the help files / tech documentatiosn, note how you can influence the output by the various combinations of PRINT and SUM while you are at it.

Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001

Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005

Ignored post by rfbowley posted

Show Post

Waz

Expert

posted

Hide Post

The other way is to create a bucket and keep adding to it.

TABLE FILE ORDNORTH
PRINT Plant  Lineprice
ON TABLE HOLD AS TMP_UNION FORMAT ALPHA
END
FILEDEF TMP_UNION DISK tmp_union.ftm (APPEND
TABLE FILE ORDSOUTH
PRINT Plant  Lineprice
ON TABLE SAVE AS TMP_UNION 
END
FILEDEF TMP_UNION DISK tmp_union.ftm

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006

Ignored post by Waz posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

[CLOSED] How to do UNION using just MRE

Focal Point | Privacy Statement | Terms of Service