Good Day everyone Thank you for trying to help first of all. My question is about; Im trying to write and sql request which will execute the following 1. When user enters a certain terminal code then the sql returns the results for that terminal 2. When user enters terminal code with mask CN then the sql request returns all terminals who have CN at the beginning of that terminal Im aware ill probably will need to use the CASE here So far i got, SELECT sm.terminalid as terminalcode, sm.sums as Latest_sums from sums1 sm CASE WHEN sm.terminalid = : p_terminal THEN sm.sums = (SELECT max(ss.sums) from sums ss WHERE ss.terminalid = : p_terminal) WHEN sm.terminalid = : p_terminal AND : p_terminal LIKE 'CN%' THEN sm.sums = (SELECT sm1.sums FROM sums1 sm1 WHERE sm1.terminalid = : p_terminal AND : p_terminal LIKE 'CN%') ELSE 'Incorrect Terminal ID' END Code (markup): Can anyone help me where am i going wrong as the selection doesnt return multiple results when CN is entered. Thank you in advance
for starters test your query directly using phpmyadmin, teamsql, etc (I'm a big fan of sqlyog) If it's complex like this one then create some sample data in a mysql fiddle and share the link. It's a bit more work on your part but helps us help you. I use case statements a lot but haven't used the syntax you have so would need to check on that. And I'm guessing that code is just the bits you're working on because the order is all wrong etc. What's the output data meant to show?
Can you handle this on the application rather than database level? It might be possible to get the result you want with CASE WHEN THEN..., but I would personally handle this through the application and not try to get the database to deal with it. And, I would prefer a stored procedure or a view to a straight query for situations like this if I was going to use the database to handle the logic.