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.