Hi All,
I'm using the SQL passthrough and I want the following SELECT in a single request.

SELECT
SUM(P_LOSS)
FROM TABLE1
WHERE (DATE_1 >= 200701) AND (DATE_1 <=200712)
-***********************
SELECT
SUM( EXP)
FROM TABLE1
WHERE (DATE_1 <=200712).
-****************************
I want to achieve the above in a single SELECT as to avoid the HOLD files.

You're asking a SQL question.

Have you looked into the UNION keyword?

SQL 
SELECT COUNTRY, MODEL FROM CAR WHERE COUNTRY = 'FRANCE'
UNION
SELECT COUNTRY, MODEL FROM CAR WHERE COUNTRY = 'ENGLAND'
END

I thought I might have a little fun with your code Francis. This is the focus code that is generated from you sql union. Some things just seem to become more complex than they need to be sometimes.

****************************************************
 ***   SQLTRANS trace level 1 - Generated DML     ***
 ****************************************************
 SET COUNTWIDTH=ON
 -SET SQLERRNUM = 0;
 TABLE FILE CAR.COUNTRY
 WRITE
 MIN.COUNTRY
 MIN.MODEL
 BY COUNTRY NOPRINT  BY MODEL NOPRINT
 WHERE ( COUNTRY EQ 'FRANCE' ) ;
 ON TABLE SET CARTESIAN ON
 ON TABLE SET ASNAMES ON
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS SQLFIL01
 END
 -RUN
 -IF &SQLERRNUM GT 0 GOTO SQLEXT01;
 -SET SQLERRNUM = &FOCERRNUM;
 -IF &RETCODE   GT 1 GOTO SQLEXT01;
 -IF &RETCODE   LT 0 GOTO SQLEXT01;
 -IF &FOCERRNUM GT 0 GOTO SQLEXT01;
 TABLE FILE CAR.COUNTRY
 WRITE
 MIN.COUNTRY
 MIN.MODEL
 BY COUNTRY NOPRINT  BY MODEL NOPRINT
 WHERE ( COUNTRY EQ 'ENGLAND' ) ;
 ON TABLE SET CARTESIAN ON
 ON TABLE SET ASNAMES ON
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS SQLFIL02
 END
 -RUN
 -IF &SQLERRNUM GT 0 GOTO SQLEXT01;
 -SET SQLERRNUM = &FOCERRNUM;
 -IF &RETCODE   GT 1 GOTO SQLEXT01;
 -IF &RETCODE   LT 0 GOTO SQLEXT01;
 -IF &FOCERRNUM GT 0 GOTO SQLEXT01;
 MATCH FILE SQLFIL01
 BY E01 BY E02
 RUN
 FILE SQLFIL02
 BY E01 BY E02
 AFTER MATCH HOLD AS SQLFIL03 OLD-OR-NEW
 END
 TABLE FILE SQLFIL03
 PRINT  E01 E02
 END
  

Gizmo, that's incredible.

By the way, please remind me how you get the FOCUS code for that SQL statement?

I imagine it's a TRACE command.

Thanks,

msam

If you need the information as a single record, you could try the following.

SQL enginename
SELECT P_LOSS, EXP
FROM
  (SELECT SUM(P_LOSS) 'P_LOSS'
     FROM TABLE1
     WHERE (DATE_1 >= 200701)
       AND (DATE_1 <= 200712) A,
  (SELECT SUM(EXP) 'EXP'
     FROM TABLE1
     WHERE DATE_1 <= 200712) B;
END
  

Francis,

To get the SQL to FOCUS translation, use

SET TRACEON=SQLTRANS/1/CLIENT

Thanks.

if you are sure that the rows won't be duplicated between your statements, UNION ALL will provide better performance for larger result sets.

thanks all.
It's working fine now and sorry for putting the SQL question in WebFOCUS.

Regards,
MSAM