Nested or Subquery help

i am new to SQL Server DB.
i have a typical query problem.
master table contains Data in following format
id stype sid svalue
1 status 1 approved
2 status 2 pending
3 request 1 draft
4 request 2 waiting
etc

in my query i need to substitute the svalue in place of sid
e.g.
select requestid ,bstatus, brequest from request ;
should give
----------
R1122 approved draft
----------
instead of
R1122 1 1
*****************
link field is column name (quite unusual )
like for status column , it is bstatus column name and it is stored in master table as status .

i need to do the following in 1 query only.
kindly suggest some nested query to do the same
kindly help
Smitaselect requestid
, s.svalue as statusvalue
, r.svalue as requestvalue
from request
inner
join master as s
on bstatus = s.sid
and s.stype = 'status'
inner
join master as r
on bstatus = r.sid
and r.stype = 'request'

Nested Loops in the Control Flow

I have a problem when using nested loops in my Control Flow. The package contains an outer Foreach Loop using the Foreach File Enumerator which in my test case will loop over two files found in a directory. Inside this loop is another Foreach Loop using the Foreach Nodelist Enumerator. Before entering the inner loop a variable, xpath, is set to a value that depends on the current file, i e /file[name = '@.CurrentFileName']/content. The Nodelist Enumerator is set to use this variable as its OuterXPATHString. Now, this is what happens:

First Iteration:
The first file is found and the value of xpath = /file[name = 'test1.txt']/content. When the inner loop is entered it iterates over the content elements under the file with name test1.txt as expected.

Second Iteration:
The second file is found and the value of xpath = /file[name = 'test2.txt']/content. When the inner loop is entered it unexpectedly still iterates over the content elements under the file with name test1.txt.

My question is: Should it not be possible to change the loop condition of an inner loop in an outer loop such that the next time it is entered it will be done based on the new condition? It seems that the xpath variable is read once, the first time, and never again. If that is the case, does anyone know of a workaround?

Regards,
Lars R?nnb?ck

I noticed that you can set which enumerator to use on the Foreach Loop container using an Expression. On the off chance that this will cause the enumerator to reload at the start of the container and thereby solving my problem described above I thought I'd try it, but I cannot find what value Foreach Enumerator should be set to. It won't accept the string Foreach NodeList Enumerator as a string with or without quotation marks, neither the number 6 works, which seems to be the index of the enumerator in the drop down list. If anyone knows how to set this, let me know and I will try it.

Edit: After reading Kirks blog on Expressions: Part III it seems that expressions applied to Foreach Enumerators are evaluated Before Saving, After Loading, Before Initialization and Before returning from GetEnumerator calls. I am guessing that GetEnumerator is called only once, which causes the behaviour above. I desperately need a workaround then.

Regards,
Lars R?nnb?ck

|||

On what property of the inner loop nodelist enumerator is the property expression set?

You can't set the enumerator to use with an expression. Expressions don't understand objects or IDispatch, so they cannot provide an enumerator to the foreach loop.

It sounds to me like you may have the property expression on the wrong property of the Nodelist Enumerator.

Can you post the package on

http://lab.msdn.microsoft.com/productfeedback/default.aspx

We can take a look. I also have a sneaking suspicion that you've found a bug.

K

|||

I'll try to give you more detailed information. The settings for the inner Foreach Loop are (in three different variations that all produce the same result):

Variation 1:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which changes for each iteration of the outer loop. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to /file/content.
Nothing is set in the other sections.
This iterates over the first FileDefinition for both iterations of the outer loop, even though it is clearly different the second time the inner loop is reached.

Variation 2:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is Variable and OuterXPathSource is set to User::xpath. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Variation 3:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and an expression is set here for the OuterXPathString to be User::xpath.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to an empty string. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Under the Expression settings for the Foreach Container there is a Property named ForeachEnumerator, which is the one I was referring to above, but I could not find a valid value for it. I have no idea if that would have helped in any way though.

For different reasons I cannot put the package on the feedback pages. I could provide it to you in confidence though. I can be reached through lars(at)delicate.se.

Thanks for the reply,
Lars

|||

Kirk,

I managed to reproduce the problem with three new small packages, one for each variation described above. They are filed as a bug at http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK43839. I'm still hoping that it's me who has done something wrong though, since this is preventing me from finishing a step in our current project. Workarounds are welcome too ;)

Regards,
Lars

|||

Thanks,

we were able to repro the problem.

The workaround will be to use to move the inner ForEach Loop to a child package

|||

Thanks Nick,

I've done that and it works as intended now. One pitfall to avoid though that took me a while to figure out, if you have package level event handlers in the parent package, they are still active when the tasks in the child package is running.

Regards,
Lars

|||

Lars,

That's because child packages are just an extension of the parent's container hierarchy. All events "bubble-up" to the top of the container hierarchy unless System::Propogate=FALSE.

-Jamie

Nested Loops in the Control Flow

I have a problem when using nested loops in my Control Flow. The package contains an outer Foreach Loop using the Foreach File Enumerator which in my test case will loop over two files found in a directory. Inside this loop is another Foreach Loop using the Foreach Nodelist Enumerator. Before entering the inner loop a variable, xpath, is set to a value that depends on the current file, i e /file[name = '@.CurrentFileName']/content. The Nodelist Enumerator is set to use this variable as its OuterXPATHString. Now, this is what happens:

First Iteration:
The first file is found and the value of xpath = /file[name = 'test1.txt']/content. When the inner loop is entered it iterates over the content elements under the file with name test1.txt as expected.

Second Iteration:
The second file is found and the value of xpath = /file[name = 'test2.txt']/content. When the inner loop is entered it unexpectedly still iterates over the content elements under the file with name test1.txt.

My question is: Should it not be possible to change the loop condition of an inner loop in an outer loop such that the next time it is entered it will be done based on the new condition? It seems that the xpath variable is read once, the first time, and never again. If that is the case, does anyone know of a workaround?

Regards,
Lars R?nnb?ck

I noticed that you can set which enumerator to use on the Foreach Loop container using an Expression. On the off chance that this will cause the enumerator to reload at the start of the container and thereby solving my problem described above I thought I'd try it, but I cannot find what value Foreach Enumerator should be set to. It won't accept the string Foreach NodeList Enumerator as a string with or without quotation marks, neither the number 6 works, which seems to be the index of the enumerator in the drop down list. If anyone knows how to set this, let me know and I will try it.

Edit: After reading Kirks blog on Expressions: Part III it seems that expressions applied to Foreach Enumerators are evaluated Before Saving, After Loading, Before Initialization and Before returning from GetEnumerator calls. I am guessing that GetEnumerator is called only once, which causes the behaviour above. I desperately need a workaround then.

Regards,
Lars R?nnb?ck

|||

On what property of the inner loop nodelist enumerator is the property expression set?

You can't set the enumerator to use with an expression. Expressions don't understand objects or IDispatch, so they cannot provide an enumerator to the foreach loop.

It sounds to me like you may have the property expression on the wrong property of the Nodelist Enumerator.

Can you post the package on

http://lab.msdn.microsoft.com/productfeedback/default.aspx

We can take a look. I also have a sneaking suspicion that you've found a bug.

K

|||

I'll try to give you more detailed information. The settings for the inner Foreach Loop are (in three different variations that all produce the same result):

Variation 1:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which changes for each iteration of the outer loop. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to /file/content.
Nothing is set in the other sections.
This iterates over the first FileDefinition for both iterations of the outer loop, even though it is clearly different the second time the inner loop is reached.

Variation 2:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is Variable and OuterXPathSource is set to User::xpath. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Variation 3:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and an expression is set here for the OuterXPathString to be User::xpath.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to an empty string. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Under the Expression settings for the Foreach Container there is a Property named ForeachEnumerator, which is the one I was referring to above, but I could not find a valid value for it. I have no idea if that would have helped in any way though.

For different reasons I cannot put the package on the feedback pages. I could provide it to you in confidence though. I can be reached through lars(at)delicate.se.

Thanks for the reply,
Lars

|||

Kirk,

I managed to reproduce the problem with three new small packages, one for each variation described above. They are filed as a bug at http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK43839. I'm still hoping that it's me who has done something wrong though, since this is preventing me from finishing a step in our current project. Workarounds are welcome too ;)

Regards,
Lars

|||

Thanks,

we were able to repro the problem.

The workaround will be to use to move the inner ForEach Loop to a child package

|||

Thanks Nick,

I've done that and it works as intended now. One pitfall to avoid though that took me a while to figure out, if you have package level event handlers in the parent package, they are still active when the tasks in the child package is running.

Regards,
Lars

|||

Lars,

That's because child packages are just an extension of the parent's container hierarchy. All events "bubble-up" to the top of the container hierarchy unless System::Propogate=FALSE.

-Jamie

negative output from neural networks

I am getting negative predictions (continuous) from a neural network model that has been trained on data that only contains positive values or zeros (no nulls).

Is there a setting that can limit the lower end of the output range to zero?

Hello

There is no algorithm setting that can limit the outputs range. However, you could use a different query construct to get this result. Assuming that your predictable column is labeled [Petal Length], your query could look like:

SELECT VBA FROM [Model] PREDICTION JOIN ...

The [IIF] function is part of a set of built-in custom functions and it is defined as

IIF( boolean_condition, true_value, false_value)

Hope this helps

Needs to put quotes around numeric data in export text file

Hi
- I'm trying to export the data from crystal report to a disk file in a CS/ tab seperated format.
-The export file contains quotes around text fileds
- Is there any way to get quotes around numerical data?
Thanks in Advance :)
Bashayou need to have that in report itself
Create formula and write code
""""+Cstr({Field})+""""

Needing 1st Non Null Value

I have a table that contains about 20 columns. However, each row may only have values in one or two columns while the rest are null. Basically, a new row is added each time a parameter is updated thus having a value for one column but not the others.

Here's an example of the data.

PatientID BloodPressure Pulse Temperature Location UpdateTime
1 120/80 NULL NULL NULL 3/8/07 11:33:00
1 NULL NULL 98.5 NULL 3/8/07 11:45:30
1 NULL 82 NULL NULL 3/9/07 6:20:15
1 NULL NULL NULL RM101 3/9/07 6:24:18
1 125/85 NULL NULL NULL 3/9/07 8:15:00
2 115/75 NULL NULL NULL 3/8/07 12:32:02

2 NULL NULL 99.5 NULL 3/8/07 12:45:30

2 NULL 88 NULL NULL 3/9/07 7:20:19

2 NULL NULL NULL RM108 3/9/07 6:24:18

2 119/85 NULL NULL NULL 3/9/07 8:16:00

I need an efficient query that will return the most recent non-null value for each patientID based on the UpdateTime. Efficiency is of utmost importance because there will be hundreds of patients with potentially thousands of records.

The result set should be:
PatientID BloodPressure Pulse Temperature Location
1 125/85 82 98.5 RM101
2 119/85 88 99.5 RM108

Any help will be greatly appreciated.

If you are using SQL Server 2005, this should work although I'm not sure how well it will perform

with cte as
(select PatientID,
BloodPressure,
row_number() over(partition by PatientID
order by case when BloodPressure is null then null else UpdateTime end desc) as BloodPressureRN,
Pulse,
row_number() over(partition by PatientID
order by case when Pulse is null then null else UpdateTime end desc) as PulseRN,
Temperature,
row_number() over(partition by PatientID
order by case when Temperature is null then null else UpdateTime end desc) as TemperatureRN,
Location,
row_number() over(partition by PatientID
order by case when Location is null then null else UpdateTime end desc) as LocationRN,
UpdateTime
from mytable)
select PatientID,
max(case when BloodPressureRN=1 then BloodPressure end) as BloodPressure,
max(case when PulseRN=1 then Pulse end) as Pulse,
max(case when TemperatureRN=1 then Temperature end) as Temperature,
max(case when LocationRN=1 then Location end) as Location
from cte
group by PatientID

|||

An alternative that works on SQL Server 2000 is:

declare @.basicStats table
( patientId integer not null,
bloodPressure varchar(8),
pulse smallint,
temperature numeric (4,1),
location varchar(12),
UpdateTime datetime
)

insert into @.basicStats values (1, '120/80', NULL, NULL, NULL, '3/8/07 11:33:00')
insert into @.basicStats values (1, NULL, NULL, 98.5, NULL, '3/8/07 11:45:30')
insert into @.basicStats values (1, NULL, 82, NULL, NULL, '3/9/07 6:20:15')
insert into @.basicStats values (1, NULL, NULL, NULL, 'RM101', '3/9/07 6:24:18')
insert into @.basicStats values (1, '125/85', NULL, NULL, NULL, '3/9/07 8:15:00')
insert into @.basicStats values (2, '115/75', NULL, NULL, NULL, '3/8/07 12:32:02')
insert into @.basicStats values (2, NULL, NULL, 99.5, NULL, '3/8/07 12:45:30')
insert into @.basicStats values (2, NULL, 88, NULL, NULL, '3/9/07 7:20:19')
insert into @.basicStats values (2, NULL, NULL, NULL, 'RM108', '3/9/07 6:24:18')
insert into @.basicStats values (2, '119/85', NULL, NULL, NULL, '3/9/07 8:16:00')

--select * from @.basicStats

select patientId,
( select top 1 BloodPressure from @.basicStats b
where b.patientId = a.patientId
and b.BloodPressure is not null
order by b.updateTime desc
) as BloodPressure,
( select top 1 pulse from @.basicStats c
where c.patientId = a.patientId
and c.pulse is not null
order by c.updateTime desc
) as pulse,
( select top 1 temperature from @.basicStats d
where d.patientId = a.patientId
and d.temperature is not null
order by d.updateTime desc
) as temperature,
( select top 1 location from @.basicStats e
where e.patientId = a.patientId
and e.location is not null
order by e.updateTime desc
) as location
from @.basicStats a
group by patientId

-- patientId BloodPressure pulse temperature location
-- -- - --
-- 1 125/85 82 98.5 RM101
-- 2 119/85 88 99.5 RM108

|||

And a third solution (sorry Kent, I pinched your table declaration and inserts for this one)...

This solution appears to be more expensive than Kent's solution, according to the execution plan on my machine. You'll probably experience the same, or you might not - it's just another option for you to try out on your hardware.

Chris

declare @.basicStats table

(

patientId integer not null,

bloodPressure varchar(8),

pulse smallint,

temperature numeric (4,1),

location varchar(12),

UpdateTime datetime

)

insert into @.basicStats values (1, '120/80', NULL, NULL, NULL, '3/8/07 11:33:00')

insert into @.basicStats values (1, NULL, NULL, 98.5, NULL, '3/8/07 11:45:30')

insert into @.basicStats values (1, NULL, 82, NULL, NULL, '3/9/07 6:20:15')

insert into @.basicStats values (1, NULL, NULL, NULL, 'RM101', '3/9/07 6:24:18')

insert into @.basicStats values (1, '125/85', NULL, NULL, NULL, '3/9/07 8:15:00')

insert into @.basicStats values (2, '115/75', NULL, NULL, NULL, '3/8/07 12:32:02')

insert into @.basicStats values (2, NULL, NULL, 99.5, NULL, '3/8/07 12:45:30')

insert into @.basicStats values (2, NULL, 88, NULL, NULL, '3/9/07 7:20:19')

insert into @.basicStats values (2, NULL, NULL, NULL, 'RM108', '3/9/07 6:24:18')

insert into @.basicStats values (2, '119/85', NULL, NULL, NULL, '3/9/07 8:16:00')

SELECT DISTINCT

p.PatientID,

pbp.BloodPressure,

ppu.Pulse,

pt.Temperature,

pl.Location

FROM @.basicStats p

INNER JOIN (SELECT PatientID, MAX(UpdateTime) AS UpdateTime FROM @.basicStats WHERE BloodPressure IS NOT NULL GROUP BY PatientID) bp ON bp.PatientID = p.PatientID

INNER JOIN @.basicStats pbp ON pbp.PatientID = bp.PatientID AND pbp.UpdateTime = bp.UpdateTime

INNER JOIN (SELECT PatientID, MAX(UpdateTime) AS UpdateTime FROM @.basicStats WHERE Pulse IS NOT NULL GROUP BY PatientID) pu ON pu.PatientID = p.PatientID

INNER JOIN @.basicStats ppu ON ppu.PatientID = bp.PatientID AND ppu.UpdateTime = pu.UpdateTime

INNER JOIN (SELECT PatientID, MAX(UpdateTime) AS UpdateTime FROM @.basicStats WHERE Temperature IS NOT NULL GROUP BY PatientID) t ON t.PatientID = p.PatientID

INNER JOIN @.basicStats pt ON pt.PatientID = bp.PatientID AND pt.UpdateTime = t.UpdateTime

INNER JOIN (SELECT PatientID, MAX(UpdateTime) AS UpdateTime FROM @.basicStats WHERE Location IS NOT NULL GROUP BY PatientID) l ON l.PatientID = p.PatientID

INNER JOIN @.basicStats pl ON pl.PatientID = bp.PatientID AND pl.UpdateTime = l.UpdateTime

|||

Gary:

Please forgive me, but this is a kind of query that I have struggled with. Since you are interested in optimum performance I think it woul be best if you could get Umachandar or someone similar to comment. I am just too afraid that I will get this wrong.

Kent

|||

Mark's solution will work fine except that you will end up sorting the entire table 4 times (once each to compute row number for bloodpressure, pulse, temperature and location). If you have a large table this will be problematic. The other solutions also suffer from the same problem. SQL Server unfortunately doesn't have a concept of filtered indexes which will help in this case. Database systems like PostgreSQL can do better job because you can create filtered index (on non-null value of each attribute you are interested). Similarly, in Oracle you can make use of index compression to reduce I/O costs due to repeated values (NULL) or use function-based indexes. DB2 also has some features that you can take advantage of for this type of schema.

In SQL Server you can use indexed views to achieve the filtered indexes functionality. So define one indexed view per property like:

create view basicStats_bloodPressure
with schemabinding
as
select patientId, bloodPressure, UpdateTime
from dbo.basicStats
where bloodPressure is not null
go
create unique clustered index idx_basicStats_bloodPressure on basicStats_bloodPressure(patientId, UpdateTime desc)
go

create view basicStats_pulse
with schemabinding
as
select patientId, pulse, UpdateTime
from dbo.basicStats
where pulse is not null
go
create unique clustered index idx_basicStats_pulse on basicStats_pulse(patientId, UpdateTime desc)
go

create view basicStats_temperature
with schemabinding
as
select patientId, temperature, UpdateTime
from dbo.basicStats
where temperature is not null
go
create unique clustered index idx_basicStats_temperature on basicStats_temperature(patientId, UpdateTime desc)
go

create view basicStats_location
with schemabinding
as
select patientId, location, UpdateTime
from dbo.basicStats
where location is not null
go
create unique clustered index idx_basicStats_location on basicStats_location(patientId, UpdateTime desc)
go

And create an index on the table too like:

create clustered index idx_basicstats_patientid on basicStats( patientId )

Now, your query is as simple as what Kent had and SQL Server will automatically use the indexed views.

select patientId,
( select top 1 BloodPressure from basicStats b
where b.patientId = a.patientId
and b.BloodPressure is not null
order by b.updateTime desc
) as BloodPressure,
( select top 1 pulse from basicStats c
where c.patientId = a.patientId
and c.pulse is not null
order by c.updateTime desc
) as pulse,
( select top 1 temperature from basicStats d
where d.patientId = a.patientId
and d.temperature is not null
order by d.updateTime desc
) as temperature,
( select top 1 location from basicStats e
where e.patientId = a.patientId
and e.location is not null
order by e.updateTime desc
) as location
from basicStats a
group by patientId;

-- or below referencing indexed views directly

select patientId
, (select top 1 t1.bloodPressure from basicStats_bloodPressure as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as bloodPressure
, (select top 1 t1.pulse from basicStats_pulse as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as pulse
, (select top 1 t1.temperature from basicStats_temperature as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as temperature
, (select top 1 t1.location from basicStats_location as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as location
from (
select distinct patientId
from basicStats
) as b

These indexed views will need to be maintained during DML operations. But this is probably the best way to get the performance for this query given the nature of your data. You do incur some additional storage costs.

Alternatively, you should simply partition the table by property and use my last query except each indexed view is a physical table and the main table only contains the patientId columns & others that are not partitioned:

select patientId
, (select top 1 t1.bloodPressure from basicStats_bloodPressure as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as bloodPressure
, (select top 1 t1.pulse from basicStats_pulse as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as pulse
, (select top 1 t1.temperature from basicStats_temperature as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as temperature
, (select top 1 t1.location from basicStats_location as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as location
from (
select distinct patientId
from basicStats
) as b

With this approach, you will save some storage also. DML operations are slightly complex and you need to do additional work instead of SQL Server with the indexed views approach. For example, if you insert any row then you need to first check if there is a row in the main table for that patientId first and then insert into the separate property table. This might work out better than the indexed views approach also. So test both and see. Indexed views approach however is straight forward and doesn't require much changes to your existing code.

|||( I knew it would be worth it; thanks, Umachandar. WOA! )|||Wow thanks guys for the advice. I'll try some of these and give some feedback.

Thanks again!

Gary B.