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,

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?

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

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,
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

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.

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