IDS Forum

RE: Can I do this?

Posted By: Kennedy, Randy
Date: Tuesday, 3 May 2011, at 4:57 p.m.

In Response To: Re: Can I do this? (Jonathan Leffler)

I apologize if my question isn't clear. I will try a different
explanation.

I have several functions that are select queries from tables/views with
additional logic paths in them, ultimately returning hundreds or
thousands of rows. All of these functions are date ranged so they take
2 dates as input parameters.
These 'detail' functions work great and I use them for many things,
mostly in automated monthly scripting.

I now need to create 'summary' functions for the rows that are returned
from the 'detail' functions. The easiest way to normally create a
summary query is to do aggregates within my select query on a table/view
and use group by. Unfortunately, these detail functions are more than
just a simple query, which is why they are functions. This would allow
me to know that the summary is always based on the exact same result as
the detail and if I need to change any logic, I only need to update the
detail function(s).

I found info on using the TABLE() function so I can do a select from
function just as I would from a table/view, so I thought I had an easy
solution as it worked fine when I tested using specific dates in the
function in the FROM.

However, when I wrap that aggregate query into a function so it can
accept the dates as input parameters (and be called from automated
scripting), it fails with the -217 errors.

I hope this makes sense, if not, please let me know and I will elaborate
anyway I can.

Thanks,
Randy

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Jonathan Leffler
Sent: Tuesday, May 03, 2011 12:10 PM
To: ids@iiug.org
Subject: Re: Can I do this? [23592]

On Tue, May 3, 2011 at 09:27, Kennedy, Randy
<RKennedy@scottsdaleaz.gov>wrote:

> I haven't seen any responses:(, is this type of SPL possible?
>

I looked briefly at the original question when you sent it, found it
hard to
make head or tail of, and filed it (leaving it for someone else to
answer -
clearly, someone else is on vacation at the moment, or otherwise
distracted).

I've looked marginally less briefly at this repeat of the question, and
I
still find it hard to understand what is being asked.

> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Kennedy, Randy
> Sent: Monday, May 02, 2011 9:39 AM
> To: ids@iiug.org
> Subject: Can I do this? [23573]
>
> OS: RHEL 5
> IDS: 11.1FC3
>
> I have several SPL functions that return detail info (multi row) using
2
>
> dates as input parameters. I would like to use the already created
> routines in summary SPL functions using the TABLE function for running

> the detail routine. This works fine when I specify the dates running
it
> as just a simple select statement, but when I put it into a foreach
loop
>
> within the SPL, I get a -217 error about the input parameters not
being
> part of the query.
>
> SELECT
> unnamed_col_3,unnamed_col_7,count(unnamed_col_2),sum(unnamed_col_33),
> sum(unnamed_col_21),sum(unnamed_col_22), sum(unnamed_col_23),
> sum(unnamed_col_24),sum(unnamed_col_25),sum(unnamed_col_26),
>
sum(unnamed_col_27),sum(unnamed_col_28),sum(unnamed_col_30),sum(unnamed_

>
> col_31)
> FROM TABLE( FUNCTION Filings('1/1/2011','1/31/2011'))
> GROUP BY 1,2
>
> That works, but
>
> Create function FilingsSummary(date as p_StartDate,date as
p_EndDate)...
>

This syntax isn't valid SPL.

Did you mean:

CREATE FUNCTION FilingsSummary(p_StartDate DATE, p_EndDate DATE)

RETURNING DATE AS r_xyz, CHAR(20) AS r_pqr, ...

(And yes, that is extremely weird syntax.)

> ....
> ....
> ....
> FOREACH
>
> SELECT
> unnamed_col_3,unnamed_col_7,count(unnamed_col_2),sum(unnamed_col_33),
> sum(unnamed_col_21),sum(unnamed_col_22), sum(unnamed_col_23),
> sum(unnamed_col_24),sum(unnamed_col_25),sum(unnamed_col_26),
>
sum(unnamed_col_27),sum(unnamed_col_28),sum(unnamed_col_30),sum(unnamed_

>
> col_31)
> INTO variables...
> FROM TABLE( FUNCTION Filings(p_StartDate,p_EndDate))
> GROUP BY 1,2
> ....
> .... function processing
> ....
> Doesn't work.
>
>
If the parameters are declared correctly but do not get passed to the
function correctly, then you may have a bug. You should be able to
demonstrate that with a relatively simple piece of SPL - two functions,
the
'inner' one generating data from (say) the orders table in the stores
database and the outer summarizing by customer, or something like that,
or
it could use your own simple schema. If the problem doesn't reproduce in

the small scale but does on the large scale, that may be a different
sort of
bug.

> Is it possible to make something like this work? I have many detail
> functions that I would like to create summary functions around without

> having to maintain the main queries twice. The detail functions are
more
> than simple queries so I would have to put all the post select
> processing into the summary functions as well and then remember to
> update both if anything changes in the logic.
>

It is most certainly sensible to reuse functions rather than have two
lots
of 'the same' code to maintain.

AFAIK, it should work. If it does not work, we need to find out why not.

--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to
be
amused."

--20cf303dd3583707e404a263e44a

************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Can I do this?
  Kennedy, Randy -- Monday, 2 May 2011, at 12:38 p.m.
  • RE: Can I do this?
    Kennedy, Randy -- Tuesday, 3 May 2011, at 12:27 p.m.
    • Re: Can I do this?
      Jonathan Leffler -- Tuesday, 3 May 2011, at 3:10 p.m.
      • RE: Can I do this?
        Kennedy, Randy -- Tuesday, 3 May 2011, at 4:57 p.m.
      • Re: Can I do this?
        Art Kagel -- Tuesday, 3 May 2011, at 9:19 p.m.
        • RE: Can I do this?
          Kennedy, Randy -- Wednesday, 4 May 2011, at 11:40 a.m.
    • Re: Can I do this?
      Art Kagel -- Tuesday, 3 May 2011, at 8:53 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.