HELP! can't join tables in frontpage 2003

J

jackle_usmc

My query works fine if I don't use a field from frontpage.

THE CODE BELOW WORKS FINE
_____________________________________________________________________________
select distinct cod4.name, wo_number, reqst_date, wo_problem,
cod2.name, cod1.name, cod3.name
from aims.wko wo
join aims.cod cod1 on (select distinct cod1.code where
cod1.type='e')=wo.trade_emp
join aims.cod cod2 on (select distinct cod2.code where
cod2.type='a')=wo.chg_ctr
join aims.cod cod3 on (select distinct cod3.code where
cod3.type='h')=wo.facility
join aims.cod cod4 on (select cod4.code where
cod4.type='w')=wo.wo_status
where wo.tag_number='IMPAC'
order by cod4.name desc, wo.wo_number
_____________________________________________________________________________

If I use a frontpage paramenter inplace of IMPAC :):tag_number::), I
get error below

Server error: Unable to retrieve schema information from the query:

SELECT * from aims.wko wo join aims.cod cod1 on (select distinct
cod1.code

against a database using the connection string

DRIVER={SQL
Server};SERVER=home_b2;DATABASE=aimsdatabase;UID=********;PWD=********

The following error message comes from the database driver software; it
may appear in a different language depending on how the driver is
configured.
-------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax
near 'code'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)
 
J

jackle_usmc

The problem seems to be the subquery in the join. If I remove the
select statement in the join, frontpage accepts the query. For
instance:
___________________________________________________
join aims.cod cod1 on (select distinct cod1.code where
cod1.type='e')=wo.trade_emp
___________________________________________________
gives me an error BUT if I remove the select statement it runs.
============================================
join aims.cod cod1 on cod1.code=wo.trade_emp
===========================================
The problem is that I need to filter the cod1.code to only give me the
ones that are cod1.type='e' because in my code column I might have two
codes that are X but they have different types.

What I found that worked for me is
-------------------------------------------------------------------------------------
where cod1.code=wo.trade_emp and cod1.type='e'
-------------------------------------------------------------------------------------

I wanted to do that in a "join" though. Anyone know how?



select distinct cod4.name, wo_number, reqst_date, wo_problem,
cod2.name, cod1.name, cod3.name
from aims.wko wo
join aims.cod cod1 on (select distinct cod1.code where
cod1.type='e')=wo.trade_emp
join aims.cod cod2 on (select distinct cod2.code where
cod2.type='a')=wo.chg_ctr
join aims.cod cod3 on (select distinct cod3.code where
cod3.type='h')=wo.facility
join aims.cod cod4 on (select cod4.code where
cod4.type='w')=wo.wo_status
where wo.tag_number='IMPAC'
order by cod4.name desc, wo.wo_number
If tag_number is a text field, it should be '::tag_number::' see
http://support.microsoft.com/kb/306430

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/


My query works fine if I don't use a field from frontpage.

THE CODE BELOW WORKS FINE
_____________________________________________________________________________
select distinct cod4.name, wo_number, reqst_date, wo_problem,
cod2.name, cod1.name, cod3.name
from aims.wko wo
join aims.cod cod1 on (select distinct cod1.code where
cod1.type='e')=wo.trade_emp
join aims.cod cod2 on (select distinct cod2.code where
cod2.type='a')=wo.chg_ctr
join aims.cod cod3 on (select distinct cod3.code where
cod3.type='h')=wo.facility
join aims.cod cod4 on (select cod4.code where
cod4.type='w')=wo.wo_status
where wo.tag_number='IMPAC'
order by cod4.name desc, wo.wo_number
_____________________________________________________________________________

If I use a frontpage paramenter inplace of IMPAC :):tag_number::), I
get error below

Server error: Unable to retrieve schema information from the query:

SELECT * from aims.wko wo join aims.cod cod1 on (select distinct
cod1.code

against a database using the connection string

DRIVER={SQL
Server};SERVER=home_b2;DATABASE=aimsdatabase;UID=********;PWD=********

The following error message comes from the database driver software; it
may appear in a different language depending on how the driver is
configured.
-------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax
near 'code'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)
Click to expand...
Click to expand...
 
B

Bob Lehmann

************************
join aims.cod cod1 on (select distinct cod1.code where
cod1.type='e')=wo.trade_emp
************************
You're trying to join a value on a column name. They both need to be column
names.

************************
I need to filter the cod1.code to only give me the
ones that are cod1.type='e'
************************
That's what a WHERE clause is for.


************************
What I found that worked for me is
---------------------------------------------------------------------------
----------
where cod1.code=wo.trade_emp and cod1.type='e'
---------------------------------------------------------------------------
----------
I wanted to do that in a "join" though. Anyone know how?
************************

That *is* a join.


Bob Lehmann

The problem seems to be the subquery in the join. If I remove the
select statement in the join, frontpage accepts the query. For
instance:
___________________________________________________
join aims.cod cod1 on (select distinct cod1.code where
cod1.type='e')=wo.trade_emp
___________________________________________________
gives me an error BUT if I remove the select statement it runs.
============================================
join aims.cod cod1 on cod1.code=wo.trade_emp
===========================================
The problem is that I need to filter the cod1.code to only give me the
ones that are cod1.type='e' because in my code column I might have two
codes that are X but they have different types.

What I found that worked for me is
-------------------------------------------------------------------------- -----------
where cod1.code=wo.trade_emp and cod1.type='e'
-------------------------------------------------------------------------- -----------

I wanted to do that in a "join" though. Anyone know how?



select distinct cod4.name, wo_number, reqst_date, wo_problem,
cod2.name, cod1.name, cod3.name
from aims.wko wo
join aims.cod cod1 on (select distinct cod1.code where
cod1.type='e')=wo.trade_emp
join aims.cod cod2 on (select distinct cod2.code where
cod2.type='a')=wo.chg_ctr
join aims.cod cod3 on (select distinct cod3.code where
cod3.type='h')=wo.facility
join aims.cod cod4 on (select cod4.code where
cod4.type='w')=wo.wo_status
where wo.tag_number='IMPAC'
order by cod4.name desc, wo.wo_number
If tag_number is a text field, it should be '::tag_number::' see
http://support.microsoft.com/kb/306430

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
Click to expand...
____________________________________________________________________________
_ ____________________________________________________________________________
_
If I use a frontpage paramenter inplace of IMPAC :):tag_number::), I
get error below

Server error: Unable to retrieve schema information from the query:

SELECT * from aims.wko wo join aims.cod cod1 on (select distinct
cod1.code

against a database using the connection string

DRIVER={SQL
Server};SERVER=home_b2;DATABASE=aimsdatabase;UID=********;PWD=********

The following error message comes from the database driver software; it
may appear in a different language depending on how the driver is
configured.
-------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax
near 'code'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)
Click to expand...
Click to expand...
Click to expand...
 
J

jackle_usmc

My thing is that for whatever reason my "JOIN" statement is not being
accepted by frontpage. The statement is a valid sql statement and
works perfectly fine in query analyzer.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can't represent the join expression 1
complex join issue - access 2003 2
Unsupported Join in Access 2003 6
Can't enter text after join tables HELP 8
Urgent Help with this script 0
problems migrating project2k3server data to project2k7server 0
Problem migrating data from project2k3server to project2k7server 0
Code works in query but not in vba - help 4

Top