Waar is de tijd, deel 2: vieze queries in SQL Server.
<cfquery name=”caller.songlist” datasource=”#request.mainDSN#”>
SELECT album_id, album_title, album_year, album_thumb, album_image,
album_number,
publisher_name, albumtype_name, artist_name,
song_id, len(song_text) as songtext,
CASE left(song_title,charindex(‘ ‘,song_title))
WHEN ‘The ‘ THEN right(song_title, len(song_title)-4)+’, The’
WHEN ‘A ‘ THEN right(song_title, len(song_title)-2)+’, A’
WHEN ‘An ‘ THEN right(song_title, len(song_title)-3)+’, An’
WHEN ‘Le ‘ THEN right(song_title, len(song_title)-3)+’, Le’
WHEN ‘La ‘ THEN right(song_title, len(song_title)-3)+’, La’
WHEN ‘Un ‘ THEN right(song_title, len(song_title)-3)+’, Un’
WHEN ‘Les ‘ THEN right(song_title, len(song_title)-4)+’, Les’
ELSE
CASE left(song_title,2)
WHEN ‘L”’ THEN right(song_title, len(song_title)-2)+’, L”’
ELSE song_title
END
END
as song_title,
sa_side, sa_track, sa_length, sa_mp3
FROM tblAlbums, tblAlbumTypes, tblArtists, tblPublishers,
tblSong_Album, tblSongs
WHERE album_albumtype_id=albumtype_id AND
album_artist_id=artist_id AND
album_publisher_id=publisher_id AND
sa_album_id=album_id AND
sa_song_id=song_id AND
<cfif isdefined(“caller.attributes.Zoekstring”) AND
len(trim(caller.attributes.zoekstring))>
( {fn lcase(song_title)} LIKE ‘%#caller.attributes.zoekstring#%’
OR
{fn lcase(song_text)} LIKE ‘%#caller.attributes.zoekstring#%’
)
<cfelseif isdefined(“caller.attributes.let”)>
CASE left(song_title,charindex(‘ ‘,song_title))
WHEN ‘The ‘ THEN right(song_title, len(song_title)-4)+’, The’
WHEN ‘A ‘ THEN right(song_title, len(song_title)-2)+’, A’
WHEN ‘An ‘ THEN right(song_title, len(song_title)-3)+’, An’
WHEN ‘Le ‘ THEN right(song_title, len(song_title)-3)+’, Le’
WHEN ‘La ‘ THEN right(song_title, len(song_title)-3)+’, La’
WHEN ‘Un ‘ THEN right(song_title, len(song_title)-3)+’, Un’
WHEN ‘Les ‘ THEN right(song_title, len(song_title)-4)+’, Les’
ELSE
CASE left(song_title,2)
WHEN ‘L”’ THEN right(song_title, len(song_title)-2)+’, L”’
ELSE song_title
END
END
LIKE ‘#caller.attributes.let#%’
<cfelse>
1=0
</cfif>
ORDER BY song_title, album_year, sa_side, sa_track
</cfquery>