UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Select Case-access Convert To SQL, Any Versions    
 
   
LilAnnCC1
post Dec 3 2019, 09:35 AM
Post#1



Posts: 854
Joined: 31-May 04
From: Wisconsin, USA


Hello all! I'm using SQL server for backend and Access for front end.

I am trying to convert a report filter that relies on a select case statement in access. I want to convert this to a stored procedure in SQL Server.

I have written out my logic in SSMS, but I'm pretty sure you all can help me write this better:

CODE
DECLARE
@StartDate Date ='2019-11-23',
@EndDate Date = '2019-12-20',
@OptionFrame int = 6,
@OptionPO nvarchar(20)='4501612779',
@OptionFixture nvarchar(25)='WF3731',
@OptionStore int = '1340'



IF @OptionFrame=1
    BEGIN
        SELECT ID, SalesOrder, CustomerPO, OrderDue, RTS, StoreNo, Location, Job, Item, Description, OrderQTY, WONo, StoreID, PartID, WOID, CustomerID
        FROM vwProduction
        WHERE RTS BETWEEN CAST(@StartDate as datetime) AND Cast(@EndDate as datetime)
    END
IF @OptionFrame=2
    BEGIN
        SELECT ID, SalesOrder, CustomerPO, OrderDue, RTS, StoreNo, Location, Job, Item, Description, OrderQTY, WONo, StoreID, PartID, WOID, CustomerID
        FROM vwProduction
        WHERE RTS BETWEEN CAST(@StartDate as datetime) AND Cast(@EndDate as datetime) AND CustomerPO Like '45%'
    END
IF @OptionFrame=3
    BEGIN
        SELECT ID, SalesOrder, CustomerPO, OrderDue, RTS, StoreNo, Location, Job, Item, Description, OrderQTY, WONo, StoreID, PartID, WOID, CustomerID
        FROM vwProduction
        WHERE RTS BETWEEN CAST(@StartDate as datetime) AND Cast(@EndDate as datetime) AND CustomerPO Like '70%'
    END
IF @OptionFrame=4
    BEGIN
        SELECT ID, SalesOrder, CustomerPO, OrderDue, RTS, StoreNo, Location, Job, Item, Description, OrderQTY, WONo, StoreID, PartID, WOID, CustomerID
        FROM vwProduction
        WHERE CustomerPO =@OptionPO
    END
IF @OptionFrame=5
    BEGIN
        SELECT ID, SalesOrder, CustomerPO, OrderDue, RTS, StoreNo, Location, Job, Item, Description, OrderQTY, WONo, StoreID, PartID, WOID, CustomerID
        FROM vwProduction
        WHERE Item =@OptionFixture
    END

IF @OptionFrame=6
    BEGIN
        SELECT ID, SalesOrder, CustomerPO, OrderDue, RTS, StoreNo, Location, Job, Item, Description, OrderQTY, WONo, StoreID, PartID, WOID, CustomerID
        FROM vwProduction
        WHERE StoreNo =@OptionStore
    END


This works as is and produces the results as expected.

There must be a more elegant solution than this. Can you offer any suggestions?

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
jleach
post Dec 3 2019, 10:55 AM
Post#2


UtterAccess Administrator
Posts: 10,281
Joined: 7-December 09
From: St. Augustine, FL


Hi,

T-SQL has it's own CASE statement: https://docs.microsoft.com/en-us/SQL/t-SQL/...ql-server-ver15

Like this aircode:
CODE
CASE
  WHEN @Value = 1 THEN BEGIN
    -- do your stuff
  END WHEN @Value = 2 THEN BEGIN
    -- other stuff
  END WHEN @Value = 3 THEN BEGIN
    -- do this
  END ELSE BEGIN
    -- do that
  END
END;


The syntax is a little awkward but you can search for examples. Useful in SELECT statements as well, sometimes.

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
cheekybuddha
post Dec 3 2019, 12:24 PM
Post#3


UtterAccess Moderator
Posts: 11,906
Joined: 6-December 03
From: Telegraph Hill


You could probably write your SQL as:
CODE
DECLARE
  @StartDate      DATE          = '2019-11-23',
  @EndDate        DATE          = '2019-12-20',
  @OptionFrame    INT           = 6,
  @OptionPO       NVARCHAR(20)  = '4501612779',
  @OptionFixture  NVARCHAR(25)  = 'WF3731',
  @OptionStore    INT           = 1340

SELECT ID, SalesOrder, CustomerPO, OrderDue, RTS, StoreNo, Location, Job, Item, Description, OrderQTY, WONo, StoreID, PartID, WOID, CustomerID
FROM vwProduction
WHERE (@OptionFrame = 1 AND RTS BETWEEN CAST(@StartDate AS DATETIME) AND CAST(@EndDate AS DATETIME))
   OR (@OptionFrame = 2 AND RTS BETWEEN CAST(@StartDate AS DATETIME) AND CAST(@EndDate AS DATETIME) AND CustomerPO LIKE '45%')
   OR (@OptionFrame = 3 AND RTS BETWEEN CAST(@StartDate AS DATETIME) AND CAST(@EndDate AS DATETIME) AND CustomerPO LIKE '70%')
   OR (@OptionFrame = 4 AND CustomerPO = @OptionPO)
   OR (@OptionFrame = 5 AND Item = @OptionFixture)
   OR (@OptionFrame = 6 AND StoreNo = @OptionStore)
;


But I would test it for speed against what you have already - you may find that the individual queries have better execution plans.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
LilAnnCC1
post Dec 4 2019, 04:05 PM
Post#4



Posts: 854
Joined: 31-May 04
From: Wisconsin, USA


Thank you, cheekybuddha!

I rewrote the procedure and compared both side by side. Your suggests actually provided better times on the server.

You definitely have a more elegant way to write a procedure!

Thanks!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
AlbertKallal
post Dec 4 2019, 10:33 PM
Post#5


UtterAccess VIP
Posts: 2,903
Joined: 12-April 07
From: Edmonton, Alberta Canada


Another way is to have the report based on the view. In other words, just save your view without conditions, linkd to the view.
base the report on the view. At this point, you not had to write one line of new code, or even bother with a store procedure.

Then just keep your old code that passes a where clause to the report. So, for any filter on the report - just pass a standard VBA where clause. Your date and formats can be formatted as you did before (use access date formats etc.). Since the report likely had this filter before, then you likly don't have to change any existing code.

You get the same performance, and all you have to do is pass the where clause to the openReport command. Likely what you had before.

You not find any performance difference, but you do save a lot of time in that you don't have to re-write any new code. Just save the view, and link to it. Now replace the source of your report that was a local (client) query to the new view. In fact, if you delete the old query and name the linked view with the same name, then you likely could make this work without even having to change the report or any VBA code at all. All this procedure code writing is not required. You find this approach saves HUGE developer time and efforts.


R
Albert
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 08:29 AM