October 23, 2008 at 12:14 pm
Hi All,
I am migrating DAO code to ADO ... in doing so, I am running into some statements that I am having difficulty translating into a single line SQL statement .... for example
DAO: ACCESS
SELECT MAX( RIGHT( CustNo ) ) as MaxCustNum
FROM Account
WHERE
IIF( LEN( CustNo ) = 9 ), LEFT( CustNo,2 ) = '01', LEFT( CustNo,2 ) = '1' )
ADO: ????
I know this can be implemented using a Batch/Multi-line query ... but I was wondering if there was a way to run from within one line select statement?
Thank you.....
October 23, 2008 at 12:22 pm
Lookup CASE in Books Online. You need to replace the IIF with CASE - but, that should be the only change.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 23, 2008 at 12:42 pm
Here are a couple of different ways.
--You can force the lenght to be 9 by concatenating and then compare.
SELECT MAX(CustNo) as MaxCustNum
FROM Account
WHERE LEFT( RIGHT('000000000' + CustNo,9), 2 ) ='01'
--You can test for both conditions in the where clause.
SELECT MAX(CustNo) as MaxCustNum
FROM Account
WHERE (LEFT(CustNo,2 ) ='01' AND LEN(CustNo) = 9) OR
(LEFT(CustNo,1 ) ='1' AND LEN(CustNo) <> 9)
--If the field is numeric you can convert it so that the leading 0 drops.
SELECT MAX(CustNo) as MaxCustNum
FROM Account
WHERE LEFT( Cast(CustNo as int), 1 ) = 1
October 23, 2008 at 1:46 pm
Thank you ALL ... I appreciate the quick response ...
Here is how i implemented:
SELECT * from ACCOUNTS_ A
where A.[CUSTOMER NUMBER] =
case
when LEN (A.[CUSTOMER NUMBER]) = 9 then
case LEFT( A.[CUSTOMER NUMBER], 2)
when '01' then a.[CUSTOMER NUMBER]
else
case LEFT ( a.[CUSTOMER NUMBER], 1 )
when '1' then A.[CUSTOMER NUMBER]
end
end
end
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply