Access query >> SQL - 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

General SQL Server Forums

New to SQL Server Programming

Access query >> SQL

Author

Topic

TiGGi
Starting Member

29 Posts

Posted - 2006-04-18 : 17:42:40

I've been using access to generate reports, queries... from progress db and now I need to switch to SQL. I realy need some help with converting my access statements into SQL. Here is statement I need help with:

LotStatus: IIf([pub_jobPhase].[warraid]="reg05" And [pub_jobphase].[Model]<>"_LotSale","Builder Available",IIf(Not IsNull([pub_jobsales].[finalsaledate] Or ([pub_jobphase].[Model]="_LotSale")),"Closed",IIf([description]=" ","Available",IIf(([sales status]="spec" Or [sales status]="model" Or [sales status]="ssbto" Or [sales status]="ssip" Or (Not IsNull([description]) And IsNull([pub_jobsales].[finalsaledate]))),"Allocated"))))

I know SQL doesn't have IF but CASE but I am stuck on multiple condition in one CASE and expecialy with if ... or... or (...and ...) type of statement!

pootle_flump

1064 Posts

Posted - 2006-04-18 : 17:59:27

Hi

something like:

CASE WHEN warraid = 'reg05' And Model <> '_LotSale'
THEN 'Builder Available' 
WHEN finalsaledate IS NOT NULL Or Model = '_LotSale'
THEN 'Closed'
WHEN description ='' 
THEN 'Available' 
WHEN "sales status" IN('spec', 'model', 'ssbto', 'ssip') Or (description IS NOT NULL And finalsaledate IS NULL)
THEN 'Allocated' 
END AS LotStatus

TiGGi
Starting Member

29 Posts

Posted - 2006-04-20 : 09:59:04

thanx alot, i'll give it a try!

couple more questions:
1. When I have IFF within IFF, how do I do that?
IIf([LotType]="Reg",IIf(Not IsNull([FinalSaleDate]),"BTO

2. Is this correct?
IIf((Not IsNull([OfferDate]) And Is Null([FinalSaleDate])),"SSIP","Spec"))

CASE WHEN OfferDate IS NOT NULL AND FinalSaleDate IS NULL THEN 'SSIP' ELSE 'Spec'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-20 : 10:22:30

1 Nested case


  Case when ([LotType]='Reg' then
       case when [FinalSaleDate] is not null then
 
       end
  end

2 yes

Madhivanan

Failing to plan is Planning to fail

TiGGi
Starting Member

29 Posts

Posted - 2006-04-20 : 10:36:23

oh ok, it's pretty much same, thanks mate

TiGGi
Starting Member

29 Posts

Posted - 2006-04-20 : 11:51:17

lol my head is already hurtin from all of this.

Can someone tell me if this wa translated right:
ACCESS:
sales status: IIf([OfferType]="E","Pending",
IIf([LotType]="Spec",IIf(Not IsNull([FinalSaleDate]),"SIP",
IIf(Not IsNull([OfferDate]) And IsNull([FinalSaleDate]),"SSIP","Spec")),
IIf([LotType]="Reg",IIf(Not IsNull([FinalSaleDate]),"BTO",
IIf(Not IsNull([OfferDate]) And IsNull([FinalSaleDate]),"SSBTO","BTO")),
IIf([LotType]="Model",IIf(Not IsNull([FinalSaleDate]),"Model Sold",
IIf(Not IsNull([OfferDate]) And IsNull([FinalSaleDate]),"Model SS","Model"))))))
SQL:
CASE WHEN JobSales.OfferType = 'E' THEN 'Pending'
ELSE CASE
WHEN JobPhase.LotType = 'Spec' THEN
CASE WHEN
JobSales.FinalSaleDate IS NOT NULL THEN 'SIP'
ELSE
CASE WHEN
JobSales.OfferDate IS NOT NULL AND JobSales.FinalSaleDate IS NULL THEN 'SSIP' ELSE 'SPEC' END END
CASE WHEN
JobPhase.LotType = 'Reg'
THEN CASE
JobSales.FinalSaleDate IS NULL THEN 'BTO' ELSE
CASE WHEN JobSales.OfferDate IN NULL and JobSales.FinalSaleDate IS NULL THEN 'SSBTO' ELSE 'BTO' END END
CASE WHEN JobPhase.LotType = 'Model' THEN
CASE WHEN JobSales.FinalSaleDate IS NOT NULL THEN 'Model Sold' ELSE
CASE WHEN JobSales.OfferDate IS NOT NULL AND JobSales.FinalSaleDate IS NULL THEN 'Model SS' ELSE 'Model' END END END END END END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources