1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Syntax error need help

Discussion in 'MySQL' started by yuratim, Mar 29, 2019.

0
  1. #1
    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
     
    Last edited by a moderator: Mar 30, 2019
    yuratim, Mar 29, 2019 IP
  2. mmerlinn

    mmerlinn Prominent Member

    Messages:
    3,197
    Likes Received:
    818
    Best Answers:
    7
    Trophy Points:
    320
    #2
    Where are you getting the syntax error message?
     
    mmerlinn, Mar 29, 2019 IP
  3. sarahk

    sarahk iTamer Staff

    Messages:
    28,498
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #3
    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?
     
    sarahk, Mar 30, 2019 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    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.
     
    jestep, Apr 1, 2019 IP