Hi,
Can anyone help me how to run a SQL query with @var?


-SET &ECHO=ALL;


-****************************************
-* SET SQLMSS ENGINE TO RUN SQL QUERIES
-* THE RESULTS IN BE ON HOLD: HDSR
-****************************************

ENGINE SQLMSS SET DEFAULT_CONNECTION VESIR21815
SQL SQLMSS PREPARE HDSR FOR
-*****************
-* QUERY DSR
-*****************


DECLARE @LAST_MONTH VARCHAR(6)
SET @LAST_MONTH = (SELECT TOP 1 LEFT(CONVERT(VARCHAR(8),RENAVAM.SRSD28_RENAVAM_Y,112),6) AS 'YEARMONTH'
FROM MSRSD28_RENAVAM RENAVAM WHERE RENAVAM.SRSD28_RENAVAM_Y >= '2015-04-01' AND RENAVAM.SRSD28_RENAVAM_Y <= '2015-05-05'
ORDER BY LEFT(CONVERT(VARCHAR(8),RENAVAM.SRSD28_RENAVAM_Y,112),6))

END


TABLE FILE HDSR
PRINT *
END

Error:
0 ERROR AT OR NEAR LINE 26 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HDSR
BYPASSING TO END OF COMMAND


I am able to run it as:
ENGINE SQLMSS SET DEFAULT_CONNECTION VESIR21815
SQL SQLMSS PREPARE HDSR FOR
-*****************
-* QUERY DSR
-*****************


SELECT TOP 1 LEFT(CONVERT(VARCHAR(8),RENAVAM.SRSD28_RENAVAM_Y,112),6) AS 'YEARMONTH'
FROM MSRSD28_RENAVAM RENAVAM WHERE RENAVAM.SRSD28_RENAVAM_Y >= '2015-04-01' AND RENAVAM.SRSD28_RENAVAM_Y <= '2015-05-05'
ORDER BY LEFT(CONVERT(VARCHAR(8),RENAVAM.SRSD28_RENAVAM_Y,112),6)

END

This message has been edited. Last edited by: Ricardo Augusto,

Not an expert, but could you do something like this instead??

 
-SET &ECHO=ALL;

-SET &LAST_MONTH = 'SELECT TOP 1 LEFT(CONVERT(VARCHAR(8),RENAVAM.SRSD28_RENAVAM_Y,112),6) AS "YEARMONTH" FROM MSRSD28_RENAVAM RENAVAM WHERE RENAVAM.SRSD28_RENAVAM_Y >= ''2015-04-01'' AND RENAVAM.SRSD28_RENAVAM_Y <= ''2015-05-05'' ORDER BY LEFT(CONVERT(VARCHAR(8),RENAVAM.SRSD28_RENAVAM_Y,112),6)';

-****************************************
-* SET SQLMSS ENGINE TO RUN SQL QUERIES
-* THE RESULTS IN BE ON HOLD: HDSR
-****************************************

ENGINE SQLMSS SET DEFAULT_CONNECTION VESIR21815
SQL SQLMSS PREPARE HDSR FOR
-*****************
-* QUERY DSR
-*****************


&LAST_MONTH.EVAL
;
END


TABLE FILE HDSR
PRINT *
END

Not sure about the double quotes around the YEARMONTH column name. We use Teradata, not MSSQL, but it works there.

Patrick

It worked Patrick. Thanks!


I also got it creating a Stored Procedure then accessing it from WebFOCUS.

See ya.