db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sonu Vijay <son...@yahoo.com>
Subject Complex SQL
Date Mon, 22 Dec 2003 17:05:53 GMT
Hi,

Is any one here using Torque Criteria to make or
replace Complex SQL queries like the following one. Is
it suggested to convert such complex queries to
Criteria or should that be run just as an SQL using
Torque generated classes:

Thanks
Vijay


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

select
	
	m.*,d.SIEBEL_ERROR_CODE
from
	ASDAT1.MDL1 m join ASDAT1.ADDRMST a
		on m.COMPANY_CODE = a.COMPANY_CODE
		and m.DLR_NO = a.DLR_NO
	left outer join SIEBELSOAP.DEALERSHIP d
		on m.COMPANY_CODE = d.COMPANY_CODE
		and m.DLR_NO = d.DLR_NO
	left outer join ASODBC.DLRLGL l
		on m.COMPANY_CODE = l.COMPANY_CODE
		and m.DLR_NO = l.DLR_NO
where
	
	a.ADDRESS_TYPE = ' '
	and a.ADDRESS_TYPE_SEQ = 1
	and
	(  
		d.SIEBEL_ERROR_CODE <> ' '  	
		
		or d.LAST_TRANSMISSION_TIMESTAMP <
m.LAST_UPDATED_TIMESTAMP
			and (
				d.STATUS_CODE <> m.STATUS_CODE
				or d.RECORD_TYPE <> m.RECORD_TYPE
				or d.LEVEL_2 <> m.LEVEL_2
				or d.LEVEL_3 <> m.LEVEL_3
				or d.B_S_DLR <> m.B_S_DLR
				or d.ORG_7 <> m.ORG_7
				or d.ORG_10 <> m.ORG_10
				or d.USER_GROUP_CODE <> m.USER_GROUP_CODE
				or d.LABOR_RATE <> m.LABOR_RATE
				or d.CANCEL_DATE <> m.CANCEL_DATE
				or l.LEGAL_NAME is null
					and d.NAME_ADDRESS_1 <> a.NAME_ADDRESS_1
				or l.LEGAL_NAME is not null
					and d.LEGAL_NAME <> l.LEGAL_NAME
				or l.DBA is null
					and d.NAME_ADDRESS_2 <> a.NAME_ADDRESS_2
				or l.DBA is not null
					and d.DBA <> l.DBA
				or d.NAME_ADDRESS_3 <> a.NAME_ADDRESS_3
				or d.CITY <> a.CITY
				or d.STATE_NAME <> a.STATE_NAME
				or d.ZIP_CODE <> a.ZIP_CODE
				or d.COUNTRY_CODE <> a.COUNTRY_CODE
				or d.PHONE_NO <> a.PHONE_NO
				or d.FAX_NO <> a.FAX_NO
				or d.EMAIL_ADDRESS <> a.EMAIL_ADDRESS
		)
	) or ( 
		d.LAST_TRANSMISSION_TIMESTAMP is null
		and m.RECORD_TYPE in ( ' ', 'I', 'E' ) 		
		and m.COMPANY_CODE || substring( m.DLR_NO, 1, 1 ) in
( '101','102','105','109','151','154','159' )
		and (	m.STATUS_CODE <> '2'  	
					or exists	(						
						select * 						
						from SIEBELSOAP.CANCELLEDD c
						where c.COMPANY_CODE = m.COMPANY_CODE
						and c.cancelled_dealer = m.dlr_no
						
					)
		)
	)
order by
	m.COMPANY_CODE,
	m.DLR_NO



__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message