[SLOVED]how to use &LINES ?

  

ENGINE SQLMSS SET DEFAULT_CONNECTION CON01
SQL SQLMSS PREPARE SQLOUT FOR
SELECT * from table where field_flag='Y1';
END
-RUN
-IF &LINES EQ 0 GOTO MSG;

TABLE FILE SQLOUT
PRINT *
END
-RUN

-*-SET &LINES=0;
SQL SQLMSS PREPARE SQLOUT FOR
SELECT * from table where field_flag='Y2';
END
-RUN
-IF &LINES  GOTO MSG;

TABLE FILE SQLOUT
PRINT *
END
-RUN


-MSG

if the 1st select result count > 1 and the 2nd select result count =0,
the top code(the 2nd &LINES always >1 ) ,if I SET &LINES=0 , the 2nd &LINES always =0 ,
how can i get the &LINES well?

Thanks,
James

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

WebFOCUS 7.6.8
WebFOCUS 7.6.10
BI
FLEX
.NET

Posts: 97 | Registered: August 18, 2009

Waz

Expert

posted

Hide Post

My guess is that &LINES is not being set if there are 0 records returned from the second SELECT.

Whats also interesting is that I always get 0, and only get &LINES set if I don'y use PREPARE SQLOUT FOR.

-*new file
-SET &ECHO=ALL;

SQL SQLMSS
SELECT * FROM sys.all_objects
WHERE name LIKE 'all%' ;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TMP_OUT
END
-RUN
-TYPE 1st LINES: &LINES

-RUN
-IF &LINES EQ 0 GOTO MSG;

-*TABLE FILE TMP_OUT
-*PRINT *
-*END
-*-RUN

SQL SQLMSS PREPARE SQLOUT FOR
SELECT * FROM sys.all_objects
WHERE name LIKE 'ALL%' ;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TMP_OUT2
END
-RUN
-TYPE 2nd LINES: &LINES
-IF &LINES GT 0 GOTO MSG;

-*TABLE FILE SQLOUT
-*PRINT *
-*END
-*-RUN

-MSG

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006

Ignored post by Waz posted

Show Post

Yangyang

Gold member

posted

Hide Post

thanks Waz,
I try to do this , It's work

you mean put the -IF &LINES GT 0 GOTO MSG;

before the below code ?

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TMP_OUT2
END
-RUN

Thanks,
James

WebFOCUS 7.6.8
WebFOCUS 7.6.10
BI
FLEX
.NET

Posts: 97 | Registered: August 18, 2009

Ignored post by Yangyang posted

Show Post

Waz

Expert

posted

Hide Post

quote:
you mean put the -IF &LINES GT 0 GOTO MSG

Its jsut left over from my initial testing.

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!