I am trying to pull the number preceeding the decimal, but I want my output in a fixed lengh. Here is what I tried thinking it might work, however it did not.
sf_retail = right('000' + floor(cast(labsf.last_retail_price as varchar)),3),
the number I am running this against is '0000001.45' I would like my output to read '001'.....I am getting only '1'
Any suggestions?We just did something like that...
Check out...
http://www.dbforums.com/t987264.html|||Thanks, that was helpful. I ended up using:
sf_retail = right('000' + convert(varchar(3), floor(labsf.last_retail_price)),3),
2012年3月29日星期四
2012年3月19日星期一
Fixed Width Text Report
I'm trying to create a fixed width text report. I've created a function that
takes a string and a length and returns the string either truncated or padded
with spaces to the given length.
I then use url parameters to modify the CSV device information settings to
change the encoding to ascii, change the extension to txt and change the
FieldDelimiter to %1f (unicode symbol for some kind of field grouping or
something).
Things seems to properly but I don't like having to set the FieldDelimiter
to anything. I tried setting it to null by saying isnull=true but that
generates an error about referencing a null object. I read something that
said to make it an empty string but I can seem to be able to do that using
url parameters. I could try it programmatically but I would prefer using the
url.
Does anyone have any ideas?(The parent post is mine, I just changed my login)
I decided that setting the FieldDelimiter to %1f was not a good idea. I
did try to set the parameter to an empty string programmatically but it
just went to the default comma delimiter.
I've decided to just plug in the url encoded value of which stands
for a null ascii character. I don't know if this is the best solution
but I am going with it. Here is my final url:
http://localhost/ReportServer?/Devel/TestFile&rs:Format=CSV&rs:Command=Render&rc:Extension=txt&rc:NoHeader=true&rc:FieldDelimiter=&rc:Encoding=ascii
If anyone else comes up with any other ideas of how to use RS to create
fixed width text files I would be glad to hear them. I can't find
anything that explains a way of doing this. This would work perfect if
I could set the FieldDemlimiter parameter to nothing but it keeps going
to the default comma.
Thanks.
Gary
takes a string and a length and returns the string either truncated or padded
with spaces to the given length.
I then use url parameters to modify the CSV device information settings to
change the encoding to ascii, change the extension to txt and change the
FieldDelimiter to %1f (unicode symbol for some kind of field grouping or
something).
Things seems to properly but I don't like having to set the FieldDelimiter
to anything. I tried setting it to null by saying isnull=true but that
generates an error about referencing a null object. I read something that
said to make it an empty string but I can seem to be able to do that using
url parameters. I could try it programmatically but I would prefer using the
url.
Does anyone have any ideas?(The parent post is mine, I just changed my login)
I decided that setting the FieldDelimiter to %1f was not a good idea. I
did try to set the parameter to an empty string programmatically but it
just went to the default comma delimiter.
I've decided to just plug in the url encoded value of which stands
for a null ascii character. I don't know if this is the best solution
but I am going with it. Here is my final url:
http://localhost/ReportServer?/Devel/TestFile&rs:Format=CSV&rs:Command=Render&rc:Extension=txt&rc:NoHeader=true&rc:FieldDelimiter=&rc:Encoding=ascii
If anyone else comes up with any other ideas of how to use RS to create
fixed width text files I would be glad to hear them. I can't find
anything that explains a way of doing this. This would work perfect if
I could set the FieldDemlimiter parameter to nothing but it keeps going
to the default comma.
Thanks.
Gary
2012年3月9日星期五
Fix Replace function plz!
Hello!
I need to replace a string which starts from "DBX:" and ends to "Addr1:"
with a word "APPLE". The cloumn is Name in #temp table. I am captuting
it correctly but not using the replace function the right way. It is
replacing eveywhere which I dont want.
Thanks for your help.
create table #temp
(ID int, Name varchar(80))
insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
Addr1: 1234')
insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
select
--ID,
--Captured = substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
from #temp
I am getting this:
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: APPLE DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
And I want this:
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: John Doe DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
*** Sent via Developersdex http://www.examnotes.net ***Test,
Try:
SELECT SUBSTRING([NAME],7,DATALENGTH([NAME])) AS 'NAME'
FROM #TEMP
HTH
Jerry
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:OwySQyZ1FHA.3864@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I need to replace a string which starts from "DBX:" and ends to "Addr1:"
> with a word "APPLE". The cloumn is Name in #temp table. I am captuting
> it correctly but not using the replace function the right way. It is
> replacing eveywhere which I dont want.
> Thanks for your help.
> create table #temp
> (ID int, Name varchar(80))
> insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
> Addr1: 1234')
> insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
> insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
> select
> --ID,
> --Captured = substring(Name, charindex('DBX:', Name) + 4,
> --charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
> Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
> charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
> from #temp
> I am getting this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: APPLE DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
> And I want this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: John Doe DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Nothing wrong with the replace function, basically what you asked for was
this:
REPLACE('Name: John Doe DBX: John Doe Addr1: 9999', 'John Doe', 'APPLE')
This is what you're looking for I think:
select
--ID,
--Captured = substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
--Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE '),
Correct = Left(Name, charindex('DBX:', Name) + 3) +
replace(substring(Name, charindex('DBX:', Name) + 4, LEN(Name)),
substring(Name, charindex('DBX:', Name) + 4,
charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
from #temp
By the way, wouldn't it be much a bit cleaner to have separate columns for
Name, DBX and Addr1?
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:OwySQyZ1FHA.3864@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I need to replace a string which starts from "DBX:" and ends to "Addr1:"
> with a word "APPLE". The cloumn is Name in #temp table. I am captuting
> it correctly but not using the replace function the right way. It is
> replacing eveywhere which I dont want.
> Thanks for your help.
> create table #temp
> (ID int, Name varchar(80))
> insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
> Addr1: 1234')
> insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
> insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
> select
> --ID,
> --Captured = substring(Name, charindex('DBX:', Name) + 4,
> --charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
> Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
> charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
> from #temp
> I am getting this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: APPLE DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
> And I want this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: John Doe DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks but this is not what I am looking for. I need this:
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: John Doe DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
The APPLE has been updated in between the DBX: and Addr1: in the Name
column. My SQL is not working in John Doe case but John Doe is in two
places and I want to see the replacement in one place only (which is
from DBX: to Addr1:).
Hope this information helps.
*** Sent via Developersdex http://www.examnotes.net ***|||I don't know what to tell you, but my query returned me this (which is what
you say you're looking for) :
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: John Doe DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
Copy and paste this (just to verify that there wasn't a copy / paste mistake
when you ran it last time) :
___
create table #temp
(ID int, Name varchar(80))
insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
Addr1: 1234')
insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
select
--ID,
--Captured = substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
--Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE '),
Correct = Left(Name, charindex('DBX:', Name) + 3) +
replace(substring(Name, charindex('DBX:', Name) + 4, LEN(Name)),
substring(Name, charindex('DBX:', Name) + 4,
charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
from #temp
___
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23YMaCDa1FHA.3816@.TK2MSFTNGP14.phx.gbl...
> Thanks but this is not what I am looking for. I need this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: John Doe DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
> The APPLE has been updated in between the DBX: and Addr1: in the Name
> column. My SQL is not working in John Doe case but John Doe is in two
> places and I want to see the replacement in one place only (which is
> from DBX: to Addr1:).
> Hope this information helps.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Sorry test...misread the data requirements prior to posting this query.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u6EPH3Z1FHA.2792@.tk2msftngp13.phx.gbl...
> Test,
> Try:
> SELECT SUBSTRING([NAME],7,DATALENGTH([NAME])) AS 'NAME'
> FROM #TEMP
>
> HTH
> Jerry
> "Test Test" <farooqhs_2000@.yahoo.com> wrote in message
> news:OwySQyZ1FHA.3864@.TK2MSFTNGP12.phx.gbl...
>|||Thanks ESPNSTI! It works fine! Sorry I missed the solution in your
initial posting. Thanks!
*** Sent via Developersdex http://www.examnotes.net ***|||ESPN! I have one more condiction to take care of which is to replace
anything after "Name:" and before "DBX:" with a word ' XXXX '.
so I tried it but again my SQL is not working for John Doe bc it is in
two place.
Now,
select replace(Name, substring(Name, charindex(':', Name)+2,
charindex('DBX:', Name)- charindex(':', Name)-2), ' XXXXX ')
from #temp
I am getting this:
Name: XXXXX DBX: Smith Jones Addr1: 1234
Name: XXXXX DBX: XXXXX Addr1: 9999
Name: XXXXX DBX: Robin Addr1: 1234
An I want this:
Name: XXXXX DBX: Smith Jones Addr1: 1234
Name: XXXXX DBX: John Doe Addr1: 9999
Name: XXXXX DBX: Robin Addr1: 1234
I would appreciate your help! Thanks.
*** Sent via Developersdex http://www.examnotes.net ***
I need to replace a string which starts from "DBX:" and ends to "Addr1:"
with a word "APPLE". The cloumn is Name in #temp table. I am captuting
it correctly but not using the replace function the right way. It is
replacing eveywhere which I dont want.
Thanks for your help.
create table #temp
(ID int, Name varchar(80))
insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
Addr1: 1234')
insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
select
--ID,
--Captured = substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
from #temp
I am getting this:
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: APPLE DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
And I want this:
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: John Doe DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
*** Sent via Developersdex http://www.examnotes.net ***Test,
Try:
SELECT SUBSTRING([NAME],7,DATALENGTH([NAME])) AS 'NAME'
FROM #TEMP
HTH
Jerry
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:OwySQyZ1FHA.3864@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I need to replace a string which starts from "DBX:" and ends to "Addr1:"
> with a word "APPLE". The cloumn is Name in #temp table. I am captuting
> it correctly but not using the replace function the right way. It is
> replacing eveywhere which I dont want.
> Thanks for your help.
> create table #temp
> (ID int, Name varchar(80))
> insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
> Addr1: 1234')
> insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
> insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
> select
> --ID,
> --Captured = substring(Name, charindex('DBX:', Name) + 4,
> --charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
> Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
> charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
> from #temp
> I am getting this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: APPLE DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
> And I want this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: John Doe DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Nothing wrong with the replace function, basically what you asked for was
this:
REPLACE('Name: John Doe DBX: John Doe Addr1: 9999', 'John Doe', 'APPLE')
This is what you're looking for I think:
select
--ID,
--Captured = substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
--Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE '),
Correct = Left(Name, charindex('DBX:', Name) + 3) +
replace(substring(Name, charindex('DBX:', Name) + 4, LEN(Name)),
substring(Name, charindex('DBX:', Name) + 4,
charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
from #temp
By the way, wouldn't it be much a bit cleaner to have separate columns for
Name, DBX and Addr1?
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:OwySQyZ1FHA.3864@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I need to replace a string which starts from "DBX:" and ends to "Addr1:"
> with a word "APPLE". The cloumn is Name in #temp table. I am captuting
> it correctly but not using the replace function the right way. It is
> replacing eveywhere which I dont want.
> Thanks for your help.
> create table #temp
> (ID int, Name varchar(80))
> insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
> Addr1: 1234')
> insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
> insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
> select
> --ID,
> --Captured = substring(Name, charindex('DBX:', Name) + 4,
> --charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
> Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
> charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
> from #temp
> I am getting this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: APPLE DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
> And I want this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: John Doe DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks but this is not what I am looking for. I need this:
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: John Doe DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
The APPLE has been updated in between the DBX: and Addr1: in the Name
column. My SQL is not working in John Doe case but John Doe is in two
places and I want to see the replacement in one place only (which is
from DBX: to Addr1:).
Hope this information helps.
*** Sent via Developersdex http://www.examnotes.net ***|||I don't know what to tell you, but my query returned me this (which is what
you say you're looking for) :
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: John Doe DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
Copy and paste this (just to verify that there wasn't a copy / paste mistake
when you ran it last time) :
___
create table #temp
(ID int, Name varchar(80))
insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
Addr1: 1234')
insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
select
--ID,
--Captured = substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
--Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE '),
Correct = Left(Name, charindex('DBX:', Name) + 3) +
replace(substring(Name, charindex('DBX:', Name) + 4, LEN(Name)),
substring(Name, charindex('DBX:', Name) + 4,
charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
from #temp
___
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23YMaCDa1FHA.3816@.TK2MSFTNGP14.phx.gbl...
> Thanks but this is not what I am looking for. I need this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: John Doe DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
> The APPLE has been updated in between the DBX: and Addr1: in the Name
> column. My SQL is not working in John Doe case but John Doe is in two
> places and I want to see the replacement in one place only (which is
> from DBX: to Addr1:).
> Hope this information helps.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Sorry test...misread the data requirements prior to posting this query.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u6EPH3Z1FHA.2792@.tk2msftngp13.phx.gbl...
> Test,
> Try:
> SELECT SUBSTRING([NAME],7,DATALENGTH([NAME])) AS 'NAME'
> FROM #TEMP
>
> HTH
> Jerry
> "Test Test" <farooqhs_2000@.yahoo.com> wrote in message
> news:OwySQyZ1FHA.3864@.TK2MSFTNGP12.phx.gbl...
>|||Thanks ESPNSTI! It works fine! Sorry I missed the solution in your
initial posting. Thanks!
*** Sent via Developersdex http://www.examnotes.net ***|||ESPN! I have one more condiction to take care of which is to replace
anything after "Name:" and before "DBX:" with a word ' XXXX '.
so I tried it but again my SQL is not working for John Doe bc it is in
two place.
Now,
select replace(Name, substring(Name, charindex(':', Name)+2,
charindex('DBX:', Name)- charindex(':', Name)-2), ' XXXXX ')
from #temp
I am getting this:
Name: XXXXX DBX: Smith Jones Addr1: 1234
Name: XXXXX DBX: XXXXX Addr1: 9999
Name: XXXXX DBX: Robin Addr1: 1234
An I want this:
Name: XXXXX DBX: Smith Jones Addr1: 1234
Name: XXXXX DBX: John Doe Addr1: 9999
Name: XXXXX DBX: Robin Addr1: 1234
I would appreciate your help! Thanks.
*** Sent via Developersdex http://www.examnotes.net ***
Fix my SQL "WHERE" Statement!
I'm working on an ASP Web application, and am having syntax issues in
a WHERE statement I'm trying to write that uses the CInt Function on a
field.
a WHERE statement I'm trying to write that uses the CInt Function on a
field.
Basically, I want to select records using criteria of Race, Gender and
Crime Code. But the Crime Code field in the table is text, and I
cannot change it. I want to use a range of crime codes, so need to
convert it to an integer on-the-fly. Here's what I have in my code so
far:
varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
PrisonRelease.PID = Defendant.PID_Code "
varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
varSQL = varSQL & "AND
(IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
(Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
"[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
Between 1800 And 1899) "
When I try to execute this code on my Web page, I get an error. But it
works fine in Access, with some minor syntax changes. What am I
missing?!
Thanks,
Rachel WeedenRachelWeeden@.hotmail.com (Rachel Weeden) wrote in message news:<f5066a28.0408230526.3f881906@.posting.google.com>...
> I'm working on an ASP Web application, and am having syntax issues in
> a WHERE statement I'm trying to write that uses the CInt Function on a
> field.
> Basically, I want to select records using criteria of Race, Gender and
> Crime Code. But the Crime Code field in the table is text, and I
> cannot change it. I want to use a range of crime codes, so need to
> convert it to an integer on-the-fly. Here's what I have in my code so
> far:
> varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
> varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
> ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
> PrisonRelease.PID = Defendant.PID_Code "
> varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
> varSQL = varSQL & "AND
> (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
> Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
> Between 1800 And 1899) "
> When I try to execute this code on my Web page, I get an error. But it
> works fine in Access, with some minor syntax changes. What am I
> missing?!
> Thanks,
> Rachel Weeden
I think it is because you are using [] brackets which is a access
syntax and not asp.|||RachelWeeden@.hotmail.com (Rachel Weeden) wrote in message news:<f5066a28.0408230526.3f881906@.posting.google.com>...
> I'm working on an ASP Web application, and am having syntax issues in
> a WHERE statement I'm trying to write that uses the CInt Function on a
> field.
> Basically, I want to select records using criteria of Race, Gender and
> Crime Code. But the Crime Code field in the table is text, and I
> cannot change it. I want to use a range of crime codes, so need to
> convert it to an integer on-the-fly. Here's what I have in my code so
> far:
> varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
> varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
> ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
> PrisonRelease.PID = Defendant.PID_Code "
> varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
> varSQL = varSQL & "AND
> (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
> Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
> Between 1800 And 1899) "
> When I try to execute this code on my Web page, I get an error. But it
> works fine in Access, with some minor syntax changes. What am I
> missing?!
> Thanks,
> Rachel Weeden
I think it is because you are using [] brackets which is a access
syntax and not asp.|||CInt is not supported in SQL-Server. You can use CAST or CONVERT
instead.
IIf it not supported in SQL-Server. You can use the CASE expression,
although it works slightly different, so you will need to rewrite that
part.
Have a look at SQL-Server Books Online for more information and
examples.
Hope this helps,
Gert-Jan
Rachel Weeden wrote:
> I'm working on an ASP Web application, and am having syntax issues in
> a WHERE statement I'm trying to write that uses the CInt Function on a
> field.
> Basically, I want to select records using criteria of Race, Gender and
> Crime Code. But the Crime Code field in the table is text, and I
> cannot change it. I want to use a range of crime codes, so need to
> convert it to an integer on-the-fly. Here's what I have in my code so
> far:
> varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
> varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
> ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
> PrisonRelease.PID = Defendant.PID_Code "
> varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
> varSQL = varSQL & "AND
> (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
> Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
> Between 1800 And 1899) "
> When I try to execute this code on my Web page, I get an error. But it
> works fine in Access, with some minor syntax changes. What am I
> missing?!
> Thanks,
> Rachel Weeden
--
(Please reply only to the newsgroup)|||"Rachel Weeden" wrote:
> I'm working on an ASP Web application, and am having syntax issues in
> a WHERE statement I'm trying to write that uses the CInt Function on a
> field.
> Basically, I want to select records using criteria of Race, Gender and
> Crime Code. But the Crime Code field in the table is text, and I
> cannot change it. I want to use a range of crime codes, so need to
> convert it to an integer on-the-fly. Here's what I have in my code so
> far:
> varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
> varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
> ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
> PrisonRelease.PID = Defendant.PID_Code "
> varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
> varSQL = varSQL & "AND
> (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
> Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
> Between 1800 And 1899) "
> When I try to execute this code on my Web page, I get an error. But it
> works fine in Access, with some minor syntax changes. What am I
> missing?!
> Thanks,
> Rachel Weeden
Rachel,
[Note: I typed some of the T-SQL code in my newsreader, so formatting and
syntax may be a little goofy, but it should get you started in the right
direction.]
The square brackets are OK in T-SQL. The problem you're having is that your
WHERE clause is using VBA functions. While this is a cool feature in the
JET database engine, you can't use it in T-SQL (or any other DB environment
that I'm aware of). As others have mentioned:
- Use CAST or CONVERT instead of CInt (or any of the VB casting functions
e.g. CStr, CDbl, etc)
- Use CASE instead of IIf
Also,
- In VB, IsNull is a boolean function that returns true if the single
argument is NULL. In SQL Server T-SQL, ISNULL is a function that takes 2
parameters; if the first argument is NULL it returns the second else it
returns the first. For example:
ISNULL(NULL, 1) returns 1
...and...
ISNULL(2, 1) returns 2
A rough translation of your code would go something like (watch out for word
wrap and funny formatting)...
AND (
CASE
WHEN ISNULL(Defendant.[CRIME_CLASSIFICATION_CODE], '') = ''
THEN 9999
WHEN Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9]' AND
Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9][0-9]'
THEN 9999
ELSE
CASE WHEN CONVERT(int, Defendant.[CRIME_CLASSIFICATION_CODE]) BETWEEN
1800 AND 1899
THEN 1
ELSE 0
END
END
)
However, it appears you want something akin to "WHERE
Defendant.[CRIME_CLASSIFICATION_CODE] isn't an appropriate numeric
representation or it is numeric and is inclusively in the range 1800-1899".
If I'm correct, you could use something like this (tested in Query Analyzer
with SQL Server 2000)...
DECLARE @.tab TABLE (
d varchar(32),
ccc varchar(20)
)
INSERT @.tab VALUES ('Num outside range', '1750')
INSERT @.tab VALUES ('Num in range', '1800')
INSERT @.tab VALUES ('Not a num', 'aaa')
INSERT @.tab VALUES ('NULL', NULL)
INSERT @.tab VALUES ('Empty string', '')
SELECT *
FROM @.tab
WHERE CASE WHEN ISNUMERIC(ccc) = 1
THEN
CASE WHEN CONVERT(int, ccc) BETWEEN 1800 AND 1899
THEN 1
ELSE 0
END
ELSE 1
END = 1
This returns everything in the test table except the 'Num outside range'
row.
Craig|||Thanks for all the input, Craig - I have taken some time to look over
your code, and I understand the basics about replacing some of my VB
functions with T-SQL ones. Problem is, I am very inexperienced with
SQL (this page is my first project, really!), so the details are a
little confusing.
For example, I've never heard of T-SQL before. I assumed I was writing
a SQL statement in a VB script on an ASP page...but that's a new
acronym for me! Also, the code you included looks totally different
than anything else on my page, so I am having trouble figuring out
where it all fits in, etc.
But I will look into this a bit more, and I'm sure your suggestions
about CAST, CONVERT, CASE, etc. will come in handy.
Thanks again,
Rachel
"Craig Kelly" <cnkelly.nospam@.nospam.net> wrote in message news:<v5tWc.504132$Gx4.393231@.bgtnsc04-news.ops.worldnet.att.net>...
> "Rachel Weeden" wrote:
> > I'm working on an ASP Web application, and am having syntax issues in
> > a WHERE statement I'm trying to write that uses the CInt Function on a
> > field.
> > Basically, I want to select records using criteria of Race, Gender and
> > Crime Code. But the Crime Code field in the table is text, and I
> > cannot change it. I want to use a range of crime codes, so need to
> > convert it to an integer on-the-fly. Here's what I have in my code so
> > far:
> > varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
> > varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
> > ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
> > PrisonRelease.PID = Defendant.PID_Code "
> > varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
> > varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
> > varSQL = varSQL & "AND
> > (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
> > Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
> > (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> > Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
> > "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
> > Between 1800 And 1899) "
> > When I try to execute this code on my Web page, I get an error. But it
> > works fine in Access, with some minor syntax changes. What am I
> > missing?!
> > Thanks,
> > Rachel Weeden
> Rachel,
> [Note: I typed some of the T-SQL code in my newsreader, so formatting and
> syntax may be a little goofy, but it should get you started in the right
> direction.]
> The square brackets are OK in T-SQL. The problem you're having is that your
> WHERE clause is using VBA functions. While this is a cool feature in the
> JET database engine, you can't use it in T-SQL (or any other DB environment
> that I'm aware of). As others have mentioned:
> - Use CAST or CONVERT instead of CInt (or any of the VB casting functions
> e.g. CStr, CDbl, etc)
> - Use CASE instead of IIf
> Also,
> - In VB, IsNull is a boolean function that returns true if the single
> argument is NULL. In SQL Server T-SQL, ISNULL is a function that takes 2
> parameters; if the first argument is NULL it returns the second else it
> returns the first. For example:
> ISNULL(NULL, 1) returns 1
> ...and...
> ISNULL(2, 1) returns 2
> A rough translation of your code would go something like (watch out for word
> wrap and funny formatting)...
> AND (
> CASE
> WHEN ISNULL(Defendant.[CRIME_CLASSIFICATION_CODE], '') = ''
> THEN 9999
> WHEN Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9]' AND
> Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9][0-9]'
> THEN 9999
> ELSE
> CASE WHEN CONVERT(int, Defendant.[CRIME_CLASSIFICATION_CODE]) BETWEEN
> 1800 AND 1899
> THEN 1
> ELSE 0
> END
> END
> )
> However, it appears you want something akin to "WHERE
> Defendant.[CRIME_CLASSIFICATION_CODE] isn't an appropriate numeric
> representation or it is numeric and is inclusively in the range 1800-1899".
> If I'm correct, you could use something like this (tested in Query Analyzer
> with SQL Server 2000)...
> DECLARE @.tab TABLE (
> d varchar(32),
> ccc varchar(20)
> )
> INSERT @.tab VALUES ('Num outside range', '1750')
> INSERT @.tab VALUES ('Num in range', '1800')
> INSERT @.tab VALUES ('Not a num', 'aaa')
> INSERT @.tab VALUES ('NULL', NULL)
> INSERT @.tab VALUES ('Empty string', '')
> SELECT *
> FROM @.tab
> WHERE CASE WHEN ISNUMERIC(ccc) = 1
> THEN
> CASE WHEN CONVERT(int, ccc) BETWEEN 1800 AND 1899
> THEN 1
> ELSE 0
> END
> ELSE 1
> END = 1
> This returns everything in the test table except the 'Num outside range'
> row.
> Craig
Fiscal or Calendar Year Function error
Hello,
I am trying to create a function that returns the first day of the fiscal or
calendar year depending on the parameter supplied. However, I get the
error: Error 443: Invalid use of the 'getdate' within a function.
Any help would be appreciated.
Thanks in advance, sck10
CREATE FUNCTION dbo.fn_GetCalendarFiscalYear (@.YearType varchar(100))
RETURNS varchar(255)
AS
BEGIN
Declare @.strCalendarFiscal varchar(255)
SELECT @.strCalendarFiscal =
CASE
WHEN @.YearType = 'calendar' Then '1/1/' + Convert(varchar(255),
year(getdate()))
WHEN @.YearType = 'fiscal' Then
CASE
WHEN month(getdate()) BETWEEN 10 AND 12 THEN
'10/1/' + Convert(varchar(255), year(getdate()))
ELSE
'10/1/' + Convert(varchar(255), year(getdate()) - 1)
END
ELSE 'Pick "calendar" or "fiscal"'
END AS 'strFiscalYearStart'
RETURN @.strCalendarFiscal
Hi,
Welcome to use MSDN Managed Newsgroup!
I am so sorry that you can not put a function that returns a varible
result in a user defined function. Check MVP Aaron Bertrand's article for
this issue
How do I use GETDATE() within a User-Defined Function (UDF)?
http://www.aspfaq.com/2439
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
I am trying to create a function that returns the first day of the fiscal or
calendar year depending on the parameter supplied. However, I get the
error: Error 443: Invalid use of the 'getdate' within a function.
Any help would be appreciated.
Thanks in advance, sck10
CREATE FUNCTION dbo.fn_GetCalendarFiscalYear (@.YearType varchar(100))
RETURNS varchar(255)
AS
BEGIN
Declare @.strCalendarFiscal varchar(255)
SELECT @.strCalendarFiscal =
CASE
WHEN @.YearType = 'calendar' Then '1/1/' + Convert(varchar(255),
year(getdate()))
WHEN @.YearType = 'fiscal' Then
CASE
WHEN month(getdate()) BETWEEN 10 AND 12 THEN
'10/1/' + Convert(varchar(255), year(getdate()))
ELSE
'10/1/' + Convert(varchar(255), year(getdate()) - 1)
END
ELSE 'Pick "calendar" or "fiscal"'
END AS 'strFiscalYearStart'
RETURN @.strCalendarFiscal
Hi,
Welcome to use MSDN Managed Newsgroup!
I am so sorry that you can not put a function that returns a varible
result in a user defined function. Check MVP Aaron Bertrand's article for
this issue
How do I use GETDATE() within a User-Defined Function (UDF)?
http://www.aspfaq.com/2439
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
Fiscal or Calendar Year Function error
Hello,
I am trying to create a function that returns the first day of the fiscal or
calendar year depending on the parameter supplied. However, I get the
error: Error 443: Invalid use of the 'getdate' within a function.
Any help would be appreciated.
Thanks in advance, sck10
CREATE FUNCTION dbo.fn_GetCalendarFiscalYear (@.YearType varchar(100))
RETURNS varchar(255)
AS
BEGIN
Declare @.strCalendarFiscal varchar(255)
SELECT @.strCalendarFiscal = CASE
WHEN @.YearType = 'calendar' Then '1/1/' + Convert(varchar(255),
year(getdate()))
WHEN @.YearType = 'fiscal' Then
CASE
WHEN month(getdate()) BETWEEN 10 AND 12 THEN
'10/1/' + Convert(varchar(255), year(getdate()))
ELSE
'10/1/' + Convert(varchar(255), year(getdate()) - 1)
END
ELSE 'Pick "calendar" or "fiscal"'
END AS 'strFiscalYearStart'
RETURN @.strCalendarFiscalHi,
Welcome to use MSDN Managed Newsgroup!
I am so sorry that you can not put a function that returns a varible
result in a user defined function. Check MVP Aaron Bertrand's article for
this issue
How do I use GETDATE() within a User-Defined Function (UDF)?
http://www.aspfaq.com/2439
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
I am trying to create a function that returns the first day of the fiscal or
calendar year depending on the parameter supplied. However, I get the
error: Error 443: Invalid use of the 'getdate' within a function.
Any help would be appreciated.
Thanks in advance, sck10
CREATE FUNCTION dbo.fn_GetCalendarFiscalYear (@.YearType varchar(100))
RETURNS varchar(255)
AS
BEGIN
Declare @.strCalendarFiscal varchar(255)
SELECT @.strCalendarFiscal = CASE
WHEN @.YearType = 'calendar' Then '1/1/' + Convert(varchar(255),
year(getdate()))
WHEN @.YearType = 'fiscal' Then
CASE
WHEN month(getdate()) BETWEEN 10 AND 12 THEN
'10/1/' + Convert(varchar(255), year(getdate()))
ELSE
'10/1/' + Convert(varchar(255), year(getdate()) - 1)
END
ELSE 'Pick "calendar" or "fiscal"'
END AS 'strFiscalYearStart'
RETURN @.strCalendarFiscalHi,
Welcome to use MSDN Managed Newsgroup!
I am so sorry that you can not put a function that returns a varible
result in a user defined function. Check MVP Aaron Bertrand's article for
this issue
How do I use GETDATE() within a User-Defined Function (UDF)?
http://www.aspfaq.com/2439
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Fiscal or Calendar Year Function error
Hello,
I am trying to create a function that returns the first day of the fiscal or
calendar year depending on the parameter supplied. However, I get the
error: Error 443: Invalid use of the 'getdate' within a function.
Any help would be appreciated.
Thanks in advance, sck10
CREATE FUNCTION dbo.fn_GetCalendarFiscalYear (@.YearType varchar(100))
RETURNS varchar(255)
AS
BEGIN
Declare @.strCalendarFiscal varchar(255)
SELECT @.strCalendarFiscal =
CASE
WHEN @.YearType = 'calendar' Then '1/1/' + Convert(varchar(255),
year(getdate()))
WHEN @.YearType = 'fiscal' Then
CASE
WHEN month(getdate()) BETWEEN 10 AND 12 THEN
'10/1/' + Convert(varchar(255), year(getdate()))
ELSE
'10/1/' + Convert(varchar(255), year(getdate()) - 1)
END
ELSE 'Pick "calendar" or "fiscal"'
END AS 'strFiscalYearStart'
RETURN @.strCalendarFiscalHi,
Welcome to use MSDN Managed Newsgroup!
I am so sorry that you can not put a function that returns a varible
result in a user defined function. Check MVP Aaron Bertrand's article for
this issue
How do I use GETDATE() within a User-Defined Function (UDF)?
http://www.aspfaq.com/2439
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
I am trying to create a function that returns the first day of the fiscal or
calendar year depending on the parameter supplied. However, I get the
error: Error 443: Invalid use of the 'getdate' within a function.
Any help would be appreciated.
Thanks in advance, sck10
CREATE FUNCTION dbo.fn_GetCalendarFiscalYear (@.YearType varchar(100))
RETURNS varchar(255)
AS
BEGIN
Declare @.strCalendarFiscal varchar(255)
SELECT @.strCalendarFiscal =
CASE
WHEN @.YearType = 'calendar' Then '1/1/' + Convert(varchar(255),
year(getdate()))
WHEN @.YearType = 'fiscal' Then
CASE
WHEN month(getdate()) BETWEEN 10 AND 12 THEN
'10/1/' + Convert(varchar(255), year(getdate()))
ELSE
'10/1/' + Convert(varchar(255), year(getdate()) - 1)
END
ELSE 'Pick "calendar" or "fiscal"'
END AS 'strFiscalYearStart'
RETURN @.strCalendarFiscalHi,
Welcome to use MSDN Managed Newsgroup!
I am so sorry that you can not put a function that returns a varible
result in a user defined function. Check MVP Aaron Bertrand's article for
this issue
How do I use GETDATE() within a User-Defined Function (UDF)?
http://www.aspfaq.com/2439
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
2012年3月7日星期三
FirstWeekOfYear as parameter of DatePart
Haw to make FirstWeekOfDay parameter work in SQL Server 2000.
In Access 2000 datepart function has 3 parameters.
Why it is't in SQL Server 2000.
Help pleaseStraight from Books Online:
SET DATEFIRST
Sets the first day of the week to a number from 1 through 7.
Syntax
SET DATEFIRST { number | @.number_var }
Arguments
number | @.number_var
Is an integer indicating the first day of the week, and can be one of these values.
Value First day of the week is
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
7 (default, U.S. English) Sunday
Remarks
Use the @.@.DATEFIRST function to check the current setting of SET DATEFIRST.
The setting of SET DATEFIRST is set at execute or run time and not at parse time.
Permissions
SET DATEFIRST permissions default to all users.
Examples
This example displays the day of the week for a date value and shows the effects of changing the DATEFIRST setting.
-- SET DATEFIRST to U.S. English default value of 7.
SET DATEFIRST 7
GO
SELECT CAST('1/1/99' AS datetime), DATEPART(dw, '1/1/99')
-- January 1, 1999 is a Friday. Because the U.S. English default
-- specifies Sunday as the first day of the week, DATEPART of 1/1/99
-- (Friday) yields a value of 6, because Friday is the sixth day of the
-- week when starting with Sunday as day 1.
SET DATEFIRST 3
-- Because Wednesday is now considered the first day of the week,
-- DATEPART should now show that 1/1/99 (a Friday) is the third day of the -- week. The following DATEPART function should return a value of 3.
SELECT CAST('1/1/99' AS datetime), DATEPART(dw, '1/1/99')
GO
In Access 2000 datepart function has 3 parameters.
Why it is't in SQL Server 2000.
Help pleaseStraight from Books Online:
SET DATEFIRST
Sets the first day of the week to a number from 1 through 7.
Syntax
SET DATEFIRST { number | @.number_var }
Arguments
number | @.number_var
Is an integer indicating the first day of the week, and can be one of these values.
Value First day of the week is
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
7 (default, U.S. English) Sunday
Remarks
Use the @.@.DATEFIRST function to check the current setting of SET DATEFIRST.
The setting of SET DATEFIRST is set at execute or run time and not at parse time.
Permissions
SET DATEFIRST permissions default to all users.
Examples
This example displays the day of the week for a date value and shows the effects of changing the DATEFIRST setting.
-- SET DATEFIRST to U.S. English default value of 7.
SET DATEFIRST 7
GO
SELECT CAST('1/1/99' AS datetime), DATEPART(dw, '1/1/99')
-- January 1, 1999 is a Friday. Because the U.S. English default
-- specifies Sunday as the first day of the week, DATEPART of 1/1/99
-- (Friday) yields a value of 6, because Friday is the sixth day of the
-- week when starting with Sunday as day 1.
SET DATEFIRST 3
-- Because Wednesday is now considered the first day of the week,
-- DATEPART should now show that 1/1/99 (a Friday) is the third day of the -- week. The following DATEPART function should return a value of 3.
SELECT CAST('1/1/99' AS datetime), DATEPART(dw, '1/1/99')
GO
First(), Last(0 function
Hello,
I have this query in access and I woul like to convert it to SQL.
--
SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
FROM DIMAGE INNER JOIN KEYC
ON DIMAGE.ProvID = KEYC.ProvID
WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
GROUP BY KEYC.ProvID;
--
the problem here is the Last function. I am running SQL 7 and it returns
with "'Last' is not a known function."
Any sugestions?
-ScottScott,
Try MIN() and MAX().
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have this query in access and I woul like to convert it to SQL.
> --
> SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> FROM DIMAGE INNER JOIN KEYC
> ON DIMAGE.ProvID = KEYC.ProvID
> WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> GROUP BY KEYC.ProvID;
> --
> the problem here is the Last function. I am running SQL 7 and it returns
> with "'Last' is not a known function."
> Any sugestions?
> --
> -Scott
>|||Excellent...That worked
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> Scott,
> Try MIN() and MAX().
> HTH
> Jerry
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
returns[vbcol=seagreen]
>|||Be aware that although you can use MAX or MIN to avoid the syntax error, you
might not get the same behavior as the original Access query. MAX/MIN will
suffice if your intent is to get an arbitrary value from the grouping.
However, since FIRST and LAST aggregate functions return values based on the
chronological order if insertion, you'll need a datetime or identity column
along with a subquery to emulate those functions in Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:O%23kYnJ7vFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Excellent...That worked
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> returns
>
I have this query in access and I woul like to convert it to SQL.
--
SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
FROM DIMAGE INNER JOIN KEYC
ON DIMAGE.ProvID = KEYC.ProvID
WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
GROUP BY KEYC.ProvID;
--
the problem here is the Last function. I am running SQL 7 and it returns
with "'Last' is not a known function."
Any sugestions?
-ScottScott,
Try MIN() and MAX().
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have this query in access and I woul like to convert it to SQL.
> --
> SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> FROM DIMAGE INNER JOIN KEYC
> ON DIMAGE.ProvID = KEYC.ProvID
> WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> GROUP BY KEYC.ProvID;
> --
> the problem here is the Last function. I am running SQL 7 and it returns
> with "'Last' is not a known function."
> Any sugestions?
> --
> -Scott
>|||Excellent...That worked
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> Scott,
> Try MIN() and MAX().
> HTH
> Jerry
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
returns[vbcol=seagreen]
>|||Be aware that although you can use MAX or MIN to avoid the syntax error, you
might not get the same behavior as the original Access query. MAX/MIN will
suffice if your intent is to get an arbitrary value from the grouping.
However, since FIRST and LAST aggregate functions return values based on the
chronological order if insertion, you'll need a datetime or identity column
along with a subquery to emulate those functions in Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:O%23kYnJ7vFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Excellent...That worked
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> returns
>
First(), Last(0 function
Hello,
I have this query in access and I woul like to convert it to SQL.
SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
FROM DIMAGE INNER JOIN KEYC
ON DIMAGE.ProvID = KEYC.ProvID
WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
GROUP BY KEYC.ProvID;
the problem here is the Last function. I am running SQL 7 and it returns
with "'Last' is not a known function."
Any sugestions?
-Scott
Scott,
Try MIN() and MAX().
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have this query in access and I woul like to convert it to SQL.
> --
> SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> FROM DIMAGE INNER JOIN KEYC
> ON DIMAGE.ProvID = KEYC.ProvID
> WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> GROUP BY KEYC.ProvID;
> --
> the problem here is the Last function. I am running SQL 7 and it returns
> with "'Last' is not a known function."
> Any sugestions?
> --
> -Scott
>
|||Excellent...That worked
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Scott,
> Try MIN() and MAX().
> HTH
> Jerry
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
returns
>
|||Be aware that although you can use MAX or MIN to avoid the syntax error, you
might not get the same behavior as the original Access query. MAX/MIN will
suffice if your intent is to get an arbitrary value from the grouping.
However, since FIRST and LAST aggregate functions return values based on the
chronological order if insertion, you'll need a datetime or identity column
along with a subquery to emulate those functions in Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:O%23kYnJ7vFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Excellent...That worked
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> returns
>
I have this query in access and I woul like to convert it to SQL.
SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
FROM DIMAGE INNER JOIN KEYC
ON DIMAGE.ProvID = KEYC.ProvID
WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
GROUP BY KEYC.ProvID;
the problem here is the Last function. I am running SQL 7 and it returns
with "'Last' is not a known function."
Any sugestions?
-Scott
Scott,
Try MIN() and MAX().
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have this query in access and I woul like to convert it to SQL.
> --
> SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> FROM DIMAGE INNER JOIN KEYC
> ON DIMAGE.ProvID = KEYC.ProvID
> WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> GROUP BY KEYC.ProvID;
> --
> the problem here is the Last function. I am running SQL 7 and it returns
> with "'Last' is not a known function."
> Any sugestions?
> --
> -Scott
>
|||Excellent...That worked
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Scott,
> Try MIN() and MAX().
> HTH
> Jerry
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
returns
>
|||Be aware that although you can use MAX or MIN to avoid the syntax error, you
might not get the same behavior as the original Access query. MAX/MIN will
suffice if your intent is to get an arbitrary value from the grouping.
However, since FIRST and LAST aggregate functions return values based on the
chronological order if insertion, you'll need a datetime or identity column
along with a subquery to emulate those functions in Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:O%23kYnJ7vFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Excellent...That worked
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> returns
>
First(), Last(0 function
Hello,
I have this query in access and I woul like to convert it to SQL.
--
SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
FROM DIMAGE INNER JOIN KEYC
ON DIMAGE.ProvID = KEYC.ProvID
WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
GROUP BY KEYC.ProvID;
--
the problem here is the Last function. I am running SQL 7 and it returns
with "'Last' is not a known function."
Any sugestions?
--
-ScottScott,
Try MIN() and MAX().
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have this query in access and I woul like to convert it to SQL.
> --
> SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> FROM DIMAGE INNER JOIN KEYC
> ON DIMAGE.ProvID = KEYC.ProvID
> WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> GROUP BY KEYC.ProvID;
> --
> the problem here is the Last function. I am running SQL 7 and it returns
> with "'Last' is not a known function."
> Any sugestions?
> --
> -Scott
>|||Excellent...That worked
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> Scott,
> Try MIN() and MAX().
> HTH
> Jerry
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> > Hello,
> > I have this query in access and I woul like to convert it to SQL.
> > --
> > SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> > FROM DIMAGE INNER JOIN KEYC
> > ON DIMAGE.ProvID = KEYC.ProvID
> > WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> > ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> > GROUP BY KEYC.ProvID;
> > --
> >
> > the problem here is the Last function. I am running SQL 7 and it
returns
> > with "'Last' is not a known function."
> > Any sugestions?
> >
> > --
> > -Scott
> >
> >
>|||Be aware that although you can use MAX or MIN to avoid the syntax error, you
might not get the same behavior as the original Access query. MAX/MIN will
suffice if your intent is to get an arbitrary value from the grouping.
However, since FIRST and LAST aggregate functions return values based on the
chronological order if insertion, you'll need a datetime or identity column
along with a subquery to emulate those functions in Transact-SQL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:O%23kYnJ7vFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Excellent...That worked
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
>> Scott,
>> Try MIN() and MAX().
>> HTH
>> Jerry
>> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
>> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
>> > Hello,
>> > I have this query in access and I woul like to convert it to SQL.
>> > --
>> > SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
>> > FROM DIMAGE INNER JOIN KEYC
>> > ON DIMAGE.ProvID = KEYC.ProvID
>> > WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
>> > ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
>> > GROUP BY KEYC.ProvID;
>> > --
>> >
>> > the problem here is the Last function. I am running SQL 7 and it
> returns
>> > with "'Last' is not a known function."
>> > Any sugestions?
>> >
>> > --
>> > -Scott
>> >
>> >
>>
>
I have this query in access and I woul like to convert it to SQL.
--
SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
FROM DIMAGE INNER JOIN KEYC
ON DIMAGE.ProvID = KEYC.ProvID
WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
GROUP BY KEYC.ProvID;
--
the problem here is the Last function. I am running SQL 7 and it returns
with "'Last' is not a known function."
Any sugestions?
--
-ScottScott,
Try MIN() and MAX().
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have this query in access and I woul like to convert it to SQL.
> --
> SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> FROM DIMAGE INNER JOIN KEYC
> ON DIMAGE.ProvID = KEYC.ProvID
> WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> GROUP BY KEYC.ProvID;
> --
> the problem here is the Last function. I am running SQL 7 and it returns
> with "'Last' is not a known function."
> Any sugestions?
> --
> -Scott
>|||Excellent...That worked
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
> Scott,
> Try MIN() and MAX().
> HTH
> Jerry
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
> > Hello,
> > I have this query in access and I woul like to convert it to SQL.
> > --
> > SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
> > FROM DIMAGE INNER JOIN KEYC
> > ON DIMAGE.ProvID = KEYC.ProvID
> > WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
> > ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
> > GROUP BY KEYC.ProvID;
> > --
> >
> > the problem here is the Last function. I am running SQL 7 and it
returns
> > with "'Last' is not a known function."
> > Any sugestions?
> >
> > --
> > -Scott
> >
> >
>|||Be aware that although you can use MAX or MIN to avoid the syntax error, you
might not get the same behavior as the original Access query. MAX/MIN will
suffice if your intent is to get an arbitrary value from the grouping.
However, since FIRST and LAST aggregate functions return values based on the
chronological order if insertion, you'll need a datetime or identity column
along with a subquery to emulate those functions in Transact-SQL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:O%23kYnJ7vFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Excellent...That worked
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fY9216vFHA.464@.TK2MSFTNGP15.phx.gbl...
>> Scott,
>> Try MIN() and MAX().
>> HTH
>> Jerry
>> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
>> news:u29xm06vFHA.2556@.TK2MSFTNGP15.phx.gbl...
>> > Hello,
>> > I have this query in access and I woul like to convert it to SQL.
>> > --
>> > SELECT KEYC.ProvID, Last(DIMAGE.ID) AS LastOfID
>> > FROM DIMAGE INNER JOIN KEYC
>> > ON DIMAGE.ProvID = KEYC.ProvID
>> > WHERE (((KEYC.PlanID)=10072 Or (KEYC.PlanID)=10125) AND
>> > ((DIMAGE.Type)="ATT" Or (DIMAGE.Type)="A00" Or (DIMAGE.Type)="ATTST"))
>> > GROUP BY KEYC.ProvID;
>> > --
>> >
>> > the problem here is the Last function. I am running SQL 7 and it
> returns
>> > with "'Last' is not a known function."
>> > Any sugestions?
>> >
>> > --
>> > -Scott
>> >
>> >
>>
>
First() to SQL?
Hi,
I'm working with a project translating Access databases to SQL Server.
Can anyone explain the mystic function First() to me?
How can it's function be replaced by SQL?
(I've posted this in the Accessforum also)As near as I remember the First() function is used as a sort of "get out of jail free card" for group by situations. Instead of grouping by the value in the column, or summing up the column, or getting a max or min of the column, Access grabs the first value it sees. Because of this, you can end up with different results in different situations, which is generally bad for business. Here is a link to some of the help I found..
http://office.microsoft.com/en-us/assistance/HA010345631033.aspx
In SQL Server, I would avoid using the concept of "first" as it does not really have any meaning, unless you impose a meaning like "chronologically first entered", in which case you would (hopefully) have an entered date to work with. Hope this helps.|||First() and Last automatically go to either the first or last record in your dataset (presumably sorted) and returns the field you specify.
In SQL you will need to do this in two stages. First, find the Primary Key value of the First or Last record, and then look up the value of the field in the record associated with that key.
select [YourValue] as FirstValue
from [YourTable]
inner join
(select min([SortKey]) as FirstKey from [YourTable]) Subquery
where [YourTable].[SortKey] = Subquery.FirstKey
If you sortkey is not unique, you will get multiple records in your result.
I'm working with a project translating Access databases to SQL Server.
Can anyone explain the mystic function First() to me?
How can it's function be replaced by SQL?
(I've posted this in the Accessforum also)As near as I remember the First() function is used as a sort of "get out of jail free card" for group by situations. Instead of grouping by the value in the column, or summing up the column, or getting a max or min of the column, Access grabs the first value it sees. Because of this, you can end up with different results in different situations, which is generally bad for business. Here is a link to some of the help I found..
http://office.microsoft.com/en-us/assistance/HA010345631033.aspx
In SQL Server, I would avoid using the concept of "first" as it does not really have any meaning, unless you impose a meaning like "chronologically first entered", in which case you would (hopefully) have an entered date to work with. Hope this helps.|||First() and Last automatically go to either the first or last record in your dataset (presumably sorted) and returns the field you specify.
In SQL you will need to do this in two stages. First, find the Primary Key value of the First or Last record, and then look up the value of the field in the record associated with that key.
select [YourValue] as FirstValue
from [YourTable]
inner join
(select min([SortKey]) as FirstKey from [YourTable]) Subquery
where [YourTable].[SortKey] = Subquery.FirstKey
If you sortkey is not unique, you will get multiple records in your result.
First() and last() function -> course says yes, Book on line says no
Richard,
> Could they make that big a mistake?
Apparently so :-)
Chief Tenaya
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:eGRLEejHEHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hi Everyone,
> I'm taking an online course to learn more about SQL. I found this great
> site...
> http://www.w3schools.com/sql/default.asp
> In one of the lessons, it mentions SQL server's functions. However,
> when I tried it, it doesn't work. I checked the Books online and it
> appears there is no such function. Could they make that big a mistake?
> I know there are other ways of doing the same thing but I found the site
> very accurate and this surprises me.
> Here is the page in question. The problems: it claims there is a First
> (and Last) function...
> http://www.w3schools.com/sql/func_first.asp
> -- here is their example
> "The FIRST function returns the value of the first record in the
> specified field.
> Tip: Use the ORDER BY clause to order the records!
> Syntax
> SELECT FIRST(column) AS [expression]
> FROM table
> Example
> SELECT FIRST(Age) AS lowest_age
> FROM Persons
> ORDER BY Age"
You need the TOP modifier. eg.
SELECT TOP 1 Age AS lowest_age
FROM Persons
ORDER BY Age
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:eGRLEejHEHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hi Everyone,
> I'm taking an online course to learn more about SQL. I found this great
> site...
> http://www.w3schools.com/sql/default.asp
> In one of the lessons, it mentions SQL server's functions. However,
> when I tried it, it doesn't work. I checked the Books online and it
> appears there is no such function. Could they make that big a mistake?
> I know there are other ways of doing the same thing but I found the site
> very accurate and this surprises me.
> Here is the page in question. The problems: it claims there is a First
> (and Last) function...
> http://www.w3schools.com/sql/func_first.asp
> -- here is their example
> "The FIRST function returns the value of the first record in the
> specified field.
> Tip: Use the ORDER BY clause to order the records!
> Syntax
> SELECT FIRST(column) AS [expression]
> FROM table
> Example
> SELECT FIRST(Age) AS lowest_age
> FROM Persons
> ORDER BY Age"
> Could they make that big a mistake?
Apparently so :-)
Chief Tenaya
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:eGRLEejHEHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hi Everyone,
> I'm taking an online course to learn more about SQL. I found this great
> site...
> http://www.w3schools.com/sql/default.asp
> In one of the lessons, it mentions SQL server's functions. However,
> when I tried it, it doesn't work. I checked the Books online and it
> appears there is no such function. Could they make that big a mistake?
> I know there are other ways of doing the same thing but I found the site
> very accurate and this surprises me.
> Here is the page in question. The problems: it claims there is a First
> (and Last) function...
> http://www.w3schools.com/sql/func_first.asp
> -- here is their example
> "The FIRST function returns the value of the first record in the
> specified field.
> Tip: Use the ORDER BY clause to order the records!
> Syntax
> SELECT FIRST(column) AS [expression]
> FROM table
> Example
> SELECT FIRST(Age) AS lowest_age
> FROM Persons
> ORDER BY Age"
You need the TOP modifier. eg.
SELECT TOP 1 Age AS lowest_age
FROM Persons
ORDER BY Age
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:eGRLEejHEHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hi Everyone,
> I'm taking an online course to learn more about SQL. I found this great
> site...
> http://www.w3schools.com/sql/default.asp
> In one of the lessons, it mentions SQL server's functions. However,
> when I tried it, it doesn't work. I checked the Books online and it
> appears there is no such function. Could they make that big a mistake?
> I know there are other ways of doing the same thing but I found the site
> very accurate and this surprises me.
> Here is the page in question. The problems: it claims there is a First
> (and Last) function...
> http://www.w3schools.com/sql/func_first.asp
> -- here is their example
> "The FIRST function returns the value of the first record in the
> specified field.
> Tip: Use the ORDER BY clause to order the records!
> Syntax
> SELECT FIRST(column) AS [expression]
> FROM table
> Example
> SELECT FIRST(Age) AS lowest_age
> FROM Persons
> ORDER BY Age"
First() and last() function -> course says yes, Book on line
Hi,
I guess that's what they meant by each flavour of SQL can have slightly
different syntax and to check with the manuals.
Thanks for confirming it.
Richard
Tenaya wrote:
> Richard,
>
>
> Apparently so :-)
> Chief Tenaya
Hi Greg,
This sounds like it should work. I checked Book Online but can't find
any mention of the top modifier ... aside from inside the complicated
full explaination of the SELECT statement. Is this what you meant?
I never realized who powerful (and complicated) the SELECT statement is
Thanks for your help
Richard
From Books Online's select (described)
...
< query specification > ::=
SELECT [ ALL | DISTINCT ]
[ { TOP integer | TOP integer PERCENT } [ WITH TIES ] ]
< select_list >
[ INTO new_table ]
[ FROM { < table_source > } [ ,...n ] ]
[ WHERE < search_condition > ]
[ GROUP BY [ ALL ] group_by_expression [ ,...n ]
[ WITH { CUBE | ROLLUP } ]
]
[ HAVING < search_condition > ]
Greg Low (MVP) wrote:
> You need the TOP modifier. eg.
> SELECT TOP 1 Age AS lowest_age
> FROM Persons
> ORDER BY Age
> HTH,
I guess that's what they meant by each flavour of SQL can have slightly
different syntax and to check with the manuals.
Thanks for confirming it.
Richard
Tenaya wrote:
> Richard,
>
>
> Apparently so :-)
> Chief Tenaya
Hi Greg,
This sounds like it should work. I checked Book Online but can't find
any mention of the top modifier ... aside from inside the complicated
full explaination of the SELECT statement. Is this what you meant?
I never realized who powerful (and complicated) the SELECT statement is
Thanks for your help
Richard
From Books Online's select (described)
...
< query specification > ::=
SELECT [ ALL | DISTINCT ]
[ { TOP integer | TOP integer PERCENT } [ WITH TIES ] ]
< select_list >
[ INTO new_table ]
[ FROM { < table_source > } [ ,...n ] ]
[ WHERE < search_condition > ]
[ GROUP BY [ ALL ] group_by_expression [ ,...n ]
[ WITH { CUBE | ROLLUP } ]
]
[ HAVING < search_condition > ]
Greg Low (MVP) wrote:
> You need the TOP modifier. eg.
> SELECT TOP 1 Age AS lowest_age
> FROM Persons
> ORDER BY Age
> HTH,
2012年2月26日星期日
First Saturday Of The Year
Hi,
Is there a function that could return the first saturday of the year? If
not, is it possible to make use of existing functions to arrive at an
equivalent function to do this?
Regards,
EricEric,
I think the easiest way to do this is to build a calendar table that holds
the data, then you can simply join to this.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Eric" wrote:
> Hi,
> Is there a function that could return the first saturday of the year? If
> not, is it possible to make use of existing functions to arrive at an
> equivalent function to do this?
> Regards,
> Eric|||There is no bult-in function to do this, but as Mark says i would prefer
building up a calendar table (Temporay, you only need the at leat first 7
Days of the year) and get the specific Weekday (depending on your sttings
for the DATEFIRST setting.
Creating of a temptale with calandar entries can be found here.:
http://www.aspfaq.com/show.asp?id=2519
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Eric" <Eric@.discussions.microsoft.com> schrieb im Newsbeitrag
news:664EF52B-BDAE-4A61-810F-D1B3FD5F0B45@.microsoft.com...
> Hi,
> Is there a function that could return the first saturday of the year? If
> not, is it possible to make use of existing functions to arrive at an
> equivalent function to do this?
> Regards,
> Eric|||On Tue, 10 May 2005 18:32:01 -0700, Eric wrote:
>Hi,
>Is there a function that could return the first saturday of the year? If
>not, is it possible to make use of existing functions to arrive at an
>equivalent function to do this?
>Regards,
>Eric
Hi Eric,
Mark and Jens are correct: a calendar table is probably the best
solution for this. However, here's a function that will give you the
desired result for years 2000 and later (assuming that the date is ion a
column called TheDate):
SELECT DATEADD(day,
DATEDIFF (day,
'20000101',
CAST(DATEPART(year, TheDate) AS varchar)
+ '0107') / 7 * 7,
'20000101')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Is there a function that could return the first saturday of the year? If
not, is it possible to make use of existing functions to arrive at an
equivalent function to do this?
Regards,
EricEric,
I think the easiest way to do this is to build a calendar table that holds
the data, then you can simply join to this.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Eric" wrote:
> Hi,
> Is there a function that could return the first saturday of the year? If
> not, is it possible to make use of existing functions to arrive at an
> equivalent function to do this?
> Regards,
> Eric|||There is no bult-in function to do this, but as Mark says i would prefer
building up a calendar table (Temporay, you only need the at leat first 7
Days of the year) and get the specific Weekday (depending on your sttings
for the DATEFIRST setting.
Creating of a temptale with calandar entries can be found here.:
http://www.aspfaq.com/show.asp?id=2519
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Eric" <Eric@.discussions.microsoft.com> schrieb im Newsbeitrag
news:664EF52B-BDAE-4A61-810F-D1B3FD5F0B45@.microsoft.com...
> Hi,
> Is there a function that could return the first saturday of the year? If
> not, is it possible to make use of existing functions to arrive at an
> equivalent function to do this?
> Regards,
> Eric|||On Tue, 10 May 2005 18:32:01 -0700, Eric wrote:
>Hi,
>Is there a function that could return the first saturday of the year? If
>not, is it possible to make use of existing functions to arrive at an
>equivalent function to do this?
>Regards,
>Eric
Hi Eric,
Mark and Jens are correct: a calendar table is probably the best
solution for this. However, here's a function that will give you the
desired result for years 2000 and later (assuming that the date is ion a
column called TheDate):
SELECT DATEADD(day,
DATEDIFF (day,
'20000101',
CAST(DATEPART(year, TheDate) AS varchar)
+ '0107') / 7 * 7,
'20000101')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
First day of a month
Hi to all..
is there a function in sql server 2k that returns the first day of a date (month)??
thx!
Ale.Hmmmm..
SELECT 1
??????|||I'm not sure that I understand, but let's try an example and maybe we can at least get closer:DECLARE @.target DATETIME
SET @.target = GetDate()
SELECT Convert(DATETIME, Convert(CHAR(8), @.target, 121) + '01'), @.target-PatP|||i wouldn't to use cast and convert in string..
i thinked there's a database function..
for example.. dateserial in Visual Basic..
i think that this function are so good =)
don't you think? =))))
ok.. thx anyway..|||What do you see as the problem? If you can coach me a bit as to what you would like to see, I might be able to find you a better answer, but I think that this code does what you requested.
-PatP|||What? 1 is not the first day of the month?
Show us an example of what you are looking for....
Usually the ask for the last day of the month....|||http://www.sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk
Maybe this would help :)
is there a function in sql server 2k that returns the first day of a date (month)??
thx!
Ale.Hmmmm..
SELECT 1
??????|||I'm not sure that I understand, but let's try an example and maybe we can at least get closer:DECLARE @.target DATETIME
SET @.target = GetDate()
SELECT Convert(DATETIME, Convert(CHAR(8), @.target, 121) + '01'), @.target-PatP|||i wouldn't to use cast and convert in string..
i thinked there's a database function..
for example.. dateserial in Visual Basic..
i think that this function are so good =)
don't you think? =))))
ok.. thx anyway..|||What do you see as the problem? If you can coach me a bit as to what you would like to see, I might be able to find you a better answer, but I think that this code does what you requested.
-PatP|||What? 1 is not the first day of the month?
Show us an example of what you are looking for....
Usually the ask for the last day of the month....|||http://www.sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk
Maybe this would help :)
2012年2月24日星期五
Firing User defined Function from Select, without using function name, is this possib
Hi all, Can anyone tell me if it is possible to fire a user defined
function in SQL Server directly from an ordinary select function.
Example:
I have a function fx_Str_Title_Case(varchar). (change string to title
case, caps first letter of each word in sentence).
At present I call this as follows:
SELECT fx_Str_Title_Case(aColumn) AS Result
FROM aTable
I wont to know if I can call this like this:
SELECT aColumn AS Result
FROM aTable
to get the same result?
ThanksPut your function in a view and query the view.
David Portas
SQL Server MVP
--|||Thanks, I though of that, but was wondering if there is any way to
trigger the function based on constraints on the table or something
like.|||You can use a function in a constraint but constraints are referenced
only for updates, not for a SELECT.
David Portas
SQL Server MVP
--|||You can create a computed column with the UDF call.
CREATE TABLE Test(name VARCHAR(15), ProperName as (dbo.Proper(name)))
INSERT INTO Test Values('abc xyz')
INSERT INTO Test Values('abc XYZ aVC')
SELECT * FROM Test
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<philip.mckee@.pramerica.ie> wrote in message
news:1123672753.295824.65610@.g44g2000cwa.googlegroups.com...
> Thanks, I though of that, but was wondering if there is any way to
> trigger the function based on constraints on the table or something
> like.
>|||On 10 Aug 2005 04:15:50 -0700, David Portas wrote:
>Put your function in a view and query the view.
Or in a computed column in the table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||What is wrong with specifying the function, and how would you know what
function is beging "fired"?
<philip.mckee@.pramerica.ie> wrote in message
news:1123672116.950885.206380@.g49g2000cwa.googlegroups.com...
> Hi all, Can anyone tell me if it is possible to fire a user defined
> function in SQL Server directly from an ordinary select function.
> Example:
> I have a function fx_Str_Title_Case(varchar). (change string to title
> case, caps first letter of each word in sentence).
> At present I call this as follows:
> SELECT fx_Str_Title_Case(aColumn) AS Result
> FROM aTable
> I wont to know if I can call this like this:
> SELECT aColumn AS Result
> FROM aTable
> to get the same result?
> Thanks
>
function in SQL Server directly from an ordinary select function.
Example:
I have a function fx_Str_Title_Case(varchar). (change string to title
case, caps first letter of each word in sentence).
At present I call this as follows:
SELECT fx_Str_Title_Case(aColumn) AS Result
FROM aTable
I wont to know if I can call this like this:
SELECT aColumn AS Result
FROM aTable
to get the same result?
ThanksPut your function in a view and query the view.
David Portas
SQL Server MVP
--|||Thanks, I though of that, but was wondering if there is any way to
trigger the function based on constraints on the table or something
like.|||You can use a function in a constraint but constraints are referenced
only for updates, not for a SELECT.
David Portas
SQL Server MVP
--|||You can create a computed column with the UDF call.
CREATE TABLE Test(name VARCHAR(15), ProperName as (dbo.Proper(name)))
INSERT INTO Test Values('abc xyz')
INSERT INTO Test Values('abc XYZ aVC')
SELECT * FROM Test
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<philip.mckee@.pramerica.ie> wrote in message
news:1123672753.295824.65610@.g44g2000cwa.googlegroups.com...
> Thanks, I though of that, but was wondering if there is any way to
> trigger the function based on constraints on the table or something
> like.
>|||On 10 Aug 2005 04:15:50 -0700, David Portas wrote:
>Put your function in a view and query the view.
Or in a computed column in the table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||What is wrong with specifying the function, and how would you know what
function is beging "fired"?
<philip.mckee@.pramerica.ie> wrote in message
news:1123672116.950885.206380@.g49g2000cwa.googlegroups.com...
> Hi all, Can anyone tell me if it is possible to fire a user defined
> function in SQL Server directly from an ordinary select function.
> Example:
> I have a function fx_Str_Title_Case(varchar). (change string to title
> case, caps first letter of each word in sentence).
> At present I call this as follows:
> SELECT fx_Str_Title_Case(aColumn) AS Result
> FROM aTable
> I wont to know if I can call this like this:
> SELECT aColumn AS Result
> FROM aTable
> to get the same result?
> Thanks
>
订阅：
博文 (Atom)