CODEWITHSUNDEEP
sqlcrystal-reports
user4187073
user4187073

Reputation:

joining error in a nested sql command - crystal reports

I have this code below, but I keep getting an error that says A_NUM was specified multiple times for A. 

SELECT Alleg,
       CASE 
             WHEN ALLEGCU LIKE '%F%' 
             OR ALLEGCU LIKE'%A%' THEN 'EF'
             ELSE ALLEGCU
         END,
         ALLEGCU
FROM

(SELECT *, CASE
    WHEN ISNUMERIC(SUBSTRING(LTRIM(RTRIM(ALL)), 1,1)) = 0
    THEN UPPER(LTRIM(RTRIM(SUBSTRING(ALL, CHARINDEX(' ', ALL)+1, LEN(ALL)
       - CHARINDEX(' ', ALL)))))
 ELSE ALL
                       END ALLEGCU

FROM ASSI_O 
      LEFT OUTER JOIN ALLS
      ON ASSI_O.A_NUM = ALLS.A_NUM ) A

Upvotes: 0

Views: 41

Answers (1)

radar
radar

Reputation: 13425

A_NUM column exists in both the tables ASSI_O AND ALLS and you are doing a SELECT * while doing the join.

Select only the needed columns and also prefix the table name to the column name

Changing SELECT * to SELECT ASSI_O.A_NUM as ASSI_NUM, ALLS.A_NUM as ALLS_ANUM should solve the problem

SELECT ASSI_O.A_NUM as ASSI_NUM, ALLS.A_NUM as ALLS_ANUM ...

Upvotes: 1

Related Questions