"Query too complex" workaround

  • Thread starter Thread starter Mishanya
  • Start date Start date
M

Mishanya

I'm building bonds coupon-payments cashflow. My DB has NextCouponPaymentDate
(NCPD), LastPrincipalPaymentDate (LPPD) and CouponFrequency (CF) (1,2 or 4
times a year) fields. The coupon is paid starting NCPD till LPPD annually,
semiannually or quarterly.
In order to get the job done I need to denormalize the data by querying
future coupon-payments dates for the next 10 years, which means a bond may
have 1 to 40 coupon-payments dates depending on its maturity length and
coupon frequency. Then would renormalize the dates' data with Union query.

My first query has the following fields:

1)Switch([CF]=1,
IIf([LPPD]>=DateAdd("yyyy",1,[NCPD]),DateAdd("yyyy",1,[NCPD]),Null), [CF]=2,
IIf([LPPD]>=DateAdd("m",6,[NCPD]),DateAdd("m",6,[NCPD]),Null), [CF]=4,
IIf([LPPD]>=DateAdd("m",3,[NCPD]),DateAdd("m",3,[NCPD]),Null)) AS
NextCouponPaymentDate2

2)Switch([CF]=1,
IIf([LPPD]>=DateAdd("yyyy",2,[NCPD]),DateAdd("yyyy",2,[NCPD]),Null), [CF]=2,
IIf([LPPD]>=DateAdd("m",12,[NCPD]),DateAdd("m",12,[NCPD]),Null), [CF]=4,
IIf([LPPD]>=DateAdd("m",6,[NCPD]),DateAdd("m",6,[NCPD]),Null)) AS
NextCouponPaymentDate3

and so on till NextCouponPaymentDate39 (wich, combined with the original
NCPD gives the required 40 dates).

The problem is that I get "Query too complex" error, which is avoided only
if I "circumsize" the query down to 24 expressions.

Can I make the query workaround for all the 39 expressions with SQL or maybe
using some self-customed VB-function (like CASE)?
 
Could there be another way to approach this?

Perhaps you could use a Cartesian Product query to generate a record for
each payment, starting with NextCouponPaymentDate, and using DateAdd() to
get the next payment (based on CouponFrequency and a counting table.)

There's an example of generating recurring records on the fly with a
counting table in a Cartesian Product query here:
Recurring events
at:
http://allenbrowne.com/AppRecur.html
 
Hi Allen.
Tryin' to grasp your example.
In tblEvents every EventDescrip has a single PeriodTypeID. In my case the
same event (NextCouponPaymentDate) has multiple period types
(CouponFrequency).
Do I go right direction?

Allen Browne said:
Could there be another way to approach this?

Perhaps you could use a Cartesian Product query to generate a record for
each payment, starting with NextCouponPaymentDate, and using DateAdd() to
get the next payment (based on CouponFrequency and a counting table.)

There's an example of generating recurring records on the fly with a
counting table in a Cartesian Product query here:
Recurring events
at:
http://allenbrowne.com/AppRecur.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Mishanya said:
I'm building bonds coupon-payments cashflow. My DB has
NextCouponPaymentDate
(NCPD), LastPrincipalPaymentDate (LPPD) and CouponFrequency (CF) (1,2 or 4
times a year) fields. The coupon is paid starting NCPD till LPPD annually,
semiannually or quarterly.
In order to get the job done I need to denormalize the data by querying
future coupon-payments dates for the next 10 years, which means a bond may
have 1 to 40 coupon-payments dates depending on its maturity length and
coupon frequency. Then would renormalize the dates' data with Union query.

My first query has the following fields:

1)Switch([CF]=1,
IIf([LPPD]>=DateAdd("yyyy",1,[NCPD]),DateAdd("yyyy",1,[NCPD]),Null),
[CF]=2,
IIf([LPPD]>=DateAdd("m",6,[NCPD]),DateAdd("m",6,[NCPD]),Null), [CF]=4,
IIf([LPPD]>=DateAdd("m",3,[NCPD]),DateAdd("m",3,[NCPD]),Null)) AS
NextCouponPaymentDate2

2)Switch([CF]=1,
IIf([LPPD]>=DateAdd("yyyy",2,[NCPD]),DateAdd("yyyy",2,[NCPD]),Null),
[CF]=2,
IIf([LPPD]>=DateAdd("m",12,[NCPD]),DateAdd("m",12,[NCPD]),Null), [CF]=4,
IIf([LPPD]>=DateAdd("m",6,[NCPD]),DateAdd("m",6,[NCPD]),Null)) AS
NextCouponPaymentDate3

and so on till NextCouponPaymentDate39 (wich, combined with the original
NCPD gives the required 40 dates).

The problem is that I get "Query too complex" error, which is avoided only
if I "circumsize" the query down to 24 expressions.

Can I make the query workaround for all the 39 expressions with SQL or
maybe
using some self-customed VB-function (like CASE)?
Click to expand...
Click to expand...
 
Allen good evening.
I've nailed it with Your brilliant idea of using unjoined tblCountID (with
values 0 to 39) in the query as a multiplying factor. This way no cumbersome
denormalizing with 40 expressions and then union-normalizing-back is needed.
Your solution is so unexpected (takes a lot of unusual thinking and knowing
of relational databases logic) and yet so simple!
Thanks a lot!

Misha.

Allen Browne said:
Could there be another way to approach this?

Perhaps you could use a Cartesian Product query to generate a record for
each payment, starting with NextCouponPaymentDate, and using DateAdd() to
get the next payment (based on CouponFrequency and a counting table.)

There's an example of generating recurring records on the fly with a
counting table in a Cartesian Product query here:
Recurring events
at:
http://allenbrowne.com/AppRecur.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Mishanya said:
I'm building bonds coupon-payments cashflow. My DB has
NextCouponPaymentDate
(NCPD), LastPrincipalPaymentDate (LPPD) and CouponFrequency (CF) (1,2 or 4
times a year) fields. The coupon is paid starting NCPD till LPPD annually,
semiannually or quarterly.
In order to get the job done I need to denormalize the data by querying
future coupon-payments dates for the next 10 years, which means a bond may
have 1 to 40 coupon-payments dates depending on its maturity length and
coupon frequency. Then would renormalize the dates' data with Union query.

My first query has the following fields:

1)Switch([CF]=1,
IIf([LPPD]>=DateAdd("yyyy",1,[NCPD]),DateAdd("yyyy",1,[NCPD]),Null),
[CF]=2,
IIf([LPPD]>=DateAdd("m",6,[NCPD]),DateAdd("m",6,[NCPD]),Null), [CF]=4,
IIf([LPPD]>=DateAdd("m",3,[NCPD]),DateAdd("m",3,[NCPD]),Null)) AS
NextCouponPaymentDate2

2)Switch([CF]=1,
IIf([LPPD]>=DateAdd("yyyy",2,[NCPD]),DateAdd("yyyy",2,[NCPD]),Null),
[CF]=2,
IIf([LPPD]>=DateAdd("m",12,[NCPD]),DateAdd("m",12,[NCPD]),Null), [CF]=4,
IIf([LPPD]>=DateAdd("m",6,[NCPD]),DateAdd("m",6,[NCPD]),Null)) AS
NextCouponPaymentDate3

and so on till NextCouponPaymentDate39 (wich, combined with the original
NCPD gives the required 40 dates).

The problem is that I get "Query too complex" error, which is avoided only
if I "circumsize" the query down to 24 expressions.

Can I make the query workaround for all the 39 expressions with SQL or
maybe
using some self-customed VB-function (like CASE)?
Click to expand...
Click to expand...
 
Back
Top