Focal Point
SQL Passthrough -Single SELECT stmt
November 15, 2007, 02:58 PM
<msam>SQL Passthrough -Single SELECT stmt
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.
November 15, 2007, 03:15 PM
Francis MarianiYou'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
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
November 15, 2007, 05:11 PM
GizmoI 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
Windows: WF 7.6.2: SQL Server 2008 R2 November 15, 2007, 05:16 PM
Francis MarianiGizmo, 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,
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
November 15, 2007, 05:17 PM
Gizmomsam
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
Windows: WF 7.6.2: SQL Server 2008 R2 November 15, 2007, 05:19 PM
GizmoFrancis,
To get the SQL to FOCUS translation, use
SET TRACEON=SQLTRANS/1/CLIENT
Windows: WF 7.6.2: SQL Server 2008 R2 November 15, 2007, 05:22 PM
Francis MarianiThanks.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
November 16, 2007, 10:57 AM
Jason K.if you are sure that the rows won't be duplicated between your statements, UNION ALL will provide better performance for larger result sets.
Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
November 16, 2007, 04:00 PM
<msam>thanks all.
It's working fine now and sorry for putting the SQL question in WebFOCUS.
Regards,
MSAM