SiddP SiddP - 5 months ago 11
SQL Question

Issue with full outer join in SQL Server

When I use below 2 queries separately I get output successfully

Query #1

DECLARE @ID VARCHAR(1000)
SET @ID = ''

SELECT @ID = @ID + CAST(int_ID AS VARCHAR(1000))
FROM MATRIX_RELATIONSHIP

SELECT COUNT(int_ID) [Count], YEAR(dt_5841_OF) [date]
FROM TRANS_IDENTIFICATIONS
WHERE smallint_209_ME = 4
AND CHARINDEX(CONVERT(VARCHAR, int_ID), ('''' + Replace((@ID),',',''',''') + '''')) > 0
GROUP BY YEAR(dt_5841_OF);


Output:

Count | year
02 2016


Query #2

SELECT
COUNT(*) [Count1], YEAR(dt_modifiedOn) [date]
FROM
MATRIX_RELATIONSHIP
GROUP BY
YEAR(dt_modifiedOn);


Output:

Count | year
02 2016


But when I use both queries to form a
full outer join
something like below:

SELECT
tab1.Count, tab2.Count1, tab1.date
FROM
(DECLARE @ID VARCHAR(1000)
SET @ID = ''

SELECT @ID = @ID+CAST(int_ID AS VARCHAR(1000))
FROM MATRIX_RELATIONSHIP

SELECT COUNT(int_ID) [Count], YEAR(dt_5841_OF) [date]
FROM TRANS_IDENTIFICATIONS
WHERE smallint_209_ME = 4
AND CHARINDEX(convert(varchar,int_ID), ('''' + Replace((@ID),',',''',''') + '''')) > 0
GROUP BY YEAR(dt_5841_OF)) tab1
FULL OUTER JOIN
(SELECT COUNT(*) [Count1], YEAR(dt_modifiedOn) [date]
FROM MATRIX_RELATIONSHIP
GROUP BY YEAR(dt_modifiedOn)) tab2 ON tab1.date = tab2.date;


I get these errors:


Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'DECLARE'.

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near ')'.

Msg 102, Level 15, State 1, Line 7

Incorrect syntax near 'tab2'.


Expected result

Count | Count1 | year
02 02 2016


EDIT

I have tried placing declare and set outside the query too but still there the issue persists

DECLARE @ID VARCHAR(1000) SET @ID=''
SELECT tab1.Count,tab2.Count1,tab1.date from
( SELECT @ID= @ID+CAST(int_ID AS VARCHAR(1000))
from MATRIX_RELATIONSHIP SELECT COUNT(int_ID)[Count],YEAR(dt_5841_OF)[date] FROM TRANS_IDENTIFICATIONS WHERE smallint_209_ME=4 and CHARINDEX(convert(varchar,int_ID), ('''' + Replace((@ID),',',''',''') + '''')) >
0 GROUP BY YEAR(dt_5841_OF)) tab1
FULL OUTER JOIN
(SELECT COUNT(*)[Count1],YEAR(dt_modifiedOn)[date]
FROM MATRIX_RELATIONSHIP GROUP BY YEAR(dt_modifiedOn)) tab2
ON tab1.date=tab2.date;


Which leads to another set of errors:


Msg 102, Level 15, State 1, Line 3

Incorrect syntax near '='.

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near ')'.

Msg 102, Level 15, State 1, Line 8

Incorrect syntax near 'tab2'.

Answer
    DECLARE  @ID VARCHAR(1000) 
    SET @ID='';

    SELECT @ID = @ID + CAST(int_ID AS VARCHAR(1000)) from MATRIX_RELATIONSHIP;

    SELECT MAX(Count) AS Count, MAX(Count1) AS Count1, date
    FROM
    (

        SELECT COUNT(int_ID)[Count], null AS [Count1], YEAR(dt_5841_OF)[date] 
        FROM TRANS_IDENTIFICATIONS 
        WHERE smallint_209_ME = 4 
        and CHARINDEX(convert(varchar,int_ID),  ('''' + Replace((@ID),',',''',''') + '''')) > 
        0 GROUP BY YEAR(dt_5841_OF)
        UNION ALL
        SELECT NULL AS [Count],  COUNT(*)[Count1],YEAR(dt_modifiedOn)[date]
        FROM MATRIX_RELATIONSHIP 
        GROUP BY YEAR(dt_modifiedOn)
    )AS temp 
    GROUP BY [date]