From: sfc@tpghq.com (Steve Caswell)
Subject: Re: Dynamic Sql and Ingres Report Writer
Organization: Palmer & Associates, Inc., Norcross, GA
Date: Wed, 16 Dec 1992 16:18:13 GMT

In article <1992Dec15.140332.2098@praxis.co.uk> bill@praxis.co.uk (William Walker) writes:
>
>In article <1992Dec10.204230.5570@brtph560.bnr.ca> you write:
>>
>>I would like to know if it is possible to have dynamic SQL statements
>>in a report specification.
>>
>>There are large number of tables and columns, and I would like to 
>>be able to prepare the select statements. But is it possible to 
>>mix dynamic sql and report writer? If so, how do you pass the select
>>string to the report function?
>
>Yes, it is possible to use dynamic sql in Ingres report writer although
>I have never seen it documented.

I'm not sure why you have never seen it documented.
This feature has been around since day one and has been well documented.  The
latest Report Writer Referencnce Manual I have (6.3/03 for UNIX and VMS) talks
about run-time parameters in Chapter 4 (pp 4-11 thru 4-13) and Chapter 5
(pp 5-26 thru 5-29).

Also, in the stricted sense, this is NOT dynamic SQL.  This is a dynamic feature
of the INGRES Report Writer tool but this is VERY different from traditional
dynamic SQL.

>
>You can use input variables as part of the from and where clause eg:
>
>	select q,w,e
>	from a, b, $input1
>	where x = 1 and y = 2 and $input2
>
>
> input1 is simply the name of a table(s) and 
>
> input2 could be any part of a normal where clause eg: 
>	"z = 3 and col2 is not null"
>
>I have NEVER tried using variables for the select clause but I would
>expect it to work in the same way.
>

Variables work very well for pieces of the select, such as the table name,
column names, even the where clause and order by clause.  There is one gotcha
if you use a variable for the where clause.  If you do something like

.QUERY
   SELECT x FROM TABLE
   $where_clause

expecting the user to enter the where clause, this works ok except that Report
Writer insists on forcing the entire $where_clause to lowercase (including
strings surrounded by single quotes).  So if the user enters:

where LNAME like 'S%'

Report Writer changes this to:

where lname like 's%'

which is not what you want.  To work around this problem, hard-code the WHERE
portion into the report query:

.QUERY
   SELECT x FROM TABLE
   WHERE $where_clause

so the input to $where_clause becomes

lname like 'S%'

This is not forced to lowercase.

>These variables can be entered at run-time - Ingres will prompt for
>each variable or you can pass them as arguments when you run the
>report.  
>
>When I used them, the user entered selection criteria on a screen and
>the 'from' and 'where' clauses were then built up by the code and
>passed to the report.
>
>Alternatively, you could build up the whole report in the code, write
>it to a .rw file and then run report writer against this file.
>
>I hope this is some help.
>
>
>Bill.
>
>-------------------------------------------------------------------------------
>      Bill Walker, Praxis plc, 20 Manvers Street, Bath BA1 1PX UK.
>  The software enginering division of Touche Ross Management Consultants
>Tel:	+44-225-444700.   Email:   bill@praxis.co.uk 	Fax: +44-225-465205


-- 

Steve Caswell           |   (404) 448-7727    |  "The opinions expressed are my
Principal Consultant    |   sfc@tpghq.com     |   own.  They may not be perfect,
The Palmer Group        |   uunet!tpghq!sfc   |   but they're all I've got."