Convert Access Query to SQL Server View

SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,
UCASE(Buildings.BuildingName) AS BuildingName,
Buildings.MasterPlanCode, Buildings.UniformBuildingCode,
Buildings.FunctionalCategoryCode, Buildings.OwnershipCode,
Buildings.ConditionCode, Format$([BasicGrossArea],"0000000") AS
dBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") AS
dCoveredUnenclosedGrossArea,
IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &
Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,
Buildings.YearConstructed, Format$([NumberLevels],"00") AS
dNumberLevels, Format$([UnrelatedGrossArea],"0000000") AS
dUnrelatedGrossArea, Buildings.YearLatestImprovement,
UCASE(Buildings.Address) AS Address, Buildings.CityCode,
CityCodes.CountyCode, Format$([Circulation],"0000000") AS dCirculation,
Format$([PublicToiletArea],"0000000") AS dPublicToiletArea,
Format$([Mechanical],"0000000") AS dMechanical,
Format$([Custodial],"0000000") AS dCustodial
FROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
Buildings.CityCode
ORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;

Please if anyone can help me in Converting the above given Access Query
to Sql Server. I don't know which function to use for format$, IIF. I
would really appreciate your suggestions.

Thanks,On 17 May 2006 10:02:37 -0700, s_wadhwa@.berkeley.edu wrote:

>SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,
>UCASE(Buildings.BuildingName) AS BuildingName,
>Buildings.MasterPlanCode, Buildings.UniformBuildingCode,
>Buildings.FunctionalCategoryCode, Buildings.OwnershipCode,
>Buildings.ConditionCode, Format$([BasicGrossArea],"0000000") AS
>dBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") AS
>dCoveredUnenclosedGrossArea,
>IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &
>Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,
>Buildings.YearConstructed, Format$([NumberLevels],"00") AS
>dNumberLevels, Format$([UnrelatedGrossArea],"0000000") AS
>dUnrelatedGrossArea, Buildings.YearLatestImprovement,
>UCASE(Buildings.Address) AS Address, Buildings.CityCode,
>CityCodes.CountyCode, Format$([Circulation],"0000000") AS dCirculation,
>Format$([PublicToiletArea],"0000000") AS dPublicToiletArea,
>Format$([Mechanical],"0000000") AS dMechanical,
>Format$([Custodial],"0000000") AS dCustodial
>FROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
>Buildings.CityCode
>ORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;
>
>Please if anyone can help me in Converting the above given Access Query
>to Sql Server. I don't know which function to use for format$, IIF. I
>would really appreciate your suggestions.
>Thanks,

Hi s_wadhwa,

Change doouble quotes to single quotes.

Use + instead of & for string concatenation.

Replace UCASE() with UPPER()

Replace IIf with CASE (look it up in Books Online)

My knowledge of Format$() is very llimited, but if (for isntance) the
function Format$([Custodial],"0000000") is intended to format Custodial
as a 7-digit numeric string with leading zerooes (e.g. 0001234), then
you can replace it with:
RIGHT (REPLICATE('0', 7) + STR(Custodial), 7)

Finally, remove the constant from the ORDER BY list. It serves no
purpose.

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
Thanks a lot for your quick response. the suggestions were really
helpful. Is there any book to refer or some online material to refer
to. I have lots of other doubts regarding converting Access queries to
SQL.

Thanks,
Shalini|||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.

PARAMETERS [[forms]!frmRooms![BuildingNumber]] Text ( 255 ),
[[forms]!frmRooms![ctlFloor]] Text ( 255 ),
[[forms]!frmRooms![DepartmentFilter]] Text ( 255 );
SELECT *
FROM Rooms
WHERE (((Rooms.BuildingNumber)=[forms]![frmRooms]![BuildingNumber]) AND
((Rooms.Floor) Like [forms]![frmRooms]![ctlFloor]) AND
((Rooms.DepartmentCode) Like
Mid$([forms]![frmRooms]![DepartmentFilter],1,4)) AND
((Rooms.RoomUseCode) Like [forms]![frmRooms]![RmCdFilter]))
ORDER BY Rooms.BuildingNumber, Rooms.RoomNumber;

thanks in advance for your help.
Shalini|||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|||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|||Ok, I've changed "GetDate()-O_DateOfAddress" to DateDiff(day,
O_DateOfAddress, GetDate()) and the only thing left has to do with
group by clause. So, is there any way I can use expressions (Expr1,
Expr2...) in GROUP By clause because obviously this is possible in
access but not in sql server...I tried both putting "Group by Expr1,
Expr2" and "Group by DateName(qq,GetDate()) + '/' +
DateName(yy,GetDate()) AS Expr1, GetDate()-O_DateOfAddress AS Expr3..."
but no success? Thanks in advance...|||thanks for guidance.
i resolved that issue.

shalini|||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

Convert a time field in the select statement of the query

Hi,

I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query.

Can anyone help me please?

Thx

I'm not entirely sure what you're trying to get at, but SQL Server has the following function that will get you the current UTC time:

GETUTCDATE()

If you are trying to convert the time from the timestamp to the corresponding local timestamp value, you would need to know the offset and add this time to the existing field.

Hope this helps some.

Simone

|||

Yeah!! That's the solution...... 2 weeks of deep frustrations and just such a simple solution..... wow!

Thx a lot!!!

|||

No problem, glad it worked for you. If you mark this as the answer it will better help others. Thanks.

Simone

sqlsql

Convert 1-column result set to 3 columns

I have a one-column result from a SELECT statement that I wish to
spread out into three columns, i.e.
Col
--
A
B
C
D
E
Turns into:
C1 C2 C3
-- -- --
A B C
D E
There is no identity column so I couldn't try using MOD() to come up
with a solution -- any ideas how this might be possible, preferably not
using a temp table?What are the criteria for this denormalisation - i.e. how do you determine
which values belong in which column?
What are you trying to achieve (on a more global scale)? It would help if
you could give us more information.
Also google for "cross-tab query".
ML
http://milambda.blogspot.com/|||ML wrote:
> What are the criteria for this denormalisation - i.e. how do you determine
> which values belong in which column?
There is no extra logic, I simply need to put a set of 1-column data
into three columns from left to right, top to bottom.

> What are you trying to achieve (on a more global scale)? It would help if
> you could give us more information.
The reason is that I am designing a report using SQL Reporting Services
where I need to put data in three columns across the page. Reporting
Services supports 'table' element which displays results in rows.
However each of my result is quite narrow so I need to fit 3 of them in
one row to save paper. And because this is not possible using report
designer, I decided to do it at SQL side.|||I've come up with a solution which uses temp table -- it works, but
since my team thinks temp table is absolutely evil it'd be great if
this can be converted into a more streamlined version!
-- Create data table --
drop table t
create table t
(
C char NOT NULL
)
insert into t values('A')
insert into t values('B')
insert into t values('C')
insert into t values('D')
insert into t values('E')
insert into t values('F')
insert into t values('G')
insert into t values('H')
select * from t
-- using temp table to reorganize result
drop table #tt1
create table #tt1
(
id int identity,
c char not null
)
-- Data
insert into #tt1 select c from t
-- Helper data used for incomplete last row
insert into #tt1 (c) values (' ')
insert into #tt1 (c) values (' ')
insert into #tt1 (c) values (' ')
-- convert one-column table into a three-column one
SELECT t1.c, t2.c, t3.c
from #tt1 t1
join #tt1 t2 on t2.id=t1.id+1
join #tt1 t3 on t3.id=t2.id+1 or (t2.id = ' ' and t3.id=' ') or (t3.id
= ' ')
where
t1.id % 3 = 1
and t2.id % 3 = 2
and t3.id % 3 = 0|||"Anything shall be evil in hands of the evil."
-- less known Apostle
If your team thinks using temporary tables is evil, then what do they say
about the fact that you're denormalizing data purely for formatting purposes
?
As long as the temporary tables are dropped after use and global (##)
temporary tables are used only when several processes use the same data, and
if there is a sufficient contextual isolation of these processes, then there
is nothing evil about temporary tables. Is an AK-47 evil by itself?
ML
http://milambda.blogspot.com/|||Can there be duplicates in the values?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Try,
create table dbo.t (
C char NOT NULL
)
insert into dbo.t values('A')
insert into dbo.t values('B')
insert into dbo.t values('C')
insert into dbo.t values('D')
insert into dbo.t values('E')
insert into dbo.t values('F')
insert into dbo.t values('G')
insert into dbo.t values('H')
go
select
max(
case when rank % 3 = 1 then c end
) as c1,
max(
case when rank % 3 = 2 then c end
) as c2,
max(
case when rank % 3 = 0 then c end
) as c3
from
(
select
count(*) - ((count(*) - 1) % 3) as pk,
count(*) as rank,
t1.c
from
dbo.t as t1
inner join
dbo.t as t2
on t2.c <= t1.c
group by
t1.c
) as a
group by
pk
order by
pk
go
drop table dbo.t
go
AMB
"ak199" wrote:

> I have a one-column result from a SELECT statement that I wish to
> spread out into three columns, i.e.
> Col
> --
> A
> B
> C
> D
> E
> Turns into:
> C1 C2 C3
> -- -- --
> A B C
> D E
> There is no identity column so I couldn't try using MOD() to come up
> with a solution -- any ideas how this might be possible, preferably not
> using a temp table?
>

Convert

I am using the following snippet of code to help me convert the date and tim
e
in a query I am writing.
SELECT dbo.Users.FirstName + ' ' + dbo.Users.LastName AS Student,
dbo.Subjects.Subject, Convert
(Char(15),dbo.TrainingSchedules.RequestedDate,101) AS Date,
Convert
(Char(8),dbo.TrainingSchedules.RequestedTime,108) AS Time,
The date converts fine to the format that I need. The time does not. It is
being displayed as military times and I want it to appear as a standard 12
format. i.e 9:00 AM
I tried all of the codes I found in BOL and none gave me what I wanted. Is
it possible to do what I want to do?
ThanksI think you can use the following:
Convert
(Char(8),dbo.TrainingSchedules.RequestedTime,100) AS Time,
HTH
Barry|||Thanks. But it still shows up as military time when I use 100.
"Barry" wrote:

> I think you can use the following:
> Convert
> (Char(8),dbo.TrainingSchedules.RequestedTime,100) AS Time,
> HTH
> Barry
>|||Umm not sure why - I have just checked the BOL and it confirms my
suggestion in the CAST and CONVERT section.
What are storing the Time as? Datetime?
Barry|||right(convert(varchar,dbo.TrainingSchedule.RequestedTime,100),7) as Time
Brennan wrote:

>I am using the following snippet of code to help me convert the date and ti
me
>in a query I am writing.
>SELECT dbo.Users.FirstName + ' ' + dbo.Users.LastName AS Student,
>dbo.Subjects.Subject, Convert
>(Char(15),dbo.TrainingSchedules.RequestedDate,101) AS Date,
> Convert
>(Char(8),dbo.TrainingSchedules.RequestedTime,108) AS Time,
>The date converts fine to the format that I need. The time does not. It i
s
>being displayed as military times and I want it to appear as a standard 12
>format. i.e 9:00 AM
>I tried all of the codes I found in BOL and none gave me what I wanted. Is
>it possible to do what I want to do?
>Thanks
>|||Usually, formatting is best left to the client since most client languages
have far better capabilities in this area. It isn't particularly clear what
datatypes you are using for the columns in question - the assumption is that
they are both datetime (or smalldatetime). If this assumption is not valid,
then you should clarify what the datatypes are and the expected formats of
the data (if applicable). One can question the wisdom of separating these
two intimately related bits of information into two separate columns -
especially given the dbms support.
If you must persist in this quest, you will most likely need to "generate"
the appropriate information in some convoluted and complex expression (and
possibly multiple queries). For the convert function, none of the available
formats has a space between the time and the AM/PM characters. If this can
be ignored, the 100 format is the closest - convert to this format and take
the last 7 characters (or all the characters from the last space to the end
of the string). You could also use the datepart functions to strip off and
convert the bits that are of interest. Experiment a bit - I think you will
understand the reason for the my first statement.|||Can you post a repro? Is the datatype really datetime? Also, I agree that fo
rmatting is best
performed in the client application.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brennan" <Brennan@.discussions.microsoft.com> wrote in message
news:753F4CC7-DC7F-496E-8834-51667F2BFD0B@.microsoft.com...
> Thanks. But it still shows up as military time when I use 100.
> "Barry" wrote:
>|||Thanks I agree with you about the client. I am using smalldatetime.
My problem is that my client is a DNN portal. I am using an add in module
that let's me dynamically display the results of an SQL statement in a grid
on any selected page.
Unfortunately, it does not give me the opportunity to adjust any formatting
which was why I was trying to approach it from a Convert perspective. And I
know nothing about asp so I can't approach the problem from the client side.
I'll try some of the solutions mentions here, but I think I'm going to end u
p
writing an RS report to provide this information to our end users.
Thanks
"Scott Morris" wrote:

> Usually, formatting is best left to the client since most client languages
> have far better capabilities in this area. It isn't particularly clear wh
at
> datatypes you are using for the columns in question - the assumption is th
at
> they are both datetime (or smalldatetime). If this assumption is not vali
d,
> then you should clarify what the datatypes are and the expected formats of
> the data (if applicable). One can question the wisdom of separating these
> two intimately related bits of information into two separate columns -
> especially given the dbms support.
> If you must persist in this quest, you will most likely need to "generate"
> the appropriate information in some convoluted and complex expression (and
> possibly multiple queries). For the convert function, none of the availab
le
> formats has a space between the time and the AM/PM characters. If this ca
n
> be ignored, the 100 format is the closest - convert to this format and tak
e
> the last 7 characters (or all the characters from the last space to the en
d
> of the string). You could also use the datepart functions to strip off an
d
> convert the bits that are of interest. Experiment a bit - I think you wil
l
> understand the reason for the my first statement.
>
>

Convert

Hi All,
Sample:-
Declare @.xx varchar(10)
select @.xx = '1x'
select convert(int, @.xx) && Error
а?i_ΥX error , H_P System .
] @.xx @., ?Twi Convert.
Thanks !Hi
What are you trying to do? 1x is not an integer value!
You may want to look at the undocumented procedure xp_varbintohexstr to
convert a varbinary value to a hexadecimal string:
declare @.hexstr varchar(100)
declare @.bin varbinary(10)
set @.bin = 0xAA
set @.hexstr = ''
exec master.dbo.xp_varbintohexstr @.bin, @.hexstr output
print @.hexstr
-- Or use cast/convert to change back
select convert(int, @.bin), CAST(@.bin as int )
John
"SOHO" wrote:

> Hi All,
> Sample:-
> Declare @.xx varchar(10)
> select @.xx = '1x'
> select convert(int, @.xx) && Error
> ?D°Y￥i§_¤￡￥?￥X error , ￥H_P System °±¤?.
> |]?° @.xx ￥??°¤@.??|ì, ?ê??¤￡?T?w￥i Convert.
>
> --
> Thanks !
>
>|||Hi John Bell,
Thanks for your reply.
а?pib SQL Server Online Books ?o_ function
"master.dbo.xp_varbintohexstr" ?k,
Χ??_?Oiw, iO "1x+&2" or "abcxyz", thanks.
Thanks !
"John Bell" <jbellnewsposts@.hotmail.com> glsD:7921B130-EB91-49BC-B88E-1C3E06A55A3
9@.microsoft.com...
> Hi
> What are you trying to do? 1x is not an integer value!
> You may want to look at the undocumented procedure xp_varbintohexstr to
> convert a varbinary value to a hexadecimal string:
> declare @.hexstr varchar(100)
> declare @.bin varbinary(10)
> set @.bin = 0xAA
> set @.hexstr = ''
> exec master.dbo.xp_varbintohexstr @.bin, @.hexstr output
> print @.hexstr
> -- Or use cast/convert to change back
> select convert(int, @.bin), CAST(@.bin as int )
> John
> "SOHO" wrote:
>|||Hi
Unfortunately the your character set is not being displayed correctly, so I
am not sure if you are wanting a reply!
John
"SOHO" wrote:

> Hi John Bell,
> Thanks for your reply.
> ?D°Y|p|ó￥i|b SQL Server Online Books ?Y¨ì3o_ó function
> "master.dbo.xp_varbintohexstr" ao￥?ak,
> ¤?§úao?ü??_è?O¤￡￥i1w′
ao, ￥iˉ_?O "1x+&2" or "abcx
yz", thanks.
>
>
> --
> Thanks !
>
> "John Bell" <jbellnewsposts@.hotmail.com> ???g?ó?l￥ó·s?D:7921B130
-EB91-49BC-B88E-1C3E06A55A39@.microsoft.com...
>
>|||John Bell skrev:

> Hi
> Unfortunately the your character set is not being displayed correctly, so=
I
> am not sure if you are wanting a reply!
> John
>
Huh? What part of " =A4=CE=A7=DA=AA=BA=C5=DC=BC=C6=AD=C8=ACO
=A4=A3=A5i=B9w=
=B4=C1=AA=BA,
=A5i=AF=E0=AC"
did you not understand ;)
/impslayer, also |||I am easily !
I hope that was not rude!!!
John :)
"impslayer" wrote:

> John Bell skrev:
>
> Huh? What part of " ¤?§úao?ü??_è?O¤￡￥i1w′
ao,
> ￥iˉ_?"
> did you not understand ;)
> /impslayer, also
>

convert

why do the results of the following sql are 0
select convert(int, 0.9)
select cast(0.9 as int)Win you are using int which is not supported with decimal try to use float
and you will have the right results..
Best regards
Shailesh Gothal
"Win" wrote:

> why do the results of the following sql are 0
> select convert(int, 0.9)
> select cast(0.9 as int)
>
>|||If you are expecting this to round up, it won't. You'll
find this mentioned in BOL in "CAST and CONVERT"
Note that using decimal(10,0) instead of int will round
select convert(decimal(10,0), 0.9)
select cast(0.9 as decimal(10,0))|||Win
So what do you suppose to get?
You convert to INTEGER not DECIMAL or something else
select cast(0.9 as decimal(5,2))
"Win" <aaa@.aaa.com> wrote in message
news:OOHe11tMGHA.2668@.tk2msftngp13.phx.gbl...
> why do the results of the following sql are 0
> select convert(int, 0.9)
> select cast(0.9 as int)
>|||Thats quite normal, the int representation of 0.9 is 0.
What did you expect ?
HTH, Jens Suessmeyer.sqlsql

Conversion of Access query using First() Aggregate

All,
I am trying to figure out how to convert this particular query, and am
stumped...
SELECT tCollatList.CollatID, tCollatList.ListCatID, tlListCategory.Category,
First(tlListCategory.[CatWholeVal%]) AS [FirstOfCatWholeVal%],
First(tlListCategory.Unit) AS FirstOfUnit, First(tlListCategory.[$Unit]) AS
[FirstOf$Unit], Sum([tCollatList]![ColListIncDec]) AS Balance,
[Balance]*[FirstOf$Unit] AS BalValue, [BalValue]*[FirstOfCatWholeVal%] AS
BalWhole
FROM tCollatList LEFT JOIN tlListCategory ON tCollatList.ListCatID =
tlListCategory.ListCatID
GROUP BY tCollatList.CollatID, tCollatList.ListCatID, tlListCategory.Categor
y
HAVING (((tCollatList.CollatID)=9));
SQL server doesn't support 'First(tlListCategory.[$Unit]) AS [FirstOf$Unit]'
Can someone please tell me what I can do, if anything to duplicate this
functionallity?
ThanksScottW wrote:
> All,
> I am trying to figure out how to convert this particular query, and am
> stumped...
> SELECT tCollatList.CollatID, tCollatList.ListCatID, tlListCategory.Categor
y,
> First(tlListCategory.[CatWholeVal%]) AS [FirstOfCatWholeVal%],
> First(tlListCategory.Unit) AS FirstOfUnit, First(tlListCategory.[$Unit]) A
S
> [FirstOf$Unit], Sum([tCollatList]![ColListIncDec]) AS Balance,
> [Balance]*[FirstOf$Unit] AS BalValue, [BalValue]*[FirstOfCatWholeVal%] AS
> BalWhole
> FROM tCollatList LEFT JOIN tlListCategory ON tCollatList.ListCatID =
> tlListCategory.ListCatID
> GROUP BY tCollatList.CollatID, tCollatList.ListCatID, tlListCategory.Categ
ory
> HAVING (((tCollatList.CollatID)=9));
> SQL server doesn't support 'First(tlListCategory.[$Unit]) AS [FirstOf$Unit
]'
> Can someone please tell me what I can do, if anything to duplicate this
> functionallity?
> Thanks
The problem is that FIRST is not a real aggregate function, even in
Access. Access's FIRST and LAST functions just return some arbitrary
value from the set of rows in question. So you might as well use MIN or
MAX instead. If you want a different answer then you need a better
specification of what you mean by "first" and "last". Tables have no
inherent order and nor do query results unless you use ORDER BY.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hello David,
The code provided was specific, using SQL Server 2000, specifically it
says First is an unknown keyword.
As for there being arbitrary values, not in this case. Each value in the
joined table is identical, which is why we need only one record, so the
values from the other table can be summed. I guess that being said, usinfg
min or max should return the desired results. Thoughts?
Thanks
"David Portas" wrote:

> ScottW wrote:
> The problem is that FIRST is not a real aggregate function, even in
> Access. Access's FIRST and LAST functions just return some arbitrary
> value from the set of rows in question. So you might as well use MIN or
> MAX instead. If you want a different answer then you need a better
> specification of what you mean by "first" and "last". Tables have no
> inherent order and nor do query results unless you use ORDER BY.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||ScottW wrote:
> Hello David,
> The code provided was specific, using SQL Server 2000, specifically it
> says First is an unknown keyword.
That's right.

> As for there being arbitrary values, not in this case. Each value in the
> joined table is identical, which is why we need only one record, so the
> values from the other table can be summed.
In that case you can just add CatWholeVal%, Unit and $Unit to the GROUP
BY list. Then you don't need to use an aggregate function at all.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sqlsql

conversion in select statement

Hey y'all,

Can someone make this right? i have an int column and need text:

SELECT (SELECT CASE score WHEN 0 THEN 'qqqqqqqq' ELSE 1 END) AS Expr1, COUNT(Score) AS Expr1

Thanks in advance

SELECT CASE score when 0 then 'qqqqqqqq' ELSE '1' END AS Expr1, COUNT(Score) AS Expr2

Like that?

|||

score is always 0,1 or 2 but i need written labels (text) for my charting...

i can do it with a selection in the build of my chart but i was wondering of i could do it in sql...

|||

SELECT CAST(score as varchar(1)) As score,count(*)

?

Conversion from MS Access SQL Code...nested if statements

INSERT INTO EligSummary ( PlanVariation)
SELECT DISTINCT

IIf(Left([BPI],1)=2 Or Left([BPI],1)=3,

'1. MED EE',

'2. MED DEP' AS PlanVariation)

The above is the code i would use in access to Assign either the value

'MED EE' or 'MED DEP' to the PlanVariation field.

I am new to SQL Server - how would I accomplish this in SQL Server 2000?

If I use SQL

INSERT INTO EligSummary (PlanVariation)
SELECT Planvariation =

CASE left([BPI],1)
WHEN 1 THEN 'EE NON MED'
WHEN 2 THEN 'MED DEP'

..it requires me to GROUP by on BPI which would cause it to enter 15 different rows for each BPI code as opposed to 2 for EE NON MED and MED DEP......

I would appreciate any help you could give me with this!

CASE has a second syntax, which will give you what you want

INSERT INTO EligSummary ( PlanVariation)
SELECT DISTINCT
CASE WHEN (left([BPI], 1) = '2' OR left([BPI], 1) = '3' THEN '1. MED EE'
ELSE '2. MED DEP' END AS PlanVariation

|||

Thanks! That did the trick..i appreciate the help.

Conversion failed when converting datetime from character string

I have a strange problem that I need help troubleshooting. I have the
following statement in a stored procedure:
SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126), ''),
'')
FROM #TFieldValues TFV
WHERE TFV.DataType = 'Date'
When this statement is run, it returns the following error;
Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
Conversion failed when converting datetime from character string.
The field #TFieldValues.Value is created as varchar(2000).
So, I run the following statement, and 21 rows are returned, where 8 are date
values and 13 are empty strings:
SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
The 8 date values returned are the following:
2/23/2006
03/21/2006
08/23/2006
1O/18/2OO5
1O/18/2OO5
1O/18/2OO5
02/26/2007
02/26/2007
I then run the following statement
SELECT
TFV.Value
FROM #TFieldValues TFV
WHERE
CASE
WHEN ISDATE(Value) = 0 THEN 0
WHEN ISDATE(Value) = 1 THEN 1
END = 1
AND TFV.DataType = 'Date'
Instead of 8 date values being returned, I only return 5, which are the
following:
2/23/2006
03/21/2006
08/23/2006
02/26/2007
02/26/2007
In just looking at the returns in the grid in Management Studio, when I run
the select statement that returned the 8 date values, it appears the
1O/18/2OO5 values are of a different font size. This can probably even be
seen as you compare the zero's from the following paste:
08/23/2006
1O/18/2OO5
Any ideas on validation, or handling this situation?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6f39ac268793b@.uwe...
>I have a strange problem that I need help troubleshooting. I have the
> following statement in a stored procedure:
> SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126),
> ''),
> '')
> FROM #TFieldValues TFV
> WHERE TFV.DataType = 'Date'
> When this statement is run, it returns the following error;
> Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
> Conversion failed when converting datetime from character string.
> The field #TFieldValues.Value is created as varchar(2000).
> So, I run the following statement, and 21 rows are returned, where 8 are
> date
> values and 13 are empty strings:
> SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
> The 8 date values returned are the following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 1O/18/2OO5
> 1O/18/2OO5
> 1O/18/2OO5
> 02/26/2007
> 02/26/2007
> I then run the following statement
> SELECT
> TFV.Value
> FROM #TFieldValues TFV
> WHERE
> CASE
> WHEN ISDATE(Value) = 0 THEN 0
> WHEN ISDATE(Value) = 1 THEN 1
> END = 1
> AND TFV.DataType = 'Date'
> Instead of 8 date values being returned, I only return 5, which are the
> following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 02/26/2007
> 02/26/2007
> In just looking at the returns in the grid in Management Studio, when I
> run
> the select statement that returned the 8 date values, it appears the
> 1O/18/2OO5 values are of a different font size. This can probably even be
> seen as you compare the zero's from the following paste:
> 08/23/2006
> 1O/18/2OO5
No - it isn't a font issue. These are capital O characters, not zeros.
Switch to a font that uses slashed zeros and you will more clearly see this.
Consider this one of the "advantages" to using the EAV data model - store
anythingsqlsql

Conversion error

I am getting the error message Error converting data type varchar to float when running the following query:

Code Snippet

select top 175816
AV.intItemID,
AV.intAttrID,
-- AV.vchValue,
CAST(AV.vchValue AS float) AS Test,
0
from tblAttrVals AV
join tblAttributes AA
on AA.intAttributeID = AV.intAttrID
and AA.intDataTypeID in (2, 3)
and (1 = isnumeric (AV.vchValue))
order by AV.intItemID, AV.intAttrID

Here is what is strange. If I bump the top count down by one it succeeds. And even stranger, if I leave the top count the same and uncomment out the line in the select statement that shows the value being converted it succeeds.

Any ideas? This seems like a bug.

Chris:

Can you show us the specific data that is giving you trouble?

|||

I found the issue. It actually had nothing to do with the data that is being returned. It had to do with the data not being returned.

Here is the info from a post that helped me:

The problem is that SQL Server 2005 is more aggressive in terms of evaluating expressions in your query and moving them to different stages of the query plan. This might result in conversion error like in your case if the CAST gets computed before the WHERE clause checks. So there is no guarantee that the expressions in the WHERE clause will be computed first. This was true even in SQL Server 2000 except that you probably never hit it for your schema/data set. You can get the same error there also if the query plan changes.

To resolve the problem, you need to either correct your data model to represent the values correctly. Use float if your data is float - don't mix values from different domains. Or you will have to use CASE in the SELECT list to avoid the conversion problem. Note that using CASE expression is the only way to control order of execution of various expressions. See link below for more details (search for unsafe expressions):

http://msdn2.microsoft.com/en-us/library/ms143359.aspx

To summarize you have two solutions:

1. Fix your data model / schema so you represent the values in their proper domain (not float values in varchar and mixing various values in string)
2. Or modify your SELECT in the 2nd view to:

SELECT cast(CASE WHEN dwpId LIKE '[0-9]%' THEN dwpId END as int) as dwpId, startDate, endDate
FROM View1

Note that even above check is not entirely correct because not all values that have just numeric digits can be successfully converted to int. You might get overflow errors for example. You could use ISNUMERIC but that checks for integer, numeric, and money conversions so it will let more data through. So it is best you correct your schema to avoid all these issues.

sqlsql

Conversion between data types

Lets say I execute: SELECT hashbytes('MD5','IMTIAZ')

Which returns: 0x60D164C6B64EE81C7E7395C01D838FEE

How do I get a varchar: 60D164C6B64EE81C7E7395C01D838FEE

Not converted.

How do I get the 0x removed from the string ?

Regards

Imtiaz

I have been googling to find a solution to this question.....

I have acome across a few places to use the xp_varbintohexstr undocumented procedure. But in SQL 2005 what is the equivalent and any pointers in this direction will be of great help.

Regards

Imtiaz

|||

Ok...here's the answer...

SELECT substring(upper(master.dbo.fn_varbintohexstr(hashbytes('MD5','SHELLEY'))),3,len(master.dbo.fn_varbintohexstr(hashbytes('MD5','IMTIAZ'))))

|||You have to write your own TSQL/SQLCLR scalar UDF to do the conversion from varbinary to hexadecimal string. Please do not use undocumented stored procedures like xp_varbintohexstr or fn_varbintohexstr. Undocumented objects can be dropped or modified in any release or even service pack of SQL Server. So you should not rely on such interfaces. It is easier to write your own code for these type of problems.

Conversion Access Query to SQL Server 2000

SELECT DISTINCTROW OILPALM.NAME, formatyear([year]) AS yearDisplay, OILPALM.YEAR, OILPALM.UNIT, OILPALM.BLOCK, OILPALM.BlockInYield, [Y_01_total_ton]+[Y_02_total_ton]+[Y_03_total_ton]+[Y_04_total_ton]+[Y_05_total_ton]+[Y_06_total_ton]+[Y_07_total_ton]+[Y_08_total_ton]+[Y_09_total_ton]+[Y_10_total_ton]+[Y_11_total_ton]+[Y_12_total_ton] AS Production, IIf([m_a]>0,[Production]/[m_a],0) AS [Avg Yield], ([Y_TH_POT]*IIf([blockinyield]=True,[size],0))*(([YieldP_01]+[YieldP_02]+[YieldP_03]+[YieldP_04]+[YieldP_05]+[YieldP_06]+[YieldP_07]+[YieldP_08]+[YieldP_09]+[YieldP_10]+[YieldP_11]+[YieldP_12])/100) AS TotalTon_Pot, IIf(IIf([blockinyield]=True,[SIZE],0)>0,([Y_TH_POT]*IIf([oilpalm].[blockinyield]=True,[oilpalm].[size],0))*(([YieldP_01]+[YieldP_02]+[YieldP_03]+[YieldP_04]+[YieldP_05]+[YieldP_06]+[YieldP_07]+[YieldP_08]+[YieldP_09]+[YieldP_10]+[YieldP_11]+[YieldP_12])/100)/IIf([blockinyield]=True,[SIZE],0),0) AS [Yield pot], [Avg Yield]-[Yield pot] AS [Yield Gap], OILPALM.Y_KGB AS [Avg bunchweight], IIf([TotalTree]>0,[Total bunches]/[TotalTree],0) AS [Avg Bunch No], OILPALM.SIZE AS [Total size], OILPALM.Y_TOTAL_mandays AS [Total workforce], OILPALM.Y_AVG_harvest_int AS Avg_Harvest_int, OILPALM.Y_AVG_harvest_int AS sum_Harvest_int_sum, IIf([Y_AVG_harvest_int]>0,1,0) AS rec_Harvest_int, IIf([rec_Harvest_int]>0,[sum_Harvest_int_sum]/[rec_Harvest_int],0) AS Calc_Harvest_int, OILPALM.Y_per_manday AS Avg_YMD, [Y_01_TOTAL_bunches]+[Y_02_TOTAL_bunches]+[Y_03_TOTAL_bunches]+[Y_04_TOTAL_bunches]+[Y_05_TOTAL_bunches]+[Y_06_TOTAL_bunches]+[Y_07_TOTAL_bunches]+[Y_08_TOTAL_bunches]+[Y_09_TOTAL_bunches]+[Y_10_TOTAL_bunches]+[Y_11_TOTAL_bunches]+[Y_12_TOTAL_bunches] AS [Total bunches], OILPALM.Y_TOTAL_mandays AS [Total manday], IIf([Blockinyield]=True,IIf([TC_M]>0,[TC_M],[TC_DENS]*[size]),0) AS TotalTree, OILPALM.TC_M, IIf([TotalTree]>0,([Production]*1000)/[TotalTree],0) AS [Avg bunchweighMTree], IIf([Total workforce]>0,[Production]/[Total workforce],0) AS TMd, (IIf(([oilpalm]![blockinyield]=-1),[OILPALM]![SIZE],0)) AS M_A
FROM OILPALM INNER JOIN SYS_soil_type ON OILPALM.SOILTYPE = SYS_soil_type.SoilType
ORDER BY OILPALM.YEAR DESC , OILPALM.UNIT, OILPALM.BLOCK;

Please Help Me....

I suggest that you take the time to work through the suggestions that have been provided to your previous posts until you understand the process.

When you work through the process and begin to understand the process, you will be able to work these out for yourself. (Otherwise, we might think that you are just trying to get folks to do your work for you. And that probably wouldn't be fair to you ...)

|||

1. Change all your IIF(CONDITION,TRUE STMT, FALSE STMT)

CASE WHEN CONDITION THEN TRUE STMT ELSE FALSE STMT END

2. You need to convert your formatyear macro logic to SQL Server

conver datetime to age... new

using the following query..
SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
FROM UserProfile
WHERE UserProfile.Birthdate IS NOT NULL AND
datediff(year,Birthdate,getdate())>=0
order by UserID
the converts the table
userid birthdate
2 1985-03-08 00:00:00.000
9 1998-06-27 00:00:00.000
to the output of:
UserID Age
2 20
9 7
The problem is it doesn't take into consideration for the current day,
rather it looks only at the year. So the age of UserID 9 is actually 6.
-Thanks HP/Thomas on the other thread.http://groups-beta.google.com/group...mming&lr=&hl=en
"chad" <chad@.discussions.microsoft.com> wrote in message
news:1667E4FD-855D-4D1A-BFB9-2CFB0D456005@.microsoft.com...
> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||See if this helps:
http://www.tech-archive.net/Archive...04-02/2296.html
AMB
"chad" wrote:

> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||I guess this is the easiest solution:
SELECT userid as UserID, Age = datediff(dd,Birthdate,getdate())/365
FROM UserProfile
WHERE UserProfile.Birthdate IS NOT NULL AND
datediff(year,Birthdate,getdate())>=0
order by UserID
"chad" wrote:

> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Here's a formula I use:
Year(getdate())
- Year(birthdate)
+ case when datepart(dy, birthdate) - datepart(dy, getdate()) < 0
then 0 else -1 end
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQlLtXoechKqOuFEgEQJ93gCgsWvUYstK/25OJhhdk7mUMSsdKqsAmwYF
rv4IxkY87JMffYj7v+i/Y31/
=AH6W
--END PGP SIGNATURE--
chad wrote:
> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||Yet another one:
datename(yy,getdate()-vt.birthday) - 1900

Controlling fields in a select statement by use of parameters

Hi to all

I wish to be able to have a standard select statement which has
additional fields added to it at run-time based on supplied
parameter(s).

ie
declare @.theTest1 nvarchar(10)
set @.theTest1='TRUE'

declare @.theTest2 nvarchar(10)
set @.theTest2='TRUE'

select
p_full_name
if @.theTest1='TRUE'
BEGIN
other field1,
END
if @.theTest2='TRUE'
BEGIN
other field2
END

from dbo.tbl_GIS_person
where record_id < 20

I do not wish to use an IF statement to test the parameter for a
condition and then repeat the entire select statement particularly as
it is a UNIONed query for three different statement

ie
declare @.theTest1 nvarchar(10)
set @.theTest1='TRUE'

declare @.theTest2 nvarchar(10)
set @.theTest2='TRUE'

if @.theTest1='TRUE' AND @.theTest2='TRUE'
BEGIN
select
p_full_name,
other field1,
other field2
from dbo.tbl_GIS_person
where record_id < 20
END

if @.theTest1='TRUE' AND @.theTest2='FALSE'
BEGIN
select
p_full_name,
other field1
from dbo.tbl_GIS_person
where record_id < 20
END
..
..
..
if @.theTest<>'TRUE'
BEGIN
select
p_full_name
from dbo.tbl_GIS_person
where record_id < 20
END

Make sense? So the select is standard in the most part but with small
variations depending on the user's choice. I want to avoid risk of
breakage by having only one spot that the FROM, JOIN and WHERE
statements need to be defined.

The query will end up being used in an XML template query.

Any help would be much appreciated

Regards

GIS AnalystIf you don't want to write three separate queries, then you'll probably
have to use dynamic SQL and build up the query string dynamically:

http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html

Alternatively, you could simply return all the columns all the time
(perhaps using CASE to return empty values for the unwanted columns so
as to minimize the data volume) and let the client decide which ones to
present/process, but in a more complex case it might not be workable.

Simon|||Hi Simon

thanks for the ideas. I did think about genearting the statement within
a stored procedure but thought I would check to see if there were
standard sql statement to do this first.
One reason for not returning all columns all the time is to avoid
record duplication when the optional fields are included. (Duplicates
apart from the optional field)

Regards

GIS Analyst

Control the paging functionality manually

hi all,

In my Reports, i want to handle the paging functionality manually, c i have a query called "Select * from employees" it has a total number of records of 100, in which it is displaying some 50 rows in one page and the next in the other page, wat my requirement is , i want to display 10 rows per page and the remaining in the next pages. this is killing me in the look and feel, so pls help me in this, wating for a reply as soon as possible, how to do it ?

Thanks in advance

Venkat.

check this:http://aspnet.4guysfromrolla.com/articles/031506-1.aspx

HTH|||

Hi,

Checked the link,its not related with reporting services, i like to do custom paging in the report (.rdl) in sql server 2000 reporting services

waiting for a reply

thanks in advance

Venkat.

|||Just setting the height of the report page will do it .

control over select output results

Hi All !

Is it possible to get rid of these dash symbols which are underlining
the column name when recordset is returned after query execution ?

For example, using isql.exe:

SELECT 'blah'
go

produces the following results:

--
blah

What I want to achieve is just
blah

I know that SET NOCOUNT ON switches the "X row affected" thing. But
how about column headers ?

Thanks for your time,

SeekerHi

I don't think there is a way to stop the dashed lines, although you could
send the output to a file and strip it out with findstr. To get rid of the
column headers (and lines) remove the check box for "print headers" in
tools/options/results or specify -h-1 when using isql or osql.

John
"." <seeker12@.subdimension.com> wrote in message
news:24693db3.0309012131.7cd01fe0@.posting.google.c om...
> Hi All !
> Is it possible to get rid of these dash symbols which are underlining
> the column name when recordset is returned after query execution ?
> For example, using isql.exe:
> SELECT 'blah'
> go
> produces the following results:
> --
> blah
> What I want to achieve is just
> blah
> I know that SET NOCOUNT ON switches the "X row affected" thing. But
> how about column headers ?
> Thanks for your time,
>
> Seeker|||seeker12@.subdimension.com (.) wrote in message news:<24693db3.0309012131.7cd01fe0@.posting.google.com>...
> Hi All !
> Is it possible to get rid of these dash symbols which are underlining
> the column name when recordset is returned after query execution ?
> For example, using isql.exe:
> SELECT 'blah'
> go
> produces the following results:
> --
> blah
> What I want to achieve is just
> blah
> I know that SET NOCOUNT ON switches the "X row affected" thing. But
> how about column headers ?
> Thanks for your time,
>
> Seeker

There is no way to remove this in an interactive session, as far as
I'm aware. If you're running from a script, you could parse the output
and remove the dashes, though.

Simon|||[posted and mailed, please reply in news]

.. (seeker12@.subdimension.com) writes:
> Is it possible to get rid of these dash symbols which are underlining
> the column name when recordset is returned after query execution ?
> For example, using isql.exe:
> SELECT 'blah'
> go
> produces the following results:
> --
> blah
> What I want to achieve is just
> blah

With ISQL and OSQL you can use -h-1 to turn off headers.

In Query Analyzer you can under Tools->Options->Results change results
to text, and select something else than column delimited.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

There could be a cludge!

Use -h-1 or remove the print headers and do a union. That forces the column
headers as the first row retrieved. You will need a method to sort the
results which probably makes it as bad as having the underlines themselves!

select 'pub_id' as pub_id, 'pub_name' as pub_name, 0 as ' '
union all
select CONVERT(varchar,pub_id), pub_name, 1 from publishers
order by 3

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f544438$0$249$ed9e5944@.reading.news.pipex.ne t...
> Hi
> I don't think there is a way to stop the dashed lines, although you could
> send the output to a file and strip it out with findstr. To get rid of the
> column headers (and lines) remove the check box for "print headers" in
> tools/options/results or specify -h-1 when using isql or osql.
> John
> "." <seeker12@.subdimension.com> wrote in message
> news:24693db3.0309012131.7cd01fe0@.posting.google.c om...
> > Hi All !
> > Is it possible to get rid of these dash symbols which are underlining
> > the column name when recordset is returned after query execution ?
> > For example, using isql.exe:
> > SELECT 'blah'
> > go
> > produces the following results:
> > --
> > blah
> > What I want to achieve is just
> > blah
> > I know that SET NOCOUNT ON switches the "X row affected" thing. But
> > how about column headers ?
> > Thanks for your time,
> > Seeker|||Thanks a heap to everyone who replied !

The -h-1 option is exactly what I was after.

What would we do without USENET !

Have fun!

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93EAE80C9CB44Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> . (seeker12@.subdimension.com) writes:
> > Is it possible to get rid of these dash symbols which are underlining
> > the column name when recordset is returned after query execution ?
> > For example, using isql.exe:
> > SELECT 'blah'
> > go
> > produces the following results:
> > --
> > blah
> > What I want to achieve is just
> > blah
> With ISQL and OSQL you can use -h-1 to turn off headers.
> In Query Analyzer you can under Tools->Options->Results change results
> to text, and select something else than column delimited.

Control number of options selected in a multi select parameter

Hi All

I have a report which has a multi-value parameter. Problem is, it can contain up to 100 options.

Is there a way to limit the number of options that is passed to the SQL statement?. EG list has 100 options, user selects 10 but only the first 4 selected options are passed to the SQL statement.


Many Thanks
Delli
I will try an expression for the query parameter (Parameters tab in the dataset properties) which passes your parameter to code-behind function that in turn filters out the parameter values accordingly.|||

After looking into this some more, I’ve found a split

function for MS SQL server, and in PL/SQL. Have system in both databases

:( grrrrrrr

The split function takes in the multi select parameter as a comma separated

list, and creates a virtual table of the results.

By using SQL code similar to the following: select top 10 element

dbo.split('string,split,code',',') I could stop the SQL engine running

for too many selected parameters. A search on Google or msn search ;) will find

codes examples for these functions. Keywords: SQL split function or PL/SQL

split function.

Also helps to inform your users on the front page of the report you have

done this!!!

Continue on INSERT error.

Hi!

Imagine this SQL statement:

Code Snippet

INSERT INTO B SELECT * FROM A

If one of the insert fails ... don't continue, the statement fail. For example if any field in A violate a constraint in B, the statement fails.

I want that the statement continue if errors occurs, if i lost a number of rows don't matter ... but if i can save or log this row will be great too !!

Is posible? Any way to do it?

Regards.

Make two statements, by adding a WHERE clause, you can verify the CONSTRAINT and add rows ONLY if the CONSTRAINT passes. Then in the second statement, in the WHERE clause, get the rows that do not pass.

FOR illustration:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
Name varchar(20) PRIMARY KEY
)

INSERT INTO @.MyTable VALUES ( 'Bill' )

DECLARE @.MyOtherTable table
( RowID int IDENTITY,
Name varchar(20)
)

DECLARE @.Failures table
( RowID int,
Name varchar(20)
)

INSERT INTO @.MyOtherTable VALUES ( 'Bill' )
INSERT INTO @.MyOtherTable VALUES ( 'Mary' )
INSERT INTO @.MyOtherTable VALUES ( 'Omar' )

-- First, isolate the CONSTRAINT Failures
INSERT INTO @.Failures
SELECT
t.RowID,
t.Name
FROM @.MyOtherTable t
JOIN @.MyTable m
ON m.Name = t.Name

-- Insert the rows that pass the CONSTRAINT test
INSERT INTO @.MyTable ( Name )
SELECT t.Name
FROM @.MyOtherTable t
JOIN @.MyTable m
ON m.Name <> t.Name

SELECT *
FROM @.MyTable

RowID Name
-- --
1 Bill
2 Mary
3 Omar

SELECT *
FROM @.Failures

RowID Name
-- --
1 Bill

|||

Thanks for your reply.

I will write my question in another way. What I want is if I can change the SQL/Server constraint behaviour when a error is thrown. I know that I can do the insert with a "WHERE" clause. But it some cases is useful to perform your own behaviour when the table has a lot of fields and a lot of rows and you are using a INSERT ... SELECT ... clause. There is some utility (NOTIFICATION, TRIGGERS) that help to do this in a speedy way?

Regards.

|||

A CONSTRAINT failure occurs BEFORE the data is inserted into the table -so a AFTER INSERT TRIGGER would not work.

You could create a BEFORE INSERT TRIGGER, but then you would STILL have to use the two step process I demonstrated in my earlier post. And there may be increased locking and blocking behavior as a result of using a TRIGGER.

Bottom line is that the CONSTRAINT prevents the data from getting into the table. Without the data getting to the table, there is little to offer in the form of Notifications, etc., and you are also, pardon the ironic pun, constrained in the ability to use a TRIGGER.

|||

OK! Thanks.

Regards.

contents of image fields

Hi,

I have a SELECT FROM TABLE query and in that table there is a field of type
image. Result of this select goes throug internet do its destination. But in
fact I need only to know if in this field is or not an image. Is there any
funciotn which gives me information about contents of image fields?

Regards, PaulIs there any funciotn which gives me information about contents of image

Quote:

Originally Posted by

fields?

The only thing SQL Server knows about image column contents is the
DATALENGTH. It is up to the application to interpret the contents. If you
store data of different types in the same column (not a good design, IMHO),
you'll need another column to indicate the type.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Koral" <koral00@.op.plwrote in message news:f7vir2$nur$1@.news.onet.pl...

Quote:

Originally Posted by

Hi,
>
I have a SELECT FROM TABLE query and in that table there is a field of
type image. Result of this select goes throug internet do its destination.
But in fact I need only to know if in this field is or not an image. Is
there any funciotn which gives me information about contents of image
fields?
>
Regards, Paul
>