help with sp_executesql

hi i'm trying to do this

DECLARE @.VARR Nvarchar(2500)
DECLARE @.VARR1 Nvarchar(2500)
DECLARE @.VARC Nvarchar(2000)
DECLARE @.VARP Nvarchar(1000)

SET @.VARC= @.VARC + ' DECLARE @.nBKN1 NUMERIC(3,0) DECLARE @.nAPP1 NUMERIC(2,0) DECLARE @.nACT1 char(20)'
SET @.VARC= @.VARC + ' DECLARE @.nGRP1 NUMERIC(2,0) DECLARE @.nCSH1 NUMERIC(12,2) DECLARE @.nNCH1 NUMERIC(12,2) DECLARE @.nTOT1 NUMERIC(12,2) DECLARE @.nCHB1 NUMERIC(12,2) '

@.VARR1 AND @.VARR AND @.VARP is similar to @.VARC. At the end i trying to open a Cursor.. all the query are inside in @.varc + @.varr + @.var1 + @.varp

WHEN I EXECUTE THIS

EXEC('EXEC sp_executesql ' + @.VARC + @.VARR + @.VARR1 + @.VARP )

THE RESULT IS

Procedure 'sp_executesql' expects parameter '@.statement', which was not supplied.

WHY?

DECLARE @.VARR Nvarchar(2500)
DECLARE @.VARR1 Nvarchar(2500)
DECLARE @.VARC Nvarchar(4000) -- nvarchar(max) SQL 2005

DECLARE @.VARP Nvarchar(1000)

SET @.VARC= @.VARC + ' DECLARE @.nBKN1 NUMERIC(3,0) DECLARE @.nAPP1 NUMERIC(2,0) DECLARE @.nACT1 char(20)'
SET

@.VARC= @.VARC + ' DECLARE @.nGRP1 NUMERIC(2,0) DECLARE @.nCSH1

NUMERIC(12,2) DECLARE @.nNCH1 NUMERIC(12,2) DECLARE @.nTOT1 NUMERIC(12,2)

DECLARE @.nCHB1 NUMERIC(12,2) '

@.VARR1

AND @.VARR AND @.VARP is similar to @.VARC. At the end i trying to open a

Cursor.. all the query are inside in @.varc + @.varr + @.var1 + @.varp

WHEN I EXECUTE THIS

set @.Varc = 'EXEC sp_executesql' + @.VARC + @.VARR + @.VARR1 + @.VARP

EXEC(@.Varc)|||

the real problem is that @.varc and @.VARR1 AND @.VARR AND @.VARP has more that 4000 chars... and i know that sp_executesql Store only accept ntext, nvarchar, image.... i using sql server 2000

i'm trying to do this

create PROCEDURE store_xxx1
(
@.i_AMLCBANKNU INT,
@.i_PERIOD INT,
@.dPRC DATETIME,
@.sDay INT,
@.eDay INT,
@.i_AMLDPERIFR DATETIME,
@.i_AMLDPERITO DATETIME
)
AS
-- SET OPTION DATFMT = *MDY, DATSEP = *DASH
BEGIN
DECLARE @.sSTM varchar(8000)--8132
DECLARE @.sSTMAUX varchar(5000)--8132
DECLARE @.VARR Nvarchar(2500)
DECLARE @.VARR1 Nvarchar(2500)
DECLARE @.VARC Nvarchar(2000)
DECLARE @.VARP Nvarchar(1000)

DECLARE @.sQTY VARCHAR(1024)
DECLARE @.sCSH VARCHAR(1024)
DECLARE @.sNCH VARCHAR(1024)
DECLARE @.sTOT VARCHAR(1024)
DECLARE @.sCHB VARCHAR(1024)
DECLARE @.sTGP VARCHAR(1024)
DECLARE @.nBKN NUMERIC(3,0) --DEFAULT 1
DECLARE @.nAPP NUMERIC(2,0)-- DEFAULT 0
DECLARE @.nACT NUMERIC(1,0)-- DEFAULT 0
DECLARE @.nACC NUMERIC(12,0) --DEFAULT 0
DECLARE @.nQTY NUMERIC(9,0) --DEFAULT 0
DECLARE @.nGRP NUMERIC(2,0) --DEFAULT 0
DECLARE @.nCSH NUMERIC(12,2) --DEFAULT 0
DECLARE @.nNCH NUMERIC(12,2) --DEFAULT 0
DECLARE @.nTOT NUMERIC(12,2) --DEFAULT 0
DECLARE @.nCHB NUMERIC(12,2) --DEFAULT 0
DECLARE @.nTGP NUMERIC(12,2) --DEFAULT 0
DECLARE @.sSEL VARCHAR(96) --DEFAULT 'SELECT AMLCBANKNU,AMLCAPPCOD,AMLCACCTYP,AMLCACCOUN,AMLCGRUTRA, '
DECLARE @.iDay INT
DECLARE @.xDay INT
DECLARE @.v_contador INT
DECLARE @.at_end int --default 0

-- DECLARE Not_Found CONDITION FOR SQLSTATE '02000'
-- DECLARE C1 DYNAMIC SCROLL CURSOR FOR sSQLSTM
DECLARE @.C1 CURSOR
--SETEANDO LA VARIABLES DEFAULT
SET @.nBKN =@.i_AMLCBANKNU
SET @.nAPP =0
SET @.nACT =0
SET @.nACC =0
SET @.nQTY =0
SET @.nGRP =0
SET @.nCSH =0
SET @.nNCH =0
SET @.nTOT =0
SET @.nCHB =0
SET @.nTGP =0
SET @.sQTY = ''
SET @.sSTM = ''
SET @.sQTY = ''
SET @.sCSH = ''
SET @.sNCH = ''
SET @.sTOT = ''
SET @.sCHB = ''
SET @.sTGP = ''
SET @.VARR = ''
SET @.sSEL= 'SELECT AMLCBANKNU,AMLCAPPCOD,AMLCACCTYP,AMLCACCOUN,AMLCGRUTRA, '
SET @.at_end=0

SET @.VARR1=''
SET @.VARP=''
--DECLARE CONTINUE HANDLER FOR Not_Found
SET @.at_end = 1
SET @.iDay = @.sDay

IF (@.sDay > @.eDay)
BEGIN
SET @.xDay = @.eDay
SET @.eDay = 31 + @.eDay
END

WHILE (@.iDay < @.eDay)
BEGIN
SET @.sQTY = @.sQTY + 'AMLNQTYT' + CONVERT(VARCHAR(2),@.iDay) + '+'
SET @.sCSH = @.sCSH + 'AMLNCSHT' + CONVERT(VARCHAR(2),@.iDay)+ '+'
SET @.sNCH = @.sNCH + 'AMLNNCHT' + CONVERT(VARCHAR(2),@.iDay)+ '+'
SET @.sTOT = @.sTOT + 'AMLNTOTT' + CONVERT(VARCHAR(2),@.iDay)+ '+'
SET @.sCHB = @.sCHB + 'AMLNCHBT' + CONVERT(VARCHAR(2),@.iDay)+ '+'
SET @.sTGP = @.sTGP + 'AMLTOGRP' + CONVERT(VARCHAR(2),@.iDay)+ '+'
SET @.iDay = @.iDay + 1
IF (@.iDay =32)
BEGIN
SET @.iDay=01
SET @.eDay=@.xDay
END
END

SET @.sQTY = ((@.sQTY + ('AMLNQTYT' + CONVERT(VARCHAR(2),@.iDay))) + ', ')
SET @.sCSH = ((@.sCSH + ('AMLNCSHT' + CONVERT(VARCHAR(2),@.iDay))) + ', ')
SET @.sNCH = ((@.sNCH + ('AMLNNCHT' + CONVERT(VARCHAR(2),@.iDay))) + ', ')
SET @.sTOT = ((@.sTOT + ('AMLNTOTT' + CONVERT(VARCHAR(2),@.iDay))) + ', ')
SET @.sCHB = ((@.sCHB + ('AMLNCHBT' + CONVERT(VARCHAR(2),@.iDay))) + ', ')
SET @.sTGP = ((@.sTGP + ('AMLTOGRP' + CONVERT(VARCHAR(2),@.iDay))) + ' ')
SET @.VARR = @.sSEL + @.sQTY + @.sCSH + @.sNCH + @.sTOT + @.sCHB + @.sTGP
SET @.VARR1 = ' FROM PAMLMSTCNS WHERE AMLCBANKNU='
+ CONVERT(VARCHAR(5),@.i_AMLCBANKNU) + ' AND (' + SUBSTRING (@.sQTY, 1, LEN(@.sQTY)-1)
+ ' > 0 OR ' + SUBSTRING (@.sCSH, 1, LEN(@.sCSH)-1)
+ ' > 0 OR ' + SUBSTRING (@.sNCH, 1, LEN(@.sNCH)-1)
+ ' > 0 OR ' + SUBSTRING (@.sTOT, 1, LEN(@.sTOT)-1)
+ ' > 0 OR ' + SUBSTRING (@.sCHB, 1, LEN(@.sCHB)-1)
+ ' > 0 OR ' + @.sTGP + ' > 0)'

--PREPARE sSQLSTM FROM @.sSTM
-- DECLARE C1 CURSOR FOR @.sSTM

IF (@.i_PERIOD <> 5)
BEGIN
SELECT @.v_contador= COUNT(*)
FROM PAMLDETCNS
WHERE AMLCBANKNU = @.nBKN
AND AMLCCONSOL = @.i_PERIOD
END

IF (@.v_contador<>0)
BEGIN
DELETE FROM PAMLDETCNS
WHERE AMLCBANKNU = @.nBKN
AND AMLCCONSOL = @.i_PERIOD
END

SET @.VARC=''
SET @.VARC= @.VARC + ' DECLARE @.nBKN1 NUMERIC(3,0) DECLARE @.nAPP1 NUMERIC(2,0) DECLARE @.nACT1 NUMERIC(1,0) DECLARE @.nACC1 NUMERIC(12,0) DECLARE @.nQTY1 NUMERIC(9,0) '
SET @.VARC= @.VARC + ' DECLARE @.nGRP1 NUMERIC(2,0) DECLARE @.nCSH1 NUMERIC(12,2) DECLARE @.nNCH1 NUMERIC(12,2) DECLARE @.nTOT1 NUMERIC(12,2) DECLARE @.nCHB1 NUMERIC(12,2) '
SET @.VARC= @.VARC + ' DECLARE @.nTGP1 NUMERIC(12,2) DECLARE @.i_PERIOD1 INT '
SET @.VARC= @.VARC + ' SET @.nBKN1 =' + CONVERT(CHAR(15),@.i_AMLCBANKNU )
SET @.VARC= @.VARC + ' SET @.i_PERIOD1 =' + CONVERT(CHAR(15),@.i_PERIOD )
SET @.VARC= @.VARC + ' SET @.nAPP1 =0'
SET @.VARC= @.VARC + ' SET @.nACT1 =0'
SET @.VARC= @.VARC + ' SET @.nACC1 =0'
SET @.VARC= @.VARC + ' SET @.nQTY1 =0'
SET @.VARC= @.VARC + ' SET @.nGRP1 =0'
SET @.VARC= @.VARC + ' SET @.nCSH1 =0'
SET @.VARC= @.VARC + ' SET @.nNCH1 =0'
SET @.VARC= @.VARC + ' SET @.nTOT1 =0'
SET @.VARC= @.VARC + ' SET @.nCHB1 =0'
SET @.VARC= @.VARC + ' SET @.nTGP1 =0'
SET @.VARC = @.VARC + ' DECLARE C10 CURSOR FOR '

SET @.VARP = @.VARP + ' OPEN C10 FETCH NEXT FROM C10 '
SET @.VARP = @.VARP + ' INTO @.nBKN1,@.nAPP1,@.nACT1,@.nACC1,@.nGRP1, @.nQTY1,@.nCSH1,@.nNCH1,@.nTOT1,@.nCHB1,@.nTGP1'
SET @.VARP = @.VARP + ' WHILE @.@.FETCH_STATUS = 0'
SET @.VARP = @.VARP + ' BEGIN'
SET @.VARP = @.VARP + ' INSERT INTO PAMLDETCNS'
SET @.VARP = @.VARP + ' (AMLCBANKNU,AMLCAPPCOD,AMLCACCTYP,AMLCACCOUN,AMLCGRUTRA,AMLCCONSOL,AMLDPERIFR,AMLDPERITO,AMLNQTYTRC,AMLNCHSTRC,AMLNNCHTRC,AMLNTOTTRC,AMLNCHBTRC, AMLTOTGRPC)'
SET @.VARP = @.VARP + ' VALUES '
SET @.VARP = @.VARP + ' (@.nBKN1,@.nAPP1,@.nACT1,@.nACC1,@.nGRP1,@.i_PERIOD1,' +CHAR(39)+CONVERT(CHAR(10),@.i_AMLDPERIFR,101)+CHAR(39)+','+CHAR(39)+CONVERT(CHAR(10),@.i_AMLDPERITO,101)+CHAR(39)+',@.nQTY1,@.nCSH1,@.nNCH1,@.nTOT1,@.nCHB1,@.nTGP1)'
SET @.VARP = @.VARP + ' FETCH NEXT FROM C10 '
SET @.VARP = @.VARP + ' INTO @.nBKN1,@.nAPP1,@.nACT1,@.nACC1,@.nGRP1, @.nQTY1,@.nCSH1,@.nNCH1,@.nTOT1,@.nCHB1,@.nTGP1'
SET @.VARP = @.VARP + ' END '
SET @.VARP = @.VARP + ' CLOSE C10 '
SET @.VARP = @.VARP + ' DEALLOCATE C10 '

print LEN(@.sSTM)
print @.VARC + @.VARR
print @.VARR1 + @.VARP
--EXEC sp_executesql @.VARC

EXEC('EXEC sp_executesql ' + @.VARC + @.VARR + @.VARR1 + @.VARP )

END
as you can see my store open a cursor inside of the query.... any suggestion?

thks

help with query!

I have 2 tables

tblStyles
StyleID int (pk)
XMLfilename nvarchar(50)

tblRules
RuleID int (pk)
Usercode int
StyleID int (fk)
ruleindex tinyint

Now in a stored proc I want to retreive XMLfilename for the first rule that matches certain criteria.
So if Usercode 5 would have 3 rules defined with ruleindex: 8, 6 and 7 (they might be in that order in the table)
and both rule 6 and 8 would match my criteria, I would like to have the xmlfilename related to the rulerecord with ruleindex 6 (as 6 is the lowest ruleindex).

Here's what I have so far:
----------
declare @.style nvarchar(50)

set @.style=(

select XMLfileid FROM (

select us.XMLfileid,r.ruleindex from tblRules r
inner join tblUserStyles us on us.StyleID=r.StyleID
wherer.usercode=@.Usercode
order by ruleindex

) as tmp

WHERE <allmycriteria>

)
----------

I get this error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

declare @.style nvarchar(50)

set @.style=(

select XMLfileid FROM (

select Top 1 us.XMLfileid,r.ruleindex from tblRules r
inner join tblUserStyles us on us.StyleID=r.StyleID
wherer.usercode=@.Usercode
order by ruleindex asc

) as tmp

WHERE <allmycriteria>

)

Coz u need only one file name of the samlest index matched . You cannot use Order By in a Sub Query without using Top

alternative to your query you can use

1Create Procedure GetXmlFile2(3@.UserCodevarchar(10)4)5AS67BEGIN89declare @.stylenvarchar(50)1011set @.style=(1213select XMLfileidFROM tblStyleswhere StyleIdin(14Select Top 1 TR.StyleIdFROM15tblRulesINNERJOIN tblStyle TSon TS.StyleId=TR.StyleId16Where TR.usercode=@.UserCode17order by Tr.ruleindexasc18)1920WHERE21Select @.Style22END23

|||

It seems to me that having the scalar value assigned in the stored procedure is overkill. Review this and let me know if it works for you:

Create Procedure GetXmlFile( @.UserCodevarchar(10))ASBEGIN SELECT ts.XMLfileidFROM tblStyles tsWHERE ts.StyleId = (select min(tr.StyleId)from tblRules trwhere tr.usercode = @.UserCodegroup by tr.usercode )END

help with query

this is my query
Select
fname
,Case
When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
Else animal
End
from tanimals
Group By fname, animal
create table tanimals
(
fname nvarchar(64),
animal nvarchar(64),
)
insert into tanimals (fname, animal) values ('James', 'cat')
insert into tanimals (fname, animal) values ('James', 'dog')
insert into tanimals (fname, animal) values ('Cal', 'dog')
insert into tanimals (fname, animal) values ('Cal', 'fish')
insert into tanimals (fname, animal) values ('Bret', 'fish')
insert into tanimals (fname, animal) values ('Kate', 'fish')
insert into tanimals (fname, animal) values ('Cal', 'turtle')
I'm trying to produce
fname animals
James 2
Cal 3
Kate fish
Bret fish
if the person have more that one animals to give me the number of
animals, but if they own just one to return the animal that they own.
Thanks
Select
fname
,Case
When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
Else MAX(animal) -- Use aggregate to satisfy Group By
End as Animals
from tanimals
Group By fname -- Not including animal
order by Animals
RLF
<vncntj@.hotmail.com> wrote in message
news:1174415339.746907.287300@.o5g2000hsb.googlegro ups.com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>
|||vncntj,
I believe this will work for you:
select
fname
, animal AnimalOrCount
from tanimals
where fname in
(
select fname
from tanimals
group by fname
having count(*) = 1
)
union all
select fname
, cast(count(*) as nvarchar(64)) AnimalOrCount
from tanimals
group by fname
having count(*) > 1
-- Bill
<vncntj@.hotmail.com> wrote in message
news:1174415339.746907.287300@.o5g2000hsb.googlegro ups.com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>
|||It is odd, but when it works, it works a charm. :-)
RLF
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OEGliNyaHHA.4008@.TK2MSFTNGP05.phx.gbl...
> LOL... That is much more clever than my approach. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eivaP9xaHHA.4888@.TK2MSFTNGP06.phx.gbl...
>
|||Thanks to everyone. It all works.
You guys rock!!
On Mar 20, 4:38 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> It is odd, but when it works, it works a charm. :-)
> RLF
> "Tibor Karaszi" <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote in
> messagenews:OEGliNyaHHA.4008@.TK2MSFTNGP05.phx.gbl. ..
>
>
>
>
>
>
>
> - Show quoted text -

help with query

this is my query
Select
fname
,Case
When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
Else animal
End
from tanimals
Group By fname, animal
create table tanimals
(
fname nvarchar(64),
animal nvarchar(64),
)
insert into tanimals (fname, animal) values ('James', 'cat')
insert into tanimals (fname, animal) values ('James', 'dog')
insert into tanimals (fname, animal) values ('Cal', 'dog')
insert into tanimals (fname, animal) values ('Cal', 'fish')
insert into tanimals (fname, animal) values ('Bret', 'fish')
insert into tanimals (fname, animal) values ('Kate', 'fish')
insert into tanimals (fname, animal) values ('Cal', 'turtle')
I'm trying to produce
fname animals
James 2
Cal 3
Kate fish
Bret fish
if the person have more that one animals to give me the number of
animals, but if they own just one to return the animal that they own.
ThanksSelect
fname
,Case
When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
Else MAX(animal) -- Use aggregate to satisfy Group By
End as Animals
from tanimals
Group By fname -- Not including animal
order by Animals
RLF
<vncntj@.hotmail.com> wrote in message
news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>|||Below are two options. Second requires SQL Server 2005:
SELECT fname, CAST(COUNT(*) AS varchar(30)) AS animals
FROM tanimals
GROUP BY fname
HAVING COUNT(*) > 1
UNION ALL
SELECT fname, animal
FROM tanimals
WHERE fname NOT IN
(
SELECT fname
FROM tanimals
GROUP BY fname
HAVING COUNT(*) > 1
);
WITH ganimals AS
(SELECT fname, CAST(COUNT(*) AS varchar(30)) AS animals
FROM tanimals
GROUP BY fname
HAVING COUNT(*) > 1)
SELECT fname, animal
FROM tanimals
WHERE fname NOT IN (SELECT fname FROM ganimals)
UNION
SELECT fname, animals FROM ganimals
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<vncntj@.hotmail.com> wrote in message news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>|||vncntj,
I believe this will work for you:
select
fname
, animal AnimalOrCount
from tanimals
where fname in
(
select fname
from tanimals
group by fname
having count(*) = 1
)
union all
select fname
, cast(count(*) as nvarchar(64)) AnimalOrCount
from tanimals
group by fname
having count(*) > 1
-- Bill
<vncntj@.hotmail.com> wrote in message
news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>|||> Else MAX(animal) -- Use aggregate to satisfy Group By
LOL... That is much more clever than my approach. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eivaP9xaHHA.4888@.TK2MSFTNGP06.phx.gbl...
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else MAX(animal) -- Use aggregate to satisfy Group By
> End as Animals
> from tanimals
> Group By fname -- Not including animal
> order by Animals
> RLF
> <vncntj@.hotmail.com> wrote in message news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
>> this is my query
>> Select
>> fname
>> ,Case
>> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
>> Else animal
>> End
>> from tanimals
>> Group By fname, animal
>> create table tanimals
>> (
>> fname nvarchar(64),
>> animal nvarchar(64),
>> )
>> insert into tanimals (fname, animal) values ('James', 'cat')
>> insert into tanimals (fname, animal) values ('James', 'dog')
>> insert into tanimals (fname, animal) values ('Cal', 'dog')
>> insert into tanimals (fname, animal) values ('Cal', 'fish')
>> insert into tanimals (fname, animal) values ('Bret', 'fish')
>> insert into tanimals (fname, animal) values ('Kate', 'fish')
>> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>>
>> I'm trying to produce
>> fname animals
>> James 2
>> Cal 3
>> Kate fish
>> Bret fish
>> if the person have more that one animals to give me the number of
>> animals, but if they own just one to return the animal that they own.
>> Thanks
>|||It is odd, but when it works, it works a charm. :-)
RLF
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OEGliNyaHHA.4008@.TK2MSFTNGP05.phx.gbl...
>> Else MAX(animal) -- Use aggregate to satisfy Group By
> LOL... That is much more clever than my approach. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eivaP9xaHHA.4888@.TK2MSFTNGP06.phx.gbl...
>> Select
>> fname
>> ,Case
>> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
>> Else MAX(animal) -- Use aggregate to satisfy Group By
>> End as Animals
>> from tanimals
>> Group By fname -- Not including animal
>> order by Animals
>> RLF
>> <vncntj@.hotmail.com> wrote in message
>> news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
>> this is my query
>> Select
>> fname
>> ,Case
>> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
>> Else animal
>> End
>> from tanimals
>> Group By fname, animal
>> create table tanimals
>> (
>> fname nvarchar(64),
>> animal nvarchar(64),
>> )
>> insert into tanimals (fname, animal) values ('James', 'cat')
>> insert into tanimals (fname, animal) values ('James', 'dog')
>> insert into tanimals (fname, animal) values ('Cal', 'dog')
>> insert into tanimals (fname, animal) values ('Cal', 'fish')
>> insert into tanimals (fname, animal) values ('Bret', 'fish')
>> insert into tanimals (fname, animal) values ('Kate', 'fish')
>> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>>
>> I'm trying to produce
>> fname animals
>> James 2
>> Cal 3
>> Kate fish
>> Bret fish
>> if the person have more that one animals to give me the number of
>> animals, but if they own just one to return the animal that they own.
>> Thanks
>>
>|||Thanks to everyone. It all works.
You guys rock!!
On Mar 20, 4:38 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> It is odd, but when it works, it works a charm. :-)
> RLF
> "Tibor Karaszi" <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote in
> messagenews:OEGliNyaHHA.4008@.TK2MSFTNGP05.phx.gbl...
>
> >> Else MAX(animal) -- Use aggregate to satisfy Group By
> > LOL... That is much more clever than my approach. :-)
> > --
> > Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://sqlblog.com/blogs/tibor_karaszi
> > "Russell Fields" <russellfie...@.nomail.com> wrote in message
> >news:eivaP9xaHHA.4888@.TK2MSFTNGP06.phx.gbl...
> >> Select
> >> fname
> >> ,Case
> >> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> >> Else MAX(animal) -- Use aggregate to satisfy Group By
> >> End as Animals
> >> from tanimals
> >> Group By fname -- Not including animal
> >> order by Animals
> >> RLF
> >> <vnc...@.hotmail.com> wrote in message
> >>news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
> >> this is my query
> >> Select
> >> fname
> >> ,Case
> >> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> >> Else animal
> >> End
> >> from tanimals
> >> Group By fname, animal
> >> create table tanimals
> >> (
> >> fname nvarchar(64),
> >> animal nvarchar(64),
> >> )
> >> insert into tanimals (fname, animal) values ('James', 'cat')
> >> insert into tanimals (fname, animal) values ('James', 'dog')
> >> insert into tanimals (fname, animal) values ('Cal', 'dog')
> >> insert into tanimals (fname, animal) values ('Cal', 'fish')
> >> insert into tanimals (fname, animal) values ('Bret', 'fish')
> >> insert into tanimals (fname, animal) values ('Kate', 'fish')
> >> insert into tanimals (fname, animal) values ('Cal', 'turtle')
> >> I'm trying to produce
> >> fname animals
> >> James 2
> >> Cal 3
> >> Kate fish
> >> Bret fish
> >> if the person have more that one animals to give me the number of
> >> animals, but if they own just one to return the animal that they own.
> >> Thanks- Hide quoted text -
> - Show quoted text -

help with query

this is my query
Select
fname
,Case
When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
Else animal
End
from tanimals
Group By fname, animal
create table tanimals
(
fname nvarchar(64),
animal nvarchar(64),
)
insert into tanimals (fname, animal) values ('James', 'cat')
insert into tanimals (fname, animal) values ('James', 'dog')
insert into tanimals (fname, animal) values ('Cal', 'dog')
insert into tanimals (fname, animal) values ('Cal', 'fish')
insert into tanimals (fname, animal) values ('Bret', 'fish')
insert into tanimals (fname, animal) values ('Kate', 'fish')
insert into tanimals (fname, animal) values ('Cal', 'turtle')
I'm trying to produce
fname animals
James 2
Cal 3
Kate fish
Bret fish
if the person have more that one animals to give me the number of
animals, but if they own just one to return the animal that they own.
ThanksSelect
fname
,Case
When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
Else MAX(animal) -- Use aggregate to satisfy Group By
End as Animals
from tanimals
Group By fname -- Not including animal
order by Animals
RLF
<vncntj@.hotmail.com> wrote in message
news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>|||Below are two options. Second requires SQL Server 2005:
SELECT fname, CAST(COUNT(*) AS varchar(30)) AS animals
FROM tanimals
GROUP BY fname
HAVING COUNT(*) > 1
UNION ALL
SELECT fname, animal
FROM tanimals
WHERE fname NOT IN
(
SELECT fname
FROM tanimals
GROUP BY fname
HAVING COUNT(*) > 1
);
WITH ganimals AS
(SELECT fname, CAST(COUNT(*) AS varchar(30)) AS animals
FROM tanimals
GROUP BY fname
HAVING COUNT(*) > 1)
SELECT fname, animal
FROM tanimals
WHERE fname NOT IN (SELECT fname FROM ganimals)
UNION
SELECT fname, animals FROM ganimals
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<vncntj@.hotmail.com> wrote in message news:1174415339.746907.287300@.o5g2000hsb.googlegroups.
com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>|||vncntj,
I believe this will work for you:
select
fname
, animal AnimalOrCount
from tanimals
where fname in
(
select fname
from tanimals
group by fname
having count(*) = 1
)
union all
select fname
, cast(count(*) as nvarchar(64)) AnimalOrCount
from tanimals
group by fname
having count(*) > 1
-- Bill
<vncntj@.hotmail.com> wrote in message
news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>|||> Else MAX(animal) -- Use aggregate to satisfy Group By
LOL... That is much more clever than my approach. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eivaP9xaHHA.4888@.TK2MSFTNGP06.phx.gbl...
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else MAX(animal) -- Use aggregate to satisfy Group By
> End as Animals
> from tanimals
> Group By fname -- Not including animal
> order by Animals
> RLF
> <vncntj@.hotmail.com> wrote in message news:1174415339.746907.287300@.o5g200
0hsb.googlegroups.com...
>|||It is odd, but when it works, it works a charm. :-)
RLF
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OEGliNyaHHA.4008@.TK2MSFTNGP05.phx.gbl...
> LOL... That is much more clever than my approach. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eivaP9xaHHA.4888@.TK2MSFTNGP06.phx.gbl...
>|||Thanks to everyone. It all works.
You guys rock!!
On Mar 20, 4:38 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> It is odd, but when it works, it works a charm. :-)
> RLF
> "Tibor Karaszi" <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote i
n
> messagenews:OEGliNyaHHA.4008@.TK2MSFTNGP05.phx.gbl...
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -