Question VS2013 Table Adapter Syntax Error: Expecting identifier or quoted identifier

[dsk96m]

If I am posting this is the wrong area, I apologize.

I am trying to add a table adapter to one of my datasets. The dataset has oracle as its datasource using ODP.NET. When I put in the query (which works elsewhere) I get this error (Syntax Error: Expecting identifier or quoted identifier). The query has left outer joins in it. If i go to query builder, it then adds stuff like { oj to the query. I knew there was an issue with previous visual studio versions, but for inner joins, but I thought they were fixed. How can I get this to work. I am banging my head against the wall on this one. Couldnt find an answer searching.

SELECT PERS.PERS_ID,
       PERS.PERS_LAST_NM,
       PERS.PERS_FIRST_NM,
       PERS.PERS_MIDDLE_INIT,
       PERS.PERS_MIDDLE_INIT,
       PERS.PERS_PREFERRED_NM,
       PERS.PERS_EMPLOYEE_NO,
       PERS.PERS_EMPLOYEE_TYP,
       PERS.PERS_STAT,
       PERS.PERS_LENT_CD                        AS PERS_ENTITY,
       PERS.PERS_TYP,
       PERS.PERS_MSTP_ID,
       PERS.PERS_BLDG_ID,
       PERS.PERS_TITLE_INIT,
       PERS.PERS_PERS_ID,
       PERS.PERS_COCC_ID,
       PERS.PERS_IORG_CD,
       PERS.PERS_CORPORATE_ID,
       CONCAT('(', CONCAT(PERS.PERS_PHONE_AREA_CD, CONCAT(')',CONCAT(PERS.PERS_PHONE_EXCH_NO,CONCAT('-',PERS.PERS_PHONE_EXTN_NO))))) AS PERS_PHONE_NO,
       UPPER(EMAIL.ELID_LONG_USERID)            AS EMPL_EMAIL,
       UPPER(USERID.ELID_USERID)                AS EMPL_USERID
FROM   CDAS.TDWHPERS PERS
       LEFT OUTER JOIN (SELECT ELID_PERS_ID, 
                               ELID_LONG_USERID
                        FROM   CDAS.TDWHELID
                        WHERE  ( ELID_EICT_CD = '0010' )
                               AND ( ELID_OPEN_IND = 'Y' )) EMAIL
                    ON PERS.PERS_ID = EMAIL.ELID_PERS_ID
       LEFT OUTER JOIN (SELECT ELID_PERS_ID,
                               ELID_USERID
                        FROM   CDAS.TDWHELID TDWHELID_1
                        WHERE  ( ELID_EICT_CD = '9100' )
                               AND ( ELID_OPEN_IND = 'Y' )) USERID
                    ON PERS.PERS_ID = USERID.ELID_PERS_ID
WHERE  ( PERS.PERS_STAT <> 'INACTIVE' )
       AND ( PERS.PERS_STAT <> 'UNKNOWN' )
ORDER  BY PERS.PERS_LAST_NM

 

[dsk96m]

Can anyone help, I still cant figure this out.

 

[jmcilhinney]

It's hard to say where the issue might be in what is a fairly complex query. Nothing jumps out on first perusal. What you should ALWAYS do in such cases is start with the simplest query you can and then build it up, piece by piece, to your final query and then you can see exactly what changed when it broke. Even if you are still unable to see what's wrong, at least you can provide us with before and after copies of the code and then we don't have to trawl through everything with no idea what we're looking for.

 

[dsk96m]

The query works just fine when run other places, just not when using the tableadapter wizard in visual studio.

 

[jmcilhinney]

The query works just fine when run other places, just not when using the tableadapter wizard in visual studio.

OK. Now do what I said in my previous post.

 

[dsk96m]

its when i add in either left join

 

[jmcilhinney]

If you're going to make no effort here then why should we? I said in an earlier post:

Even if you are still unable to see what's wrong, at least you can provide us with before and after copies of the code and then we don't have to trawl through everything with no idea what we're looking for.

I'm not seeing where you've done that. Post the simplest before and after code that you can that demonstrates the issue.

 

[dsk96m]

Didnt know stating that adding the left joins wasnt enough. So this works, when i remove the left joins and the last two items from the select. Once I add in either left join, i get the error.

It isnt a problem with the SQL, it is an issue with how Visual Studio is trying to parse it, I believe.

SELECT PERS.PERS_ID,
       PERS.PERS_LAST_NM,
       PERS.PERS_FIRST_NM,
       PERS.PERS_MIDDLE_INIT,
       PERS.PERS_MIDDLE_INIT,
       PERS.PERS_PREFERRED_NM,
       PERS.PERS_EMPLOYEE_NO,
       PERS.PERS_EMPLOYEE_TYP,
       PERS.PERS_STAT,
       PERS.PERS_LENT_CD                        AS PERS_ENTITY,
       PERS.PERS_TYP,
       PERS.PERS_MSTP_ID,
       PERS.PERS_BLDG_ID,
       PERS.PERS_TITLE_INIT,
       PERS.PERS_PERS_ID,
       PERS.PERS_COCC_ID,
       PERS.PERS_IORG_CD,
       PERS.PERS_CORPORATE_ID,
       CONCAT('(', CONCAT(PERS.PERS_PHONE_AREA_CD, CONCAT(')',CONCAT(PERS.PERS_PHONE_EXCH_NO,CONCAT('-',PERS.PERS_PHONE_EXTN_NO))))) AS PERS_PHONE_NO
FROM   CDAS.TDWHPERS PERS
WHERE  ( PERS.PERS_STAT <> 'INACTIVE' )
       AND ( PERS.PERS_STAT <> 'UNKNOWN' )
ORDER  BY PERS.PERS_LAST_NM

 

[cjard]

Bit of an odd way to write a query, but try doing a simpler one in the tableadapter wizard (one that selects all the columns you want, including dummies for any columsn coming from a join you omit) and then directly pasting the complex one into the CommandText property of the select command on the TA; it should bypass the designer's attempts to parse the query text. You can also paste it into the XML behind, though you might want to use CDATA tags to prevent not-equal <> being picked up as xml.. Either that or use !=

Your query could perhaps be written more normally as:

SELECT PERS.PERS_ID,
       PERS.PERS_LAST_NM,
       PERS.PERS_FIRST_NM,
       PERS.PERS_MIDDLE_INIT,
       PERS.PERS_MIDDLE_INIT,
       PERS.PERS_PREFERRED_NM,
       PERS.PERS_EMPLOYEE_NO,
       PERS.PERS_EMPLOYEE_TYP,
       PERS.PERS_STAT,
       PERS.PERS_LENT_CD                        AS PERS_ENTITY,
       PERS.PERS_TYP,
       PERS.PERS_MSTP_ID,
       PERS.PERS_BLDG_ID,
       PERS.PERS_TITLE_INIT,
       PERS.PERS_PERS_ID,
       PERS.PERS_COCC_ID,
       PERS.PERS_IORG_CD,
       PERS.PERS_CORPORATE_ID,
       CONCAT('(', CONCAT(PERS.PERS_PHONE_AREA_CD, CONCAT(')',CONCAT(PERS.PERS_PHONE_EXCH_NO,CONCAT('-',PERS.PERS_PHONE_EXTN_NO))))) AS PERS_PHONE_NO,
       UPPER(tdwe.ELID_LONG_USERID)            AS EMPL_EMAIL,
       UPPER(tdwu.ELID_USERID)                AS EMPL_USERID
FROM   CDAS.TDWHPERS PERS

       LEFT OUTER JOIN 
       CDAS.TDWHELID tdwe
       ON 
           tdwe.ELID_EICT_CD = '0010' AND
           tdwe.ELID_OPEN_IND = 'Y' AND
           tdwe.ELID_PERS_ID = PERS.PERS_ID

       LEFT OUTER JOIN
       CDAS.TDWHELID tdwu
       ON
           tdwu.ELID_EICT_CD = '9100' AND 
           tdwu.ELID_OPEN_IND = 'Y' AND
           tdwu.ELID_PERS_ID PERS.PERS_ID

WHERE  ( PERS.PERS_STAT <> 'INACTIVE' )
       AND ( PERS.PERS_STAT <> 'UNKNOWN' )
ORDER  BY PERS.PERS_LAST_NM

oh, and those CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT())))))))))))))))) are awful, no?

use: 'My name is' ||firstname||' and i am '||age||' years old'
for something a bit more readable