SQL incorrect syntax near '('.

Question

So my query looks like this
select
tbl_3_1_PolygonDetails.(Pit)+ '_'+(SHOTID)
as BlastName
,tbl_3_1_PolygonDetails.SUBSTRING ( (Pit)+ ''+(SHOTID), 1 , LEN((Pit)+ ''+(SHOTID))-1 ) Blast_ID
,tbl_3_1_PolygonDetails.Load_Date from tbl_3_1_PolygonDetails right join tbl_2_1_WencoData
on
tbl_3_1_PolygonDetails.SUBSTRING ( (Pit)+ ''+(SHOTID), 1 , LEN((Pit)+ ''+(SHOTID) -1 ) Blast_ID=tbl_2_1_WencoData.Blast_ID

Answer 1

So my query looks like this
tbl_3_1_PolygonDetails.(Pit)

Is (Pit) really the column name, including round brackets ( ) ? The you have to set the column name in square brackest [ ] to get a valid object qualifier like

select tbl_3_1_PolygonDetails.[(Pit)]  

Better don't use special characters for object names.

Answer 2

What you posted is not valid Microsoft TSQL statements. You cannot surround column names with () in TSQL or ANSI SQL.

Answer 3

Hi @sylvia ,

Try this code:

select  
tbl_3_1_PolygonDetails.Pit+ ''+SHOTID  
as BlastName  
,SUBSTRING(tbl_3_1_PolygonDetails. Pit+ ''+SHOTID, 1 , LEN(Pit+ ''+SHOTID)-1 ) Blast_ID  
,tbl_3_1_PolygonDetails.Load_Date from tbl_3_1_PolygonDetails right join tbl_2_1_WencoData  
on  
SUBSTRING(tbl_3_1_PolygonDetails.  Pit+ ''+SHOTID, 1 , LEN(Pit+ '_'+SHOTID) -1 ) =tbl_2_1_WencoData.Blast_ID  

Best regards
Niko

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".