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.

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

    mmerlinn Prominent Member

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

    sarahk iTamer Staff

    Messages:
    26,762
    Likes Received:
    4,068
    Best Answers:
    115
    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 Premium Member

    Messages:
    3,661
    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