Conditional SQL syntax

[Kevin Witty]

I have a table which contains (roughly) a Route# for Mon, Tue, Wed, etc. and
an override Route# and date. I'd like to run a query to which I supply a
date and which says:

In this column, if the date I supply matches the override date, show the
override route#, otherwise, if there is a Route# for the weekday of the date
I supply, show the Route# for that date.

I thought this would be fairly simple, but it ain't workin'.

iif(OverRideDate = SuppliedDate,OverrideRouteNum, iif(weekday(SuppliedDate)
= 2,MondayRouteNum,iif(... etc.

only gives me a value if the first condition is True, and nothing if False.
What am I doing wrong here???

Thanks,

Kevin

 

[Bas Cost Budde]

I have a table which contains (roughly) a Route# for Mon, Tue, Wed, etc. and
an override Route# and date. I'd like to run a query to which I supply a
date and which says:

In this column, if the date I supply matches the override date, show the
override route#, otherwise, if there is a Route# for the weekday of the date
I supply, show the Route# for that date.

I thought this would be fairly simple, but it ain't workin'.

iif(OverRideDate = SuppliedDate,OverrideRouteNum, iif(weekday(SuppliedDate)
= 2,MondayRouteNum,iif(... etc.

only gives me a value if the first condition is True, and nothing if False.
What am I doing wrong here???

I have no clear picture of your data yet. Especially the (roughly) keeps
me behind the fence ;-)

My intuition tells me you can use a LEFT JOIN here, however I cannot
tell you how.

Have you got default routes for every weekday? Or, equivalently, for
every weekday your parameter date may be? In that case you can make a
query that suggests a route, taking the weekday of the parameter date to
its route. Left join this query on the override; in the output, use an
expression close to

foundRoute: iif(isnull(overridedate),routenumfromquery,overrideroutenum)

You tell me what your tables look like, I provide more details. Deal? Or
you understand perfectly what I mean and figure the rest out yourself

 

[Kevin Witty]

Sorry I was unclear. The (abbreviated) table looks like:

CustomerKey,MondayRouteNum,TuesdayRouteNum,...OverRideDate,OverrideRouteNum.

Some records have a routenum for only one day, some for more, some for none,
some have an override date. I want records which have an override date = the
one I supply, or have a routenum in the appropriate day field, and to return,
among other fields, a single field with the appropriate routenum.

The select statement is composed in VBA as

"select * from MyTable where OverrideDate = #" & SuppliedDate & "# or " &
format(SuplliedDate,"dddd") & "RouteNum is not null;"

What I'm trying to do is replace the * with a list of field names, including
a conditional column:

"iif(not isnull(OverrideRouteNum),OverrideRouteNum,iif(weekday(" &
SuppliedDate & ") = 2,MondayRouteNum,iif(weekday(" & SuppliedDate & ") =
3,TuesdayRouteNum,....))) as RouteNum"

I'm not sure how a join would come into this.

Kevin

 

[John Vinson]

Sorry I was unclear. The (abbreviated) table looks like:

CustomerKey,MondayRouteNum,TuesdayRouteNum,...OverRideDate,OverrideRouteNum.

Then your table is incorrectly structured, Kevin. You're "committing
spreadsheet" by storing data (days) in fieldnames. You actually have a
one (customer) to many (RouteNum) relationship; it should be modeled
using two tables as a one to many relationship.

Some records have a routenum for only one day, some for more, some for none,
some have an override date. I want records which have an override date = the
one I supply, or have a routenum in the appropriate day field, and to return,
among other fields, a single field with the appropriate routenum.

Then the many table needs fields for the basic route and the override
date.

The select statement is composed in VBA as

"select * from MyTable where OverrideDate = #" & SuppliedDate & "# or " &
format(SuplliedDate,"dddd") & "RouteNum is not null;"

What I'm trying to do is replace the * with a list of field names, including
a conditional column:

"iif(not isnull(OverrideRouteNum),OverrideRouteNum,iif(weekday(" &
SuppliedDate & ") = 2,MondayRouteNum,iif(weekday(" & SuppliedDate & ") =
3,TuesdayRouteNum,....))) as RouteNum"

I'm not sure how a join would come into this.

It would... if your table were correctly normalized.

John W. Vinson[MVP]

 

[Kevin Witty]

Hi, John!

Remember that old saying, "Things should be as normal as possible, but not
too normal"? This is one of those, I think It's working very nicely for
presentation and for effficiency. The only problem I'm having is making that
SQL code work. This table is the "driver", i.e., scheduler, for when orders
are released, not for the orders themselves, i.e., a customer is scheduled to
get a pickup every Monday, Wednesday and Thursday, and this table drives the
creation of the actual orders, which are normalized. I originally started by
normalizing it and decided it was more trouble than it was worth, and there
were more benefits in denormalizing it. Am I forgiven?

And if so, what's wrong with my SQL code?

Best,

Kevin

 

[Bas Cost Budde]

It's working very nicely for
presentation and for effficiency. The only problem I'm having is making that
SQL code work.

Isn't that roughly equivalent to "it's looking all right, it just
doesn't work, that's all" ?

This table is the "driver", i.e., scheduler, for when orders
are released, not for the orders themselves, i.e., a customer is scheduled to
get a pickup every Monday, Wednesday and Thursday, and this table drives the
creation of the actual orders, which are normalized.

You bet!

I originally started by
normalizing it and decided it was more trouble than it was worth, and there
were more benefits in denormalizing it. Am I forgiven?

Actually, I would like to re-think that chain with you. There is little
point in exclaiming that a table should be in that-and-that normal form,
if one doesn't know the environment. I don't want to do that anyway.

As for presentation, a crosstab query on the "normalized driver" would
give you the same "sheet" you have now. Speaking of which: I do have a
solution to edit a crosstab query on my site. It's under Code Modules.

As for efffficiency, I can't tell from a distance, of course.

 

[Bas Cost Budde]

Sorry I was unclear. The (abbreviated) table looks like:

CustomerKey,MondayRouteNum,TuesdayRouteNum,...OverRideDate,OverrideRouteNum.

Some records have a routenum for only one day, some for more, some for none,
some have an override date. I want records which have an override date = the
one I supply, or have a routenum in the appropriate day field, and to return,
among other fields, a single field with the appropriate routenum.

So basically your input is a customer number and a date, and the output
is a route number. What do you return if no route is specified?

The select statement is composed in VBA as

"select * from MyTable where OverrideDate = #" & SuppliedDate & "# or " &
format(SuplliedDate,"dddd") & "RouteNum is not null;"

What I'm trying to do is replace the * with a list of field names, including
a conditional column:

"iif(not isnull(OverrideRouteNum),OverrideRouteNum,iif(weekday(" &
SuppliedDate & ") = 2,MondayRouteNum,iif(weekday(" & SuppliedDate & ") =
3,TuesdayRouteNum,....))) as RouteNum"

Riight. I see what you are trying to do.

Would the Choose function help you out? Like Choose(weekday("&
supplieddate &"),mondayroutenum,tuesdayroutenum...

I'm not sure how a join would come into this.

It would do something on a further normalized table. At present there is
no function in a join.

Can't you just calculate the name of the column to return as you did in
the WHERE clause? You then have the outer IIf, the one that chooses
between the override and the day default, as literal; and the if-not
parameter becomes an expression outside quotes; much like

<thinks>

The point is still that you have data in your field names. There must be
a decision per record, in the query, so there is no composing
beforehand. Mmm... Do you want to negotiate on the normalization?

 

[Kevin Witty]

Thanks for the reminder about Choose... I always forget about that. Works a
treat now.
....
tDayRouteNum = Choose(Weekday(tActualDate), "SundayRouteNum",
"MondayRouteNum", _
"TuesdayRouteNum", "WednesdayRouteNum", "ThursdayRouteNum", _
"FridayRouteNum", "SaturdayRouteNum")
....
tSql = "select ....
& "IIf(tStandingOrders.RoutingSchedule ='Weekly',
tStandingOrders." & tDayRouteNum & ", tStandingOrders.RouteNum) as Route, " _
& "from tStandingOrders where ....."
....

Kevin