Cursors and Where clauses

[Darren Reely]

Glenn wrote:
> A few Questions! ==
> 
>    1. A declare cusor statement supports how many characters? I think it
>       may be 255; given compilation errors. We have since trimmied
>       variable names to achieve ~230 with Ok compilations but reduced
>       "parms" as passed from a calling screen.

If your thinking the cursor is limited to a size of 255 bytes, that is 
incorrect. I have one that is 74 lines, 283 words, and about 2849 
characters.


>    2. Still needing more; "Where" clause content; from the passed
>       variables eg data ranges, invoice ranges etc -  lot's of "or t_var
>       = blank"; I have used the Access statement on the record cursor.
>       The documentation suggests that this supplements any pre-existing
>       where clauses {as above} ie "and". It seems to work.

temp t_passed_in_var varchar*10
...
	where column = TRUNC(:t_passed_in_var)


>    3. As I still need to add more selects/where clauses to the new SQL;
>       but before trying; some assistance with respect to ::where_and etc
>       substitution would be great! The documentation reads well, but I
>       am not positive as to where to create the substitution strings - a
>       define, a temporary - neither seem to work - yet.

Place text such as ::and_where_clause into the cursor following your 
last where entry such as this. Note, you do not make this a temp or a 
define.

    where ....
    and lot_status not in ('OPEN') &
    ::and_where_clause &

Then your open cursor text should look something like this;

    SQL OPEN BINS_RLSD_LOTS_V &
      and_where_clause ( AND bin_id = :trunc(t_push_bin_id) )

You can do the same thing with order by also.


>    4. Assistance with the SET LIST SQL clause would also be handy. With
>       Axiant, you can set this statement for Quick screens within the
>       active "Build Profile" - but where does it go? I would like to see
>       the resulting SQL as passed to the server.

The SET LIST SQL goes at the beginning of your source file. This allows 
you to see how Powerhouse is going to translate your cursor. Perhaps Joe 
answered you question with a better result.


>    5. And Lastly:! A previous select clause used onb the Hp3000 using
>       PH8 select syntax was : "and if 0> index(Passed_Variable, column)
>       i.e. is the Column value in the passed_variable? so to speak. A
>       pointer at specific SQL syntax would be handy! The Passed_Variable
>       contains a list of Customer_Codes, joined as {abcd+your+comp}, a
>       Customer_Code will always be a qualified "abcd", via lookup noton
>       stuff on the calling form. 

Try; AND 0 < POSITION('STR' IN 'TEST STRING')

I found that at 
http://developer.mimer.com/documentation/html_91/Mimer_SQL_Engine_DocSet/functions4.html#1113964

The thing to remember is Powerhouse supports ANSI standard SQL. 92 I 
think. So vendor specific functions such as the DECODE() statement in 
Oracle are not allowed. Does anyone know of a replacement for DECODE()?

Darren