fraggle5
asked on
Query generated in MS access work on linked sybase tables but not on its own - please translate!
I basically have an access database with linked tables to a Sybase DB. I need to use this query in an ASP page, but it won't work. Have also tried in Sybase interactive but still no joy - can anyone help? I know its a problem with the left and right joins, and I'm guessing just a syntax issue, but not sure what.
Thanks!
SELECT FFUsers.Companies.Name, FFUsers.Assignments.title, FFUsers.Assignments.status , FFUsers.Assignments.assign _no, FFUsers.Assignments.consul t_2, FFUsers.Invoices.inv_date, FFUsers.Invoices.inv_type, FFUsers.Consultant_Fees.fe e_comment, FFUsers.UsersPreferences.U ser_Long, FFUsers.Consultant_Fees.ra te1
FROM (FFUsers.Consultant_Fees LEFT OUTER JOIN (FFUsers.Invoices INNER JOIN (FFUsers.Assignments INNER JOIN FFUsers.Companies ON FFUsers.Assignments.client _company_g uid = FFUsers.Companies.Companie s_GUID) ON FFUsers.Invoices.assignmen ts_guid = FFUsers.Assignments.Assign ments_guid ) ON FFUsers.Consultant_Fees.re lated_invo ice_guid = FFUsers.Invoices.invoice_g uid) LEFT OUTER JOIN FFUsers.UsersPreferences ON FFUsers.Consultant_Fees.co nsultant = FFUsers.UsersPreferences.U ser_Short
WHERE FFUsers.Assignments.status Like 'Open*' AND Year(FFUsers.Invoices.inv_ date)=2004 AND FFUsers.Invoices.inv_type Like 'Stage*'
ORDER BY FFUsers.UsersPreferences.U ser_Long DESC;
Thanks!
SELECT FFUsers.Companies.Name, FFUsers.Assignments.title,
FROM (FFUsers.Consultant_Fees LEFT OUTER JOIN (FFUsers.Invoices INNER JOIN (FFUsers.Assignments INNER JOIN FFUsers.Companies ON FFUsers.Assignments.client
WHERE FFUsers.Assignments.status
ORDER BY FFUsers.UsersPreferences.U
I reformatted this into the a format that makes sense in Sybase.
SELECT
C.Name,
A.title,
A.status,
A.assign_no,
A.consult_2,
I.inv_date,
I.inv_type,
CF.fee_comment,
UP.User_Long,
CF.rate1
FROM
FFUsers.Consultant_Fees CF,
FFUsers.Invoices I,
FFUsers.Assignments A,
FFUsers.Companies C,
FFUsers.UsersPreferences UP
WHERE
/* THESE ARE JOINING CLAUSES */
A.client_company_guid = C.Companies_GUID
AND
A.client_company_guid = C.Companies_GUID
AND
I.assignments_guid = A.Assignments_guid
AND
CF.related_invoice_guid = I.invoice_guid
AND
CF.consultant = UP.User_Short
AND
/* THESE ARE FILTER CLAUSES */
A.status Like 'Open%'
AND
Year(I.inv_date)=2004
AND
I.inv_type Like 'Stage%'
Note that in the WHERE clause there are JOINING CLAUSES. These are equivalent to the ON in ANSI-92 Syntax.
1)
A couple of things. I used all INNER JOINS to reformat your SQL. To make an OUTER JOIN use a * in the side of the = that you want all records from. Like this:
A.client_company_guid =* C.Companies_GUID
-- This would yield all rows from C with matching data from A.
A.client_company_guid *= C.Companies_GUID
-- This would yield all rows from A with matching data from C.
Try to get the joins the way you want and run it in Sybase. Then when you get the desired results paste it into Access in SQL View and Access should reformat to what it thinks should work.
If Access doesn't like the reformatted SQL, make it a pass through query and it should go write through the OLE DB Provider and make the call natively on Sybase and return results to Access.
2)
Also, I have never seen * used as a wildcard with a LIKE operator. Usually the format is LIKE '%string%'. Try replacing the * with a %. Access may be a little too forgiving.
SELECT
C.Name,
A.title,
A.status,
A.assign_no,
A.consult_2,
I.inv_date,
I.inv_type,
CF.fee_comment,
UP.User_Long,
CF.rate1
FROM
FFUsers.Consultant_Fees CF,
FFUsers.Invoices I,
FFUsers.Assignments A,
FFUsers.Companies C,
FFUsers.UsersPreferences UP
WHERE
/* THESE ARE JOINING CLAUSES */
A.client_company_guid = C.Companies_GUID
AND
A.client_company_guid = C.Companies_GUID
AND
I.assignments_guid = A.Assignments_guid
AND
CF.related_invoice_guid = I.invoice_guid
AND
CF.consultant = UP.User_Short
AND
/* THESE ARE FILTER CLAUSES */
A.status Like 'Open%'
AND
Year(I.inv_date)=2004
AND
I.inv_type Like 'Stage%'
Note that in the WHERE clause there are JOINING CLAUSES. These are equivalent to the ON in ANSI-92 Syntax.
1)
A couple of things. I used all INNER JOINS to reformat your SQL. To make an OUTER JOIN use a * in the side of the = that you want all records from. Like this:
A.client_company_guid =* C.Companies_GUID
-- This would yield all rows from C with matching data from A.
A.client_company_guid *= C.Companies_GUID
-- This would yield all rows from A with matching data from C.
Try to get the joins the way you want and run it in Sybase. Then when you get the desired results paste it into Access in SQL View and Access should reformat to what it thinks should work.
If Access doesn't like the reformatted SQL, make it a pass through query and it should go write through the OLE DB Provider and make the call natively on Sybase and return results to Access.
2)
Also, I have never seen * used as a wildcard with a LIKE operator. Usually the format is LIKE '%string%'. Try replacing the * with a %. Access may be a little too forgiving.
Oh yeah, I used Table Aliases. Like FFUsers.UsersPreferences becomes UP. This just makes your code easier to read. I suggest you do that yourself.
In Access, right Click on the table and type the Alias in.
:)
In Access, right Click on the table and type the Alias in.
:)
ASKER
Thanks Frostbyte Zero - have made a few modifications and it's (almost) working OK now - using the above with slight modifications it works in Sybase, but not in ASP. I suspect it's something to do with the *= join syntax - I don't think ASP likes it - I don't get an error, just no results...
PS - ASP doesn't like % as a wildcard, but * works fine hence the change.
PPS - Haven't used aliases as I'm not sure if ASP will like it, and I'd rather minimise the scope for errors.
PPPS - In "FFUsers.Consultant_Fees.r elated_inv oice_guid = FFUsers.Invoices.invoice_g uid" I tried to do =* but I get an error - 'Invalid expression in where clause of transact-sql outer join'
SELECT FFUsers.Companies.Name, FFUsers.Assignments.title, FFUsers.Assignments.consul t_2, FFUsers.Invoices.inv_date, FFUsers.Invoices.inv_type, FFUsers.Consultant_Fees.fe e_comment, FFUsers.UsersPreferences.U ser_Long, FFUsers.Consultant_Fees.ra te1, FFUsers.Consultant_Fees.co nsultant
FROM FFUsers.Consultant_Fees, FFUsers.Invoices, FFUsers.Assignments, FFUsers.Companies, FFUsers.UsersPreferences
WHERE
FFUsers.Assignments.client _company_g uid = FFUsers.Companies.Companie s_GUID
AND
FFUsers.Invoices.assignmen ts_guid = FFUsers.Assignments.Assign ments_guid
AND
FFUsers.Consultant_Fees.re lated_invo ice_guid = FFUsers.Invoices.invoice_g uid
AND
FFUsers.Consultant_Fees.co nsultant *= FFUsers.UsersPreferences.U ser_short
AND
FFUsers.Assignments.status Like 'Open%'
AND
Year(FFusers.Invoices.inv_ date)=2004
AND
FFUsers.Invoices.inv_type Like 'Stage%'
ORDER BY User_Long DESC;
PS - ASP doesn't like % as a wildcard, but * works fine hence the change.
PPS - Haven't used aliases as I'm not sure if ASP will like it, and I'd rather minimise the scope for errors.
PPPS - In "FFUsers.Consultant_Fees.r
SELECT FFUsers.Companies.Name, FFUsers.Assignments.title,
FROM FFUsers.Consultant_Fees, FFUsers.Invoices, FFUsers.Assignments, FFUsers.Companies, FFUsers.UsersPreferences
WHERE
FFUsers.Assignments.client
AND
FFUsers.Invoices.assignmen
AND
FFUsers.Consultant_Fees.re
AND
FFUsers.Consultant_Fees.co
AND
FFUsers.Assignments.status
AND
Year(FFusers.Invoices.inv_
AND
FFUsers.Invoices.inv_type Like 'Stage%'
ORDER BY User_Long DESC;
Are you using ASP or ASP.NET? What version of Sybase?
PS - ASP doesn't like % as a wildcard, but * works fine hence the change.
A: ASP Doesn't care. ACCESS uses a "*". All other SQL RDBMS use a "%"
PPS - Haven't used aliases as I'm not sure if ASP will like it, and I'd rather minimise the scope for errors.
A: ASP Doesn't care. Aliases are a standard practice. ACCESS prefers if you specify it as:
SELECT * FROM yourtable AS youralias
However Sybase will accept:
SELECT * FROM yourtable youralias
PPPS - In "FFUsers.Consultant_Fees.r elated_inv oice_guid = FFUsers.Invoices.invoice_g uid" I tried to do =* but I get an error - 'Invalid expression in where clause of transact-sql outer join'
A: ACCESS is still trying to validate the syntax using its own rules. If the SQL works in Sybase, do not change it. Create a Select Query in Access without the Wizard. When the window prompts for you to select a table, close it. From the Main Menu Select Query/SQLSpecific/PassThro ugh. Then Paste the Query into SQL Pass-Through Query. Then save it.
This will make ACCESS ignore the SQL Syntax and pass it directly into Sybase. The results are returned to ACCESS and are then available to you.
This is a difficult way of doing it. You may be better off avoiding ACCESS altogether and consider connecting directly to SYBASE from ASP. You just have to create a DSN Connection using Sybase ODBC on the IIS Server. Or if you have an OLEDB Compliant Driver, you could set the ConnectionString property directly in your ASP. Having ACCESS in the middle just adds another layer of complexity. Expecially if the data actually resides on SYBASE.
The best way to troubleshoot further errors is to have ASP do a Response.Write SQL (assuming SQL is your SQL String Variable). Copy the output from ASP to your clipboard and paste it into WISQL32 (or whatever you use for testing on Sybase). Then modify your ASP to format the string correctly.
PS - ASP doesn't like % as a wildcard, but * works fine hence the change.
A: ASP Doesn't care. ACCESS uses a "*". All other SQL RDBMS use a "%"
PPS - Haven't used aliases as I'm not sure if ASP will like it, and I'd rather minimise the scope for errors.
A: ASP Doesn't care. Aliases are a standard practice. ACCESS prefers if you specify it as:
SELECT * FROM yourtable AS youralias
However Sybase will accept:
SELECT * FROM yourtable youralias
PPPS - In "FFUsers.Consultant_Fees.r
A: ACCESS is still trying to validate the syntax using its own rules. If the SQL works in Sybase, do not change it. Create a Select Query in Access without the Wizard. When the window prompts for you to select a table, close it. From the Main Menu Select Query/SQLSpecific/PassThro
This will make ACCESS ignore the SQL Syntax and pass it directly into Sybase. The results are returned to ACCESS and are then available to you.
This is a difficult way of doing it. You may be better off avoiding ACCESS altogether and consider connecting directly to SYBASE from ASP. You just have to create a DSN Connection using Sybase ODBC on the IIS Server. Or if you have an OLEDB Compliant Driver, you could set the ConnectionString property directly in your ASP. Having ACCESS in the middle just adds another layer of complexity. Expecially if the data actually resides on SYBASE.
The best way to troubleshoot further errors is to have ASP do a Response.Write SQL (assuming SQL is your SQL String Variable). Copy the output from ASP to your clipboard and paste it into WISQL32 (or whatever you use for testing on Sybase). Then modify your ASP to format the string correctly.
ASKER
I'm using standard ASP on Windows server 2003. Sybase is ASA 7.0
Re wildcards, I've tried with a % and I get: ADODB.Recordset (0x800A0CC1) Item cannot be found in the collection corresponding to the requested name or ordinal. Error disappears if I substitute it for a *. Not a major concern at the moment I think - I've used * on other ASP queries on Sybase and no problems.
Re aliases, thanks - will still leave as is for the time; I don't think it's causing the problem, but I'll use when I get this sorted.
Don't think I made myself clear enough first time around - I used access to do the initial query as that's where I had it originally (and it's easier for me as I can do it using the design wizard without writing the SQL to being with - not my strong point). Once I had the SQL from access, I then changed it slightly and ran the query directly in Sybase. The ASP page does not go through access, it goes straight into Sybase.
The query used in the ASP page is exactly as above, except for substituting * for % as wilcards - but in Sybase I get results, and the ASP page shows 0 results - no error message though.
Re wildcards, I've tried with a % and I get: ADODB.Recordset (0x800A0CC1) Item cannot be found in the collection corresponding to the requested name or ordinal. Error disappears if I substitute it for a *. Not a major concern at the moment I think - I've used * on other ASP queries on Sybase and no problems.
Re aliases, thanks - will still leave as is for the time; I don't think it's causing the problem, but I'll use when I get this sorted.
Don't think I made myself clear enough first time around - I used access to do the initial query as that's where I had it originally (and it's easier for me as I can do it using the design wizard without writing the SQL to being with - not my strong point). Once I had the SQL from access, I then changed it slightly and ran the query directly in Sybase. The ASP page does not go through access, it goes straight into Sybase.
The query used in the ASP page is exactly as above, except for substituting * for % as wilcards - but in Sybase I get results, and the ASP page shows 0 results - no error message though.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER