Rx_
Nothing In Moderation
- Local time
- Yesterday, 17:02
- Joined
- Oct 22, 2009
- Messages
- 2,803
A facility name generally ends with WSW, SWD, or WIW
By generally, there can be spaces or an extra character following it. It is a Proper Names might have these three letters as part of the name.
Taking the LEFT 6 of the full name of the Facility field, could end up with WSW, or WSW , or WSW X, or WSW 1 for example.
the goal is a purse SQL statement that can eventually move over to TSQL. Just wondering if there is a fancy combination of the IN clause that can use wildcards?
' this doesn't work with a wild card
SELECT Facility.ID_Facility, Wells.WDesc, Right([Facility_Name],6) AS RightFacilityName
FROM Facility
WHERE (((Facility.ID_Facility)=<variable>) AND ((Right([RightFacilityName],5)) In ('wsw','swd','wiw'))); (can't do wild card here)
VS ' this works
SELECT Facility.ID_Wells, Wells.WDesc, Right([Facility_Name],6) AS RightWellName
FROM Wells
WHERE ((((Facility.ID_Facility)=<variable>) AND Right([Facilityl_Name],6)) Like "*WSW*" Or (Right([Facility_Name],6)) Like "*WIW*" Or (Right([Facility_Name],6)) Like "*SWD*"));
This is part of a rules engine that needs to run very efficiently.
The Or statements have me concerned that it could take several times longer.
By generally, there can be spaces or an extra character following it. It is a Proper Names might have these three letters as part of the name.
Taking the LEFT 6 of the full name of the Facility field, could end up with WSW, or WSW , or WSW X, or WSW 1 for example.
the goal is a purse SQL statement that can eventually move over to TSQL. Just wondering if there is a fancy combination of the IN clause that can use wildcards?
' this doesn't work with a wild card
SELECT Facility.ID_Facility, Wells.WDesc, Right([Facility_Name],6) AS RightFacilityName
FROM Facility
WHERE (((Facility.ID_Facility)=<variable>) AND ((Right([RightFacilityName],5)) In ('wsw','swd','wiw'))); (can't do wild card here)
VS ' this works
SELECT Facility.ID_Wells, Wells.WDesc, Right([Facility_Name],6) AS RightWellName
FROM Wells
WHERE ((((Facility.ID_Facility)=<variable>) AND Right([Facilityl_Name],6)) Like "*WSW*" Or (Right([Facility_Name],6)) Like "*WIW*" Or (Right([Facility_Name],6)) Like "*SWD*"));
This is part of a rules engine that needs to run very efficiently.
The Or statements have me concerned that it could take several times longer.
Last edited: