On 30-5-2015 02:53, Etienne Charland wrote:
> I'm using SQLiteStudio.
>
> It's not easy to run it manually because of how the parameters must be
> passed, and the function that doesn't exist within the database.
>
> I tried replacing all parameters by their value, and replacing the function
> with a simple multiplication. It throws an error, but doesn't tell me
> anything about the error. So it fails... but it could be because I made an
> error in editing it, or because it is too long.
>
> Here's the script I'm running.
>
> Most likely I'm running into a limit of SQLite engine?
>
>
> SELECT
> [Project27].[MediaTypeId] AS [MediaTypeId],
> [Project27].[MediaId] AS [MediaId],
> [Project27].[Artist] AS [Artist],
> [Project27].[Title] AS [Title],
> [Project27].[Album] AS [Album],
> [Project27].[MediaCategoryId] AS [MediaCategoryId],
> [Project27].[FileName] AS [FileName],
> [Project27].[Preference] AS [Preference],
> [Project27].[C1] AS [C1],
> [Project27].[C2] AS [C2],
> [Project27].[BuyUrl] AS [BuyUrl],
> [Project27].[C5] AS [C3],
> [Project27].[C6] AS [C4],
> [Project27].[C7] AS [C5],
> [Project27].[C8] AS [C6],
> [Project27].[C9] AS [C7],
> [Project27].[C10] AS [C8],
> [Project27].[C11] AS [C9],
> [Project27].[C12] AS [C10],
> [Project27].[C3] AS [C11],
> [Project27].[C4] AS [C12]
> FROM ( SELECT
> [Project26].[MediaId] AS [MediaId],
> [Project26].[MediaTypeId] AS [MediaTypeId],
> [Project26].[Artist] AS [Artist],
> [Project26].[Title] AS [Title],
> [Project26].[Album] AS [Album],
> [Project26].[MediaCategoryId] AS [MediaCategoryId],
> [Project26].[FileName] AS [FileName],
> [Project26].[Preference] AS [Preference],
> [Project26].[BuyUrl] AS [BuyUrl],
> [Project26].[Length] AS [C1],
> CASE WHEN ('' <> [Project26].[DownloadUrl]) THEN 1 WHEN ('' =
> [Project26].[DownloadUrl]) THEN 0 END AS [C2],
> CASE WHEN (('' IS NULL) OR ((LENGTH('')) = 0)) THEN NULL ELSE
> [Project26].[C9] END AS [C3],
> 1 AS [C4],
> [Project26].[C1] AS [C5],
> [Project26].[C2] AS [C6],
> [Project26].[C3] AS [C7],
> [Project26].[C4] AS [C8],
> [Project26].[C5] AS [C9],
> [Project26].[C6] AS [C10],
> [Project26].[C7] AS [C11],
> [Project26].[C8] AS [C12]
> FROM ( SELECT
> [Project24].[MediaId] AS [MediaId],
> [Project24].[MediaTypeId] AS [MediaTypeId],
> [Project24].[Artist] AS [Artist],
> [Project24].[Title] AS [Title],
> [Project24].[Album] AS [Album],
> [Project24].[MediaCategoryId] AS [MediaCategoryId],
> [Project24].[FileName] AS [FileName],
> [Project24].[Preference] AS [Preference],
> [Project24].[Length] AS [Length],
> [Project24].[DownloadUrl] AS [DownloadUrl],
> [Project24].[BuyUrl] AS [BuyUrl],
> [Project24].[C1] AS [C1],
> [Project24].[C2] AS [C2],
> [Project24].[C3] AS [C3],
> [Project24].[C4] AS [C4],
> [Project24].[C5] AS [C5],
> [Project24].[C6] AS [C6],
> [Project24].[C7] AS [C7],
> [Project24].[C8] AS [C8],
> (SELECT
> ([Extent18].[Height] * [Extent18].[Depth]) AS [C1]
> FROM [MediaRatings] AS [Extent18]
> INNER JOIN [RatingCategories] AS [Extent19] ON
> [Extent18].[RatingId] = [Extent19].[RatingId]
> WHERE (([Project24].[MediaId] = [Extent18].[MediaId]) AND
> (([Extent19].[Name] = '') OR (1 = 0))) AND ([Extent19].[Custom] = 1) LIMIT 1)
> AS [C9]
> FROM ( SELECT
> [Project23].[MediaId] AS [MediaId],
> [Project23].[MediaTypeId] AS [MediaTypeId],
> [Project23].[Artist] AS [Artist],
> [Project23].[Title] AS [Title],
> [Project23].[Album] AS [Album],
> [Project23].[MediaCategoryId] AS [MediaCategoryId],
> [Project23].[FileName] AS [FileName],
> [Project23].[Preference] AS [Preference],
> [Project23].[Length] AS [Length],
> [Project23].[DownloadUrl] AS [DownloadUrl],
> [Project23].[BuyUrl] AS [BuyUrl],
> [Project23].[C1] AS [C1],
> [Project23].[C2] AS [C2],
> [Project23].[C3] AS [C3],
> [Project23].[C4] AS [C4],
> [Project23].[C5] AS [C5],
> [Project23].[C6] AS [C6],
> [Project23].[C7] AS [C7],
> [Project23].[C8] AS [C8]
> FROM ( SELECT
> [Project21].[MediaId] AS [MediaId],
> [Project21].[MediaTypeId] AS [MediaTypeId],
> [Project21].[Artist] AS [Artist],
> [Project21].[Title] AS [Title],
> [Project21].[Album] AS [Album],
> [Project21].[MediaCategoryId] AS [MediaCategoryId],
> [Project21].[FileName] AS [FileName],
> [Project21].[Preference] AS [Preference],
> [Project21].[Length] AS [Length],
> [Project21].[DownloadUrl] AS [DownloadUrl],
> [Project21].[BuyUrl] AS [BuyUrl],
> [Project21].[C1] AS [C1],
> [Project21].[C2] AS [C2],
> [Project21].[C3] AS [C3],
> [Project21].[C4] AS [C4],
> [Project21].[C5] AS [C5],
> [Project21].[C6] AS [C6],
> [Project21].[C7] AS [C7],
> (SELECT
> ([Extent16].[Height] * [Extent16].[Depth] AS [C1]
> FROM [MediaRatings] AS [Extent16]
> INNER JOIN [RatingCategories] AS [Extent17] ON
> [Extent16].[RatingId] = [Extent17].[RatingId]
> WHERE ([Project21].[MediaId] = [Extent16].[MediaId]) AND
> ('Egoless' = [Extent17].[Name]) LIMIT 1) AS [C8]
> FROM ( SELECT
> [Project20].[MediaId] AS [MediaId],
> [Project20].[MediaTypeId] AS [MediaTypeId],
> [Project20].[Artist] AS [Artist],
> [Project20].[Title] AS [Title],
> [Project20].[Album] AS [Album],
> [Project20].[MediaCategoryId] AS [MediaCategoryId],
> [Project20].[FileName] AS [FileName],
> [Project20].[Preference] AS [Preference],
> [Project20].[Length] AS [Length],
> [Project20].[DownloadUrl] AS [DownloadUrl],
> [Project20].[BuyUrl] AS [BuyUrl],
> [Project20].[C1] AS [C1],
> [Project20].[C2] AS [C2],
> [Project20].[C3] AS [C3],
> [Project20].[C4] AS [C4],
> [Project20].[C5] AS [C5],
> [Project20].[C6] AS [C6],
> [Project20].[C7] AS [C7]
> FROM ( SELECT
> [Project18].[MediaId] AS [MediaId],
> [Project18].[MediaTypeId] AS [MediaTypeId],
> [Project18].[Artist] AS [Artist],
> [Project18].[Title] AS [Title],
> [Project18].[Album] AS [Album],
> [Project18].[MediaCategoryId] AS [MediaCategoryId],
> [Project18].[FileName] AS [FileName],
> [Project18].[Preference] AS [Preference],
> [Project18].[Length] AS [Length],
> [Project18].[DownloadUrl] AS [DownloadUrl],
> [Project18].[BuyUrl] AS [BuyUrl],
> [Project18].[C1] AS [C1],
> [Project18].[C2] AS [C2],
> [Project18].[C3] AS [C3],
> [Project18].[C4] AS [C4],
> [Project18].[C5] AS [C5],
> [Project18].[C6] AS [C6],
> (SELECT
> ([Extent14].[Height] * [Extent14].[Depth]) AS
> [C1]
> FROM [MediaRatings] AS [Extent14]
> INNER JOIN [RatingCategories] AS [Extent15] ON
> [Extent14].[RatingId] = [Extent15].[RatingId]
> WHERE ([Project18].[MediaId] =
> [Extent14].[MediaId]) AND ('Love' = [Extent15].[Name]) LIMIT 1) AS [C7]
> FROM ( SELECT
> [Project17].[MediaId] AS [MediaId],
> [Project17].[MediaTypeId] AS [MediaTypeId],
> [Project17].[Artist] AS [Artist],
> [Project17].[Title] AS [Title],
> [Project17].[Album] AS [Album],
> [Project17].[MediaCategoryId] AS
> [MediaCategoryId],
> [Project17].[FileName] AS [FileName],
> [Project17].[Preference] AS [Preference],
> [Project17].[Length] AS [Length],
> [Project17].[DownloadUrl] AS [DownloadUrl],
> [Project17].[BuyUrl] AS [BuyUrl],
> [Project17].[C1] AS [C1],
> [Project17].[C2] AS [C2],
> [Project17].[C3] AS [C3],
> [Project17].[C4] AS [C4],
> [Project17].[C5] AS [C5],
> [Project17].[C6] AS [C6]
> FROM ( SELECT
> [Project15].[MediaId] AS [MediaId],
> [Project15].[MediaTypeId] AS [MediaTypeId],
> [Project15].[Artist] AS [Artist],
> [Project15].[Title] AS [Title],
> [Project15].[Album] AS [Album],
> [Project15].[MediaCategoryId] AS
> [MediaCategoryId],
> [Project15].[FileName] AS [FileName],
> [Project15].[Preference] AS [Preference],
> [Project15].[Length] AS [Length],
> [Project15].[DownloadUrl] AS [DownloadUrl],
> [Project15].[BuyUrl] AS [BuyUrl],
> [Project15].[C1] AS [C1],
> [Project15].[C2] AS [C2],
> [Project15].[C3] AS [C3],
> [Project15].[C4] AS [C4],
> [Project15].[C5] AS [C5],
> (SELECT
> ([Extent12].[Height] *
> [Extent12].[Depth]) AS [C1]
> FROM [MediaRatings] AS [Extent12]
> INNER JOIN [RatingCategories] AS
> [Extent13] ON [Extent12].[RatingId] = [Extent13].[RatingId]
> WHERE ([Project15].[MediaId] =
> [Extent12].[MediaId]) AND ('Spiritual Feminine' = [Extent13].[Name]) LIMIT 1)
> AS [C6]
> FROM ( SELECT
> [Project14].[MediaId] AS [MediaId],
> [Project14].[MediaTypeId] AS
> [MediaTypeId],
> [Project14].[Artist] AS [Artist],
> [Project14].[Title] AS [Title],
> [Project14].[Album] AS [Album],
> [Project14].[MediaCategoryId] AS
> [MediaCategoryId],
> [Project14].[FileName] AS [FileName],
> [Project14].[Preference] AS [Preference],
> [Project14].[Length] AS [Length],
> [Project14].[DownloadUrl] AS
> [DownloadUrl],
> [Project14].[BuyUrl] AS [BuyUrl],
> [Project14].[C1] AS [C1],
> [Project14].[C2] AS [C2],
> [Project14].[C3] AS [C3],
> [Project14].[C4] AS [C4],
> [Project14].[C5] AS [C5]
> FROM ( SELECT
> [Project12].[MediaId] AS [MediaId],
> [Project12].[MediaTypeId] AS
> [MediaTypeId],
> [Project12].[Artist] AS [Artist],
> [Project12].[Title] AS [Title],
> [Project12].[Album] AS [Album],
> [Project12].[MediaCategoryId] AS
> [MediaCategoryId],
> [Project12].[FileName] AS [FileName],
> [Project12].[Preference] AS
> [Preference],
> [Project12].[Length] AS [Length],
> [Project12].[DownloadUrl] AS
> [DownloadUrl],
> [Project12].[BuyUrl] AS [BuyUrl],
> [Project12].[C1] AS [C1],
> [Project12].[C2] AS [C2],
> [Project12].[C3] AS [C3],
> [Project12].[C4] AS [C4],
> (SELECT
> ([Extent10].[Height] *
> [Extent10].[Depth]) AS [C1]
> FROM [MediaRatings] AS
> [Extent10]
> INNER JOIN [RatingCategories] AS
> [Extent11] ON [Extent10].[RatingId] = [Extent11].[RatingId]
> WHERE ([Project12].[MediaId] =
> [Extent10].[MediaId]) AND ('Spiritual Masculine' = [Extent11].[Name]) LIMIT
> 1) AS [C5]
> FROM ( SELECT
> [Project11].[MediaId] AS
> [MediaId],
> [Project11].[MediaTypeId] AS
> [MediaTypeId],
> [Project11].[Artist] AS [Artist],
> [Project11].[Title] AS [Title],
> [Project11].[Album] AS [Album],
> [Project11].[MediaCategoryId] AS
> [MediaCategoryId],
> [Project11].[FileName] AS
> [FileName],
> [Project11].[Preference] AS
> [Preference],
> [Project11].[Length] AS [Length],
> [Project11].[DownloadUrl] AS
> [DownloadUrl],
> [Project11].[BuyUrl] AS [BuyUrl],
> [Project11].[C1] AS [C1],
> [Project11].[C2] AS [C2],
> [Project11].[C3] AS [C3],
> [Project11].[C4] AS [C4]
> FROM ( SELECT
> [Project9].[MediaId] AS
> [MediaId],
> [Project9].[MediaTypeId] AS
> [MediaTypeId],
> [Project9].[Artist] AS
> [Artist],
> [Project9].[Title] AS
> [Title],
> [Project9].[Album] AS
> [Album],
> [Project9].[MediaCategoryId]
> AS [MediaCategoryId],
> [Project9].[FileName] AS
> [FileName],
> [Project9].[Preference] AS
> [Preference],
> [Project9].[Length] AS
> [Length],
> [Project9].[DownloadUrl] AS
> [DownloadUrl],
> [Project9].[BuyUrl] AS
> [BuyUrl],
> [Project9].[C1] AS [C1],
> [Project9].[C2] AS [C2],
> [Project9].[C3] AS [C3],
> (SELECT
> ([Extent8].[Height] *
> [Extent8].[Depth]) AS [C1]
> FROM [MediaRatings] AS
> [Extent8]
> INNER JOIN
> [RatingCategories] AS [Extent9] ON [Extent8].[RatingId] = [Extent9].[RatingId]
> WHERE
> ([Project9].[MediaId] = [Extent8].[MediaId]) AND ('Emotional Feminine' =
> [Extent9].[Name]) LIMIT 1) AS [C4]
> FROM ( SELECT
> [Project8].[MediaId] AS
> [MediaId],
> [Project8].[MediaTypeId]
> AS [MediaTypeId],
> [Project8].[Artist] AS
> [Artist],
> [Project8].[Title] AS
> [Title],
> [Project8].[Album] AS
> [Album],
>
> [Project8].[MediaCategoryId] AS [MediaCategoryId],
> [Project8].[FileName] AS
> [FileName],
> [Project8].[Preference]
> AS [Preference],
> [Project8].[Length] AS
> [Length],
> [Project8].[DownloadUrl]
> AS [DownloadUrl],
> [Project8].[BuyUrl] AS
> [BuyUrl],
> [Project8].[C1] AS [C1],
> [Project8].[C2] AS [C2],
> [Project8].[C3] AS [C3]
> FROM ( SELECT
> [Project6].[MediaId]
> AS [MediaId],
>
> [Project6].[MediaTypeId] AS [MediaTypeId],
> [Project6].[Artist]
> AS [Artist],
> [Project6].[Title]
> AS [Title],
> [Project6].[Album]
> AS [Album],
>
> [Project6].[MediaCategoryId] AS [MediaCategoryId],
>
> [Project6].[FileName] AS [FileName],
>
> [Project6].[Preference] AS [Preference],
> [Project6].[Length]
> AS [Length],
>
> [Project6].[DownloadUrl] AS [DownloadUrl],
> [Project6].[BuyUrl]
> AS [BuyUrl],
> [Project6].[C1] AS
> [C1],
> [Project6].[C2] AS
> [C2],
> (SELECT
>
> ([Extent6].[Height] * [Extent6].[Depth]) AS [C1]
> FROM
> [MediaRatings] AS [Extent6]
> INNER JOIN
> [RatingCategories] AS [Extent7] ON [Extent6].[RatingId] = [Extent7].[RatingId]
> WHERE
> ([Project6].[MediaId] = [Extent6].[MediaId]) AND ('Emotional Masculine' =
> [Extent7].[Name]) LIMIT 1) AS [C3]
> FROM ( SELECT
>
> [Project5].[MediaId] AS [MediaId],
>
> [Project5].[MediaTypeId] AS [MediaTypeId],
>
> [Project5].[Artist] AS [Artist],
>
> [Project5].[Title] AS [Title],
>
> [Project5].[Album] AS [Album],
>
> [Project5].[MediaCategoryId] AS [MediaCategoryId],
>
> [Project5].[FileName] AS [FileName],
>
> [Project5].[Preference] AS [Preference],
>
> [Project5].[Length] AS [Length],
>
> [Project5].[DownloadUrl] AS [DownloadUrl],
>
> [Project5].[BuyUrl] AS [BuyUrl],
> [Project5].[C1]
> AS [C1],
> [Project5].[C2]
> AS [C2]
> FROM ( SELECT
>
> [Project3].[MediaId] AS [MediaId],
>
> [Project3].[MediaTypeId] AS [MediaTypeId],
>
> [Project3].[Artist] AS [Artist],
>
> [Project3].[Title] AS [Title],
>
> [Project3].[Album] AS [Album],
>
> [Project3].[MediaCategoryId] AS [MediaCategoryId],
>
> [Project3].[FileName] AS [FileName],
>
> [Project3].[Preference] AS [Preference],
>
> [Project3].[Length] AS [Length],
>
> [Project3].[DownloadUrl] AS [DownloadUrl],
>
> [Project3].[BuyUrl] AS [BuyUrl],
>
> [Project3].[C1] AS [C1],
> (SELECT
>
> ([Extent4].[Height] * [Extent4].[Depth]) AS [C1]
> FROM
> [MediaRatings] AS [Extent4]
> INNER
> JOIN [RatingCategories] AS [Extent5] ON [Extent4].[RatingId] =
> [Extent5].[RatingId]
> WHERE
> ([Project3].[MediaId] = [Extent4].[MediaId]) AND ('Physical Feminine' =
> [Extent5].[Name]) LIMIT 1) AS [C2]
> FROM ( SELECT
>
> [Project2].[MediaId] AS [MediaId],
>
> [Project2].[MediaTypeId] AS [MediaTypeId],
>
> [Project2].[Artist] AS [Artist],
>
> [Project2].[Title] AS [Title],
>
> [Project2].[Album] AS [Album],
>
> [Project2].[MediaCategoryId] AS [MediaCategoryId],
>
> [Project2].[FileName] AS [FileName],
>
> [Project2].[Preference] AS [Preference],
>
> [Project2].[Length] AS [Length],
>
> [Project2].[DownloadUrl] AS [DownloadUrl],
>
> [Project2].[BuyUrl] AS [BuyUrl],
>
> [Project2].[C1] AS [C1]
> FROM (
> SELECT
>
> [Extent1].[MediaId] AS [MediaId],
>
> [Extent1].[MediaTypeId] AS [MediaTypeId],
>
> [Extent1].[Artist] AS [Artist],
>
> [Extent1].[Title] AS [Title],
>
> [Extent1].[Album] AS [Album],
>
> [Extent1].[MediaCategoryId] AS [MediaCategoryId],
>
> [Extent1].[FileName] AS [FileName],
>
> [Extent1].[Preference] AS [Preference],
>
> [Extent1].[Length] AS [Length],
>
> [Extent1].[DownloadUrl] AS [DownloadUrl],
>
> [Extent1].[BuyUrl] AS [BuyUrl],
>
> (SELECT
>
> ([Extent2].[Height] * [Extent2].[Depth]) AS [C1]
>
> FROM [MediaRatings] AS [Extent2]
>
> INNER JOIN [RatingCategories] AS [Extent3] ON [Extent2].[RatingId] =
> [Extent3].[RatingId]
>
> WHERE ([Extent1].[MediaId] = [Extent2].[MediaId]) AND ('Physical Masculine' =
> [Extent3].[Name]) LIMIT 1) AS [C1]
> FROM
> [Media] AS [Extent1]
>
> WHERE [Extent1].[MediaTypeId] = 0
> ) AS
> [Project2]
> ) AS
> [Project3]
> ) AS [Project5]
> ) AS [Project6]
> ) AS [Project8]
> ) AS [Project9]
> ) AS [Project11]
> ) AS [Project12]
> ) AS [Project14]
> ) AS [Project15]
> ) AS [Project17]
> ) AS [Project18]
> ) AS [Project20]
> ) AS [Project21]
> ) AS [Project23]
> ) AS [Project24]
> ) AS [Project26]
> ) AS [Project27]
> ORDER BY [Project27].[C4] DESC, [Project27].[Artist] ASC, [Project27].[Title]
> ASC
>
>
>
Maybe you should first add column '[C1]' to the table '[Media]'
Because the 'innermost query', which seems to get repeated a couple of
times, would get simpler.....
Where the 'inner most query' is:
SELECT
[Extent1].[MediaId] AS [MediaId],
[Extent1].[MediaTypeId] AS [MediaTypeId],
[Extent1].[Artist] AS [Artist],
[Extent1].[Title] AS [Title],
[Extent1].[Album] AS [Album],
[Extent1].[MediaCategoryId] AS [MediaCategoryId],
[Extent1].[FileName] AS [FileName],
[Extent1].[Preference] AS [Preference],
[Extent1].[Length] AS [Length],
[Extent1].[DownloadUrl] AS [DownloadUrl],
[Extent1].[BuyUrl] AS [BuyUrl],
(SELECT
([Extent2].[Height] * [Extent2].[Depth]) AS [C1]
FROM [MediaRatings] AS [Extent2]
INNER JOIN [RatingCategories] AS [Extent3] ON [Extent2].[RatingId] =
[Extent3].[RatingId]
WHERE ([Extent1].[MediaId] = [Extent2].[MediaId]) AND ('Physical
Masculine' = [Extent3].[Name]) LIMIT 1) AS [C1]
FROM [Media] AS [Extent1]
WHERE [Extent1].[MediaTypeId] = 0
And simplified:
SELECT
[Extent1].[MediaId] AS [MediaId],
[Extent1].[MediaTypeId] AS [MediaTypeId],
[Extent1].[Artist] AS [Artist],
[Extent1].[Title] AS [Title],
[Extent1].[Album] AS [Album],
[Extent1].[MediaCategoryId] AS [MediaCategoryId],
[Extent1].[FileName] AS [FileName],
[Extent1].[Preference] AS [Preference],
[Extent1].[Length] AS [Length],
[Extent1].[DownloadUrl] AS [DownloadUrl],
[Extent1].[BuyUrl] AS [BuyUrl],
[C1]
FROM [Media] AS [Extent1]
WHERE [Extent1].[MediaTypeId] = 0
I know that this might 'violate' some normalization rules....
but, they are not invented to make life easy ;)
