CODEWITHSUNDEEP
sql
user2618988
user2618988

Reputation: 43

Table aliases in SQL query

I've got this query where I'm trying to use column identifiers (I don't know how it's called), but some appear to be working, and other not.

SELECT R1.EC_Date, R1.JO_Num, R1.EC_Intitule, R1.EC_Sens, R1.EC_RefPiece, R1.EC_Piece, R1.CG_Num, R1.EC_Reference, R1.JM_Date, R1.EC_Jour, R2.EC_Lettrage, R2.EC_Pointage
FROM ((F_COMPTEA CA INNER JOIN F_ECRITUREA EA ON CA.CA_Num = EA.CA_Num)
               INNER JOIN F_ECRITUREC EC ON EA.EC_No = EC.EC_No) R1
        INNER JOIN (
            SELECT * 
            FROM F_ECRITUREC
            WHERE (CG_Num LIKE '401%' OR CG_Num LIKE '411%')
            AND (JO_Num = 'RAN' OR JO_Num = 'ACH' OR JO_Num = 'VTE')
            AND EC_RefPiece IN (
                SELECT EC_RefPiece
                FROM F_ECRITUREA EA INNER JOIN F_ECRITUREC EC ON EA.EC_No = EC.EC_No
                WHERE EC_Jour BETWEEN '1' AND '31'
                AND JM_Date = '2013-01-07'
                AND JO_Num = 'ACH'
                AND EA.CA_Num LIKE '%')) R2
        ON R1.EC_RefPiece = R2.EC_RefPiece
WHERE EC_Jour BETWEEN '1' AND '31'
AND JM_Date = '2013-01-07'
AND JO_Num = 'ACH'
AND EA.CA_Num LIKE '%';

In that case, the CA, EA and EC identifiers are working correctly, and the JOIN is working, but when I try to add the last JOIN with the R1 AND R2 identifiers, it doesn't appears to be working, and I can't find the difference. The errors I have are : Incorrect syntax near R1/R2. I've tried to use AS, but it's not working either, so I'm sure if it's a syntax issue, or just the result of a problem elsewhere. It's MS SQL by the way.

Any help would be much appreciated, thanks.

Upvotes: 0

Views: 125

Answers (2)

JsonStatham
JsonStatham

Reputation: 10364

SELECT
R1.EC_Date,
R1.JO_Num,
R1.EC_Intitule,
R1.EC_Sens,
R1.EC_RefPiece,
R1.EC_Piece,
R1.CG_Num,
R1.EC_Reference,
R1.JM_Date,
R1.EC_Jour,

R2.EC_Lettrage,
R2.EC_Pointage

FROM

(SELECT *
FROM
F_COMPTEA 
INNER JOIN F_ECRITUREA EA
ON CA.CA_Num = EA.CA_Num
INNER JOIN F_ECRITUREC EC
ON EA.EC_No = EC.EC_No
) AS R1


INNER JOIN
(
SELECT * 
FROM
F_ECRITUREC
WHERE
(CG_Num LIKE '401%' OR CG_Num LIKE '411%')
AND (JO_Num = 'RAN' OR JO_Num = 'ACH' OR JO_Num = 'VTE')
AND EC_RefPiece IN (SELECT EC_RefPiece FROM F_ECRITUREA EA INNER JOIN F_ECRITUREC EC ON EA.EC_No = EC.EC_No
WHERE EC_Jour BETWEEN '1' AND '31'
AND JM_Date = '2013-01-07'
AND JO_Num = 'ACH'
AND EA.CA_Num LIKE '%')

) AS R2

ON R1.EC_RefPiece = R2.EC_RefPiece

WHERE 
R2.EC_Jour BETWEEN '1' AND '31'
AND R2.JM_Date = '2013-01-07'
AND R2.JO_Num = 'ACH'
--THERE IS NO CONDITION WITHIN THIS LIKE?
AND R2.CA_Num LIKE '%'

Upvotes: 0

peterm
peterm

Reputation: 92785

SELECT clause is missing for subquery R1. And you probably no longer need WHERE conditions in your outer select. You applied them in R2. But for sure, you're no longer can reference EA columns in outer select. Only R1 and R2 aliases available in your outer query.

That being said I believe your query should look something like this

SELECT R1.EC_Date, 
       R1.JO_Num, 
       R1.EC_Intitule, 
       R1.EC_Sens, 
       R1.EC_RefPiece, 
       R1.EC_Piece, 
       R1.CG_Num, 
       R1.EC_Reference, 
       R1.JM_Date, 
       R1.EC_Jour, 
       R2.EC_Lettrage, 
       R2.EC_Pointage
  FROM 
(
    SELECT * -- you has been missing SELECT here
      FROM F_COMPTEA CA JOIN F_ECRITUREA EA 
        ON CA.CA_Num = EA.CA_Num JOIN F_ECRITUREC EC 
        ON EA.EC_No = EC.EC_No
) R1 JOIN 
(
    SELECT * 
      FROM F_ECRITUREC
     WHERE (CG_Num LIKE '401%' OR CG_Num LIKE '411%')
       AND (JO_Num = 'RAN' OR JO_Num = 'ACH' OR JO_Num = 'VTE')
       AND EC_RefPiece IN 
       (
            SELECT EC_RefPiece
              FROM F_ECRITUREA EA INNER JOIN F_ECRITUREC EC ON EA.EC_No = EC.EC_No
             WHERE EC_Jour BETWEEN '1' AND '31'
               AND JM_Date = '2013-01-07'
               AND JO_Num = 'ACH'
               AND EA.CA_Num LIKE '%'
        )
) R2 ON R1.EC_RefPiece = R2.EC_RefPiece
-- WHERE EC_Jour BETWEEN '1' AND '31'
--   AND JM_Date = '2013-01-07'
--   AND JO_Num = 'ACH'
--   AND EA.CA_Num LIKE '%' you're no longer can reference `EA` columns in outer select. Only R1 and R2 aliases available in your outer query

Upvotes: 1

Related Questions