Using LEFT or SUBSTRING in Add Command?

[TripperDay]

I've got two views I want to link together, but the field I want to link on in one table has some extra characters, so I want to add a command like LEFT or SUBSTRING, but get an error like "Expected lexical element not found: <identifier>" or "Expected lexical element not found: FROM"

I want my SELECT statement to look like this:

SELECT LEFT("View1"."Request ID",11) AS "ReqID", "View2"."Request Number"

Then link View1 to ReqID in Command, and View2 to Request Number in Command.

 

[lbass]

So you are using one command as your datasource? Is this an Oracle database? In Oracle, you wouldn't use "as", you would just use:

SELECT LEFT("View1"."Request ID",11) "ReqID", "View2"."Request Number"
From //etc.

You might also try: {fn left("View1"."Request ID", 11)}. But you should be using this in the join condition. Please show us the entire command as you have it so far.

-LB

 

[TripperDay]

It's a SQL Server 2k database. I'm not using the command as a data source. I think I could, but first what I want to do is manipulate the data in a column so it can be used to join two tables together.

Here's the command I'm trying to use:

SELECT "JOIN_ACM_Contacts_ResApprover"."Request ID", LEFT("JOIN_ACM_Contacts_ResApprover"."Request ID",11) AS "ReqIDent"
FROM "JOIN:ACM_Contacts_ResApprover" "JOIN_ACM_Contacts_ResApprover"


If I can do that, I can join one table to the Command on "ReqIDent" and the other table to the command on "Request ID".

 

[lbass]

You have a typo in your from clause--in case this is a copy of what you are trying. Please go to the field explorer->SQL expression and just try the following to see if it works:

LEFT("JOIN_ACM_Contacts_ResApprover"."Request ID",11)

If it doesn't, try the function I showed before. If that works, substitute it into your command.

Linking commands to other tables causes the linking to occur locally and will dramatically slow your report. You would be better off adding the two tables in a command that you use as your datasource. You could then link them in the from clause like this:

from "JOIN_ACM_Contacts_ResApprover" ""JOIN_ACM_Contacts_ResApprover" inner join "table2" "table2" on
LEFT("JOIN_ACM_Contacts_ResApprover"."Request ID",11) = "table2"."field"

-LB

 

[TripperDay]

I got the same error message, and I realized it may be because we're using some special funky driver. Could that be it?

 

[lbass]

Yes.

-LB