Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    3

    Unanswered: Nightmare query upgrade from Access

    Hi, nice to meet you all ^^

    I've been given this access database to look at which could eventually be upgraded to SQL, I am conducting a kind of initital research in to how we would go about it. The previous system has developed over time and is pretty messy. I'm not really a database developer, but as everyone is busy on other things, this side project has been given to me to look at as a warm up!

    Basically I've managed to upsize all the tables and their contents, and have begun copying the forms over to a .adp - creating most of the queries as stored procedures.

    I'm working through cronologically, and this is the 2nd query the system requires... It was full of IIf statements which I have commented out. The adp works, but is missing a lot of data - can't really work out where it was meant to be feeding from, I personally can't stand access - but it's what the original was created in and what they want to use.

    Code:
    ALTER PROC [qrySaleLotEntry] @saleid nvarchar(3)
    AS
    SELECT [Sale & Lot].SaleNum, [Sale Details].SaleDate, [Sale Details].SaleDesc, [Sale & Lot].LotNum, 
    [Sale & Lot].onsite, [Sale & Lot].StockNum, [Sale & Lot].ThisEntryFee, [Sale & Lot].EntryFeePaid, 
    Accounts.[4x4ENTRYFEE], Accounts.CARENTRYFEE, [Sale & Lot].Vendor, Accounts.VENDORDESC, Accounts.ACCOUNTNAME, 
    Accounts.[GROUP], Accounts.CONTACT, Accounts.PHONE, Accounts.MOBILPHONE, Accounts.VATNO, Accounts.[e-mail], 
    [Sale & Lot].RegYear, [Sale & Lot].RegLetter, [Sale & Lot].RegDate, [Sale & Lot].RegNum, [Sale & Lot].ManuYear, 
    [Sale & Lot].Manufacturer, [Sale & Lot].Model, [Sale & Lot].[Type], [Sale & Lot].CAPCode, [Sale & Lot].Colour, 
    [Sale & Lot].MileageNum, [Sale & Lot].Kilometres, [Sale & Lot].Warranted, [Sale & Lot].ServiceHistory, 
    [Sale & Lot].ServiceNum, [Sale & Lot].ServiceMileage, [Sale & Lot].ServiceDate, [Sale & Lot].MoTDay, [Sale & Lot].MoT, 
    [Sale & Lot].Tax, [Sale & Lot].ReservePrice, [Sale & Lot].VATCode, [Sale & Lot].CAPValueClean, [Sale & Lot].CAPValueAve, 
    [Sale & Lot].CAPValuePoor, [Sale & Lot].AgrNum, [Sale & Lot].Extra1, [Sale & Lot].Extra2, [Sale & Lot].Extra3, 
    [Sale & Lot].Extra4, [Sale & Lot].Extra5, [Sale & Lot].Extra6, [Sale & Lot].Extra7, [Sale & Lot].Extra8, 
    [Sale & Lot].Extra9, [Sale & Lot].Extra10, [Sale & Lot].ASSEEN, Accounts.ASSEEN, Accounts.UNWARRANTED, 
    [Sale & Lot].NoMMF, [Sale & Lot].UNROADWORTHY, [Sale & Lot].PRESALEHPI, [Sale & Lot].[COLLECTION], 
    Accounts.[CollectionRequired?], [Sale & Lot].TotalLoss, [Sale & Lot].Accident, [Sale & Lot].FinanceOwed, 
    [Sale & Lot].Taxi, [Sale & Lot].V5, [Sale & Lot].V5Part2, [Sale & Lot].PlateTfr, [Sale & Lot].PlateTfrCharge, 
    Accounts.PlateTfrCharge, [Sale & Lot].PlateTfrPaid, [Sale & Lot].Fuel, [Sale & Lot].FuelCharge, Accounts.FuelCharge, 
    [Sale & Lot].PreSalePrep, [Sale & Lot].PreSalePrepCharge, Accounts.PreSalePrepCharge, [Sale & Lot].Misc1, 
    [Sale & Lot].Misc1Desc, [Sale & Lot].Misc1Charge, [Sale & Lot].Misc2, [Sale & Lot].Misc2Desc, [Sale & Lot].Misc2Charge, 
    [Sale & Lot].DeliveryIn, [Sale & Lot].DeliveryInCharge, Accounts.DeliveryInCharge, [Sale & Lot].DeliveryOut, 
    [Sale & Lot].DeliveryOutCharge, Accounts.DeliveryOutCharge, [Sale & Lot].WashOff, [Sale & Lot].WashOffCharge, 
    Accounts.WashOffCharge, [Sale & Lot].Polish, [Sale & Lot].PolishCharge, Accounts.PolishCharge, [Sale & Lot].ValetA, 
    [Sale & Lot].ValetACharge, Accounts.ValetACharge, [Sale & Lot].ValetB, [Sale & Lot].ValetBCharge, 
    Accounts.ValetBCharge, [Sale & Lot].ValetC, [Sale & Lot].ValetCCharge, Accounts.ValetCCharge, [Sale & Lot].Delogo, 
    [Sale & Lot].DelogoCharge, Accounts.DelogoCharge, [Sale & Lot].ENGINEER, [Sale & Lot].EngineersCharge, 
    Accounts.EngineersCharge, [Sale & Lot].Repairs, [Sale & Lot].RepairsCharge, Accounts.RepairsCharge, 
    [Sale & Lot].Provisional, [Sale & Lot].Purchaser, Accounts_1.ACCOUNTNAME, Accounts_1.CONTACT, Accounts_1.PHONE, 
    Accounts_1.MOBILPHONE, Accounts_1.[e-mail], [Sale & Lot].SalePrice, [Sale & Lot].Nett, [Sale & Lot].VAT, 
    [Sale & Lot].VATDesc, [Sale & Lot].TransInbound, [Sale & Lot].TransInboundPaid, [Sale & Lot].TransReturn, 
    [Sale & Lot].TransReturnPaid, [Sale & Lot].[Transfer Complete], [Sale & Lot].datetostock, [Sale & Lot].Reentry, 
    [Sale & Lot].ChassisNum, [Sale & Lot].Comments, [Sale & Lot].PackNumber, [Sale & Lot].MissedDeadline, 
    [Sale & Lot].Created, [Sale & Lot].CreatedBy, [Sale & Lot].Modified, [Sale & Lot].ModifiedBy, Accounts.TradeStatus, 
    [Sale & Lot].VIN, [Sale & Lot].PrevVRM, [Sale & Lot].DateVRMChanged, [Sale & Lot].ExpModel, [Sale & Lot].EngineNum, 
    [Sale & Lot].OrigCol, [Sale & Lot].NumPrevCols, [Sale & Lot].DateLastColChange, [Sale & Lot].PrevCol, 
    [Sale & Lot].AgreementType, [Sale & Lot].AgreementTerm, [Sale & Lot].AgreementDate, [Sale & Lot].FinanceCo, 
    [Sale & Lot].FinanceTel, [Sale & Lot].FinanceAgrNum, [Sale & Lot].FinanceDesc, [Sale & Lot].PoliceForce, 
    [Sale & Lot].PoliceTel, [Sale & Lot].PoliceReportDate, [Sale & Lot].RiskCompany, [Sale & Lot].RiskTel, 
    [Sale & Lot].RiskRef, [Sale & Lot].RiskPeriod, [Sale & Lot].RiskInterestDate, [Sale & Lot].RiskType, 
    [Sale & Lot].RiskOther, [Sale & Lot].ConditionInsurer, [Sale & Lot].ConditionClaimNum, [Sale & Lot].ConditionTel, 
    [Sale & Lot].ConditionMIAFTRDate, [Sale & Lot].ConditionMake, [Sale & Lot].ConditionModel, [Sale & Lot].KeeperPrevNum,
    [Sale & Lot].KeeperChangeDate, [Sale & Lot].KeeperDateAcquired, [Sale & Lot].KeeperDateDisposed, [Sale & Lot].Scrapped, [Sale & Lot].Exported
    FROM (Accounts RIGHT JOIN ([Sale Details] INNER JOIN [Sale & Lot] ON [Sale Details].SaleNum = [Sale & Lot].SaleNum) ON Accounts.ACCOUNT = [Sale & Lot].Vendor) LEFT JOIN Accounts AS Accounts_1 ON [Sale & Lot].Purchaser = Accounts_1.ACCOUNT
    WHERE dbo.[Sale & Lot].SaleNum = @saleid
    ORDER BY [Sale & Lot].SaleNum, [Sale & Lot].LotNum;
    
    
    /*
    IIf([saledesc] Like "C*",[sale & lot.LotNum] & "C",[sale & lot.LotNum] & "F") AS SaleLetter, 
    IIf([Accounts.VENDORDESC] Not Like "","Direct from") AS DirectFrom, [Sale & Lot.Manufacturer] & " " & [Sale & Lot.Model] & " " & [Sale & Lot.Type] AS [Desc], StrConv([sale & lot.RegNum],1) AS Reg, 
    IIf([accounts.unwarranted]=-1,"Unwarranted - Company Policy",
    IIf([sale & lot.warranted] Like "1",[sale & lot.MileageNum] & " " & IIf([sale & lot.kilometres]=-1,"kms ") & "Warranted",
    IIf([sale & lot.warranted] Like "2","Unwarranted",IIf([sale & lot.warranted] Like "3","Incorrect")))) AS MileageStatus, 
    IIf([Sale & Lot.VATCode] Like 1,"Plus VAT",
    IIf([Sale & Lot.VATCode] Like 2,"No VAT",
    IIf([Sale & Lot.VATCode] Like 3,"Inc VAT"))) AS VATStatus, 
    IIf([sale & lot.v5]=-1,"V5 here",IIf([sale & lot.v5part2]=-1,"V5/2 here",IIf([sale & lot.v5]=0 And [sale & lot.v5part2]=0,"V5 not here"))) AS V5here, IIf([Sale & Lot.Tax] Is Not Null,"Tax " & [Sale & Lot.Tax],"No Tax") AS Taxhere, IIf([sale & lot.MoT] Is Not Null,"MoT " & [sale & lot.MoTDay] & " " & [sale & lot.MoT],"No MoT") AS MoThere, IIf([Sale & Lot.ServiceHistory]<>-1 And [Sale & Lot.ServiceNum] Is Null,"No Service History",IIf([Sale & Lot.ServiceHistory]=-1 And [Sale & Lot.ServiceNum] Is Null,"Service History",IIf([Sale & Lot.ServiceNum]=1,"1 Service",[Sale & Lot.ServiceNum] & " " & "Services"))) AS Services, IIf([Sale & Lot.ASSEEN]=-1,"As Seen",IIf([Sale & Lot.NoMMF]=-1,"No Major Mechanical Faults",IIf([Sale & Lot.UNROADWORTHY]=-1,"Unroadworthy"))) AS MechDesc, IIf([Sale & Lot.PrevLotNum]>0,[Sale & Lot.PrevSaleNum] & "/" & [Sale & Lot.PrevLotNum],"") AS Prev, IIf([sale & lot.MoT] Is Not Null,Trim([sale & lot.MoTDay] & " " & [sale & lot.MoT]),"No") AS PendragonMoT, IIf([Sale & Lot.ServiceHistory]<>-1 And [Sale & Lot.ServiceNum] Is Null,"No",IIf([Sale & Lot.ServiceHistory]=-1 And [Sale & Lot.ServiceNum] Is Null,"Yes",IIf([Sale & Lot.ServiceNum]=1,"1 Service",[Sale & Lot.ServiceNum] & " " & "Services"))) AS PendragonServiceHist, IIf([Sale & Lot.ServiceMileage] Is Null,"","(Last Serviced " & [Sale & Lot.ServiceMileage] & ", " & [Sale & Lot.ServiceDate] & ")") AS LastServiced, IIf([Sale & Lot.Tax] Is Not Null,[Sale & Lot.Tax],"No") AS PendragonTax, Trim([Sale & Lot]![Extra2] & [Sale & Lot]![Extra3] & [Sale & Lot]![Extra4] & [Sale & Lot]![Extra5] & [Sale & Lot]![Extra6] & [Sale & Lot]![Extra7] & [Sale & Lot]![Extra8] & [Sale & Lot]![Extra9] & [Sale & Lot]![Extra10] & IIf([Sale & Lot]![TotalLoss]=-1," Total Loss") & IIf([Sale & Lot]![Accident]="No",""," " & [Sale & Lot.Accident]) & IIf([Sale & Lot]![Taxi]="No",""," " & [Sale & Lot.Taxi])) AS PendragonExtras, IIf([sale & lot.v5]=-1,"YES","NO") AS LogBook, IIf([sale & lot.Extra2] Like " Service History,","YES","NO") AS ServiceHistCondRep
    FROM (Accounts RIGHT JOIN ([Sale Details] INNER JOIN [Sale & Lot] ON [Sale Details].SaleNum = [Sale & Lot].SaleNum) ON Accounts.ACCOUNT = [Sale & Lot].Vendor) LEFT JOIN Accounts AS Accounts_1 ON [Sale & Lot].Purchaser = Accounts_1.ACCOUNT
    ORDER BY [Sale & Lot].SaleNum, [Sale & Lot].LotNum;
    
    */
    I've commented out the IIf statements. I began by trying to convert them in to CASE statements and such - but frankly can't get my head around that.

    Getting back to basics... I suppose the question would be: I am upsizing from access to SQL, what the heck do I do with all these IIf statements?

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    IS your question how to change IIF(Experssion,true,false) to a case statement

    iif(1=2,'One=Two','ONE<>TWO')

    would return 'ONE<>TWO'

    case when 1=2 then 'One=Two' else 'ONE<>TWO' end

    would return 'ONE<>TWO'

    Also

    IIf([saledesc] Like "C*",[sale & lot.LotNum] & "C",[sale & lot.LotNum] & "F") AS SaleLetter,

    CASE WHEN SALEDESC like 'C%' then lotnum + 'C' else lotnum + 'F' end

  3. #3
    Join Date
    Jul 2006
    Posts
    3
    Thanks - that's a starting step for me!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •