I have a question on how to do a union in Webfocus. I need to union F4211 and F42119 (limited to one SO# or group of SO#'s). I don't know how to do a union in Webfocus language, but I do in SQL. But after I do this union I also need to do a join with F4801, so I really need to save to a hold file. Can you save to a hold file when using SQL? If not, can anyone explain how to do a union without resorting to SQL?

This is what I have so far.

SQL
(Select SDDOCO, SDPQOR,
SDUOPN, SDSOQS, SDQTYT, SDLTTR_CODE, SDNXTR_CODE, SDSHAN,
SDAN8, SDADDJ, SDRORN, SDRCTO_CODE, SDUORG, SDSOQS
FROM F4211
WHERE SDDOCO = &so)
UNION
(Select SDDOCO, SDPQOR,
SDUOPN, SDSOQS, SDQTYT, SDLTTR_CODE, SDNXTR_CODE, SDSHAN,
SDAN8, SDADDJ, SDRORN, SDRCTO_CODE, SDUORG, SDSOQS
FROM F42119
WHERE SDDOCO = &so);

END

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

Hi,

You can use a MATCH FILE with OLD-OR-NEW option in WebFOCUS to get the effect of a Union.
If you want to save the output of the SQL you wrote run a query against SQLOUT file and hold the data to a file...

TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD AS HLD1 FORMAT ALPHA
END

Then you can do a JOIN on this hld1 file.

thanks
Sashanka

Insert the following line after the semicolon:

TABLE ON TABLE HOLD AS HOLDFILENAME

Since it appears the exact same columns are being extracted from both tables, you can use the MORE command to concatenate extracts from the two tables:

TABLE FILE F4211
 PRINT SDDOCO SDPQOR SDUOPN SDSOQS SDQTYT SDLTTR_CODE SDNXTR_CODE
       SDSHAN SDAN8 SDADDJ SDRORN SDRCTO_CODE SDUORG SDSOQS
 WHERE SDDOCO EQ &so ;
 ON TABLE HOLD
MORE
FILE F42119
END

Would this work for you?

TABLE FILE F4211
PRINT
      SDDOCO
      SDPQOR
      SDUOPN
      SDSOQS
      SDQTYT
      SDLTTR_CODE
      SDNXTR_CODE
      SDSHAN
      SDAN8
      SDADDJ
      SDRORN
      SDRCTO_CODE
      SDUORG
      SDSOQS
WHERE SDDOCO EQ &so 
MORE
FILE F42119
MORE
FILE F4801
END  

Thank you very much. The MORE syntax works. But now I need to hold that so I can convert the SDRORN from text to a number to join with F4801.

I try this and get an error only when I add the ON TABLE HOLD.

TABLE FILE F4211
BY SDUOPN BY SDSOQS BY SDQTYT BY SDLTTR_CODE BY SDNXTR_CODE
BY SDSHAN BY SDAN8 BY SDADDJ BY SDRORN BY SDRCTO_CODE
BY SDUORG BY SDSOQS
WHERE SDDOCO EQ 124847
MORE
FILE F42119
WHERE SDDOCO EQ 124847
ON TABLE HOLD AS PART1
END

The error I get is "0 ERROR AT OR NEAR LINE 29 IN PROCEDURE _ADHOCRQFOCEXEC *
(FOC953) INVALID STRUCTURE IN SUBREQUEST: ON
BYPASSING TO END OF COMMAND"

ON TABLE HOLD must be before the first MORE command.