Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3064
  • Last Modified:

Basic SQL Case Statement for Date/Time Ranges

I am writing an SQL with "Oracle SQL Developer" to search for data based on user selection.  The two choices are:  6AM-3PM (1st Production Shift) and 3PM-12AM (2nd Production Shift).  I am ok with writing two separate SQLs to complete this task, but I would like to have one SQL making the decision based on a prompt for user input (i.e. Please enter shift "1" or "2").  Can someone please help me with the details of setting up this CASE?
0
Derek_Indianapolis
Asked:
Derek_Indianapolis
  • 2
1 Solution
 
patriktCommented:
You can use construction
WHEN (myparam=1 AND condition for case 1>) OR (m\yparam=2 and <second case condition>)
0
 
Derek_IndianapolisAuthor Commented:
That leaves quite a bit to the imagination, perhaps I should give more details.  Here is the sql, with attempting to use the above outline...

SELECT *
FROM inventory_transactions a
WHERE when(myparam=1 and a.editdate = '&&date')
                OR (myparam=2 and a.editdate ='&&date')

1. I would like the "myparam" to prompt the user for input when the SQL is run.
2. Typically, I use the '&' to force user input, and from my understanding the double '&' retains the initial input for future use throughout the SQL.  This means I need to user prompts one for "myparam" and one for 'Date/Time Range"

I am using a "Date/TIme" field to find the period of time (shift 1 and shift 2) of records to select.  The sql above does not work, as I am sure that I'm not using appropriate syntax and should probably be putting the statement under a different clause like SELECT
0
 
patriktCommented:
Sorry for my misstype WHEN should be WHERE so you dont need "when" word in the query.

I have no knowledge of your database or tool used so could not help with interfacing with user.
If the date is fixed string you should hardcode it into query and let choose use only 1 or 2 possibility. it is why I put myparam as selector into query.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now