Multiple joins

[zhuanyi]

Hello,
I have the following code for a multiple join:

INSERT INTO [AppendAllFields]SELECT [TreatyList].[Treaty] AS [Treaty],[MLAC 42 Treaty Xref ER].[tai treaty] AS [TreatyType],[txn 01/04].[Policy_Number]
(and more other fields from [txn 01/04] table)
FROM [txn 01/04] INNER JOIN [MLAC 42 Treaty Xref ER] ON TRIM([txn 01/04].[Policy_Number]) = TRIM([MLAC 42 Treaty Xref ER].[Polnum]) 
INNER JOIN [TreatyList] ON TRIM([MLAC 42 Treaty Xref ER].[tai treaty]) = TRIM([TreatyList].[TreatyNo]);

Basically, the txn 01/04 table has a corresponding Polnum field in the MLAC 42 table, and MLAC 42 table has a tai treaty field, which corresponds to TreatyNo in TreatyList table.

However, when I tried to run this, I got an Syntax error.

Could anyone please help?

Thanks!

 

[zhuanyi]

anyone could help me? please. Thanks!

 

[WayneRyan]

Z,

What's the definition of [AppendAllFields]? It is a table right?

I don't think the missing space in "[AppendAllFields]SELECT ..."
is a problem.

Where's the rest of the query? --> (and more other fields from [txn 01/04] table)

Also, what's the syntax error?

Need more info,

Wayne

 

[zhuanyi]

Thanks for the reply.
AppendAllFields is a table.
The screenshot of the error I have got is attached here. And the full code is here:

 INSERT INTO [AppendAllFields]SELECT [TreatyList].[Treaty] AS [Treaty],[MLAC 42 Treaty Xref ER].[tai treaty] AS [TreatyType],[txn 01/04].[Policy_Number],[txn 01/04].[dob],[txn 01/04].[Insured-pref-risk-ind],[txn 01/04].[insured-sequence-nuamber],[txn 01/04].[joint_ind],[txn 01/04].[seq no],[txn 01/04].[Lastname],[txn 01/04].[Firstname],[txn 01/04].[sex_code],[txn 01/04].[place-birth],[txn 01/04].[Residence],[txn 01/04].[Insured-Canadian-ind],[txn 01/04].[Insured-social-security],[txn 01/04].[Age-basis],[txn 01/04].[t2-prod-bonus-eligibility],[txn 01/04].[Ceding-co-control-number],[txn 01/04].[Insured-smoking-habit],[txn 01/04].[t2-insured-medical-code],[txn 01/04].[t2-insured-issue-type],[txn 01/04].[Auto-fac-code],[txn 01/04].[plan code],[txn 01/04].[coverage-risk-code],[txn 01/04].[System-processingDate],[txn 01/04].[Coverage_trans_code],[txn 01/04].[Transactiondate],[txn 01/04].[t3-reinsurance-method],[txn 01/04].[t3-ul-db-options],[txn 01/04].[Rating],[txn 01/04].[FE-1-rate],[txn 01/04].[FE-1-years-payable],[txn 01/04].[Fe-2-rate],[txn 01/04].[Fe-2-years-payable],[txn 01/04].[Smoker-rate-ind],[txn 01/04].[Issue-age],[txn 01/04].[t3-joint-issue-age],[txn 01/04].[PYD],[txn 01/04].[Attained-age],[txn 01/04].[t3-reinsurance-duration],[txn 01/04].[Direct-face-amountNew],[txn 01/04].[ReinsuranceFA1],[txn 01/04].[ReinsNAAR1],[txn 01/04].[t3-reins-premium-type],[txn 01/04].[t3-reins-premium-mode],[txn 01/04].[ReinspaidtoDate],[txn 01/04].[OriginalPolicyDate],[txn 01/04].[t3-prev-policy-number-1],[txn 01/04].[t3-prev-policy-number-2],[txn 01/04].[t3-prev-policy-number-3],[txn 01/04].[t3-maturity-ccyy],[txn 01/04].[AnnualPremium],[txn 01/04].[BaseMthlyPremium],[txn 01/04].[SUBstandardPrem],[txn 01/04].[Flatextraprem],[txn 01/04].[StdAllowance],[txn 01/04].[SBstdAllowance],[txn 01/04].[Flat extraAllowance],[txn 01/04].[PremTax],[txn 01/04].[coverage-sequence-number],[txn 01/04].[BilingType],[txn 01/04].[Mode Factor],[txn 01/04].[Embedded AD Face Amt],[txn 01/04].[Embedded WP Multiplier],[txn 01/04].[Duration Offset],[txn 01/04].[Occupation Class],[txn 01/04].[Benefit PeriodAccident],[txn 01/04].[Benefit Period Sickness],[txn 01/04].[Elimination Period],[txn 01/04].[Cost of living Rider],[txn 01/04].[PDR],[txn 01/04].[FDHR],[txn 01/04].[ACE],[txn 01/04].[AIR],[txn 01/04].[Reins co code],[txn 01/04].[SOA Identifier],[txn 01/04].[duroffset] FROM [txn 01/04]INNER JOIN [MLAC 42 Treaty Xref ER] ON TRIM([txn 01/04].[Policy_Number]) = TRIM([MLAC 42 Treaty Xref ER].[Polnum])INNER JOIN [TreatyList] ON TRIM([MLAC 42 Treaty Xref ER].[tai treaty]) = TRIM([TreatyList].[TreatyNo]);

Thank you!

Z,

What's the definition of [AppendAllFields]? It is a table right?

I don't think the missing space in "[AppendAllFields]SELECT ..."
is a problem.

Where's the rest of the query? --> (and more other fields from [txn 01/04] table)

Also, what's the syntax error?

Need more info,

Wayne

 

[WayneRyan]

Z,

Your sample code correctly has a right-parenthesis at the very end.

The error message doesn't display that.

The only other thing I can suggest is that you insert some spaces
before the words "Select" and "Inner Join".

If that doesn't work, can you post a sample?

Wayne