Parry
Reputation: 187
Error in a SQL Join Query for a listing page
I have an application which has a listing page with search functions which lists the records that match the query. This application is working perfectly well with MySQL. But with same data on a Windows server and MS SQL. It is giving the following error (the query is included in the error).
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Conversion failed when converting the nvarchar value 'contract_i' to data type int.
SELECT ctbl_contract_funding_info.*,
ctbl_contract_funding_mgmt.funding_manager,
ctbl_contract_workflow.id AS id1,
ctbl_contract_workflow.family_id AS main_id,
ctbl_contract_workflow.parent_type
FROM ctbl_contract_workflow
LEFT JOIN ctbl_contract_funding_info
ON ctbl_contract_funding_info.contract_id =
ctbl_contract_workflow.id
LEFT JOIN ctbl_contract_funding_mgmt
ON ctbl_contract_funding_mgmt.contract_id =
ctbl_contract_workflow.id
LEFT JOIN ctbl_contract_funding_fin
ON ctbl_contract_funding_fin.contract_id =
ctbl_contract_funding_mgmt.contract_id
ORDER BY ctbl_contract_workflow.id ASC
The table definitions are as below
/***** Object: Table [dbo].[ctbl_contract_compliance_mgmt] Script Date: 01/03/2017 14:58:54 *****/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE TABLE [dbo].[ctbl_contract_compliance_mgmt](
[id] [int] IDENTITY(1,1) NOT NULL,
[save_flag] [nvarchar](255) NULL,
[contract_id] [nvarchar](10) NULL,
[opening_meeting] [nvarchar](255) NULL,
[closing_meeting] [nvarchar](255) NULL,
[Procurement_rules] [nvarchar](max) NULL,
[budget_variance] [nvarchar](max) NULL,
[audit] [nvarchar](max) NULL,
[end_use_equipment] [nvarchar](max) NULL,
[bank_interest] [nvarchar](max) NULL,
[visibility_requiremnt] [nvarchar](max) NULL,
[special_requiremet] [nvarchar](max) NULL,
[donor_guide] [nvarchar](max) NULL,
[donor_guide_text] [nvarchar](max) NULL,
[special_requiremet_text] [nvarchar](max) NULL,
[visibility_requiremnt_text] [nvarchar](max) NULL,
[type_topic] [nvarchar](max) NULL,
[type_scope] [nvarchar](max) NULL,
[type_incident] [nvarchar](max) NULL,
[identified_through] [nvarchar](max) NULL,
[date_initital_report] [nvarchar](255) NULL,
[incident_status] [nvarchar](max) NULL,
[createdby] [int] NULL,
[createddate] [datetime] NULL,
[flag] [int] NULL,
[updatedby] [int] NULL,
[updatedtime] [datetime] NULL,
[retention_period] [nvarchar](max) NULL,
[sub-awarding-permitted] [nvarchar](max) NULL,
[sub_awarding_permitted] [nvarchar](255) NULL,
[reporting_language] [nvarchar](255) NULL,
[currency_type] [nvarchar](255) NULL,
[comments_procurement_rules] [nvarchar](255) NULL,
[comments_budget_variance] [nvarchar](255) NULL,
[comments_audit] [nvarchar](255) NULL,
[comments_end_use_equipment] [nvarchar](255) NULL,
[comments_bank_interest] [nvarchar](255) NULL,
[comments_sub_awarding_permitted] [nvarchar](255) NULL,
[comments_visibility_requiremnt] [nvarchar](255) NULL,
[comments_retention_period] [nvarchar](255) NULL,
[comments_special_requiremet] [nvarchar](255) NULL,
[comments_donor_guide] [nvarchar](255) NULL,
[special_provisions] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ctbl_contract_compliance_mgmt] ADD CONSTRAINT [DF_ctbl_contract_compliance_mgmt_flag] DEFAULT ((0)) FOR [flag]
GO
***** Object: Table [dbo].[ctbl_contract_funding_fin] Script Date: 01/03/2017 14:59:28 *****/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ctbl_contract_funding_fin](
[id] [int] IDENTITY(1,1) NOT NULL,
[save_flag] [nvarchar](255) NULL,
[contract_id] [nvarchar](10) NULL,
[funding_mechanism] [nvarchar](255) NULL,
[expense_recovery] [nvarchar](255) NULL,
[type_in_kind_donation] [nvarchar](255) NULL,
[type_financing_mechanis] [nvarchar](255) NULL,
[funding_currency] [nvarchar](255) NULL,
[funding_ceiling_currency] [nvarchar](255) NULL,
[funding_ceiling_gbp] [nvarchar](255) NULL,
[total_budget_currency] [nvarchar](255) NULL,
[total_budget_gbp] [nvarchar](255) NULL,
[total_budget_exchange_rate_date] [nvarchar](max) NULL,
[total_budget_exchange_rate] [nvarchar](255) NULL,
[modified_total_budget_currency] [nvarchar](255) NULL,
[modified_total_budget_gbp] [nvarchar](255) NULL,
[modified_budget_exchange_rate] [nvarchar](255) NULL,
[modified_total_budget] [nvarchar](255) NULL,
[obligated_amount] [nvarchar](max) NULL,
[total_obligated_amount_currency] [nvarchar](255) NULL,
[total_obligated_amount_gbp] [nvarchar](255) NULL,
[end_date_finance_period] [nvarchar](255) NULL,
[desc_partial_obligated_amount] [nvarchar](255) NULL,
[period_financed_partial_obligated_months] [nvarchar](255) NULL,
[partial_obligated_amount_currency] [nvarchar](255) NULL,
[expenditure_latest_accounts_currency] [nvarchar](255) NULL,
[expenditure_latest_closed_accounts_gbp] [nvarchar](255) NULL,
[balance_latest_closed_accounts_gbp] [nvarchar](255) NULL,
[balance_latest_closed_accounts_currency] [nvarchar](255) NULL,
[payment_mechanism] [nvarchar](255) NULL,
[periodicity] [nvarchar](255) NULL,
[payment_requested] [nvarchar](255) NULL,
[payment_requested_date] [datetime] NULL,
[payment_received_date] [datetime] NULL,
[payment_requested_amount_currency] [nvarchar](255) NULL,
[payment_received_amount_currency] [nvarchar](255) NULL,
[recovery_cost] [nvarchar](255) NULL,
[cost_recovery_cost_currency] [nvarchar](255) NULL,
[cost_recovery_cost_gbp] [nvarchar](255) NULL,
[direct_charge] [nvarchar](255) NULL,
[percentage_total_budget] [nvarchar](255) NULL,
[indirect_cost] [nvarchar](255) NULL,
[percentage_direct_cost_currency] [nvarchar](255) NULL,
[waiver_approver_position] [nvarchar](max) NULL,
[waiver_approver] [nvarchar](max) NULL,
[co_financing_requirements] [nvarchar](max) NULL,
[co_financing_amount] [nvarchar](255) NULL,
[co_financing_total_budget] [nvarchar](255) NULL,
[co_financing_conditions] [nvarchar](max) NULL,
[createdby] [int] NULL,
[createddate] [datetime] NULL,
[updatedby] [int] NULL,
[updatedtime] [datetime] NULL,
[flag] [int] NULL,
[location] [nvarchar](255) NULL,
[financed_period_date] [nvarchar](255) NULL,
[type_financing_mechanis_others] [nvarchar](max) NULL,
[periodicity_comment] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
/***** Object: Table [dbo].[ctbl_contract_funding_mgmt] Script Date: 01/03/2017 15:00:40 *****/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ctbl_contract_funding_mgmt](
[id] [int] IDENTITY(1,1) NOT NULL,
[save_flag] [nvarchar](255) NULL,
[contract_id] [nvarchar](10) NULL,
[funding_manager] [nvarchar](max) NULL,
[programme_name] [nvarchar](max) NULL,
[programme_position] [nvarchar](max) NULL,
[programme_responsibility] [nvarchar](max) NULL,
[operations_name] [nvarchar](max) NULL,
[operations_position] [nvarchar](max) NULL,
[operations_responsibility] [nvarchar](max) NULL,
[international_operations_auth_app_name] [nvarchar](max) NULL,
[international_operations_auth_app_position] [nvarchar](max) NULL,
[international_operations_auth_app_responsibility] [nvarchar](max) NULL,
[project_accountant_name] [nvarchar](max) NULL,
[project_accountant_position] [nvarchar](max) NULL,
[project_accountant_responsibility] [nvarchar](max) NULL,
[donor_compliance_name] [nvarchar](max) NULL,
[donor_compliance_position] [nvarchar](max) NULL,
[donor_compliance_responsibility] [nvarchar](max) NULL,
[donor_lead_name] [nvarchar](max) NULL,
[donor_lead_position] [nvarchar](max) NULL,
[donor_lead_responsibility] [nvarchar](max) NULL,
[focal_point_name] [nvarchar](max) NULL,
[focal_point_position] [nvarchar](max) NULL,
[focal_point_responsibility] [nvarchar](max) NULL,
[funding_officer] [nvarchar](max) NULL,
[donor_phone] [nvarchar](max) NULL,
[donor_email] [nvarchar](max) NULL,
[donor_address] [nvarchar](max) NULL,
[external_funding_contact] [nvarchar](max) NULL,
[external_funding_phone] [nvarchar](max) NULL,
[external_funding_email] [nvarchar](max) NULL,
[external_funding_address] [nvarchar](max) NULL,
[createdby] [int] NULL,
[createddate] [datetime] NULL,
[flag] [int] NULL,
[updatedby] [int] NULL,
[updatedtime] [datetime] NULL,
[sub-awarding-permitted] [nvarchar](255) NULL,
[workflow_programme_name] [nvarchar](255) NULL,
[workflow_programme_position] [nvarchar](255) NULL,
[workflow_operations_name] [nvarchar](255) NULL,
[workflow_operations_position] [nvarchar](255) NULL,
[international_workflow_operations_auth_app_name] [nvarchar](255) NULL,
[international_workflow_operations_auth_app_position] [nvarchar](255) NULL,
[workflow_project_accountant_name] [nvarchar](255) NULL,
[workflow_project_accountant_position] [nvarchar](255) NULL,
[workflow_donor_compliance_name] [nvarchar](255) NULL,
[workflow_donor_compliance_position] [nvarchar](255) NULL,
[workflow_donor_compliance_name_extra] [nvarchar](max) NULL,
[workflow_donor_compliance_position_extra] [nvarchar](max) NULL,
[workflow_signatory_name] [nvarchar](max) NULL,
[workflow_signatory_position] [nvarchar](max) NULL,
[workflow_msi_support_office_name] [nvarchar](max) NULL,
[workflow_msi_support_office_position] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ctbl_contract_funding_mgmt] ADD CONSTRAINT [DF_ctbl_contract_funding_mgmt_flag] DEFAULT ((0)) FOR [flag]
GO
/***** Object: Table [dbo].[ctbl_contract_workflow] Script Date: 01/03/2017 15:01:11 *****/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ctbl_contract_workflow](
[id] [int] IDENTITY(1,1) NOT NULL,
[record_id] [int] NULL,
[save_flag] [nvarchar](255) NULL,
[entry_id] [nvarchar](5) NULL,
[fundingstatus] [nvarchar](max) NULL,
[project_code] [nvarchar](max) NULL,
[fundingHierarchy] [nvarchar](max) NULL,
[ContractStatus] [int] NULL,
[OperationResponsible] [nvarchar](max) NULL,
[createdby] [int] NULL,
[createddate] [datetime] NULL,
[updatedby] [int] NULL,
[updatedtime] [datetime] NULL,
[flag] [int] NULL,
[parent_id] [int] NULL,
[parent_type] [nvarchar](50) NULL,
[family_id] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Upvotes: 0
Views: 62
Answers (1)
Bohemian
Reputation: 424993
Just using my psychic abilities, one of the columns called contract_id is defined as nvarchar(10) and contains the value 'contract_i', because someone accidentally coded inserted/updated a row with the literal "contract_id" when they intended to update/insert with a variable called contract_id (eg may have left off the leading $ when using PHP etc).
MySQL "converts" (coerces) text to numeric by converting all the leading numeric characters (if any) to a number, discarding the rest, eg
123abc -> 123
12ab34 -> 12
abc -> 0
So MySQL converted 'contract_i' to 0, of course not matching anything, but not giving an error either.
Upvotes: 1
Related Questions
- Issue with Join Query
- Syntax error near JOIN in SQL Server database
- Why am I getting an error when trying to join tables in my SQL Server query
- SQL COLUMN ERROR for JOIN
- Problems with Sql query join
- Incorrect syntax error in join query sql
- SQL Multiple Joins returning error 'Incorrect syntax near the keyword 'JOIN'.'
- SQL - EXCEPTION TO A JOIN STATEMENT
- SQL Query Join Issue
- Syntax Error SQL Join Statement