|
|
Posted by lakimaki on 10/02/07 11:48
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...
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
Navigation:
[Reply to this message]
|