Left Join into Structure not working

Left Join into Structure not working

  

Hi All,

I am trying to use a SQL statements to left join 2 tables and insert them into a structure.

I can't seem to get the column to be joined to go into the structure and I keep getting a 'mismatch in column number' error message.

Code: 

SyntaxEditor Code Snippet

SyntaxEditor Code Snippet

SELECT 
    {Paragraph}.[Id]
    ,{Paragraph}.[Reg_para_Ref]
    ,{Paragraph}.[Reg_Directive]
    ,{Paragraph}.[ArticleNumber]
    ,{Paragraph}.[ParagraphNumber]
    ,{Paragraph}.[Paper]
    ,{Paragraph}.[PaperName]
    ,{Paragraph}.[Paragraph_Text]
FROM 
    {Paragraph}
WHERE 
    {Paragraph}.[Id] IN
    (
    SELECT {Paragraph_Mapping}.[ParagraphId2]
    FROM {Paragraph_Mapping}
    WHERE {Paragraph_Mapping}.[ParagraphId1]=@ParagraphId
    )
UNION
SELECT 
    {Paragraph}.[Id]
    ,{Paragraph}.[Reg_para_Ref]
    ,{Paragraph}.[Reg_Directive]
    ,{Paragraph}.[ArticleNumber]
    ,{Paragraph}.[ParagraphNumber]
    ,{Paragraph}.[Paper]
    ,{Paragraph}.[PaperName]
    ,{Paragraph}.[Paragraph_Text]
FROM 
    {Paragraph}
WHERE 
    {Paragraph}.[Id] IN
    (
    SELECT {Paragraph_Mapping}.[ParagraphId1]
    FROM {Paragraph_Mapping}
    WHERE {Paragraph_Mapping}.[ParagraphId2]=@ParagraphId
    )
LEFT JOIN (SELECT {Regulatory_Framework}.[RegulatoryFrameworkName]) on {Paragraph}.[Id]={Regulatory_Framework}.[Id]

Could you try somthing like this?

SELECT X.a, X.b, X.c, X.d, X.e, X.f, X.g, X.h, r.z2
FROM
	(
	SELECT 
		{Paragraph}.[Id] AS a
		,{Paragraph}.[Reg_para_Ref] AS b
		,{Paragraph}.[Reg_Directive] AS c
		,{Paragraph}.[ArticleNumber] AS d
		,{Paragraph}.[ParagraphNumber] AS e
		,{Paragraph}.[Paper] AS f
		,{Paragraph}.[PaperName]AS g
		,{Paragraph}.[Paragraph_Text] AS h
	FROM 
		{Paragraph}
	WHERE 
		{Paragraph}.[Id] IN
		(
		SELECT {Paragraph_Mapping}.[ParagraphId2]
		FROM {Paragraph_Mapping}
		WHERE {Paragraph_Mapping}.[ParagraphId1]=@ParagraphId
		)
	UNION
	SELECT 
		{Paragraph}.[Id] AS a
		,{Paragraph}.[Reg_para_Ref] AS b
		,{Paragraph}.[Reg_Directive] AS c
		,{Paragraph}.[ArticleNumber] AS d
		,{Paragraph}.[ParagraphNumber] AS e
		,{Paragraph}.[Paper] AS f
		,{Paragraph}.[PaperName]AS g
		,{Paragraph}.[Paragraph_Text] AS h
	FROM 
		{Paragraph}
	WHERE 
		{Paragraph}.[Id] IN
		(
		SELECT {Paragraph_Mapping}.[ParagraphId1]
		FROM {Paragraph_Mapping}
		WHERE {Paragraph_Mapping}.[ParagraphId2]=@ParagraphId
		)
	) X
LEFT JOIN 
	(SELECT {Regulatory_Framework}.[Id] AS z1, {Regulatory_Framework}.[RegulatoryFrameworkName] AS z2 FROM {Regulatory_Framework}) r 
	ON {Paragraph}.[Id]=r.z1

I'm guessing that this is a syntax problem.
Also, notice that I included the {Regulatory_Framework}.[RegulatoryFrameworkName] in the SELECT, because I think you want it in the output.

Not sure this is 100% correct, but you got the idea.

Cheers,
Eduardo Jauch

Eduardo Jauch wrote:

Could you try somthing like this?

SELECT X.a, X.b, X.c, X.d, X.e, X.f, X.g, X.h, r.z2
FROM
(
SELECT 
{Paragraph}.[Id] AS a
,{Paragraph}.[Reg_para_Ref] AS b
,{Paragraph}.[Reg_Directive] AS c
,{Paragraph}.[ArticleNumber] AS d
,{Paragraph}.[ParagraphNumber] AS e
,{Paragraph}.[Paper] AS f
,{Paragraph}.[PaperName]AS g
,{Paragraph}.[Paragraph_Text] AS h
FROM 
{Paragraph}
WHERE 
{Paragraph}.[Id] IN
(
SELECT {Paragraph_Mapping}.[ParagraphId2]
FROM {Paragraph_Mapping}
WHERE {Paragraph_Mapping}.[ParagraphId1]=@ParagraphId
)
UNION
SELECT 
{Paragraph}.[Id] AS a
,{Paragraph}.[Reg_para_Ref] AS b
,{Paragraph}.[Reg_Directive] AS c
,{Paragraph}.[ArticleNumber] AS d
,{Paragraph}.[ParagraphNumber] AS e
,{Paragraph}.[Paper] AS f
,{Paragraph}.[PaperName]AS g
,{Paragraph}.[Paragraph_Text] AS h
FROM 
{Paragraph}
WHERE 
{Paragraph}.[Id] IN
(
SELECT {Paragraph_Mapping}.[ParagraphId1]
FROM {Paragraph_Mapping}
WHERE {Paragraph_Mapping}.[ParagraphId2]=@ParagraphId
)
) X
LEFT JOIN 
(SELECT {Regulatory_Framework}.[Id] AS z1, {Regulatory_Framework}.[RegulatoryFrameworkName] AS z2 FROM {Regulatory_Framework}) r 
ON {Paragraph}.[Id]=r.z1

I'm guessing that this is a syntax problem.
Also, notice that I included the {Regulatory_Framework}.[RegulatoryFrameworkName] in the SELECT, because I think you want it in the output.

Not sure this is 100% correct, but you got the idea.

Cheers,
Eduardo Jauch


Hi Eduardo,


Thanks for your reply.

Unfortunately for this code, i get the below error:

Thanks,

James

Sorry, this is because you need to reference the Field by X, and the field must be available in the internal selects.

Eduardo Jauch wrote:

Sorry, this is because you need to reference the Field by X, and the field must be available in the internal selects.

Hi Eduardo,


Forgive me if I am being a bit dim here but I am not sure what you mean?


Thanks,

James


Solution

Hi James, you can't use an entity/attribute that is not available (than the error).

To fix it, you need to change the query I sent you previously (that contains the error) to this:


SELECT X.a, X.b, X.c, X.d, X.e, X.f, X.g, X.h, r.z2
FROM
(
SELECT 
{Paragraph}.[Id] AS a
,{Paragraph}.[Reg_para_Ref] AS b
,{Paragraph}.[Reg_Directive] AS c
,{Paragraph}.[ArticleNumber] AS d
,{Paragraph}.[ParagraphNumber] AS e
,{Paragraph}.[Paper] AS f
,{Paragraph}.[PaperName]AS g
,{Paragraph}.[Paragraph_Text] AS h
FROM 
{Paragraph}
WHERE 
{Paragraph}.[Id] IN
(
SELECT {Paragraph_Mapping}.[ParagraphId2]
FROM {Paragraph_Mapping}
WHERE {Paragraph_Mapping}.[ParagraphId1]=@ParagraphId
)
UNION
SELECT 
{Paragraph}.[Id] AS a
,{Paragraph}.[Reg_para_Ref] AS b
,{Paragraph}.[Reg_Directive] AS c
,{Paragraph}.[ArticleNumber] AS d
,{Paragraph}.[ParagraphNumber] AS e
,{Paragraph}.[Paper] AS f
,{Paragraph}.[PaperName]AS g
,{Paragraph}.[Paragraph_Text] AS h
FROM 
{Paragraph}
WHERE 
{Paragraph}.[Id] IN
(
SELECT {Paragraph_Mapping}.[ParagraphId1]
FROM {Paragraph_Mapping}
WHERE {Paragraph_Mapping}.[ParagraphId2]=@ParagraphId
)
) X
LEFT JOIN 
(SELECT {Regulatory_Framework}.[Id] AS z1, {Regulatory_Framework}.[RegulatoryFrameworkName] AS z2 FROM {Regulatory_Framework}) r 
Change this => ON {Paragraph}.[Id]=r.z1
To this => ON X.a

Cheers,
Eduardo Jauch

Solution

Eduardo Jauch wrote:

Hi James, you can't use an entity/attribute that is not available (than the error).

To fix it, you need to change the query I sent you previously (that contains the error) to this:


SELECT X.a, X.b, X.c, X.d, X.e, X.f, X.g, X.h, r.z2
FROM
(
SELECT 
{Paragraph}.[Id] AS a
,{Paragraph}.[Reg_para_Ref] AS b
,{Paragraph}.[Reg_Directive] AS c
,{Paragraph}.[ArticleNumber] AS d
,{Paragraph}.[ParagraphNumber] AS e
,{Paragraph}.[Paper] AS f
,{Paragraph}.[PaperName]AS g
,{Paragraph}.[Paragraph_Text] AS h
FROM 
{Paragraph}
WHERE 
{Paragraph}.[Id] IN
(
SELECT {Paragraph_Mapping}.[ParagraphId2]
FROM {Paragraph_Mapping}
WHERE {Paragraph_Mapping}.[ParagraphId1]=@ParagraphId
)
UNION
SELECT 
{Paragraph}.[Id] AS a
,{Paragraph}.[Reg_para_Ref] AS b
,{Paragraph}.[Reg_Directive] AS c
,{Paragraph}.[ArticleNumber] AS d
,{Paragraph}.[ParagraphNumber] AS e
,{Paragraph}.[Paper] AS f
,{Paragraph}.[PaperName]AS g
,{Paragraph}.[Paragraph_Text] AS h
FROM 
{Paragraph}
WHERE 
{Paragraph}.[Id] IN
(
SELECT {Paragraph_Mapping}.[ParagraphId1]
FROM {Paragraph_Mapping}
WHERE {Paragraph_Mapping}.[ParagraphId2]=@ParagraphId
)
) X
LEFT JOIN 
(SELECT {Regulatory_Framework}.[Id] AS z1, {Regulatory_Framework}.[RegulatoryFrameworkName] AS z2 FROM {Regulatory_Framework}) r 
Change this => ON {Paragraph}.[Id]=r.z1
To this => ON X.a

Cheers,
Eduardo Jauch


Thanks Eduardo! It is working!