ANSI Problem

ANSI Problem

Post by Gary Fairchil » Wed, 09 Aug 2000 04:00:00



Hello all:
I am in the process of converting our queries and sp's to ANSI standards in
preperation for SQL2000. We currently use SQL7 and 6.5

I am having a problem with this query. I will post the current query which
works and then it will be followed by the ANSI Query.
The problem is I don't see a way to join the company c table to the accounts
eb table.

----------------------------------------------------------------------------
---------------------------------------
Current query:
Select se.enrollment_name, a.f_name + ' ' + a.l_name 'Client_Name',
ci.ssn 'SSN', convert(varchar(10),seli.start_date, 101) 'Start_Date',
'date_closed'=case when st.date_closed is null then 'Open'
when st.date_closed is not null then convert(varchar(10), date_closed, 101)
end
From service_enrollments se, accounts a, customer_info ci, company c,
accounts eb,
service_enrollment_line_items seli, service_enrollment_terminations st
where a.account_id = ci.account_id
and a.account_id = se.client_id
AND se.service_enrollment_id = seli.service_enrollment_id
AND se.service_enrollment_id *= st.service_enrollment_id
AND eb.company_id = c.company_id
AND se.enrolled_by = eb.account_id

----------------------------------------------------------------------------
-----------------------------------------
ANSI Query:
Select se.enrollment_name, a.f_name + ' ' + a.l_name 'Client_Name',
ci.ssn 'SSN', convert(varchar(10),seli.start_date, 101) 'Start_Date',
'date_closed'=case when st.date_closed is null then 'Open'
when st.date_closed is not null then convert(varchar(10), date_closed, 101)
end
From service_enrollments se

 JOIN service_enrollment_line_items seli
 ON se.service_enrollment_id = seli.service_enrollment_id

 left outer JOIN service_enrollment_terminations st
 ON se.service_enrollment_id = st.service_enrollment_id
*****************************************************
 JOIN accounts eb
<-------------Here is the area that is in question
 ON eb.company_id = c.company_id
*****************************************************
 JOIN accounts a
 ON a.account_id = se.client_id

 JOIN customer_info ci
 ON a.account_id = ci.account_id

 AND se.enrolled_by = eb.account_id

----------------------------------------------------------------------------
------

Thanks for the help

Gary

 
 
 

1. SET ANSI problem

I am using Crystal Reports and it returns the following
message:
Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This
ensures consistent query semantics. Enable these options
and then reissue your query.

I have set ANSI_NULLS and ANSI_WARNINGS ON in the stored
procedure. The stored procedure returns results when it is
executed in query analyser.

Anybody an idea how to solve this?

2. IIIIIEEEE! BDE 4.5 problems

3. SQL7 - Linked Server - OEM/ANSI Problem

4. Help BCP Unexpected EOF in BCP data-file.

5. DOS / ANSI Problem with OSQL (MSsql7)

6. NT internal error after master log file moved

7. SQL7 - Linked Server - OEM/ANSI Problem

8. Accept Key F1

9. ANSI problems between 6.5 and 7.0

10. ?UNICODE/ANSI problems when using DAO

11. ?UNICODE/ANSI problems with DAO database

12. NewEra, OEM vs. ANSI - Problem

13. ANSI-92 vs ANSI-99