oracle database: .net project sql issue

montoyam

I have a .net Framework project that is connecting to an oracle database. There seems to be an issue when I introduce a left join.

with this controller

                var response = new Editor(db, "FND_STANISLAUS_USERS as su", "USERID")
                    .Field(new Field("USERID"))
                    //.LeftJoin("FND_USER", "FND_USER.USER_NAME", "=", "su.USERID")
                    .Debug(true)
                    .Process(request)
                    .Data();
                return Json(response);

With just one table, I get this in the debug:

Query: "SELECT  "USERID" as "USERID" FROM  FND_STANISLAUS_USERS su "

and it returns data no problem.

When I add in the left join, for the .Field I then need to add the table name, so it becomes

                var response = new Editor(db, "FND_STANISLAUS_USERS as su", "USERID")
                    .Field(new Field("su.USERID"))
                    .LeftJoin("FND_USER", "FND_USER.USER_NAME", "=", "su.USERID")
                    .Debug(true)
                    .Process(request)
                    .Data();
                return Json(response);

and I see this in the debug

SELECT  "su"."USERID" as "su.USERID" FROM  FND_STANISLAUS_USERS su LEFT JOIN "FND_USER" ON "FND_USER"."USER_NAME" = "su"."USERID"

and I get the error:

"ORA-00904: "su"."USERID": invalid identifier"

If I take this sql statement to Oracle SQL Manager, I get the same error. when I strip out the quotation marks to get this:

SELECT  su.USERID
FROM  FND_STANISLAUS_USERS su 
LEFT JOIN "FND_USER" ON "FND_USER"."USER_NAME" = su.USERID

data gets returned in Oracle SQL Manager. How do I get rid of the quotes in the controller?

montoyam

wow. I don't remember why it occurred to me to try this, but I changed it to capitol 'SU' instead of lowercase and it worked!!

Please close this question as resolved.