Results 1 to 3 of 3
  1. #1
    d.reinon is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    2

    Question Query not working

    Hello, I have the following query:
    Code:
    PARAMETERS [Instrumento] Text ( 255 );
    SELECT a.Alu_ID AS [ID de Alumno], SWITCH(
      ac.AluCur_Curso LIKE '*60', '1 h.',
      ac.AluCur_Curso LIKE '*45',  '45 min.',
      ac.AluCur_Curso LIKE '*30', '30 min.'
    ) AS Duración, ac.AluCur_FechaMatr AS [Fecha de Matrícula], a.Alu_Nombre AS Nombre, a.Alu_Apellido1 AS [Apellido 1], a.Alu_Apellido2 AS [Apellido 2], a.Alu_DNI AS DNI, a.Alu_Telefono AS Teléfono
    FROM Alumnos AS a
    INNER JOIN Alumnos_Cursos AS ac ON (a.Alu_ID=ac.AluCur_Alumno AND ac.AluCur_Curso LIKE (SELECT Codigo&'*' FROM Instrumentos_AUX i WHERE i.Instrumento = [Instrumento] LIMIT 1))
    WHERE a.Alu_ID IN (SELECT ac.AluCur_Alumno
    FROM Alumnos_Cursos ac
    WHERE ac.AluCur_Curso LIKE (SELECT Codigo&'*' FROM Instrumentos_AUX i WHERE i.Instrumento = [Instrumento] LIMIT 1)
    ORDER BY a.Alu_ID;
    The LIKE clauses aren't working because I think I can't use select in it. What I'm trying to do with that select and like together is to assign a code depending on the text of the parameter. For example: if the parameter text is Clarinet, then the code would be CLA. Before, I tried a switch but it was so long that the query was too complex, and I read in a forum to create an auxiliar table with the parameter expected text and its respecting code, but I don't know how to subquery that auxiliar table here.



    Any helps would be much appreciated.
    Thanks in advance.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    dont think the Switch will allow it.
    try using a table instead of switch. join it to your main table to filter out like the switch does.

  3. #3
    d.reinon is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    2
    Quote Originally Posted by ranman256 View Post
    dont think the Switch will allow it.
    try using a table instead of switch. join it to your main table to filter out like the switch does.
    Hello, thanks for answering! I actually don't know how to join it with my main table since I already have an inner join with another table and what I'm trying to use the parameter code thing is in the ON statement of the join.
    Could you be able to show me how to code it please?

    Thanks again!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 05-10-2020, 09:49 AM
  2. Replies: 2
    Last Post: 03-26-2019, 12:40 PM
  3. Working query stops working after importing
    By Abacus1234 in forum Import/Export Data
    Replies: 3
    Last Post: 10-25-2015, 09:12 PM
  4. Replies: 3
    Last Post: 10-17-2012, 09:24 AM
  5. Query is not working
    By pushpm in forum Programming
    Replies: 3
    Last Post: 04-14-2009, 07:16 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums