Linking two different tables using Add Command

[ChrisLC]

thread767-1588161

I tried this:
select table1.field1, table2.field1
from table1
inner join table2 on
left(table1.field1,4) = table2.field1

And this:
select table1.field1, table1.field2, table2.field1
from table1
inner join table2 on
mid(table1.acct,4) = table2.acct

And other variations of the above and still get "Failed to Open a Rowset. Unexpected extra token INNER"

Here is my actual ADD COMMAND below that gets me the error. I want the first 7 digits of "JV_ProjectMaster"."D493_NodeID" to link with "SO_SalesOrderHeader"."SalesOrderNo" Can anyone tell me how to get these to join? I have verified that both fields are strings.

SELECT "SO_SalesOrderHeader"."SalesOrderNo", "JV_ProjectMaster"."D493_NodeID"
FROM "JV_ProjectMaster" "JV_ProjectMaster", "SO_SalesOrderHeader" "SO_SalesOrderHeader"
INNER JOIN LEFT("JV_ProjectMaster"."D493_NodeID",7)="SO_SalesOrderHeader"."SalesOrderNo")

If I don't try to restrict to the first 7 digits this ADD COMMAND works (but it defeats the purpose of linking only the first 7 digits!):
SELECT "SO_SalesOrderHeader"."SalesOrderNo", "JV_ProjectMaster"."D493_NodeID"
FROM "JV_ProjectMaster" "JV_ProjectMaster", "SO_SalesOrderHeader" "SO_SalesOrderHeader"
WHERE "JV_ProjectMaster"."D493_NodeID"="SO_SalesOrderHeader"."SalesOrderNo"

 

[Turkbear]

Hi,
Your second example ( not using the INNER JOIN phrase)
should be adjustable to get what you need. try:

SELECT "SO_SalesOrderHeader"."SalesOrderNo", "JV_ProjectMaster"."D493_NodeID"
 FROM   "JV_ProjectMaster" "JV_ProjectMaster", "SO_SalesOrderHeader" "SO_SalesOrderHeader"
 WHERE  LEFT("JV_ProjectMaster"."D493_NodeID",7)="SO_SalesOrderHeader"."SalesOrderNo"

Just be sure your database uses that syntax for substrings.




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ChrisLC]

Tried your suggestion, still failed. I assume my database used that syntax since this was cut/paste after linking tables and selecting "Show SQL Query". So I'm still stuck. FYI, this is Providex database from MAS200 software.

At my wits end! Other ideas?

 

[lbass]

What was your error message when you tried TurkBear's suggestion--which should have worked? You can try:

SELECT "SO_SalesOrderHeader"."SalesOrderNo", "JV_ProjectMaster"."D493_NodeID"
FROM (
"JV_ProjectMaster" "JV_ProjectMaster"
INNER JOIN "SO_SalesOrderHeader" "SO_SalesOrderHeader" on
LEFT("JV_ProjectMaster"."D493_NodeID",7)="SO_SalesOrderHeader"."SalesOrderNo"
)

If your datasource doesn't support left(), you could try replacing:

LEFT("JV_ProjectMaster"."D493_NodeID",7)

...with:

{fn LEFT("JV_ProjectMaster"."D493_NodeID",7)}

-LB

 

[Turkbear]

Hi,
What database?

Try this variant ( AS WELL AS LB's , she is usually right)

SELECT "SO_SalesOrderHeader"."SalesOrderNo", "JV_ProjectMaster"."D493_NodeID"
 FROM   "JV_ProjectMaster" "JV_ProjectMaster", "SO_SalesOrderHeader" "SO_SalesOrderHeader"
 WHERE  SUBSTR("JV_ProjectMaster"."D493_NodeID",1,7)="SO_SalesOrderHeader"."SalesOrderNo"

To Paraphrase:"The Help you get is proportional to the Help you give.."