March 24, 2004 at 9:36 am
I've written the following statement in Query Analyzer and it works fine:
select a.orderbookedstoreid, a.[name], a.DlvyChrg
, sum(a.DeliveryDollars) as DeliveryDollars
, sum(a.PickUpDollars) as PickUpDollars
, sum(a.TakenDollars) as TakenDollars
, sum(a.DirectShipDollars) as DirectShipDollars
, count(a.DeliveryUnits) as DeliveryTickets
, count(a.PickupUnits) as PickupTickets
, count(a.TakenUnits) as TakenTickets
, count(a.DirShipUnits) as DirectShipTickets
from (
select ivc.orderbookedstoreid, sto.[name], ivc.orderid
, ivc.DlvyChrg
, case ivc.transcodeid when 50 then ivc.dlvysubtot *-1 else
case ivc.transcodeid when 30 then ivc.dlvysubtot *-1 else
ivc.dlvysubtot end end as DeliveryDollars
, case ivc.transcodeid when 50 then ivc.pickupsubtot *-1 else
case ivc.transcodeid when 30 then ivc.pickupsubtot *-1 else
ivc.pickupsubtot end end as PickUpDollars
, case ivc.transcodeid when 50 then ivc.takensubtot *-1 else
case ivc.transcodeid when 30 then ivc.takensubtot *-1 else
ivc.takensubtot end end as TakenDollars
, case ivc.transcodeid when 50 then ivc.dirshipsubtot *-1 else
case ivc.transcodeid when 30 then ivc.dirshipsubtot *-1 else
ivc.dirshipsubtot end end as DirectShipDollars
, case ivc.DlvyItems when 0 then Null else ivc.DlvyItems
end as DeliveryUnits
, case ivc.PickupItems when 0 then Null else ivc.PickupItems
end as PickupUnits
, case ivc.TakenItems when 0 then Null else ivc.TakenItems
end as TakenUnits
, case ivc.DirShipItems when 0 then Null else ivc.DirShipItems
end as DirShipUnits
from invoice ivc
inner join store sto
on (ivc.orderbookedstoreid=sto.storeid)
where ivc.invoicedate between 'Dec 1 2003' and 'Feb 29 2004'
AND TRANSCODEID IN ('50','30','0','1','20')
) a
group by a.orderbookedstoreid, a.[name], a.DlvyChrg
order by a.orderbookedstoreid
I want to put the query in Microsoft Query. When I do, I get the following error message: "SQL Query can't be represented graphically. Continue Anyway?"
Has anyone encountered this message before? I want to put the query in so that I can define the date range as a parameter that can be ran using run-time questions from excel. Any help would be very appreciated.
March 24, 2004 at 11:58 am
That message is OK to ignore. It just means that Query analyzer can't display it in the Query By Example (QBE) area. The CASE statements are too complex for it to represent.
You'll get similar messages if you create UNION queries, and often if you're doing pivot/crosstab type queries.
The query will most likely run ok since it runs in ISQL...
March 24, 2004 at 12:04 pm
The problem is that I don't want the dates hardcoded. I want to set them as parameters in Microsoft Query and cannot due to the "graphically" error message. When this message shows up in MS Query I am unable to access the criteria line to set any parameters. If I go into SQL and try to change the line:
where ivc.invoicedate between 'Dec 1 2003' and ' Feb 29 2003'
To:
where (ivc.invoicedate between ? and ?)
I get the message that parameters are not allowed in queries that are displayed graphically.
Any other thoughts?
March 24, 2004 at 12:45 pm
Can you turn your query into a view and then have your query in MSQuery do the date filtering against the view?
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
March 24, 2004 at 12:51 pm
I thought of that too, but need to get with our dba's to give me access to create views. That may be my best bet though.
March 25, 2004 at 10:13 pm
Since it's being run in Access, perhaps this will work:
where (ivc.invoicedate between ? and ?)
where (ivc.invoicedate between [LowDate] and [HighDate])
Where the query "should" ask for the low and high dates, since these are not fields the query understands.
Good luck!
Butch
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply