|
|
Posted by Hugo Kornelis on 10/02/78 11:48
On 23 May 2006 05:51:47 -0700, lakimaki wrote:
>Hi there,
>
>I have an Access query that I've been trying to convert to SQL Server
>view and although I've converted most of the syntax from access to sql
>I still can't make it work. I'm getting errors within GROUP BY and
>HAVING clauses. Anyone has an idea? Thanks...
(snip)
Hi lakimaki,
There are several things in yoour query that I don't understand.
1. Why are you joining in the tblState table? Unless I am missing
something, you only use it to display the tblState.State column, which
is also the joining column. Why not display O_State and remove the join
to tblState?
2. Why do you need the GROUP BY clause? Since you include all columns in
the WHERE clause and since there are no aggregates used anywhere, this
is the same as just using DISTINCT to remove duplicates - and if your
table is well designed, there shouldn't be any duplicates. Just remove
the entire GROUP BY and change HAVING to WHERE.
3. The HAVING part looks strange too. It consists of two parts, combined
with OR. But the second part is an exact copy of the first part, with
only one extra requirement added. And that extra requirement has the
result that this second part can NEVER be true - since DateOfAddress can
never be both NULL and in the current quarter.
4. The test that DateOfAddress is in the current quarter is not done in
the most efficient way. Instead of using an expression to extract
quarter and year from this column, you shoould compare this column to
the start of the current and the next quarter - that way, an index on
DateOfAddress (if any exists) can be used to quickly narrow down the
amount of rows to process.
As far as I see, you could rewrite your query to:
SELECT DATENAME(qq, CURRENT_TIMESTAMP) + '/'
+ DATENAME(yy, CURRENT_TIMESTAMP) AS Expr1,
DATEDIFF(day, O_DateOfAddress, CURRENT_TIMESTAMP) AS Expr3,
DATENAME(qq, O_DateofAddress) + '/'
+ DATENAME(yy, O_DateofAddress) AS Expr2,
O_VerificationQuarter,
O_Probation,
O_active_case,
O_OutOfCounty,
O_deceased,
O_Sex_Probation,
O_DateofAddress,
O_Doc,
O_LastName,
O_FirstName,
O_MiddleName,
O_Address,
O_CityEntry,
O_State,
O_Zip,
O_Sector,
O_State,
Ca_WarrantIssued,
O_DateofBirth,
O_Absconder
FROM qry_offender_master
WHERE O_DateOfAddress >= DATEADD(qq,
DATEDIFF(qq, '20000101',
CURRENT_TIMESTAMP),
'20000101')
AND O_DateOfAddress < DATEADD(qq,
DATEDIFF(qq, '20000101',
CURRENT_TIMESTAMP),
'20000401')
AND O_VerificationQuarter = DATENAME(qq, CURRENT_TIMESTAMP)
AND O_Probation = 0
AND O_OutOfCounty = 0
AND O_deceased = 0
AND O_Sex_Probation = 0
AND O_Absconder = 0
ORDER BY O_LastName
(Untested - see www.aspfaq.com.5006 if you prefer a tested reply)
>
>SELECT DateName(qq,GetDate()) + '/' + DateName(yy,GetDate()) AS Expr1,
>GetDate()-O_DateOfAddress AS Expr3,
>DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress) AS
>Expr2,
>qry_offender_master.O_VerificationQuarter,
>qry_offender_master.O_Probation,
>qry_offender_master.O_active_case, qry_offender_master.O_OutOfCounty,
>qry_offender_master.O_deceased, qry_offender_master.O_Sex_Probation,
>qry_offender_master.O_DateofAddress, qry_offender_master.O_Doc,
>qry_offender_master.O_LastName, qry_offender_master.O_FirstName,
>qry_offender_master.O_MiddleName, qry_offender_master.O_Address,
>qry_offender_master.O_CityEntry, qry_offender_master.O_State,
>qry_offender_master.O_Zip, qry_offender_master.O_Sector,
>tbl_state.State,
>qry_offender_master.Ca_WarrantIssued,
>qry_offender_master.O_DateofBirth,
>qry_offender_master.O_Absconder
>FROM qry_offender_master LEFT JOIN tbl_state ON
>qry_offender_master.O_State = tbl_state.State
>GROUP BY DateName(qq,GetDate()) + '/' + DateName(yy,GetDate()),
>GetDate()-O_DateOfAddress,
>DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress),
>qry_offender_master.O_VerificationQuarter,
>qry_offender_master.O_Probation,
>qry_offender_master.O_active_case, qry_offender_master.O_OutOfCounty,
>qry_offender_master.O_deceased, qry_offender_master.O_Sex_Probation,
>qry_offender_master.O_DateofAddress, qry_offender_master.O_Doc,
>qry_offender_master.O_LastName, qry_offender_master.O_FirstName,
>qry_offender_master.O_MiddleName, qry_offender_master.O_Address,
>qry_offender_master.O_CityEntry, qry_offender_master.O_State,
>qry_offender_master.O_Zip, qry_offender_master.O_Sector,
>tbl_state.State,
>qry_offender_master.Ca_WarrantIssued,
>qry_offender_master.O_DateofBirth,
>qry_offender_master.O_Absconder
>HAVING (((DateName(qq,O_DateofAddress) + '/' +
>DateName(yy,O_DateofAddress)) <> DateName(qq,GetDate()) + '/' +
>DateName(yy,GetDate())) AND
>((qry_offender_master.O_VerificationQuarter)= DateName(qq,GetDate()))
>AND ((qry_offender_master.O_Probation)=0) AND
>((qry_offender_master.O_OutOfCounty)=0) AND
>((qry_offender_master.O_deceased)=0) AND
>((qry_offender_master.O_Sex_Probation)=0) AND
>((qry_offender_master.O_Absconder)=0)) OR
>(((DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress))<>
>DateName(qq,GetDate()) + '/' + DateName(yy,GetDate())) AND
>((qry_offender_master.O_VerificationQuarter)= DateName(qq,GetDate()))
>AND ((qry_offender_master.O_Probation)=0) AND
>((qry_offender_master.O_OutOfCounty)=0) AND
>((qry_offender_master.O_deceased)=0) AND
>((qry_offender_master.O_Sex_Probation)=0) AND
>((qry_offender_master.O_DateofAddress) Is Null) AND
>((qry_offender_master.O_Absconder)=0))
>ORDER BY qry_offender_master.O_LastName
>
>
>Hugo Kornelis wrote:
>> On 18 May 2006 12:59:23 -0700, s_wadhwa@berkeley.edu wrote:
>>
>> >Hi,
>> >
>> >If anyone has done the conversion of parameterized query in Access to
>> >SQL Server. Please guide me how to solve this query and how to take
>> >input in SQL query from Access forms.
>>
>> Hi Shalini,
>>
>> I see that your parameters refer to forms. Keep in mind that SQL Server
>> is not aware of the forms in yoour front-end - a big difference from
>> Access, which is DB and front-end lumped together in one application.
>>
>> One possible way to convert this to SQL Server would be to create a
>> stored procedure:
>>
>> CREATE PROC GoodNameGoesHere
>> (@BuildingNumber varchar(255),
>> @ctlFloor varchar(255),
>> @DepartmentFilter varchar(255))
>> AS
>> SELECT Col1, Col2, Col3, ... -- Never use SELECT * in production code
>> FROM Rooms
>> WHERE BuildingNumber = @BuildingNumber
>> AND "Floor" LIKE @ctlFloor
>> AND DepartmentCode LIKE SUBSTRING(@DepartmentFilter, 1, 4)
>> ORDER BY BuildingNumber, RoomNumber;
>>
>> BTW, using a char datatype for a column named "Number" is highly
>> suspicious to me...
>>
>> --
>> Hugo Kornelis, SQL Server MVP
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|