TABLE 1: STUDENT GROUP PHONE_NO A 1 333-4567 B 2 333-4578 C 1 333-4589
TABLE 2: STUDENT GROUP MOBILE_NO A 1 999-8888 B 2 999-6541 C 1 999-7777
using FOCUS I'd like to show the following in the report:
STUDENT MOBILE A 999-8888 B 999-7777
I wrote down :
SET SQLENGINE=SQLORA SQL PREPARE FOR TEMPTABLE SELECT A.STUDENT, DECODE(A.GROUP,1,B.MOBILE_NO,A.PHONE_NO) FROM TABLE1 A, TABLE2 B WHERE A.STUDENT=B.STUDENT AND A.GROUP=B.GROUP END -RUN
TABLE FILE TEMPTABLE PRINT * END -RUN
It didn't print anything in the report. Am I missing anything or is it the right way to do it?
Please help ASAP,
new focuser
March 22, 2004, 07:27 AM
<Grzegorz>
If you would like to use prepared SQL statement you can use the following syntax (I changed GROUP column name to GROUP_ID because my Oracle RDBMS does not like GROUP as a column name):
SET SQLENGINE=SQLORA -* SQL PREPARE STUDENT_QUERY FROM SELECT A.STUDENT, DECODE(A.GROUP_ID,1,B.MOBILE_NO,A.PHONE_NO) MOBILE FROM TABLE1 A, TABLE2 B WHERE A.STUDENT=B.STUDENT AND A.GROUP_ID=B.GROUP_ID END -* SQL EXECUTE STUDENT_QUERY; TABLE ON TABLE HOLD END -RUN TABLE FILE HOLD PRINT * END If you do not want to use prepared statement, there is even simpler syntax:
SQL SQLORA SELECT A.STUDENT, DECODE(A.GROUP_ID,1,B.MOBILE_NO,A.PHONE_NO) MOBILE FROM TABLE1 A, TABLE2 B WHERE A.STUDENT=B.STUDENT AND A.GROUP_ID=B.GROUP_ID; END or
SQL SQLORA SELECT A.STUDENT, DECODE(A.GROUP_ID,1,B.MOBILE_NO,A.PHONE_NO) MOBILE FROM TABLE1 A, TABLE2 B WHERE A.STUDENT=B.STUDENT AND A.GROUP_ID=B.GROUP_ID; TABLE ON TABLE HOLD END TABLE FILE HOLD -* Use the FOCUS syntax, here: -* ... ENDThe "FOCUS-SQL" syntax is described and explained within the manual: "IWay SQL Reference Version 5 Release 2.0".
Hope this helps GrzegorzThis message has been edited. Last edited by: <Mabel>,
March 22, 2004, 11:08 AM
<beginner>
Thank you very much for you help. I have further questions. What if I want the following:
STUDENT GROUP NUMBER A 1 999-8888 B 2 333-4578 C 1 999-7777
note: if group = 1, show mobile number if group = 2, shoue phone number
Please help me
March 22, 2004, 12:44 PM
<Grzegorz>
The following query is just doing it (using Oracle syntax):
SQL SQLORA SELECT A.STUDENT, A.GROUP_ID, DECODE(A.GROUP_ID, 1, B.MOBILE_NO, 2, A.PHONE_NO) PH_NUMBER FROM TABLE1 A, TABLE2 B WHERE A.STUDENT=B.STUDENT AND A.GROUP_ID=B.GROUP_ID; END The equivalent syntax in FOCUS is (at least one of the possibilities):
SQL SQLORA SELECT A.STUDENT, A.GROUP_ID, B.MOBILE_NO, A.PHONE_NO FROM TABLE1 A, TABLE2 B WHERE A.STUDENT=B.STUDENT AND A.GROUP_ID=B.GROUP_ID; TABLE ON TABLE HOLD AS STUDENTS END -* DEFINE FILE STUDENTS NUMBER/A9 = IF GROUP_ID EQ '1' THEN MOBILE_NO ELSE PHONE_NO; END -* TABLE FILE STUDENTS PRINT NUMBER BY STUDENT BY GROUP_ID AS 'GROUP' END You can, of course, create WF synonym with joined TABLE1 and TABLE2, and the Oracle query will be generated "in the background".
Hope this helps GrzegorzThis message has been edited. Last edited by: <Mabel>,