Help with SQL Function - Cant change Null value

What I'm trying to do is to build a string that will print all the presences of a user for a session. My problem is that I'd like to put the - value when the pr_presence isn't True or False but right now it only returns pr_presence when it contains a boolean value. This way I can't treat the NULL or blank value. Any help would be really appreciate !

Here is my fonction:

DECLARE strPresence VARCHAR;
sessionID alias for $1;
userID alias for $2;
idr record;
BEGIN
strPresence := '';
For idr in
SELECT CASE When pr_presence = 't' Then 'P'
When pr_presence = 'f' Then 'A'
Else '-'
End as "TypePresence"
FROM seance, InscriptionEtat, inscriptionSession
RIGHT JOIN presence ON inscriptionSession.usr_id = presence.usr_id
LEFT JOIN session ON inscriptionSession.ses_id = session.ses_id
WHERE session.ses_id = sessionID
AND presence.usr_id = userID
AND presence.sea_id = seance.sea_id
AND seance.ses_id = session.ses_id
AND seance.sea_valide = 't'
AND inscriptionSession.usr_id = usager.usr_id
AND inscriptionSession.ie_id = inscriptionEtat.ie_id
AND inscriptionEtat.ie_OK = 't'
ORDER BY seance.sea_datedebut
LOOP
strPresence:= strPresence||', '||idr."TypePresence";
END LOOP;
strPresence:= substring(strPresence,char_length(', ')+1);
RETURN strPresence;
END;I don't quite understand your problem. The CASE statement works OK:

SQL> SELECT pr_presence, CASE When pr_presence = 't' Then 'P'
2 When pr_presence = 'f' Then 'A'
3 Else '-'
4 End as "TypePresence"
5 FROM seance;

P T
- -
t P
f A
-
x -|||Originally posted by andrewst
I don't quite understand your problem. The CASE statement works OK:

SQL> SELECT pr_presence, CASE When pr_presence = 't' Then 'P'
2 When pr_presence = 'f' Then 'A'
3 Else '-'
4 End as "TypePresence"
5 FROM seance;

P T
- -
t P
f A
-
x -

If my session contains 4 seances, and the user only enters 1 presence for these seances, my string should looks like "P,-,-,-" because the other 3 pr_presence would be Null
Right now, my string is returning "P" when I test it... I also tought my case was ok but I'm now wondering why I don't get what I want. Thanks for your help

Help with SELECT please

I need to select 2 rows from a table for every SSN. DDL for the table:

CREATE TABLE [dbo].[tblResidentRotations] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ResidentProgram] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RotationID] [int] NULL ,
[MonthName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RotationLocationID] [int] NULL ,
[CallLocationID] [int] NULL ,
[IMClinicDay] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[IMClinicDateLast] [datetime] NULL ,
[IMClinicDateFirst] [datetime] NULL ,
[PedsClinicDay] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Comments] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClinicScheduleComments] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LastFirstComments] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PGYLevel] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AcademicYear] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]

For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
"Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
data in mm/dd/yy format. I need to pull IMClinicDay for the date given
in IMClinicDateFirst. For example, if my data looks like this:

SSN: 999999999
MonthName: July
IMClinicDay: Monday PM
IMClinicDateLast: 07/01/05
IMClinicDateFirst: 09/01/05

then I need to pull what the IMClinicDay would be for this SSN in
September.

Thanks for any help and advice.Stop using that silly redundant "tbl-" prefix; If nobody told you
yet, SQL only has one data structure. Then you might want to read a
basic book on data modeling - you always name a data element for what
it is, not for how it is stored, where it is used, etc.

Next, we need keys to have proper tables. An IDENTITY is **never** a
key by definition. SSN is never VARCHAR() but it is fixed length, so
all you did was invite a loss of data integrity. Ditto when you pulled
oversize numbers out of the air for the other column sizes. Since there
are no non-NOT NULL columns, you cannot ever have a key!! Think about
VARCHAR(1) and what it means.

Do you know of a month name that is CHAR(15)? Why are you using
strings for temporal data in SQL? Why are you using vague strings like
"Monday PM" for temporal data? Why did you violate ISO-8601 formats
for the bad dates?

What does the resident's academic year have to do with rotations?
The whole mess looks denormalized. Just based on a few decades of
prior experience, I would guess this ought o reduce down to something
like this:

CREATE TABLE ResidentRotations
(ssn VARCHAR(9) NOT NULL
REFERENCES Residents(ssn),
resident_program VARCHAR(20) NOT NULL,
rotation_loc INTEGER NOT NULL
REFERENCES LocationsCodes(loc_nbr),
rotation_start_time DATETIME NOT NULL,
rotation_end_time DATETIME NOT NULL,
CHECK (rotation_start_time < rotation_end_time),
call_loc INTEGER NOT NULL
REFERENCES LocationsCodes(loc_nbr),
PRIMARY KEY (ssn, rotation_start_time));

>> I need to pull what the IMClinicDay would be for this SSN in September. <<

You need to use a Calendar table and insert the scheduled shifts in
advance for the known duration. You can take care of holidays,
re-scheduling, etc. with this approach.

You might want to read Rick Snodgrass' s book on Temporal queries in
SQL after you get thru a basic data modeling book and a few ISO
standards. Pretty much everything you did was fundamentally wrong.|||(manning_news@.hotmail.com) writes:
> For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
> IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
> "Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
> data in mm/dd/yy format.

No, they are declared as datetime, which means that they are in a
binary format. If you say

SELECT * FROM tbl WHERE datecol = '07/01/05'

You could get rows from from 2007-01-05, 2005-01-07 or any other
of the six possible permutations, depending on the current settings.
On the other hand:

SELECT * FROM tbl WHERE datecol = '20070105'

will always give the same set of data.

OK, so that is not what you asked about, but since you had an apparent
misunderstanding about datetime, I figured I should point it out.

> I need to pull IMClinicDay for the date given
> in IMClinicDateFirst. For example, if my data looks like this:
> SSN: 999999999
> MonthName: July
> IMClinicDay: Monday PM
> IMClinicDateLast: 07/01/05
> IMClinicDateFirst: 09/01/05
> then I need to pull what the IMClinicDay would be for this SSN in
> September.

If I understand this correctly, you should have a look at the datename()
function in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here's a sample of the data I have:

SSN: 999999999 (first row)
MonthName: July
IMClinicDay: Monday PM
IMClinicDateLast: 07/01/05
IMClinicDateFirst: 09/01/05

SSN: 999999999 (nth row)
MonthName: September
IMClinicDay: Wednesday PM
IMClinicDateLast: 09/01/05
IMClinicDateFirst: 10/01/05

With a SELECT statement, I want to return all of the first row and only
the IMClinicDay (Wednesday PM) of the nth row using IMClinicDateFirst
in the first row to get this data.

Thanks for any help.

Erland Sommarskog wrote:
> (manning_news@.hotmail.com) writes:
> > For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
> > IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
> > "Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
> > data in mm/dd/yy format.
> No, they are declared as datetime, which means that they are in a
> binary format. If you say
> SELECT * FROM tbl WHERE datecol = '07/01/05'
> You could get rows from from 2007-01-05, 2005-01-07 or any other
> of the six possible permutations, depending on the current settings.
> On the other hand:
> SELECT * FROM tbl WHERE datecol = '20070105'
> will always give the same set of data.
> OK, so that is not what you asked about, but since you had an apparent
> misunderstanding about datetime, I figured I should point it out.
> > I need to pull IMClinicDay for the date given
> > in IMClinicDateFirst. For example, if my data looks like this:
> > SSN: 999999999
> > MonthName: July
> > IMClinicDay: Monday PM
> > IMClinicDateLast: 07/01/05
> > IMClinicDateFirst: 09/01/05
> > then I need to pull what the IMClinicDay would be for this SSN in
> > September.
> If I understand this correctly, you should have a look at the datename()
> function in Books Online.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I just want to know if I can get to the row I want. If it's impossible
with the structure I have then just say so.

--CELKO-- wrote:
> Stop using that silly redundant "tbl-" prefix; If nobody told you
> yet, SQL only has one data structure. Then you might want to read a
> basic book on data modeling - you always name a data element for what
> it is, not for how it is stored, where it is used, etc.
> Next, we need keys to have proper tables. An IDENTITY is **never** a
> key by definition. SSN is never VARCHAR() but it is fixed length, so
> all you did was invite a loss of data integrity. Ditto when you pulled
> oversize numbers out of the air for the other column sizes. Since there
> are no non-NOT NULL columns, you cannot ever have a key!! Think about
> VARCHAR(1) and what it means.
> Do you know of a month name that is CHAR(15)? Why are you using
> strings for temporal data in SQL? Why are you using vague strings like
> "Monday PM" for temporal data? Why did you violate ISO-8601 formats
> for the bad dates?
> What does the resident's academic year have to do with rotations?
> The whole mess looks denormalized. Just based on a few decades of
> prior experience, I would guess this ought o reduce down to something
> like this:
> CREATE TABLE ResidentRotations
> (ssn VARCHAR(9) NOT NULL
> REFERENCES Residents(ssn),
> resident_program VARCHAR(20) NOT NULL,
> rotation_loc INTEGER NOT NULL
> REFERENCES LocationsCodes(loc_nbr),
> rotation_start_time DATETIME NOT NULL,
> rotation_end_time DATETIME NOT NULL,
> CHECK (rotation_start_time < rotation_end_time),
> call_loc INTEGER NOT NULL
> REFERENCES LocationsCodes(loc_nbr),
> PRIMARY KEY (ssn, rotation_start_time));
> >> I need to pull what the IMClinicDay would be for this SSN in September. <<
> You need to use a Calendar table and insert the scheduled shifts in
> advance for the known duration. You can take care of holidays,
> re-scheduling, etc. with this approach.
> You might want to read Rick Snodgrass' s book on Temporal queries in
> SQL after you get thru a basic data modeling book and a few ISO
> standards. Pretty much everything you did was fundamentally wrong.|||(manning_news@.hotmail.com) writes:
> Here's a sample of the data I have:
> SSN: 999999999 (first row)
> MonthName: July
> IMClinicDay: Monday PM
> IMClinicDateLast: 07/01/05
> IMClinicDateFirst: 09/01/05
> SSN: 999999999 (nth row)
> MonthName: September
> IMClinicDay: Wednesday PM
> IMClinicDateLast: 09/01/05
> IMClinicDateFirst: 10/01/05
> With a SELECT statement, I want to return all of the first row and only
> the IMClinicDay (Wednesday PM) of the nth row using IMClinicDateFirst
> in the first row to get this data.

Since your table definition did not include any information about keys,
I cannot be sure that this query works:

SELECT a.SSN, a.ResidentProgram, ..., b.IMClinicDay
FROM tblResidentRotations a
LEFT JOIN b tblResidentRotations
ON a.SSN = b.SSN
AND a.IMClinicDateLast = b.IMClinicDateFirst
WHERE a.IMClinicDateFirst >= @.yearmonth + '01' AND
a.IMClincDateFirst < dateadd(MONTH, 1, @.yearmonth + '01')

I assume that @.yearmonth holds the month you are looking for on the
form YYYYMM.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Help with Select Minimum Value

I need help in doing a select statment with a minimum
here is the statement
CREATE TABLE [Test] (
[rIndex] [int] IDENTITY (1, 1) NOT NULL ,
[Defaul_] [int] NULL ,
[FilterType] [int] NULL ,
[ProtScr] [int] NULL ,
[ProtRank] [int] NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ageStart] [int] NULL ,
[ageStop] [int] NULL ,
[Sex] [int] NULL ,
CONSTRAINT [PK_Test_1] PRIMARY KEY CLUSTERED
(
[rIndex]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,5,'FOBT',50, 90,1)
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,3,'Sigmoidoscopy',50, 90,1)
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,5,'Colonoscopy',65, 90,1)
I have a subquery to try and get the minimum ProtScr and ProtRank
When I do the subquery
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
I get 2 values
ProtScr ProtRank
1 3
1 5
if possible I want the subquery to only pick the lower value only. The main
select statement that I tried to do is located below to select the values
based on the subquery.
Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
ageStart,AgeStop,sex from Test
where ProtRank EXISTS in
(Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1))
Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65) AND
(Sex IN (1, 2)) AND (Defaul_ = 1))
Thanks
Stephen K. MiyasatoHi
SELECT ProtScr,MIN(ProtRank) AS ProtRank
FROM
(
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
) AS Der
GROUP BY ProtScr
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:OkzZlunTGHA.5496@.TK2MSFTNGP11.phx.gbl...
>I need help in doing a select statment with a minimum
> here is the statement
> CREATE TABLE [Test] (
> [rIndex] [int] IDENTITY (1, 1) NOT NULL ,
> [Defaul_] [int] NULL ,
> [FilterType] [int] NULL ,
> [ProtScr] [int] NULL ,
> [ProtRank] [int] NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ageStart] [int] NULL ,
> [ageStop] [int] NULL ,
> [Sex] [int] NULL ,
> CONSTRAINT [PK_Test_1] PRIMARY KEY CLUSTERED
> (
> [rIndex]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,5,'FOBT',50, 90,1)
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,3,'Sigmoidoscopy',50, 90,1)
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,5,'Colonoscopy',65, 90,1)
> I have a subquery to try and get the minimum ProtScr and ProtRank
> When I do the subquery
> Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1)
> I get 2 values
> ProtScr ProtRank
> 1 3
> 1 5
> if possible I want the subquery to only pick the lower value only. The
> main select statement that I tried to do is located below to select the
> values based on the subquery.
> Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
> ageStart,AgeStop,sex from Test
> where ProtRank EXISTS in
> (Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1))
> Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1))
> Thanks
> Stephen K. Miyasato
>|||Thanks that helped
I get
protScr ProtRank
1 3
The subquery works but I could not get the main query to work
Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
ageStart,AgeStop,sex from Test
where protScr, ProtRank EXISTS in -- need some help here
( -- subquery begins
SELECT ProtScr,MIN(ProtRank) AS ProtRank
FROM
(
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
) AS Der
GROUP BY ProtScr
) -- subquery ends
Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65) AND
(Sex IN (1, 2)) AND (Defaul_ = 1))
Thanks
Stephen K. Miyasato
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e8bbKynTGHA.776@.TK2MSFTNGP09.phx.gbl...
> Hi
> SELECT ProtScr,MIN(ProtRank) AS ProtRank
> FROM
> (
> Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1)
> ) AS Der
> GROUP BY ProtScr
>
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:OkzZlunTGHA.5496@.TK2MSFTNGP11.phx.gbl...
>

Help with Query and Null

I have the following syntax
Sum([SalesQty1]+[SalesQty2]+[SalesQty3]+[SalesQty4]+[SalesQty5]+[SalesQty6]+[SalesQty7]+[SalesQty8]+[SalesQty9]+[SalesQty10]+[SalesQty11]+[SalesQty12])
AS YrSales
This works fine if none of the fields are NULL
How can I cater for the scenario where anyone of the fields could be null?
Thanks
found it eventually - isnull
"Ali" <nospamthanks@.iveenuf.com> wrote in message
news:uJ1XhduLFHA.1308@.tk2msftngp13.phx.gbl...
>I have the following syntax
> Sum([SalesQty1]+[SalesQty2]+[SalesQty3]+[SalesQty4]+[SalesQty5]+[SalesQty6]+[SalesQty7]+[SalesQty8]+[SalesQty9]+[SalesQty10]+[SalesQty11]+[SalesQty12])
> AS YrSales
> This works fine if none of the fields are NULL
> How can I cater for the scenario where anyone of the fields could be null?
> Thanks
>
>

Help with query

Hi all,
I have the following table:
CREATE TABLE [dbo].[Company](
[CompanyID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](100) NOT NULL,
[CompanyTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL)
These companies can have a variety of CompanyTypeID's. I want to
retrieve a different number of records for each CompanyTypeID in one
call.
Ex.:
DECLARE @.countType1 int
DECLARE @.countType2 int
DECLARE @.countType3 int
DECLARE @.countType4 int
DECLARE @.countType5 int
SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
UNION
SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
UNION
SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
UNION
SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
At this point, Im doing it all with a UNION and getting what I want.
IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
HAVING and use the @.count variables to return the desired number of
records per companytype.
Thanks,
VictorWhat's wrong with using the union?
you'll want to change it to a UNION ALL in the interim, though, since
there won't be any duplicates to eliminate.
ViLo wrote:
> Hi all,
>
> I have the following table:
> CREATE TABLE [dbo].[Company](
> [CompanyID] [int] IDENTITY(1,1) NOT NULL,
> [CompanyName] [varchar](100) NOT NULL,
> [CompanyTypeID] [int] NOT NULL,
> [Active] [bit] NOT NULL)
>
> These companies can have a variety of CompanyTypeID's. I want to
> retrieve a different number of records for each CompanyTypeID in one
> call.
>
> Ex.:
>
> DECLARE @.countType1 int
> DECLARE @.countType2 int
> DECLARE @.countType3 int
> DECLARE @.countType4 int
> DECLARE @.countType5 int
>
> SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
> UNION
> SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
> UNION
> SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
> UNION
> SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
>
> At this point, Im doing it all with a UNION and getting what I want.
> IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
> HAVING and use the @.count variables to return the desired number of
> records per companytype.
>
> Thanks,
> Victor
>|||ViLo (victor@.lamovidarecords.com) writes:
> These companies can have a variety of CompanyTypeID's. I want to
> retrieve a different number of records for each CompanyTypeID in one
> call.
>
> Ex.:
>
> DECLARE @.countType1 int
> DECLARE @.countType2 int
> DECLARE @.countType3 int
> DECLARE @.countType4 int
> DECLARE @.countType5 int
>
> SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
> UNION
> SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
> UNION
> SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
> UNION
> SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
>
> At this point, Im doing it all with a UNION and getting what I want.
> IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
> HAVING and use the @.count variables to return the desired number of
> records per companytype.
First of all, your query looks funny, as you say TOP 100 but as
there is no ORDER BY, you will get the 100, 200, etc as the optimizer
sees fit. Which will be neither random, nor deterministic.
If you are on SQL 2005 you can use TOP (@.count) if the arbitrary
choice is OK to you.
On SQL 2000, you probably need to use a temp table, particularly if you
want the TOP 200 by alphabet or so.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help with Outer join

How can I join 3 tables that represent many-many realtions, in a way
that returns all the values of one table and an extra column of
true/false or null or some way to to show that the record exists or
doesnt exist in the link table.
This is so that when I pass a paramter of an author ID I can create an
array of checkboxes form the record set that are either ticked or not
ticked.
E.g for Pubs, output something like this:
author exists title
1 False 1
1 True 2
Thanks
hals_leftUSE Pubs
SELECT A.au_id, T.title,
CASE WHEN U.title_id IS NOT NULL
THEN 'Y' ELSE 'N' END AS exist
FROM Authors AS A
JOIN Titles AS T
ON A.au_id = '267-41-2394'
LEFT JOIN TitleAuthor AS U
ON U.au_id = A.au_id
AND U.title_id = T.title_id
David Portas
SQL Server MVP
--|||use pubs
Go
SELECT A.au_id, CASE WHEN EXISTS(SELECT 1 FROM TitleAuthor T
WHERE T.Au_id= A.Au_id) Then 1 Else 0 End as Exist
FROM Authors A
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:c04136bd.0502280242.5c8a6a27@.posting.google.com...
> How can I join 3 tables that represent many-many realtions, in a way
> that returns all the values of one table and an extra column of
> true/false or null or some way to to show that the record exists or
> doesnt exist in the link table.
> This is so that when I pass a paramter of an author ID I can create an
> array of checkboxes form the record set that are either ticked or not
> ticked.
> E.g for Pubs, output something like this:
> author exists title
> 1 False 1
> 1 True 2
> Thanks
> hals_left

Help with optional parameter query with IN statements

I have a query with 17 separate, optional, parameters. I have declared each parameter = NULL so that I can test for NULL in the case that the user didn’t not pass in the parameter.

I am new enough to SQL Server that I am having difficulty building the WHERE clause with all of these optional parameters.

One solution I was advised on by a well paid SQL programmer, was to use a string in the stored proc and dynamically build the WHERE clause and exec it at the end of the sp. But the whole point of a stored proc is that it can be compiled and cached to make it faster, yet the string approach makes it have to compile every time it’s run! Not a good solution, but maybe it’s the best I can do . . .

I have tried many different approaches using different functions, etc. but I’ve hit a brick wall. Any help in sorting it out with YOUR techniques would be greatly appreciated:

1. To add the parameter to the WHERE clause and test for NULL I’ve used the COALESCE function such as “WHERE table.fieldname = COALESCE(@.Param, table.fieldname)”. This works well if there is only one item in the parameter, but in the case that I pass multiple items to the parameter, it completely fails.

2. To handle multiple items, for example, if @.Param = ‘3,7,98’ (essentially, a csv separated list of keys)

Code Snippet

WHERE table.fieldname IN(COALESCE(@.Param, table.fieldname))

doesn’t work because @.Param needs to be parsed from a string into an array of integers in the parameter. So, I am using a UDF I discovered to parse the multi-item parameter. The UDF can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp and it returns a table variable that can be used in an IN statement. So I’m using

Code Snippet

ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@.Param,’,’))

which works brilliantly in my WHERE statement AS LONG AS @.Param ISN’T NULL. So how do I test for NULL first and still use this approach to multi-item parameters?

I’ve tried

Code Snippet

WHERE @.Param IS NULL OR ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@.Param,’,’))

and though it works, the OR causes it to slow way down as it compares every record for the OR. (It slows down by approximately 800%.) The other thing I tried was

Code Snippet

ISNULL (table.fieldname, 0) IN (CASE WHEN @.Param IS NULL THEN ISNULL(table.fieldname, 0) ELSE (SELECT value FROM dbo.fn_Split(@.Param,’,’)))

This fails with “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression” due to the multiple values in the parameter. (I can’t understand why the line without the CASE statement works, but the CASE line doesn’t!)

Am I even on the right track, cuz this is driving me mad and I just need a way to deal with optional multi-item parameters in an IN statement? HELP!

First of all, I think we need more background information...

-What is the end goal of the query?

-How are users interacting with the application? (reporting services, asp.net, etc.)

-What is the database structure like? How many tables are being joined, Approx size of tables

-Are all 17 parameters needed? Perhaps you could develop a set of linked reports that give the user more detailed information as they drill deeper.

As far as performance, I wouldn't worry too much about having the query compiled every time, this really doesn't take that long. Query performance in most cases comes from proper indexing.

|||

Anthony,

Thank you for your response!

Here's the info:

- I migrated an Access Jet database to SQL but still use Access as the client.

- Query is the source for an Access report that is the most comprehensive report in the program. The user has sorting and grouping options in the report that allow them to view most everything they need from one report.

- The database structure is very good, and the tables aren't huge, the average size of a table is 15k records, but this query touches approximately 1/4 of all tables in the db. 20 tables are being joined and the largest table of the 20 has 28k records.

- This query is inserting all the values into a temp table which I then run my report queries on. Here's ALL the code with some of the WHERE clause: (I've put a couple of different multi-item parameter techniques in the WHERE clause- of course, neither of them work.)

Code Snippet

ALTER PROCEDURE [dbo].[spAG_ProjectStatusTESTFunction]

-- Add the parameters for this sp here

@.AGProjectKey varchar(255) = NULL,

@.OpenClosed bit = NULL,

@.ClientKey varchar(200) = NULL,

@.ProdCoKey varchar(255) = NULL,

@.ProjectTitle varchar(100) = NULL,

@.AgentKey varchar(50) = NULL,

@.GenreKey varchar(200) = NULL,

@.ProjectTypeKey varchar(255) = NULL,

@.ProjectType2Key varchar(255) = NULL,

@.DirectorKey varchar(255) = NULL,

@.ProdKey varchar(255) = NULL,

@.ExecProdKey varchar(255) = NULL,

@.ContactKey varchar(255) = NULL,

@.ProjectStatus varchar(50) = NULL,

@.Distributor varchar(50) = NULL,

@.MusSup varchar(255) = NULL,

@.NonClientFinalOut varchar(255) = NULL

AS

BEGIN

DECLARE @.sSQL varchar(8000);

DECLARE @.sWHERE varchar(8000);

DECLARE @.sORDERBY varchar(8000);

CREATE TABLE #rAG_ProjectStatusPre

(

[CoProdCompany] varchar(255) NULL, [ProductionCompanyDesc] varchar(500) NULL,

[AGProjectKey] int NOT NULL, [ProjectTitle] nvarchar(100) NULL, [ProdOfficeAddr1] nvarchar(50) NULL,

[ProdOfficeAddr2] nvarchar(50) NULL, [ProdOfficeAddr3] nvarchar(50) NULL, [ProdOfficeCity] nvarchar(15) NULL,

[ProdOfficeState] nvarchar(2) NULL, [ProdOfficeFax] nvarchar(20) NULL, [ProdOfficePhone] nvarchar(20) NULL,

[ProdOfficeZip] nvarchar(9) NULL, [ClientKey] int NULL, [FirstName] nvarchar(15) NULL, [LastName] nvarchar(20) NULL,

[AgentKey] int NULL, [AgentName] nvarchar(30) NULL, [Producer] nvarchar(30) NULL,

[ExecProducer] nvarchar(30) NULL, [Director] nvarchar(30) NULL, [Contact] nvarchar(30) NULL,

[Cast] nvarchar(30) NULL, [Source] nvarchar(50) NULL, [ProjectTypeDesc] nvarchar(50) NULL,

[ProjectType2Desc] nvarchar(50) NULL, [GenreDesc] nvarchar(50) NULL, [Network] nvarchar(50) NULL,

[ProductionStatusDesc] nvarchar(50) NULL, [ClosedProject] bit NULL, [ClosedDate] datetime NULL,

[MusicSupervisor] nvarchar(255) NULL, [Location] nvarchar(255) NULL, [ProdStartDate] nvarchar(50) NULL,

[WrapShootDate] nvarchar(50) NULL, [SpottingDate] nvarchar(50) NULL, [RecordingDate] nvarchar(50) NULL,

[MixingDate] nvarchar(50) NULL, [DeliveryDate] nvarchar(50) NULL, [ReleaseDate] nvarchar(50) NULL,

[FinalDub] nvarchar(50) NULL, [FilmTVBudget] nvarchar(50) NULL, [MusicBudget] nvarchar(50) NULL,

[Synopsis] nvarchar(255) NULL, [ProjectStatus] nvarchar(255) NULL, [NonClientFinalOut] nvarchar(255) NULL

);

BEGIN

SET @.sORDERBY = '';

SET @.sWHERE = '';

SET @.sSQL = '';

INSERT INTO #rAG_ProjectStatusPre (CoProdCompany, ProductionCompanyDesc, AGProjectKey, ProjectTitle,

ProdOfficeAddr1, ProdOfficeAddr2, ProdOfficeAddr3, ProdOfficeCity, ProdOfficeState, ProdOfficeZip, ProdOfficeFax, ProdOfficePhone,

ClientKey, [FirstName], [LastName], AgentKey, AgentName, Producer, ExecProducer, Director, Contact, [Cast], Source,

ProjectTypeDesc, ProjectType2Desc, GenreDesc, Network, ProductionStatusDesc, ClosedProject, ClosedDate, MusicSupervisor,

Location, ProdStartDate, WrapShootDate, SpottingDate, RecordingDate, MixingDate, DeliveryDate,

ReleaseDate, FinalDub, FilmTVBudget, MusicBudget, Synopsis, ProjectStatus, NonClientFinalOut)

SELECT tProductionCompanies_1.ProductionCompanyDesc AS CoProdCompany,

tProductionCompanies.ProductionCompanyDesc, tAGProjects.AGProjectKey,

tAGProjects.ProjectTitle, tAGProjects.ProdOfficeAddr1, tAGProjects.ProdOfficeAddr2,

tAGProjects.ProdOfficeAddr3, tAGProjects.ProdOfficeCity, tAGProjects.ProdOfficeState,

tAGProjects.ProdOfficeZip, tAGProjects.ProdOfficeFax, tAGProjects.ProdOfficePhone,

tClients.ClientNumber AS [ClientKey], tClients.FirstName AS [FirstName],

tClients.LastName AS [LastName], tAgents.AgentKey, tAgents.AgentName,

[tMailList].[FirstName] + ' ' + [tMailList].[LastName] AS Producer,

[tMailList_1].[FirstName] + ' ' + [tMailList_1].[LastName] AS ExecProducer,

[tMailList_2].[FirstName] + ' ' + [tMailList_2].[LastName] AS Director,

[tMailList_3].[FirstName] + ' ' + [tMailList_3].[LastName] AS Contact,

[tMailList_4].[FirstName] + ' ' + [tMailList_4].[LastName] AS [Cast],

tProjectSources.Source, tProjectTypes.ProjectTypeDesc, tProjectType2s.ProjectType2Desc,

tGenre.GenreDesc, tAGProjects.Network, tProductionStatus.ProductionStatusDesc, tAGProjects.ClosedProject,

tAGProjects.ClosedDate, tAGProjects.MusicSupervisor, tAGProjects.Location, tAGProjects.ProdStartDate,

tAGProjects.WrapShootDate, tAGProjects.SpottingDate, tAGProjects.RecordingDate,

tAGProjects.MixingDate, tAGProjects.DeliveryDate, tAGProjects.ReleaseDate, tAGProjects.FinalDub,

tAGProjects.FilmTVBudget, tAGProjects.MusicBudget, tAGProjects.Synopsis,

tAGProjects.ProjectStatus, tAGProjects.NonClientFinalOut

FROM (tMailList RIGHT JOIN (tClients RIGHT JOIN ((((((((((((((tAGProjects LEFT JOIN tProductionCompanies ON

tAGProjects.ProductionCompanyKey = tProductionCompanies.ProductionCompanyKey) LEFT JOIN

tProductionCompanies AS tProductionCompanies_1 ON tAGProjects.CoProductionCompanyKey =

tProductionCompanies_1.ProductionCompanyKey) LEFT JOIN tProjectTypes ON tAGProjects.ProjectTypeKey =

tProjectTypes.ProjectTypeKey) LEFT JOIN tProjectType2s ON tAGProjects.Type2Key = tProjectType2s.ProjectType2Key)

LEFT JOIN tGenre ON tAGProjects.GenreKey = tGenre.GenreKey) LEFT JOIN tProductionStatus ON

tAGProjects.ProductionStatusKey = tProductionStatus.ProductionStatusKey) LEFT JOIN

(tProjectAgents LEFT JOIN tAgents ON tProjectAgents.AgentKey = tAgents.AgentKey) ON

tAGProjects.AGProjectKey = tProjectAgents.AGProjectKey) LEFT JOIN (tProjectCast LEFT JOIN

tMailList AS tMailList_4 ON tProjectCast.ContactKey = tMailList_4.MailListKey) ON

tAGProjects.AGProjectKey = tProjectCast.AGProjectKey) LEFT JOIN tProjectClients ON

tAGProjects.AGProjectKey = tProjectClients.AGProjectKey) LEFT JOIN (tProjectContacts LEFT JOIN

tMailList AS tMailList_3 ON tProjectContacts.ContactKey = tMailList_3.MailListKey) ON

tAGProjects.AGProjectKey = tProjectContacts.AGProjectKey) LEFT JOIN (tProjectDirectors LEFT JOIN

tMailList AS tMailList_2 ON tProjectDirectors.ContactKey = tMailList_2.MailListKey) ON

tAGProjects.AGProjectKey = tProjectDirectors.AGProjectKey) LEFT JOIN (tProjectExecProducers LEFT JOIN

tMailList AS tMailList_1 ON tProjectExecProducers.ContactKey = tMailList_1.MailListKey) ON

tAGProjects.AGProjectKey = tProjectExecProducers.AGProjectKey) LEFT JOIN tProjectProducers ON

tAGProjects.AGProjectKey = tProjectProducers.AGProjectKey) LEFT JOIN tProjectSources ON

tAGProjects.AGProjectKey = tProjectSources.AGProjectKey) ON tClients.ClientNumber = tProjectClients.ClientKey) ON tMailList.MailListKey = tProjectProducers.ContactKey)

WHERE tAGProjects.ClosedProject = COALESCE(@.OpenClosed, tAGProjects.ClosedProject)

AND @.AGProjectKey IS NULL OR ISNULL(tAGProjects.AGProjectKey, 0) IN (SELECT Value FROM dbo.fn_Split(@.AGProjectKey,','))

AND tAGProjects.ProjectTitle LIKE COALESCE(@.ProjectTitle, tAGProjects.ProjectTitle)

AND ISNULL(tClients.ClientNumber, 0) IN(CASE WHEN @.ClientKey IS NULL THEN ISNULL(tClients.ClientNumber, 0) ELSE @.ClientKey END)

END

BEGIN

SELECT DISTINCT AGProjectKey, ProjectTitle, FirstName, ClientKey, LastName, ProdOfficeAddr1, ProdOfficeAddr2, ProdOfficeAddr3, ProdOfficeCity, ProdOfficeState, ProdOfficeZip, ProdOfficeFax, ProdOfficePhone,

Network, ClosedProject, ClosedDate, MusicSupervisor, Location, ProdStartDate, WrapShootDate, SpottingDate, RecordingDate, MixingDate, DeliveryDate,

ReleaseDate, FinalDub, FilmTVBudget, MusicBudget, Synopsis, ProjectStatus, NonClientFinalOut, ProjectTypeDesc, ProjectType2Desc,

GenreDesc, ProductionCompanyDesc, CoProdCompany, ProductionStatusDesc

FROM #rAG_ProjectStatusPre

END

DROP TABLE #rAG_ProjectStatusPre

END

|||

Try:

case

when (@.Param is null) or (table.fieldname is null) then 1

else case when exists(select * from dbo.fn_split(@.Param, ',') as t where t.value = table.fieldname) then 1 else 0 end

end = 1

Not sure it will give good performance.

Here is a very good article about this theme.

Dynamic Search Conditions in T-SQL

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

AMB

|||

Okay, thanks for the follow-up information.

The previous poster's code will work, but in my opinion it makes the code rather confusing for future developers. I would code it to dynamically build the where clause.

As the table get larger performance will almost certainly become an issue due to the large number of joins. Make sure that you at least index the larger tables on the join columns and if possible create a 'covered index'. If you are unaware what that is you can learn more on books-online or try googling it.

You could code the where clause something like this...

declare @.where varchar(1000)

set @.where='where '

if @.AGProjectKey is not null

begin

set @.where=@.where + @.AGProject + ' and '

end

if @.ClientKey is not null

begin

set @.where=@.where + @.ClientKey + ' and '

end

Good luck. Let me know if you have any more questions.

Help with migrating jobs from SQL 2000 to SQL 2005

We need to migrate 500 jobs from SQL 2000 to SQL 2005 but we are getting
error hat one of the fields can’t be NULL. The field in question is OWNER_SID.
The question is how do we get the field from SQL 2000 ?
What we have try using Business Intelligent Studio to copy the jobs with out
success.
Jarek
You said that scripting out the jobs and the running them on SS2005 does
not work? In that case I think you are going to re-create them manually
"Jarek Gal" <JarekGal@.discussions.microsoft.com> wrote in message
news:DE8B79F8-FD96-4EDB-8BFE-742EC5379305@.microsoft.com...
> We need to migrate 500 jobs from SQL 2000 to SQL 2005 but we are getting
> error hat one of the fields cant be NULL. The field in question is
> OWNER_SID.
> The question is how do we get the field from SQL 2000 ?
> What we have try using Business Intelligent Studio to copy the jobs with
> out
> success.
>
|||Uri
Yes we try that and it's aking for OWNER_SID which it looks like was not in
the script.
Thanks
"Uri Dimant" wrote:

> Jarek
> You said that scripting out the jobs and the running them on SS2005 does
> not work? In that case I think you are going to re-create them manually
>
>
>
> "Jarek Gal" <JarekGal@.discussions.microsoft.com> wrote in message
> news:DE8B79F8-FD96-4EDB-8BFE-742EC5379305@.microsoft.com...
>
>

Help with migrating jobs from SQL 2000 to SQL 2005

We need to migrate 500 jobs from SQL 2000 to SQL 2005 but we are getting
error hat one of the fields can’t be NULL. The field in question is OWNER_
SID.
The question is how do we get the field from SQL 2000 ?
What we have try using Business Intelligent Studio to copy the jobs with out
success.Jarek
You said that scripting out the jobs and the running them on SS2005 does
not work? In that case I think you are going to re-create them manually
"Jarek Gal" <JarekGal@.discussions.microsoft.com> wrote in message
news:DE8B79F8-FD96-4EDB-8BFE-742EC5379305@.microsoft.com...
> We need to migrate 500 jobs from SQL 2000 to SQL 2005 but we are getting
> error hat one of the fields cant be NULL. The field in question is
> OWNER_SID.
> The question is how do we get the field from SQL 2000 ?
> What we have try using Business Intelligent Studio to copy the jobs with
> out
> success.
>|||Uri
Yes we try that and it's aking for OWNER_SID which it looks like was not in
the script.
Thanks
"Uri Dimant" wrote:

> Jarek
> You said that scripting out the jobs and the running them on SS2005 does
> not work? In that case I think you are going to re-create them manually
>
>
>
> "Jarek Gal" <JarekGal@.discussions.microsoft.com> wrote in message
> news:DE8B79F8-FD96-4EDB-8BFE-742EC5379305@.microsoft.com...
>
>