Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] How to do UNION using just MRE
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] How to do UNION using just MRE
 Login/Join
 
Member
posted
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, 2012Reply With QuoteReport This Post
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, 2004Reply With QuoteReport This Post
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, 2012Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2012Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] How to do UNION using just MRE

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.