Query Help

I have a query that returns a recordset that contains this kind of data:

Description Rate Amount

item1 2 .32

item2 3 1.34

item3 5 12.33

I want change the query to put all this data in ONE single field. What is the best why to do this?

Thanks!

What the result will look like in the ONE single field?

|||

item1 2 .32 item2 3 1.34 item3 5 12.33

Just all these dumped into one field like above. However id like to use the final query in and update so i cant simply concatenate each field then each row and save it in a variable.

I have the query below but i cant use it in an update for multiple records without using a fetch, or at least I cant figure out a better way.

DECLARE @.blob varchar(2000)

SELECT
@.blob = ISNULL(@.blob,'') + (def.[description] + ' ' +
CAST(sd.amount as varchar(10)) + ' ' +
CAST(sd.minutes as varchar(10)) + CHAR(13) + CHAR(10) )
FROM
section_detail sd,
def_section_detail_description def
WHERE
sd.def_section_detail_descr_id = def.def_section_detail_descr_id
and sd.section_id = 2274893453

Query Help

I have the following query that returns extra header info in the results
when there is more than 1 day with no records in the query. I'm trying to
figure out how to run this and not get the additional header information.
Here is the query:
SET NOCOUNT ON
DECLARE @.datestart int
DECLARE @.datestop int
SET @.datestart = 1
SET @.datestop = 2
WHILE
(SELECT count(1)
FROM [tables]
WHERE (a.Start > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE())))
AND (pp.Class = 17) AND (a.Start < DATEADD(d, @.datestop, DATEDIFF(d, 0,
GETDATE())))) = 0
BEGIN
SET @.datestart = @.datestart + 1
SET @.datestop = @.datestop + 1
SELECT [statement]
FROM [tables]
WHERE (a.Start > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE()))) AND
(pp.Class = 17) AND (a.Start < DATEADD(d, @.datestop, DATEDIFF(d, 0,
GETDATE())))
ORDER BY pp.Last
END
Here's the output:
Date Name Class
--
Date Name Class
-- --
Date Name Class
-- --
May 16 2005 David L Afor 17
May 16 2005 Tina M Coll 17
May 16 2005 Dan O Doer 17chad, You are looping through code and running the select statement for eac
h
value of the @.dateStart variable, while there are records in the table, but
for each iteration, you check the Count() with one value of @.datestart,
@.datestop, and then you increment the values first, before you run the
select.. . After you change the values, there may not be any records that
match the criteria...
Also, in both your selects you refer to columns with a table prefox 'a', and
another 'pp', but these are not defined anywhere in the query... This does
not look like working code...
"chad" wrote:

> I have the following query that returns extra header info in the results
> when there is more than 1 day with no records in the query. I'm trying to
> figure out how to run this and not get the additional header information.
> Here is the query:
> SET NOCOUNT ON
> DECLARE @.datestart int
> DECLARE @.datestop int
> SET @.datestart = 1
> SET @.datestop = 2
> WHILE
> (SELECT count(1)
> FROM [tables]
> WHERE (a.Start > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE())))
> AND (pp.Class = 17) AND (a.Start < DATEADD(d, @.datestop, DATEDIFF(d, 0,
> GETDATE())))) = 0
> BEGIN
> SET @.datestart = @.datestart + 1
> SET @.datestop = @.datestop + 1
> SELECT [statement]
> FROM [tables]
> WHERE (a.Start > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE()))) AN
D
> (pp.Class = 17) AND (a.Start < DATEADD(d, @.datestop, DATEDIFF(d, 0,
> GETDATE())))
> ORDER BY pp.Last
> END
> Here's the output:
> Date Name Class
> --
> Date Name Class
> -- --
> Date Name Class
> -- --
> May 16 2005 David L Afor 17
> May 16 2005 Tina M Coll 17
> May 16 2005 Dan O Doer 17|||chad,
The way you have it the code will run from DateStart = 1 until it finds
any date with no records, even if subsequent dates have records... Is that
what you want? If not, then I suggest you define the endDate and change loop
as so:
Declare @.DateStart int Set @.DateStart = 1
Declare @.DateStop int Set @.DateStop = 350 -- or whatever
While @.DateStart < @.DateStop
Begin
If Exists (Select * from [Tables]
Where pp.Class = 17
And a.Start > DateAdd(d, @.datestart, DateDiff(d, 0,
getDate()))
And a.Start < DateAdd(d, @.datestart, DateDiff(d, 1,
getDate())))
Select <Stuff> from [Tables]
Where pp.Class = 17
And a.Start > DateAdd(d, @.datestart, DateDiff(d, 0,
getDate()))
And a.Start < DateAdd(d, @.datestart, DateDiff(d, 1,
getDate()))
Set @.DateStart = @.DateStart + 1
End
"chad" wrote:

> I have the following query that returns extra header info in the results
> when there is more than 1 day with no records in the query. I'm trying to
> figure out how to run this and not get the additional header information.
> Here is the query:
> SET NOCOUNT ON
> DECLARE @.datestart int
> DECLARE @.datestop int
> SET @.datestart = 1
> SET @.datestop = 2
> WHILE
> (SELECT count(1)
> FROM [tables]
> WHERE (a.Start > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE())))
> AND (pp.Class = 17) AND (a.Start < DATEADD(d, @.datestop, DATEDIFF(d, 0,
> GETDATE())))) = 0
> BEGIN
> SET @.datestart = @.datestart + 1
> SET @.datestop = @.datestop + 1
> SELECT [statement]
> FROM [tables]
> WHERE (a.Start > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE()))) AN
D
> (pp.Class = 17) AND (a.Start < DATEADD(d, @.datestop, DATEDIFF(d, 0,
> GETDATE())))
> ORDER BY pp.Last
> END
> Here's the output:
> Date Name Class
> --
> Date Name Class
> -- --
> Date Name Class
> -- --
> May 16 2005 David L Afor 17
> May 16 2005 Tina M Coll 17
> May 16 2005 Dan O Doer 17|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Your names in the sample output are a mix of reserved
words and useless violations of basic ISO-11179 conventions. This
totally useless.
The use of a loop tells us that you do not understand a declarative
language. The use of the word "record" tells us do not know SQL, which
is why you are writing loops and other procedural code tha twill runs
ordersof magnitude slower than good code. .
In clear English, so we do not have to guess, what do you want to do.
And post minimal DDL next time. Was CBretana right about about looking
for a run starting at a given date?|||This query returns records correctly when there is only one day of no
records. It is when I hit two days of zero records that I get an additional
header row with no data that messes it up. I set the @.datestart and
@.datestop at 1 and 2 respectfully to look for tomorrows records, if zero the
n
increment by 1 and rerun till it finds a count. Once that is satisfied then
it needs to run the second part of the query to retun data.
-- Here is the entire query with table names changed
--
SET NOCOUNT ON
DECLARE @.datestart int
DECLARE @.datestop int
SET @.datestart = 1
SET @.datestop = 2
WHILE
(SELECT count(*)
FROM dbo.TableA a LEFT OUTER JOIN
dbo.TableAC ac ON a.ApptChainId = ac.ApptChainId LEFT
OUTER JOIN
dbo.TableASET aset ON a.ApptSetId = aset.ApptSetId INNER
JOIN
dbo.TableDFF dff ON a.FacilityId = dff.DoctorFacilityId
INNER JOIN
dbo.TablePP pp ON a.OwnerId = pp.PatientProfileId INNER JOIN
dbo.TableDFF dfr ON a.ResourceId = dfr.DoctorFacilityId
LEFT OUTER JOIN
dbo.TableAT at ON a.ApptTypeId = at.ApptTypeId LEFT OUTER JOIN
dbo.TableMLFC mlfc ON pp.FinancialClassMId = mlfc.MedListsId LEFT OUTER
JOIN
dbo.TableC c ON a.CasesId = c.CasesId LEFT OUTER JOIN
dbo.TableMLFCC mlfcc ON c.FinancialClassMId = mlfcc.MedListsId
WHERE (a.ApptStart > DATEADD(d, @.datestart, DATEDIFF(d, 0,
GETDATE()))) AND (pp.FinancialClassMId = 1739) AND (a.ApptStart < DATEADD(d,
@.datestop, DATEDIFF(d, 0, GETDATE())))) = 0
BEGIN
SET @.datestart = @.datestart + 1
SET @.datestop = @.datestop + 1
SELECT CAST(a.ApptStart AS varchar(11)) AS Date, ISNULL(pp.First + ' ',
'') + ISNULL(pp.Middle + ' ', '') + pp.Last AS Name, dfr.ListName AS
ResourceName,
pp.SSN AS SSN, pp.PatientId, ISNULL(at.Name,
'Unknown') AS ApptType, pp.Phone1 AS Phone1, CAST(pp.Birthdate AS
varchar(11)) AS Birthdate,
pp.FinancialClassMId
FROM dbo.TableA a LEFT OUTER JOIN
dbo.TableAC ac ON a.ApptChainId = ac.ApptChainId LEFT
OUTER JOIN
dbo.TableASET aset ON a.ApptSetId = aset.ApptSetId INNER
JOIN
dbo.TableDFF dff ON a.FacilityId = dff.DoctorFacilityId
INNER JOIN
dbo.TablePP pp ON a.OwnerId = pp.PatientProfileId INNER JOIN
dbo.TableDFF dfr ON a.ResourceId = dfr.DoctorFacilityId
LEFT OUTER JOIN
dbo.TableAT at ON a.ApptTypeId = at.ApptTypeId LEFT OUTER JOIN
dbo.TableMLFC mlfc ON pp.FinancialClassMId = mlfc.MedListsId LEFT OUTER
JOIN
dbo.TableC c ON a.CasesId = c.CasesId LEFT OUTER JOIN
dbo.TableMLFCC mlfcc ON c.FinancialClassMId = mlfcc.MedListsId
WHERE (a.ApptStart > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE())))
AND (pp.FinancialClassMId = 1739) AND (a.ApptStart < DATEADD(d, @.datestop,
DATEDIFF(d, 0,
GETDATE())))
ORDER BY pp.Last
END|||chad,
The way you have your code written, is equivilent to this psueo code
While (There's 1 or more records using DateStart, DateStart +1)
Begin
Increment DateStart
Return Records for NEW CHANGED VALUE of Datestart, datestart +1
End
-- This *sounds* illogical... It means that whenever the code gets to a
place where there ARE records for the current date, (which would have been
returne the last time through the loop), but NONE FOR THE NEXT day, (althoug
h
it doesn't know that yet) it will enter the while loop, return an empty set,
(That's where you're getting your header with no records) and then, when it
retests the while it will stop...
Your boolean test, in the while clause (with the COunt(1) ...) is testing
how many records were selected in the last time through the loop... , becaus
e
you're incrementing the control variables BEFORE you run the select...
"chad" wrote:

> This query returns records correctly when there is only one day of no
> records. It is when I hit two days of zero records that I get an additiona
l
> header row with no data that messes it up. I set the @.datestart and
> @.datestop at 1 and 2 respectfully to look for tomorrows records, if zero t
hen
> increment by 1 and rerun till it finds a count. Once that is satisfied the
n
> it needs to run the second part of the query to retun data.
>
> -- Here is the entire query with table names changed
> --
> SET NOCOUNT ON
> DECLARE @.datestart int
> DECLARE @.datestop int
> SET @.datestart = 1
> SET @.datestop = 2
> WHILE
> (SELECT count(*)
> FROM dbo.TableA a LEFT OUTER JOIN
> dbo.TableAC ac ON a.ApptChainId = ac.ApptChainId LEFT
> OUTER JOIN
> dbo.TableASET aset ON a.ApptSetId = aset.ApptSetId INNER
> JOIN
> dbo.TableDFF dff ON a.FacilityId = dff.DoctorFacilityId
> INNER JOIN
> dbo.TablePP pp ON a.OwnerId = pp.PatientProfileId INNER
JOIN
> dbo.TableDFF dfr ON a.ResourceId = dfr.DoctorFacilityId
> LEFT OUTER JOIN
> dbo.TableAT at ON a.ApptTypeId = at.ApptTypeId LEFT OUTER JOIN
> dbo.TableMLFC mlfc ON pp.FinancialClassMId = mlfc.MedListsId LEFT OUTER
> JOIN
> dbo.TableC c ON a.CasesId = c.CasesId LEFT OUTER JOIN
> dbo.TableMLFCC mlfcc ON c.FinancialClassMId = mlfcc.MedListsId
> WHERE (a.ApptStart > DATEADD(d, @.datestart, DATEDIFF(d, 0,
> GETDATE()))) AND (pp.FinancialClassMId = 1739) AND (a.ApptStart < DATEADD(
d,
> @.datestop, DATEDIFF(d, 0, GETDATE())))) = 0
> BEGIN
> SET @.datestart = @.datestart + 1
> SET @.datestop = @.datestop + 1
> SELECT CAST(a.ApptStart AS varchar(11)) AS Date, ISNULL(pp.First + '
',
> '') + ISNULL(pp.Middle + ' ', '') + pp.Last AS Name, dfr.ListName AS
> ResourceName,
> pp.SSN AS SSN, pp.PatientId, ISNULL(at.Name,
> 'Unknown') AS ApptType, pp.Phone1 AS Phone1, CAST(pp.Birthdate AS
> varchar(11)) AS Birthdate,
> pp.FinancialClassMId
> FROM dbo.TableA a LEFT OUTER JOIN
> dbo.TableAC ac ON a.ApptChainId = ac.ApptChainId LEFT
> OUTER JOIN
> dbo.TableASET aset ON a.ApptSetId = aset.ApptSetId INNER
> JOIN
> dbo.TableDFF dff ON a.FacilityId = dff.DoctorFacilityId
> INNER JOIN
> dbo.TablePP pp ON a.OwnerId = pp.PatientProfileId INNER
JOIN
> dbo.TableDFF dfr ON a.ResourceId = dfr.DoctorFacilityId
> LEFT OUTER JOIN
> dbo.TableAT at ON a.ApptTypeId = at.ApptTypeId LEFT OUTER JOIN
> dbo.TableMLFC mlfc ON pp.FinancialClassMId = mlfc.MedListsId LEFT OUTER
> JOIN
> dbo.TableC c ON a.CasesId = c.CasesId LEFT OUTER JOIN
> dbo.TableMLFCC mlfcc ON c.FinancialClassMId = mlfcc.MedListsId
> WHERE (a.ApptStart > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE()))
)
> AND (pp.FinancialClassMId = 1739) AND (a.ApptStart < DATEADD(d, @.datestop,
> DATEDIFF(d, 0,
> GETDATE())))
> ORDER BY pp.Last
> END|||CBretana-
I changed the code to resemble your suggestion in the prior post, and it
complete successfully but returns no data.
Declare @.DateStart int
Declare @.DateStop int
Set @.DateStart = 1
Set @.DateStop = 2
WHILE @.DateStart < @.DateStop
BEGIN
IF EXISTS (SELECT CAST(a.ApptStart AS varchar(11)) AS Date,
ISNULL(pp.First + ' ', '') + ISNULL(pp.Middle + ' ', '') + pp.Last AS Name,
dfr.ListName AS ResourceName,
pp.SSN AS SSN, pp.PatientId, ISNULL(at.Name,
'Unknown') AS ApptType, pp.Phone1 AS Phone1, CAST(pp.Birthdate AS
varchar(11)) AS Birthdate, pp.FinancialClassMId
FROM dbo.Appointments a LEFT OUTER JOIN
dbo.ApptChain ac ON a.ApptChainId = ac.ApptChainId LEFT OUTER JOIN
dbo.ApptSet aset ON a.ApptSetId = aset.ApptSetId INNER JOIN
dbo.DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
INNER JOIN
dbo.PatientProfile pp ON a.OwnerId = pp.PatientProfileId INNER JOIN
dbo.DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
LEFT OUTER JOIN
dbo.ApptType at ON a.ApptTypeId = at.ApptTypeId
WHERE pp.FinancialClassMId = 1739 And a.ApptStart >DATEADD(d,
@.datestart, DATEDIFF(d, 0, GETDATE())) And a.ApptStart < DATEADD(d,
@.datestart, DATEDIFF(d, 1, GETDATE())))
SELECT CAST(a.ApptStart AS varchar(11)) AS Date, ISNULL(pp.First + ' ', '')
+ ISNULL(pp.Middle + ' ', '') + pp.Last AS Name, dfr.ListName AS
ResourceName,
pp.SSN AS SSN, pp.PatientId, ISNULL(at.Name, 'Unknown') AS ApptType,
pp.Phone1 AS Phone1, CAST(pp.Birthdate AS varchar(11)) AS Birthdate,
pp.FinancialClassMId
FROM dbo.Appointments a LEFT OUTER JOIN
dbo.ApptChain ac ON a.ApptChainId = ac.ApptChainId LEFT OUTER JOIN
dbo.ApptSet aset ON a.ApptSetId = aset.ApptSetId INNER JOIN
dbo.DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId INNER
JOIN
dbo.PatientProfile pp ON a.OwnerId = pp.PatientProfileId INNER JOIN
dbo.DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId LEFT
OUTER JOIN
dbo.ApptType at ON a.ApptTypeId = at.ApptTypeId
WHERE pp.FinancialClassMId = 1739 And a.ApptStart > DATEADD(d, @.datestart,
DATEDIFF(d, 0, GETDATE())) And a.ApptStart < DATEADD(d, @.datestart,
DATEDIFF(d, 1, GETDATE()))
ORDER BY pp.Last
SET @.DateStart = @.DateStart + 1
END|||>> I changed the code to resemble your suggestion in the prior post,
and it complete successfully but returns no data. <<
And now, how about some DDL? And try to fix up the obviously absurd
things like "type_id" in the data model.
day of no records [sic]. It is when I hit two days of zero records
[sic] that I get an additional header row with no data that messes it
up. <<
You might also want to learn the differences between records and rows.
If you keep talking in fiel system terms, you will keep producing file
system code, like your cursors.
Sample data and expect results would be nice, too. Are you looking for
runs with gaps of two or more days between them, so a gap of one is
fine?|||On Thu, 12 May 2005 16:10:27 -0700, chad wrote:

>I have the following query that returns extra header info in the results
>when there is more than 1 day with no records in the query. I'm trying to
>figure out how to run this and not get the additional header information.
(snip)
Hi Chad,
I'm not sure if you're still reading., since this question is already 5
days old.
Anyway, reading your code I have the feeling that you could do this in
one SELECT statement instead of using a loop:
SELECT statement
FROM tables
WHERE CONVERT(char(10), Start, 114)
= (SELECT CONVERT(char(10), MIN(Start), 114)
FROM tables
WHERE CONVERT(char(10), Start, 114)
> CONVERT(char(10), CURRENT_TIMESTAMP, 114))
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query help

Hi,
I have an employee table having fields empid and managerid.
I need a query which returns all the children once the parentid is given.
Here is the script
create table employee (empid int , parentID int )
GO
insert into employee select 1,null
insert into employee select 2,1
insert into employee select 3,1
insert into employee select 4,3
insert into employee select 5,3
insert into employee select 6,4
insert into employee select 7,4
insert into employee select 8,5
insert into employee select 9,5
insert into employee select 10,6
insert into employee select 11,6
go
So, when i pass the empid 3, it should return all the children under 3; i.e.
4,5,6,7,8,9,10 and 11
Thanking in advance
regards
Laraselect * from employee as e
where e.parentid=<managerID>
"Lara" <lara169@.gmail.com> wrote in message
news:OTS6tH18FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have an employee table having fields empid and managerid.
> I need a query which returns all the children once the parentid is given.
> Here is the script
> create table employee (empid int , parentID int )
> GO
> insert into employee select 1,null
> insert into employee select 2,1
> insert into employee select 3,1
> insert into employee select 4,3
> insert into employee select 5,3
> insert into employee select 6,4
> insert into employee select 7,4
> insert into employee select 8,5
> insert into employee select 9,5
> insert into employee select 10,6
> insert into employee select 11,6
> go
> So, when i pass the empid 3, it should return all the children under 3;
> i.e. 4,5,6,7,8,9,10 and 11
>
> Thanking in advance
> regards
> Lara
>|||Thanks martin,
But this is not the one i needed. i need all the children under this
parentid
"Martin" <x@.y.z> wrote in message
news:%23tqUlX18FHA.636@.TK2MSFTNGP10.phx.gbl...
> select * from employee as e
> where e.parentid=<managerID>
> "Lara" <lara169@.gmail.com> wrote in message
> news:OTS6tH18FHA.4076@.tk2msftngp13.phx.gbl...
>|||Do you mean recursively?
"Lara" <lara169@.gmail.com> wrote in message
news:exAsfZ18FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Thanks martin,
> But this is not the one i needed. i need all the children under this
> parentid
>
> "Martin" <x@.y.z> wrote in message
> news:%23tqUlX18FHA.636@.TK2MSFTNGP10.phx.gbl...
>|||Select
c.*
from employee as m
left join employee as c
on c.ParentID = m.EmpID
"Lara" <lara169@.gmail.com>, haber iletisinde unlar
yazd:OTS6tH18FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have an employee table having fields empid and managerid.
> I need a query which returns all the children once the parentid is given.
> Here is the script
> create table employee (empid int , parentID int )
> GO
> insert into employee select 1,null
> insert into employee select 2,1
> insert into employee select 3,1
> insert into employee select 4,3
> insert into employee select 5,3
> insert into employee select 6,4
> insert into employee select 7,4
> insert into employee select 8,5
> insert into employee select 9,5
> insert into employee select 10,6
> insert into employee select 11,6
> go
> So, when i pass the empid 3, it should return all the children under 3;
> i.e. 4,5,6,7,8,9,10 and 11
>
> Thanking in advance
> regards
> Lara
>|||Something like this (I've not tested it - there are some syntax errors)
declare @.tab table (empid int ,parentid int)
declare @.rowsaffected int
set @.rowsaffected=-1
declare @.empid int
set @.empid=3
insert into @.tab (select * from employee where empid=@.empid)
set @.rowsaffected=@.@.rowcount
while @.rowsaffected<>0 -- stop when there are no more employees added to
table
begin
insert into @.tab (select * from employee
where
parentid in (select * empid from @.tab) and -- select next level down
from those already selected
empid not in (select empid from @.tab) -- don't double select
)
set @.rowsaffected=@.@.rowcount
end
select empid from @.tab where empid<>@.empid -- don't return orginal employee
id
"Martin" <x@.y.z> wrote in message
news:Oi$Jzb18FHA.808@.TK2MSFTNGP09.phx.gbl...
> Do you mean recursively?
> "Lara" <lara169@.gmail.com> wrote in message
> news:exAsfZ18FHA.2364@.TK2MSFTNGP12.phx.gbl...
>|||Here's the correct syntax
declare @.tab table (empid int ,parentid int)
declare @.rowsaffected int
set @.rowsaffected=-1
declare @.empid int
set @.empid=3
insert into @.tab(empid,parentid) (select empid,parentid from employee where
empid=@.empid)
set @.rowsaffected=@.@.rowcount
while @.rowsaffected<>0
begin
insert into @.tab(empid,parentid) (select empid,parentid from employee
where
parentid in (select empid from @.tab) and -- select next level down from
those already selected
empid not in (select empid from @.tab) -- don't double select
)
set @.rowsaffected=@.@.rowcount
end
select empid from @.tab where empid<>@.empid -- don't return orginal employee
id
"Martin" <x@.y.z> wrote in message
news:eA1O6n18FHA.3984@.TK2MSFTNGP11.phx.gbl...
> Something like this (I've not tested it - there are some syntax errors)
> declare @.tab table (empid int ,parentid int)
> declare @.rowsaffected int
> set @.rowsaffected=-1
> declare @.empid int
> set @.empid=3
> insert into @.tab (select * from employee where empid=@.empid)
> set @.rowsaffected=@.@.rowcount
> while @.rowsaffected<>0 -- stop when there are no more employees added to
> table
> begin
> insert into @.tab (select * from employee
> where
> parentid in (select * empid from @.tab) and -- select next level down
> from those already selected
> empid not in (select empid from @.tab) -- don't double select
> )
> set @.rowsaffected=@.@.rowcount
> end
> select empid from @.tab where empid<>@.empid -- don't return orginal
> employee id
>
> "Martin" <x@.y.z> wrote in message
> news:Oi$Jzb18FHA.808@.TK2MSFTNGP09.phx.gbl...
>|||Have a look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
When I say 'look' I mean copy/paste/test.
ML|||Lara (lara169@.gmail.com) writes:
> I have an employee table having fields empid and managerid.
> I need a query which returns all the children once the parentid is given.
> Here is the script
> create table employee (empid int , parentID int )
> GO
> insert into employee select 1,null
> insert into employee select 2,1
> insert into employee select 3,1
> insert into employee select 4,3
> insert into employee select 5,3
> insert into employee select 6,4
> insert into employee select 7,4
> insert into employee select 8,5
> insert into employee select 9,5
> insert into employee select 10,6
> insert into employee select 11,6
> go
> So, when i pass the empid 3, it should return all the children under 3;
> i.e. 4,5,6,7,8,9,10 and 11
Here is how you can do this on SQL 2005:
with emp (empid, parentID) as
(select empid, parentID = NULL
from employee
where empid = 3
union all
select e.empid, e.parentID
from emp
join employee e on e.parentID = emp.empid)
select empid from emp where empid <> 3
I reckon that you are likely to still use SQL 2000, but I wanted to show
that this is a lot easier on SQL 2005.
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|||Thanks Martin, But can we do it in a single query ?
"Martin" <x@.y.z> wrote in message
news:%23loxEq18FHA.4084@.TK2MSFTNGP10.phx.gbl...
> Here's the correct syntax
>
> declare @.tab table (empid int ,parentid int)
> declare @.rowsaffected int
> set @.rowsaffected=-1
> declare @.empid int
> set @.empid=3
> insert into @.tab(empid,parentid) (select empid,parentid from employee
> where empid=@.empid)
> set @.rowsaffected=@.@.rowcount
> while @.rowsaffected<>0
> begin
> insert into @.tab(empid,parentid) (select empid,parentid from employee
> where
> parentid in (select empid from @.tab) and -- select next level down from
> those already selected
> empid not in (select empid from @.tab) -- don't double select
> )
> set @.rowsaffected=@.@.rowcount
> end
> select empid from @.tab where empid<>@.empid -- don't return orginal
> employee id
> "Martin" <x@.y.z> wrote in message
> news:eA1O6n18FHA.3984@.TK2MSFTNGP11.phx.gbl...
>sql

Query Help

Hoping the community can help me with this problem. I have a stored
procedure that returns a few alias fields.
Here is the select clause to give you an idea what I am talking about (FROM
and WHERE omitted to shorten the length):
SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
Table].LocationName,
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
END AS DLPhoto,
CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
[DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current Driver
Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
[DriverEmploymentDateHired] >= '8/17/2001' AND
[DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
Pre-employment Form (40.25(j)' END AS DPre4025DrugTest
This query has 4 alias's to be returned; DLRenewDate,DLPhoto,[DPre-Employ],
and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
not want the record to be returned with the query. If I could have a WHERE
like this:
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULL
then it would not be a problem, but you cannot test alias's in this manner
(as far as I can tell). What are my options for solving this problem? If I
was in Access I could run a second query on the first and test my
conditions. Can I use another Stored procedure in this manner?
For Example a second stored procedure that works like this:
Select * From StoredProcedure1
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULLHi
To reduce the amount of typing you have to do, you can use a table alias for
the table. see Books Online:
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_05_4ofn.htm
Instead of using the column alias in the WHERE clause, use the actual
expression. As these are CASE statements returning non null values when they
are satisfied they will only return null if non of the clauses are returned,
so these can be tested in the WHERE clause
e.g
For:
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days'
END AS DLRenewDate
This will only be null if
NOT ( DriverLicenseRenewDate <= GETDATE()
OR ( DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE()) ) )
Which is the same as
DriverLicenseRenewDate > GETDATE()
AND DriverLicenseRenewDate >= DATEADD(dd, 60, GETDATE()) )
HTH
John
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>|||That makes sense, thank you.
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>

Query Help

Help, please!
I have 3 columns with numeric values. i want to write a query that returns
the following:
If all 3 columns are populated, then the middle value (not the average);
If one colunn has a null, then the lesser value,
If 2 are null, then the remaining value.
For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
the result to be "10". If ColumnB were null, then the result would be "5".
Making sense?
Is there a way to do this, without having to create a bazillion Case When
statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
.. and so on).
Just wondering.
Thank you!
Kyra
--
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBAKyra,
I think the bazillion CASEs is the only way to go. Here's one attempt:
CREATE TABLE T1 (T1ID INT NOT NULL IDENTITY, Col1 int, Col2 int, Col3 int)
GO
INSERT T1 VALUES (NULL, NUll, 1)
INSERT T1 VALUES (NULL, 2, NULL)
INSERT T1 VALUES (3, NUll, NULL)
INSERT T1 VALUES (NULL, 4, 5)
INSERT T1 VALUES (6, NULL, 7)
INSERT T1 VALUES (8,9, NULL)
INSERT T1 VALUES (10,11,12)
GO
SELECT
CASE
WHEN Col1 IS NULL AND Col2 IS NULL THEN Col3
WHEN Col2 IS NULL AND Col2 IS NULL THEN Col1
WHEN Col1 IS NULL AND Col3 IS NULL THEN Col2
WHEN Col1 IS NULL AND Col2 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col2 < Col3 THEN Col2 Else Col3 END
WHEN Col2 IS NULL AND Col1 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col1 < Col3 THEN Col1 Else Col3 END
WHEN Col3 IS NULL AND Col1 IS NOT NULL AND Col2 IS NOT NULL THEN
CASE WHEN Col1 < Col2 THEN Col1 Else Col2 END
WHEN Col1 <= Col2 AND Col2 <= Col3 THEN Col2
WHEN Col2 <= Col3 AND Col3 <= Col1 THEN Col3
WHEN Col3 <= Col1 AND Col1 <= Col2 THEN Col1
END
FROM T1
One thing your conditions left out: what if they're all NULL?
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:3147EC21-B5A3-4725-B156-6A40834E5561@.microsoft.com...
> Help, please!
> I have 3 columns with numeric values. i want to write a query that returns
> the following:
> If all 3 columns are populated, then the middle value (not the average);
> If one colunn has a null, then the lesser value,
> If 2 are null, then the remaining value.
> For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
> the result to be "10". If ColumnB were null, then the result would be "5".
> Making sense?
> Is there a way to do this, without having to create a bazillion Case When
> statements? (case when columnA > ColumnB and ColumnB < ColumnC then
> ColumnB
> else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else .
> .
> .. and so on).
> Just wondering.
> Thank you!
> Kyra
> --
> Financial Systems Analyst
> CCNA, MCSE, MCSA, MCDBA|||Ysandre,
Here is an alternative to Ron's solution. In general, you
would have an easier time if all the values were in one
column..
SELECT
T1ID,
CASE cntC
WHEN 1 THEN maxC
WHEN 2 THEN minC
WHEN 3 THEN sumC - maxC - minC
END AS C
FROM (
SELECT
T1ID,
SUM(C) as sumC,
MIN(C) as minC,
MAX(C) as maxC,
COUNT(C) as cntC
FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T
GROUP BY T1ID
) T
or
SELECT T1ID, MIN(C) FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T1
WHERE C IN (
SELECT TOP 2 C FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
If your table were in the form I used for the derived table above (one
value column instead of three), you could write
SELECT T1ID, MIN(C) FROM T1
WHERE C IN (
SELECT TOP 2 C FROM T1 AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
Steve Kass
Drew University
Ysandre wrote:
>Help, please!
>I have 3 columns with numeric values. i want to write a query that returns
>the following:
>If all 3 columns are populated, then the middle value (not the average);
>If one colunn has a null, then the lesser value,
>If 2 are null, then the remaining value.
>For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
>the result to be "10". If ColumnB were null, then the result would be "5".
>Making sense?
>Is there a way to do this, without having to create a bazillion Case When
>statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
>else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
>.. and so on).
>Just wondering.
>Thank you!
>Kyra
>
>|||Thank you Ron and Steve, that was very helpful!!!!
Thanks,
ysandre
"Steve Kass" wrote:
> Ysandre,
> Here is an alternative to Ron's solution. In general, you
> would have an easier time if all the values were in one
> column..
> SELECT
> T1ID,
> CASE cntC
> WHEN 1 THEN maxC
> WHEN 2 THEN minC
> WHEN 3 THEN sumC - maxC - minC
> END AS C
> FROM (
> SELECT
> T1ID,
> SUM(C) as sumC,
> MIN(C) as minC,
> MAX(C) as maxC,
> COUNT(C) as cntC
> FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T
> GROUP BY T1ID
> ) T
> or
> SELECT T1ID, MIN(C) FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T1
> WHERE C IN (
> SELECT TOP 2 C FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> If your table were in the form I used for the derived table above (one
> value column instead of three), you could write
> SELECT T1ID, MIN(C) FROM T1
> WHERE C IN (
> SELECT TOP 2 C FROM T1 AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> Steve Kass
> Drew University
>
> Ysandre wrote:
> >Help, please!
> >
> >I have 3 columns with numeric values. i want to write a query that returns
> >the following:
> >If all 3 columns are populated, then the middle value (not the average);
> >If one colunn has a null, then the lesser value,
> >If 2 are null, then the remaining value.
> >
> >For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
> >the result to be "10". If ColumnB were null, then the result would be "5".
> >Making sense?
> >
> >Is there a way to do this, without having to create a bazillion Case When
> >statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
> >else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
> >.. and so on).
> >
> >Just wondering.
> >Thank you!
> >Kyra
> >
> >
> >
>

Query Help

Hoping the community can help me with this problem. I have a stored
procedure that returns a few alias fields.
Here is the select clause to give you an idea what I am talking about (FROM
and WHERE omitted to shorten the length):
SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
Table].LocationName,
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
END AS DLPhoto,
CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
[DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current Driver
Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
[DriverEmploymentDateHired] >= '8/17/2001' AND
[DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
Pre-employment Form (§40.25(j)' END AS DPre4025DrugTest
This query has 4 alias's to be returned; DLRenewDate,DLPhoto,[DPre-Employ],
and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
not want the record to be returned with the query. If I could have a WHERE
like this:
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULL
then it would not be a problem, but you cannot test alias's in this manner
(as far as I can tell). What are my options for solving this problem? If I
was in Access I could run a second query on the first and test my
conditions. Can I use another Stored procedure in this manner?
For Example a second stored procedure that works like this:
Select * From StoredProcedure1
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULLHi
To reduce the amount of typing you have to do, you can use a table alias for
the table. see Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_05_4ofn.htm
Instead of using the column alias in the WHERE clause, use the actual
expression. As these are CASE statements returning non null values when they
are satisfied they will only return null if non of the clauses are returned,
so these can be tested in the WHERE clause
e.g
For:
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days'
END AS DLRenewDate
This will only be null if
NOT ( DriverLicenseRenewDate <= GETDATE()
OR ( DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE()) ) )
Which is the same as
DriverLicenseRenewDate > GETDATE()
AND DriverLicenseRenewDate >= DATEADD(dd, 60, GETDATE()) )
HTH
John
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (§40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>|||That makes sense, thank you.
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (§40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>sql

Query Help

I have a query below that returns a table with some client information along
with the last project manager that met with them and the date they met.
Nothing is included in the table if there are no meetings for that client.
Is there a way that I can still have the client information included even if
there are no meetings?

SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
FROM dbo.MeetingView T INNER JOIN
dbo.ContactView ON T.ContactIDNum =
dbo.ContactView.ContactIDNum
WHERE (T.[Date] =
(SELECT MAX([Date])
FROM Meeting
WHERE ContactIDNum = T.ContactIDNum))

--
--
Karl A. Homburg
Electrical Engineer
U.P. Engineers & Architects, Inc.
100 Portage Street, Houghton, MI 49931
PH: (906) 482-4810 FX: (906) 482-9799Karl A. Homburg (k-n-o-s-p-a-m-homburg@.upea.com) writes:
> I have a query below that returns a table with some client information
> along with the last project manager that met with them and the date they
> met. Nothing is included in the table if there are no meetings for that
> client. Is there a way that I can still have the client information
> included even if there are no meetings?
> SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
> dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
> FROM dbo.MeetingView T INNER JOIN
> dbo.ContactView ON T.ContactIDNum =
> dbo.ContactView.ContactIDNum
> WHERE (T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum))

For this type of query, it is always helpful to include CREATE TABLE
statements of your tables, and INSERT statements with sample data
and finally the desired output from the sample. Failing to provide
that increases the risk that you answer is based on a fair amount of
guesswork, like this suggestion:

SELECT T.ContactIDNum, C.Organization, C.Name,
C.UPEAPM, T.UPEAContact, T.[Date]
FROM dbo.ContactView C
LEFT JOIN dbo.MeetingView T
ON T.ContactIDNum = C.ContactIDNum
AND (T.[Date] = (SELECT MAX([Date])
FROM Meeting M
WHERE M.ContactIDNum = T.ContactIDNum))

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Wed, 25 Aug 2004 15:40:03 -0400, Karl A. Homburg wrote:

>I have a query below that returns a table with some client information along
>with the last project manager that met with them and the date they met.
>Nothing is included in the table if there are no meetings for that client.
>Is there a way that I can still have the client information included even if
>there are no meetings?
>SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
>dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
>FROM dbo.MeetingView T INNER JOIN
> dbo.ContactView ON T.ContactIDNum =
>dbo.ContactView.ContactIDNum
>WHERE (T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum))

Hi Karl,

SELECT T.ContactIDNum, dbo.ContactView.Organization,
dbo.ContactView.Name, dbo.ContactView.UPEAPM,
T.UPEAContact, T.[Date]
FROM dbo.MeetingView T
RIGHT JOIN dbo.ContactView
ON T.ContactIDNum = dbo.ContactView.ContactIDNum
AND T.[Date] = (SELECT MAX([Date])
FROM Meeting
WHERE ContactIDNum = T.ContactIDNum))
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 25 Aug 2004 15:40:03 -0400, Karl A. Homburg wrote:

> I have a query below that returns a table with some client information along
> with the last project manager that met with them and the date they met.
> Nothing is included in the table if there are no meetings for that client.
> Is there a way that I can still have the client information included even if
> there are no meetings?
> SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
> dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
> FROM dbo.MeetingView T INNER JOIN
> dbo.ContactView ON T.ContactIDNum =
> dbo.ContactView.ContactIDNum
> WHERE (T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum))

try

SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
FROM dbo.MeetingView T
RIGHT JOIN dbo.ContactView ON T.ContactIDNum = dbo.ContactView.ContactIDNum
WHERE (T.[Date] IS NULL
OR T.[Date] =
(SELECT MAX([Date])
FROM Meeting
WHERE ContactIDNum = T.ContactIDNum))|||This one almost works. The only problem is that the ClientIDNum for the
rows that do not have any meetings shows up as null.

--
--
Karl A. Homburg
Electrical Engineer
U.P. Engineers & Architects, Inc.
100 Portage Street, Houghton, MI 49931
PH: (906) 482-4810 FX: (906) 482-9799
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:xw2983mmhlyy.1liq73j8yi7i1$.dlg@.40tude.net...
> On Wed, 25 Aug 2004 15:40:03 -0400, Karl A. Homburg wrote:
>> I have a query below that returns a table with some client information
>> along
>> with the last project manager that met with them and the date they met.
>> Nothing is included in the table if there are no meetings for that
>> client.
>> Is there a way that I can still have the client information included even
>> if
>> there are no meetings?
>>
>> SELECT T.ContactIDNum, dbo.ContactView.Organization,
>> dbo.ContactView.Name,
>> dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
>> FROM dbo.MeetingView T INNER JOIN
>> dbo.ContactView ON T.ContactIDNum =
>> dbo.ContactView.ContactIDNum
>> WHERE (T.[Date] =
>> (SELECT MAX([Date])
>> FROM Meeting
>> WHERE ContactIDNum = T.ContactIDNum))
> try
> SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
> dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
> FROM dbo.MeetingView T
> RIGHT JOIN dbo.ContactView ON T.ContactIDNum =
> dbo.ContactView.ContactIDNum
> WHERE (T.[Date] IS NULL
> OR T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum))|||On Wed, 25 Aug 2004 18:55:46 -0400, Karl A. Homburg wrote:

>This one almost works. The only problem is that the ClientIDNum for the
>rows that do not have any meetings shows up as null.

Hi Karl,

Do the suggestions of Erland and me "almost work" as well?

Displaying the ClientIDNum for the rows without any meeting can be
achieved by replacing T.ContactIDNum (in the SELECT list) with
dbo.ContactView.ContactIDNum.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Query Help

This query works except when the count for the Wins and losses is 0, the
query returns nothing. The count has to be at least 1 or more. ANy
ideas?

Select fname+' '+lname as full_name,SubQuery.Wins, Query.losses from
tbl_Participants a inner
join
(Select Part_ID,count(*) as Wins from tbl_Weekly_Picks where pick_winner
= actual_winner and actual_winner <> 0 group by part_id)
SubQuery
ON
a.Part_ID=SubQUery.Part_ID
inner
join
(Select Part_ID,count(*) as losses from tbl_Weekly_Picks where
pick_winner
<> actual_winner and actual_winner <> 0 group by part_id)
Query
ON
a.Part_ID=Query.Part_ID
order by wins desc

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!I think you might want to look at Outer joins rather than Inner

________________________________________
Atrax. MVP, IIS
http://rtfm.atrax.co.uk/

newsflash : Atrax.Richedit 1.0 now released.
http://rtfm.atrax.co.uk/infinitemon...Atrax.RichEdit/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||SELECT MIN(fname+' '+lname) AS full_name,
COUNT(CASE WHEN pick_winner = actual_winner THEN 1 END) AS wins,
COUNT(CASE WHEN pick_winner <> actual_winner THEN 1 END) AS losses
FROM tbl_Weekly_Picks AS W
JOIN tbl_Participants AS P
ON W.part_id = P.part_id
GROUP BY part_id

(untested)

--
David Portas
----
Please reply only to the newsgroup
--

Query goes from 1 second to 40 minutes with only 1 more record?

I have a very strange problem where I have a simple select count(*) with a handle of of joined tables that works fine and returns a count of records in under 1 second when the criteria in the where clause limits the count to 36984. But if I change the criteria to where the number of records would be 1 higher, 36985, the query takes ~40 minutes!

Here's the query

SELECT count(*)
FROM AT JOIN Al ON Al.AlID= AT.ALID
JOIN DProfile ON Al.DProfileID= DProfile.DProfileID
JOIN Label ON DProfile.LabelID = Label.LabelID
JOIN PricingTier ON PricingTier.LabelID = DProfile.LabelID
JOIN PricingTarget on PricingTarget.PricingTargetID = PricingTier.PricingTargetID
JOIN ATP ON ATP.PricingTierID = PricingTier.PricingTierID
WHERE PricingTarget.Target = 'AT'
AND PricingTier.MaxAgeInDays = 0
AND ATP.LengthMultiple = 0
AND AT.ATID > 408095
AND AT.ATID < 451199

Notice the part of the where clause in red. This is how I'm changing the number of rows that select count(*) should find.

Here's the output from showplan

|--Compute Scalar(DEFINE:([Expr1021]=CONVERT_IMPLICIT(int,[Expr1028],0)))
|--Stream Aggregate(DEFINE:([Expr1028]=Count(*)))
|--Hash Match(Inner Join, HASH:([MGN2].[dbo].[AL].[ALID])=([MGN2].[dbo].[AT].[ALID]))
|--Hash Match(Inner Join, HASH:([MGN2].[dbo].[DProfile].[DProfileID])=([MGN2].[dbo].[AL].[DProfileID]), RESIDUAL:([MGN2].[dbo].[DProfile].[DProfileID]=[MGN2].[dbo].[AL].[DProfileID]))
| |--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[PricingTier].[LabelID]=[MGN2].[dbo].[DProfile].[LabelID]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([MGN2].[dbo].[PricingTier].[PricingTargetID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([MGN2].[dbo].[ATP].[PricingTierID]))
| | | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[ATP].[PK_ATPID]), WHERE:([MGN2].[dbo].[ATP].[LengthMultiple]=(0)))
| | | | |--Clustered Index Seek(OBJECT:([MGN2].[dbo].[PricingTier].[PK_PricingTierID]), SEEK:([MGN2].[dbo].[PricingTier].[PricingTierID]=[MGN2].[dbo].[ATP].[PricingTierID]), WHERE:([MGN2].[dbo].[PricingTier].[MaxAgeInDays]=(0)) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([MGN2].[dbo].[PricingTarget].[PK_PricingTargetID]), SEEK:([MGN2].[dbo].[PricingTarget].[PricingTargetID]=[MGN2].[dbo].[PricingTier].[PricingTargetID]), WHERE:([MGN2].[dbo].[PricingTarget].[Target]='AT') ORDERED FORWARD)
| | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[DProfile].[PK_D_Profiles]))
| |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[AL].[PK_AL]))
|--Clustered Index Seek(OBJECT:([MGN2].[dbo].[AT].[PK_Track]), SEEK:([MGN2].[dbo].[AT].[ATID] > (408095) AND [MGN2].[dbo].[AT].[ATID] < (451199)) ORDERED FORWARD)

Now, if I change the part of the where clause from 'AND AT.ATID > 408095' to 'AND AT.ATID > 408094', increase the number returned by one, the query goes from taking 1 second to 40 minutes. Here's the showplan text that is different, when the only difference in the query is changing the number in the where clause.

|--Compute Scalar(DEFINE:([Expr1021]=CONVERT_IMPLICIT(int,[Expr1028],0)))
|--Stream Aggregate(DEFINE:([Expr1028]=Count(*)))
|--Merge Join(Inner Join, MERGE:([MGN2].[dbo].[PricingTarget].[PricingTargetID])=([MGN2].[dbo].[PricingTier].[PricingTargetID]), RESIDUAL:([MGN2].[dbo].[PricingTarget].[PricingTargetID]=[MGN2].[dbo].[PricingTier].[PricingTargetID]))
|--Clustered Index Scan(OBJECT:([MGN2].[dbo].[PricingTarget].[PK_PricingTargetID]), WHERE:([MGN2].[dbo].[PricingTarget].[Target]='AT') ORDERED FORWARD)
|--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[AL].[ALID]=[MGN2].[dbo].[AT].[ALID]))
|--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[ATPrice].[PricingTierID]=[MGN2].[dbo].[PricingTier].[PricingTierID]))
| |--Sort(ORDER BY:([MGN2].[dbo].[PricingTier].[PricingTargetID] ASC))
| | |--Hash Match(Inner Join, HASH:([MGN2].[dbo].[DProfile].[DProfileID])=([MGN2].[dbo].[AL].[DProfileID]), RESIDUAL:([MGN2].[dbo].[DProfile].[DProfileID]=[MGN2].[dbo].[AL].[DProfileID]))
| | |--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[PricingTier].[LabelID]=[MGN2].[dbo].[DProfile].[LabelID]))
| | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[PricingTier].[PK_PricingTierID]), WHERE:([MGN2].[dbo].[PricingTier].[MaxAgeInDays]=(0)) ORDERED FORWARD)
| | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[DProfile].[PK_D_Profiles]))
| | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[AL].[PK_AL]))
| |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[ATPrice].[PK_ATPriceID]), WHERE:([MGN2].[dbo].[ATPrice].[LengthMultiple]=(0)))
|--Clustered Index Seek(OBJECT:([MGN2].[dbo].[AT].[PK_Track]), SEEK:([MGN2].[dbo].[AT].[ATID] > (408094) AND [MGN2].[dbo].[AT].[ATID] < (451199)) ORDERED FORWARD)

I tried increasing the size of the TempDB from the default of 8 MB to 1000 MB for both the files, but it behaves the exact same way. I'm at a loss as to what is causing this dramatic difference for such a simple query. Anyone have an idea?

Thanks!

By the way, this is on SQL Server 2005, if that helps any.|||

Jim:

Good post. I appreciate your inclusion of the plan and the research you put into your question. It appears to me that you are at a plan "crossover" point. What I mean is that you are at the point where the mere addition of one more record to the output range causes the query optimizer to choose a different query plan. In your plan I see many "clustered index scans". A cover index might be appropriate to improve the performance of your query. Can you post the indexes, keys and unique constraints of all of your tables?

What follows below is academic. I am not sure whether I need to include this or not because it looks like you have already done this with your testing, so it looks like you already have a good handle on this. If this is of no interest, just skip the rest.

Dave

I created a mock-up table with 32767 rows of data. One of the columns is a "testDate" column with an associated index. After creating the table I performed a number of select queries with SHOWPLAN_TEXT turned on so that I could discover the filter criteria at which the query plan switched from "INDEX SEEK" to "CLUSTERED INDEX SCAN". I found that when I switch the filter date from "1/31/7" to "1/30/7" that the plan switched. Therefore I ran these two queries with SHOWPLAN _TEXT turned on to illustrate the query plan "crossover" point. I think that your query has a similar issue. My main question to you is was it your intent to find the crossover or is this something that jumped up and bit you? OUCH!

if exists
( select 0 from sysobjects
where type = 'U'
and id = object_id ('dbo.crossoverTest')
)
drop table dbo.crossoverTest
go

create table dbo.crossoverTest
( rid integer
constraint pk_crossoverTest primary key,
filler char (200),
testDate datetime
)
go

create index testDate_ndx
on dbo.crossoverTest (testDate)
go

insert into dbo.crossoverTest
select iter,
'Filler',
dateadd (mi, -17*iter, convert(datetime, '2/3/7'))
from (
select 256*b.number + a.number as iter
from master.dbo.spt_values a (nolock)
inner join master.dbo.spt_values b (nolock)
on a.[name] is null
and b.[name] is null
and b.number <= 127
and a.number <= 255
and 256*b.number + a.number > 0
) as small_iterator

go

update statistics dbo.crossoverTest
go

set showplan_text on
go

select rid,
left (filler, 10) as Filler,
testDate
from crossoverTest
where testDate >= '1/31/7'

go

-- StmtText
-- --
-- |--Compute Scalar(DEFINE:([Expr1002]=substring(Convert([crossoverTest].[filler]), 1, 10)))
-- |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[crossoverTest]) WITH PREFETCH)
-- |--Index Seek(OBJECT:([tempdb].[dbo].[crossoverTest].[testDate_ndx]), SEEK:([crossoverTest].[testDate] >= 'Jan 31 2007 12:00AM') ORDERED FORWARD)

-- Table 'crossoverTest'. Scan count 1, logical reads 813, physical reads 0, read-ahead reads 0.

go

select rid,
left (filler, 10) as Filler,
testDate
from crossoverTest
where testDate >= '1/30/7'

go

-- StmtText
--
-- |--Compute Scalar(DEFINE:([Expr1002]=substring(Convert([crossoverTest].[filler]), 1, 10)))
-- |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[crossoverTest].[pk_crossoverTest]), WHERE:([crossoverTest].[testDate]>='Jan 30 2007 12:00AM'))

-- Table 'crossoverTest'. Scan count 1, logical reads 913, physical reads 0, read-ahead reads 0.

go

set showplan_text off
go

|||

Thanks Dave,

Ultimately, a simple index was the issue. What perplexed me was just that the difference between the queries when it returned one more row was soooo dramatic. And to answer your question, I had just come upon the crossover issue through trial and error. I hadnt noticed the query plans were different until I got to the point that 1 row was the difference between success and failure.

Query From ACCCESS to MSDE

First of all...sorry for writing in english.
I'm trying to pass from access to msde.
I have a query that returns some values to populate a listview.
With access this query works in the right way, but in msde i have some
problems .
That's the query
rs.Open "select * from anagp inner join ingrp on anagp.incr = ingrp.rif
where ingrp.lotto='" & ListView2.SelectedItem.SubItems(1) & "' order by
anagp.data desc", cn, adOpenStatic, adLockOptimistic
I've checked out in the immediate window that the query works well but when
I populate the LW I have this problem
li.SubItems(2) = "" & rs("anagp.nome").Value
where li has the dimention of a listitem...
Error: cannot find anag.nome
Why'
Any suggestion will be very apprecitated
Thanx
IAKwhy dont to try adps
"PiantoTardivo" wrote:

> First of all...sorry for writing in english.
> I'm trying to pass from access to msde.
> I have a query that returns some values to populate a listview.
> With access this query works in the right way, but in msde i have some
> problems .
> That's the query
> rs.Open "select * from anagp inner join ingrp on anagp.incr = ingrp.rif
> where ingrp.lotto='" & ListView2.SelectedItem.SubItems(1) & "' order by
> anagp.data desc", cn, adOpenStatic, adLockOptimistic
> I've checked out in the immediate window that the query works well but whe
n
> I populate the LW I have this problem
> li.SubItems(2) = "" & rs("anagp.nome").Value
> where li has the dimention of a listitem...
> Error: cannot find anag.nome
> Why'
> Any suggestion will be very apprecitated
> Thanx
> IAK
>
>|||Looks like zour problem is the name of the column.
1. make sure that the column names are correct, you wrote once anagp.nome
and once anag.nome
2. instead of doing select * ... and then acessing anag(p).nome consider
someting like this:
select anagp.nome as a_nome, ...
and then you can use
rs("a_nome")
to access the column
HTH, Fred
"PiantoTardivo" wrote:

> First of all...sorry for writing in english.
> I'm trying to pass from access to msde.
> I have a query that returns some values to populate a listview.
> With access this query works in the right way, but in msde i have some
> problems .
> That's the query
> rs.Open "select * from anagp inner join ingrp on anagp.incr = ingrp.rif
> where ingrp.lotto='" & ListView2.SelectedItem.SubItems(1) & "' order by
> anagp.data desc", cn, adOpenStatic, adLockOptimistic
> I've checked out in the immediate window that the query works well but whe
n
> I populate the LW I have this problem
> li.SubItems(2) = "" & rs("anagp.nome").Value
> where li has the dimention of a listitem...
> Error: cannot find anag.nome
> Why'
> Any suggestion will be very apprecitated
> Thanx
> IAK
>
>

Query for multiple values

I was able to create a query that returns data based on a single order
number. Now I need to create a query that returns data based on
multiple order numbers. Obviously, when I run this query, it prompts me
for an order number. How do I change this so I can enter multiple order
numbers?
SELECT ORDNUMBE, CUSTNAME
FROM SOP10100
WHERE (ORDNUMBE = @.ordnumbe)
Thanks!http://www.sommarskog.se/arrays-in-sql.html
<2retread@.gmail.com> wrote in message
news:1126021459.621452.89010@.z14g2000cwz.googlegroups.com...
>I was able to create a query that returns data based on a single order
> number. Now I need to create a query that returns data based on
> multiple order numbers. Obviously, when I run this query, it prompts me
> for an order number. How do I change this so I can enter multiple order
> numbers?
> SELECT ORDNUMBE, CUSTNAME
> FROM SOP10100
> WHERE (ORDNUMBE = @.ordnumbe)
> Thanks!
>|||See if this helps:
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"2retread@.gmail.com" wrote:

> I was able to create a query that returns data based on a single order
> number. Now I need to create a query that returns data based on
> multiple order numbers. Obviously, when I run this query, it prompts me
> for an order number. How do I change this so I can enter multiple order
> numbers?
> SELECT ORDNUMBE, CUSTNAME
> FROM SOP10100
> WHERE (ORDNUMBE = @.ordnumbe)
> Thanks!
>|||CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm
FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
WHERE parm IS NOT NULL
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
In SQL Server, the VALUES() table constructor has to be faked with
(SELECT p1 UNION SELECT p2.. SELECT pn) AS Parmlist (p).|||On 6 Sep 2005 20:19:28 -0700, --CELKO-- wrote:

>CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
>INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
>BEGIN
>SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm
> FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
> WHERE parm IS NOT NULL
> AND <other conditions> )
> AND <more predicates>;
><more code>;
>END;
>In SQL Server, the VALUES() table constructor has to be faked with
>(SELECT p1 UNION SELECT p2.. SELECT pn) AS Parmlist (p).
Hi Joe,
And how exactly is your query better than
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (p1, p2, .., pN)
AND <more predicates>;
?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I usually wind up adding some CAST(), UPPER() and other procedures to
the SELECTs or in theVALUES() list.|||On 7 Sep 2005 12:49:10 -0700, --CELKO-- wrote:

>I usually wind up adding some CAST(), UPPER() and other procedures to
>the SELECTs or in theVALUES() list.
Hi Joe,
Assuming that you meant "functions", not "procedures", there's still no
reason to make it so complicated:
WHERE my_col IN (UPPER(p1), CAST(p2 AS verchar(10), ..., LOWER(pN))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Assuming that you meant "functions", not "procedures", there's still no r
eason to make it so complicated: <<
Opps! I grant that you can do a lot of edit work with CASE epxrssions,
too:
CASE WHEN @.p1 NOT BETWEEN 1 AND 10 THEN NULL ELSE @.p1 END
But it is hard to do things that involve another table, such as
translating codes in the parameters to the target table format::
(SELECT shoesize_eur
FROM ShoeSizes.
WHERE UPPER (shoesize_usa) = @.p1)
or multiple-parameter look-ups:
(SELECT address_nbr
FROM StandardAddresseNumbers AS A
WHERE CAST (x_ord AS INTEGER) = @.p1
AND CAST (y_ord AS INTEGER) = @.p2)|||On 8 Sep 2005 07:57:01 -0700, --CELKO-- wrote:

>Opps! I grant that you can do a lot of edit work with CASE epxrssions,
>too:
>CASE WHEN @.p1 NOT BETWEEN 1 AND 10 THEN NULL ELSE @.p1 END
>But it is hard to do things that involve another table, such as
>translating codes in the parameters to the target table format::
>(SELECT shoesize_eur
> FROM ShoeSizes.
>WHERE UPPER (shoesize_usa) = @.p1)
>or multiple-parameter look-ups:
>(SELECT address_nbr
> FROM StandardAddresseNumbers AS A
> WHERE CAST (x_ord AS INTEGER) = @.p1
> AND CAST (y_ord AS INTEGER) = @.p2)
Hi Joe,
Assuming that we're still discussing what can and can't be placed in an
IN (expression, expression, ...) string, I still fail to see how CASE
expressions and subqueries like the examples you posted would introduce
the need to move from IN with a list of expressions to IN with a
subquery, like the one you posted at the start of this thread.
I don't have the full SQL-92 standard at my disposal, but Books Online
says this:
Syntax
test_expression [ NOT ] IN
(
subquery
| expression [ ,...n ]
)
Arguments
(snip)
expression [,...n]
Is a list of expressions to test for a match. All expressions must be of
the same type as test_expression.
There are no further requirements for the expressions in the list of
expressions. So you should bne able to just slam the subqueries in
there.
Of course, the end result would be quite complicated - but adding an
extra layer of complexity by using a subquery with SELECT .. FROM VALUES
(...) would make it even more complicated, not less so!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query for multiple tables

Hi,

I have a query that returns values from three seperate data tables into a dataset:

SELECT [MediaFileData].[FileIdentifier], [AudioPCData].[RecorderID],
[AudioPCData].[Channel], [MediaFileData].[SubmittingUser], [MediaFileData].[DateTimeAsString],
[MetaData].[FieldText]

FROM [MediaFileData] INNER JOIN [AudioPCData] ON MediaFileData.FileIdentifier = AudioPCData.FileIdentifier
INNER JOIN [MetaData] ON MediaFileData.FileIdentifier = MetaData.FileIdentifier
INNER JOIN [SSRData] ON MediaFileData.FileIdentifier = SSRData.FileIdentifier

WHERE ([MediaFileData].[SubmittingUser] = '{0}') AND ([AudioPCData].[RecorderID] = '{0}')
AND ([MediaFileData].[MediaDescription] = '{0}') AND ([SSRData].[ModelUsed] = '{0}')

Each table has only got one 'FileIdentifier' apart from MetaData. This table has three columns 'FileIdentifer', 'FiledName' and 'FieldText'. One file can have more than one field and therefore It will have the same 'FileIdentifier' e.g.

FileIdentifier FieldName FieldText

1 Field 1 Hello

1 Field 2 Goodbye

The first problem is I only want to display the first and second field 'FiledText' in my results grid but still load all the other fields into my dataset.

The second problem is that it creates a new row for every field, whereas I want the fields with the same 'FileIdentifier' to be in the same row!

At the moment mt results gridlooks like this:

FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2

1 MyPC 1 Me 03/05/07 14:24 Hello Hello

1 MyPC 1 Me 03/05/07 14:24 GoodBye Goodbye

I need it to look like this:

FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2

1 MyPC 1 Me 03/05/07 14:24 Hello GoodBye

Thanks,

Guy

Depends on which version of SQL Server you are using. Using SQL 2k5 you should take a look on Cross apply.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Guy,

Could you be more clear as to what your goal is, and what the purpose of these fields might be, their scope, the number of overlapping entries, and generally how you view this thing to be structured? On first blush, this seems like a situation where a relational database setup could save you some storage and access time in the long run if you can identify the shared fields and refactor your tables. The problem with setting Field1 and Field2 appropriately is also interesting in that you want Field1 to have "hello" in it and field2 to have "goodbye" in it whereas other than that string nothing differs between the records. This behaviour seems highly specific to your problem, and so knowing more about what your goals are for this database might help us to identify how best to solve your immediate problems in such a manner as to have long-term benefits.

Some natural things to weigh might be:

How do these components logically relate, and what are valid values for things in the database? (e.g. identical entries with field1 and field2 values, should this happen?, should field1 and field2 be mirrored?, etc.).

Does this solution need to last, or is it a one-time only operation?

How important is it to retain the database structure?

Is there a rule to how you want this join to work in the long run that requires domain specific knowledge?

How important is the performance of your database in the long run?

How often do you intend to perform this operation, and how much development work is "worth it"?

I think that as we understand the constraints of your task and what you intend to accomplish, we can find a solution that best fits your needs.

Thanks,

John (MSFT)