JOIN query syntax problem

[Rousseau10]

query syntax problem using DTS wizard query builder

the query builder generated all but the last 3 lines. I noticed it did not join the tables so I inserted the Join code.

select "elgemp"."eegpno", "elgnot"."eunmbr", "elgnot"."eudpcd", "elgnot"."eucatg", "elgnot"."eudpfl", "elgnot"."euflag", "elgnot"."euoper_1", "elgnot"."euoper_2", "elgnot"."euoper_3", "elgnot"."euoper_4", "elgnot"."euoper_5", "elgnot"."euoper_6", "elgnot"."euoper_7", "elgnot"."euoper_8", "elgnot"."euoper_9", "elgnot"."euoper_10", "elgnot"."eudate_1", "elgnot"."eudate_2", "elgnot"."eudate_3", "elgnot"."eudate_4", "elgnot"."eudate_5", "elgnot"."eudate_6", "elgnot"."eudate_7", "elgnot"."eudate_8", "elgnot"."eudate_9", "elgnot"."eudate_10", "elgnot"."eucmmt_1", "elgnot"."eucmmt_2", "elgnot"."eucmmt_3", "elgnot"."eucmmt_4", "elgnot"."eucmmt_5", "elgnot"."eucmmt_6", "elgnot"."eucmmt_7", "elgnot"."eucmmt_8", "elgnot"."eucmmt_9", "elgnot"."eucmmt_10"
from "elgemp" INNER JOIN "elgnot" ON "elgemp"."eenmbr"="elgnot"."eunmbr"
order by "elgemp"."eegpno"

TANX!

I was the first American Soldier to put a basketball hoop up in Iraq, only to have it stolen by a soldier from different camp. Newbee - Adam

 

[Rousseau10]

well it wrapped here so I inserted the last 2 lines!

I was the first American Soldier to put a basketball hoop up in Iraq, only to have it stolen by a soldier from different camp. Newbee - Adam

 

[GShen]

What problem are you having? I notice that the 1st field in your select is "elgemp"."eegpno" and the field you are joining on is "elgemp"."eenmbr". Is that what you want?

Remember when... everything worked and there was a reason for it?

 

[Rousseau10]

yes I have never used query builder and was not sure if I needed to select what I need to join on but not see.
I need the elgemp"."eegpno" to be visibible in output it is the group number for a company. But the join column in the elgemp table is "elgemp"."eenmbr" but does not need to be visible in output. I did try it in the select to no availe, same syntax error.

The error message says:...from "elgemp" INNER JOIN <<<???>>> "elgnot" ON "elg

context: error calling Prepare on the command text




I was the first American Soldier to put a basketball hoop up in Iraq, only to have it stolen by a soldier from different camp. Newbee - Adam

 

[GShen]

Can you run the select in Query Analyzer? Does it work?
Just cut and paste it in. Also, since it looks like all your fields are unique, you can get rid of all those double quotes and prefixes for the table names. They kind of muddy the water. Something more like the following:

SELECT eegpno,
rest of fields
FROM elgemp
INNER JOIN elgnot ON
eenmbr = eunmbr
ORDER BY
eegpno

Remember when... everything worked and there was a reason for it?

 

[Rousseau10]

can I do that since that, I am using the Query Builder utility from the dts wizard, the datasource is not sql tables, they are old flat files made to resemble tables?

I was the first American Soldier to put a basketball hoop up in Iraq, only to have it stolen by a soldier from different camp. Newbee - Adam

 

[GShen]

I thought these were all SQL tables. Are you importing the flat files to SQL tables?

Remember when... everything worked and there was a reason for it?

 

[Rousseau10]

no, I am using dts to transfer flat files made to resemble tables into excel

I was the first American Soldier to put a basketball hoop up in Iraq, only to have it stolen by a soldier from different camp. Newbee - Adam

 

[GShen]

Hi,
I have have never used DTS to create an excel table but how in the world would you perform an inner join on a table which is not an sql table? Sorry I could not be of more help but I am perplexed at your statement and what you are trying to do.

Remember when... everything worked and there was a reason for it?

 

[Rousseau10]

I all started with Access 2000. Here at work we Link to external tables using ODBC (relativity software driver)to our corporate database, which I understand are just flat files somehow organized to be a reational database. So we have for the most part been treating it as a relational database. Each flat file a table. We use complex joins to query this database.
But I have been learning SQL server and was trying to make a simple join and pull a table(file) down from the corporate db. Access was taking so DAng long I said heck I can just use DTS and choose the ODBC (relativity driver) as my source and an excel file as my target. Only prob is I am having problem with my query which I have succesfully executed on the tables(files) from access.

Tanks

I was the first American Soldier to put a basketball hoop up in Iraq, only to have it stolen by a soldier from different camp. Newbee - Adam

 

[GShen]

Sorry,
I am read through all your posts again and I am still confused. You said they were flat files but then you said you can querry the tables from ACCESS. I will assume they are SQL tables from what I gather. If that is the case, yes you run the SQL code from Query Analyzer. It will spit back proper error messages. Try running it without the join 1st with 1 field in the select and then add the join and then add the rest of the code. You can then paste it all back into the DTS.

Remember when... everything worked and there was a reason for it?

 

[Rousseau10]

No the linked tables are not on an sql server.

OK forget the flat file part becuase for all intents and purposes our IT department can link to these tables and treat the as if it was a relational db ( iwas just told that in actual truth they are just flst files made to resemble a rdbs somehow).

This exact query works in Access but very slow, that is where I stole the syntax from, access sql view.

And since it is not an sql table, I cannot test in QA
TANX!!!

I was the first American Soldier to put a basketball hoop up in Iraq, only to have it stolen by a soldier from different camp. Newbee - Adam

 

[GShen]

Sorry,
Try and repost under DTS.

Remember when... everything worked and there was a reason for it?

 

[Rousseau10]

thanks for trying!!!!! I think it a provider problem

I was the first American Soldier to put a basketball hoop up in Iraq, only to have it stolen by a soldier from different camp. Newbee - Adam