SQLTeam.com | Weblogs | Forums

Help with Complexed SQL Query


#1

Hi Gurus,

I have Ruby on Rails app running with microsft sql server(i know not ideal) but have the following error below, Ive copied and pasted into my development studio but can figure out what the issue is. Anyone shed some light, very frustrating at this stage.

TinyTds::Error: Incorrect syntax near 'matching_companies'.: EXEC sp_executesql N'SELECT TOP (50) [__rnt].[id], [__rnt].[name], [__rnt].[company_status_id], [__rnt].[ultimate_holding_company_id], [__rnt].[holding_company_id], [__rnt].[reg_no], [__rnt].[website], [__rnt].[email], [__rnt].[incorporation_date], [__rnt].[last_annual_return], [__rnt].[bulk_list_share_holders], [__rnt].[excluded_at], [__rnt].[symbol], [__rnt].[website_status_id], [__rnt].[bvd_dep_id_number], [__rnt].[indexing_priority], [__rnt].[email_status_id], [__rnt].[primary_company_id], [__rnt].[merged_company_id], [__rnt].[email_seperator], [__rnt].[email_reg_exp_id], [__rnt].[updated_at], [__rnt].[created_at], [__rnt].[website_photo], [__rnt].[ticker_code], [__rnt].[news_feed_code], [__rnt].[legal_form_id], [__rnt].[publicly_status_id], [__rnt].[import_status_id], [__rnt].[exchange_id], [__rnt].[reporting_basis_id], [__rnt].[statement_date], [__rnt].[currency_id], [__rnt].[recorded_shareholders], [__rnt].[recorded_subsidiaries], [__rnt].[peer_group_id], [__rnt].[former_name], [__rnt].[desc], [__rnt].[profile], [__rnt].[last_researched_at], [__rnt].[from_import], [__rnt].[researching_priority],company_matrices.fit_index, ISNULL(fit_categories.name, ''''n/a'''') fit_category_name, company_matrices.sum_of_hits FROM ( SELECT ROW_NUMBER() OVER (ORDER BY fit_category_name ASC, fit_index DESC) AS [__rn], [companies].[id], [companies].[name], [companies].[company_status_id], [companies].[ultimate_holding_company_id], [companies].[holding_company_id], [companies].[reg_no], [companies].[website], [companies].[email], [companies].[incorporation_date], [companies].[last_annual_return], [companies].[bulk_list_share_holders], [companies].[excluded_at], [companies].[symbol], [companies].[website_status_id], [companies].[bvd_dep_id_number], [companies].[indexing_priority], [companies].[email_status_id], [companies].[primary_company_id], [companies].[merged_company_id], [companies].[email_seperator], [companies].[email_reg_exp_id], [companies].[updated_at], [companies].[created_at], [companies].[website_photo], [companies].[ticker_code], [companies].[news_feed_code], [companies].[legal_form_id], [companies].[publicly_status_id], [companies].[import_status_id], [companies].[exchange_id], [companies].[reporting_basis_id], [companies].[statement_date], [companies].[currency_id], [companies].[recorded_shareholders], [companies].[recorded_subsidiaries], [companies].[peer_group_id], [companies].[former_name], [companies].[desc], [companies].[profile], [companies].[last_researched_at], [companies].[from_import], [companies].[researching_priority], company_matrices.fit_index, ISNULL(fit_categories.name, ''''n/a'''') fit_category_name, company_matrices.sum_of_hits FROM [companies] LEFT JOIN financials financials_year_1 ON financials_year_1.company_id = companies.id and DATEPART(year, financials_year_1.period_ending) = (select DATEPART(year, max(period_ending))-0 from financials f where f.company_id = companies.id) INNER JOIN matrix_item_companies ON companies.id = matrix_item_companies.company_id INNER JOIN company_matrices ON matrix_item_companies.company_id = company_matrices.company_id and matrix_item_companies.matrix_id = company_matrices.matrix_id LEFT JOIN fit_categories ON company_matrices.fit_category_id = fit_categories.id INNER JOIN (
SELECT companies.id
FROM companies
LEFT JOIN financials financials_year_1 ON financials_year_1.company_id = companies.id and DATEPART(year, financials_year_1.period_ending) = (select DATEPART(year, max(period_ending))-0 from financials f where f.company_id = companies.id) INNER JOIN matrix_item_companies ON companies.id = matrix_item_companies.company_id INNER JOIN company_matrices ON matrix_item_companies.company_id = company_matrices.company_id and matrix_item_companies.matrix_id = company_matrices.matrix_id LEFT JOIN fit_categories ON company_matrices.fit_category_id = fit_categories.id
WHERE (1=1 AND companies.company_status_id IN (1,2,3,4,5,6,7) AND matrix_item_companies.matrix_id = 206 AND matrix_item_companies.list_id IS NULL AND matrix_item_companies.short_listed IS NULL)
GROUP BY companies.id
) matching_companies ON matching_companies.id = companies.id WHERE (1=1 AND companies.company_status_id IN (1,2,3,4,5,6,7) AND matrix_item_companies.matrix_id = 206 AND matrix_item_companies.list_id IS NULL AND matrix_item_companies.short_listed IS NULL) GROUP BY [companies].[id], [companies].[name], [companies].[company_status_id], [companies].[ultimate_holding_company_id], [companies].[holding_company_id], [companies].[reg_no], [companies].[website], [companies].[email], [companies].[incorporation_date], [companies].[last_annual_return], [companies].[bulk_list_share_holders], [companies].[excluded_at], [companies].[symbol], [companies].[website_status_id], [companies].[bvd_dep_id_number], [companies].[indexing_priority], [companies].[email_status_id], [companies].[primary_company_id], [companies].[merged_company_id], [companies].[email_seperator], [companies].[email_reg_exp_id], [companies].[updated_at], [companies].[created_at], [companies].[website_photo], [companies].[ticker_code], [companies].[news_feed_code], [companies].[legal_form_id], [companies].[publicly_status_id], [companies].[import_status_id], [companies].[exchange_id], [companies].[reporting_basis_id], [companies].[statement_date], [companies].[currency_id], [companies].[recorded_shareholders], [companies].[recorded_subsidiaries], [companies].[peer_group_id], [companies].[former_name], [companies].[desc], [companies].[profile], [companies].[last_researched_at], [companies].[from_import], [companies].[researching_priority], company_matrices.fit_index, ISNULL(fit_categories.name, ''''n/a'''') fit_category_name, company_matrices.sum_of_hits, fit_category_name, fit_index ) AS [__rnt] WHERE [__rnt].[__rn] > (0) ORDER BY [__rnt].[__rn] ASC'


#2

EXEC sp_executesql N'SELECT TOP (50) [rnt].[id], [rnt].[name], [rnt].[company_status_id], [rnt].[ultimate_holding_company_id], [rnt].[holding_company_id], [rnt].[reg_no], [rnt].[website], [rnt].[email], [rnt].[incorporation_date], [rnt].[last_annual_return], [rnt].[bulk_list_share_holders], [rnt].[excluded_at], [rnt].[symbol], [rnt].[website_status_id], [rnt].[bvd_dep_id_number], [rnt].[indexing_priority], [rnt].[email_status_id], [rnt].[primary_company_id], [rnt].[merged_company_id], [rnt].[email_seperator], [rnt].[email_reg_exp_id], [rnt].[updated_at], [rnt].[created_at], [rnt].[website_photo], [rnt].[ticker_code], [rnt].[news_feed_code], [rnt].[legal_form_id], [rnt].[publicly_status_id], [rnt].[import_status_id], [rnt].[exchange_id], [rnt].[reporting_basis_id], [rnt].[statement_date], [rnt].[currency_id], [rnt].[recorded_shareholders], [rnt].[recorded_subsidiaries], [rnt].[peer_group_id], [rnt].[former_name], [rnt].[desc], [rnt].[profile], [rnt].[last_researched_at], [rnt].[from_import], [rnt].[researching_priority],company_matrices.fit_index, ISNULL(fit_categories.name, ''''n/a'''') fit_category_name, company_matrices.sum_of_hits FROM ( SELECT ROW_NUMBER() OVER (ORDER BY fit_category_name ASC, fit_index DESC) AS [_rn], [companies].[id], [companies].[name], [companies].[companystatus_id], [companies].[ultimate_holding_company_id], [companies].[holding_company_id], [companies].[reg_no], [companies].[website], [companies].[email], [companies].[incorporation_date], [companies].[last_annual_return], [companies].[bulk_list_share_holders], [companies].[excluded_at], [companies].[symbol], [companies].[website_status_id], [companies].[bvd_dep_id_number], [companies].[indexing_priority], [companies].[email_status_id], [companies].[primary_company_id], [companies].[merged_company_id], [companies].[email_seperator], [companies].[email_reg_exp_id], [companies].[updated_at], [companies].[created_at], [companies].[website_photo], [companies].[ticker_code], [companies].[news_feed_code], [companies].[legal_form_id], [companies].[publicly_status_id], [companies].[import_status_id], [companies].[exchange_id], [companies].[reporting_basis_id], [companies].[statement_date], [companies].[currency_id], [companies].[recorded_shareholders], [companies].[recorded_subsidiaries], [companies].[peer_group_id], [companies].[former_name], [companies].[desc], [companies].[profile], [companies].[last_researched_at], [companies].[from_import], [companies].[researching_priority], company_matrices.fit_index, ISNULL(fit_categories.name, ''''n/a'''') fit_category_name, company_matrices.sum_of_hits FROM [companies] LEFT JOIN financials financials_year_1 ON financials_year_1.company_id = companies.id and DATEPART(year, financials_year_1.period_ending) = (select DATEPART(year, max(period_ending))-0 from financials f where f.company_id = companies.id) INNER JOIN matrix_item_companies ON companies.id = matrix_item_companies.company_id INNER JOIN company_matrices ON matrix_item_companies.company_id = company_matrices.company_id and matrix_item_companies.matrix_id = company_matrices.matrix_id LEFT JOIN fit_categories ON company_matrices.fit_category_id = fit_categories.id INNER JOIN (
SELECT companies.id
FROM companies
LEFT JOIN financials financials_year_1 ON financials_year_1.company_id = companies.id and DATEPART(year, financials_year_1.period_ending) = (select DATEPART(year, max(period_ending))-0 from financials f where f.company_id = companies.id) INNER JOIN matrix_item_companies ON companies.id = matrix_item_companies.company_id INNER JOIN company_matrices ON matrix_item_companies.company_id = company_matrices.company_id and matrix_item_companies.matrix_id = company_matrices.matrix_id LEFT JOIN fit_categories ON company_matrices.fit_category_id = fit_categories.id
WHERE (1=1 AND companies.company_status_id IN (1,2,3,4,5,6,7) AND matrix_item_companies.matrix_id = 206 AND matrix_item_companies.list_id IS NULL AND matrix_item_companies.short_listed IS NULL)
GROUP BY companies.id
) matching_companies ON matching_companies.id = companies.id WHERE (1=1 AND companies.company_status_id IN (1,2,3,4,5,6,7) AND matrix_item_companies.matrix_id = 206 AND matrix_item_companies.list_id IS NULL AND matrix_item_companies.short_listed IS NULL) GROUP BY [companies].[id], [companies].[name], [companies].[company_status_id], [companies].[ultimate_holding_company_id], [companies].[holding_company_id], [companies].[reg_no], [companies].[website], [companies].[email], [companies].[incorporation_date], [companies].[last_annual_return], [companies].[bulk_list_share_holders], [companies].[excluded_at], [companies].[symbol], [companies].[website_status_id], [companies].[bvd_dep_id_number], [companies].[indexing_priority], [companies].[email_status_id], [companies].[primary_company_id], [companies].[merged_company_id], [companies].[email_seperator], [companies].[email_reg_exp_id], [companies].[updated_at], [companies].[created_at], [companies].[website_photo], [companies].[ticker_code], [companies].[news_feed_code], [companies].[legal_form_id], [companies].[publicly_status_id], [companies].[import_status_id], [companies].[exchange_id], [companies].[reporting_basis_id], [companies].[statement_date], [companies].[currency_id], [companies].[recorded_shareholders], [companies].[recorded_subsidiaries], [companies].[peer_group_id], [companies].[former_name], [companies].[desc], [companies].[profile], [companies].[last_researched_at], [companies].[from_import], [companies].[researching_priority], company_matrices.fit_index, ISNULL(fit_categories.name, ''''n/a''''), company_matrices.sum_of_hits, fit_category_name, fit_index ) AS [rnt] WHERE [rnt].[rn] > (0) ORDER BY [rnt].[__rn] ASC'