conditional select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

conditional select query

Author

Topic

cblgi
Starting Member

3 Posts

Posted - 2002-09-15 : 21:54:03

I want to make to a query for reporting purposes. I'm familiar w/ normal SELECT queries using more than one condition. I'm taking data from two tables (table1, table2). Depending on the value of the field "type" in table1, I want data from one of three fields in table2. If table1.type="s" then I want table2.standamt to be selected. If table1.type="d", I want table2.dualamt selected. If table1.type="m", I want table2.muleamt selected. I'm not sure of how I can manage this with a straight sql query. Is it possible? If not, any suggestions of how I can do this would be appreciated.

Thanks

mfemenel
Professor Frink

1421 Posts

Posted - 2002-09-15 : 23:31:58

Try this version. Let me know how you make out.
select
case table1.type
when 's' then table2.standamt
when 'd' then table2.dualamt
when 'm' then table2.muleamt
end Myfield
from table1
inner join rest of your join syntax here

Mike
"oh, that monkey is going to pay"

cblgi
Starting Member

3 Posts

Posted - 2002-09-16 : 01:23:31

This is what I took from your suggestion:
SELECT TicketTable.TruckerName, TicketTable.UnitNumber, TicketTable.Date, CASE (TicketTable.TruckType)
(WHEN ( "s" (THEN (UnitHaulRateTable1.StandAmt))
(WHEN ("d" (THEN (UnitHaulRateTable1.DualAmt)
(WHEN ("m" (THEN (UnitHaulRateTable1.MuleAmt)))))))))
FROM TicketTable, UnitHaulRateTable1
WHERE TicketTable.UnitNumber=UnitHaulRateTable1.UnitNumber AND TicketTable.DestinationCode=UnitHaulRateTable1.DestinationCode

These are the actual table & field names. I'm not really up on the punctuation involved in Access (I just plug until it works), but it took what is above for me to be able to save it. Then, when I try to run it, it gives me: Invalid use of '.','!', or '()' in expression....
If I take () out, I can't resave it, if I leave them all in, then I can't run it. I ommitted "end Myfield" because I wasn't sure if that was generic (and I wasn't sure what it does). I'm also not familiar with using case statements w/ sql. Any suggestions for tweaks?

Thanks for getting me this far

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-09-16 : 05:03:15

Access does not support case expresssions. Use the iif function instead, i.e.

select iif(table1.type = 's', table2.standamt,iif(table2.type='d',...)) ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources