I'd like to have a chart with a category "group on" expression like this:
=Fields![Parameters!MyParameter.Value].Value
The above doesn't work. Is it because it can't be done, or because I don't have the right syntax?
Never mind. Found it:
=Fields.Item(Parameters!MyParameter.Value).Value
I need to evalute two different fields in some text boxes.
=IIF(Fields!BadAddressFlg.Value > Nothing,"Red",IIF(Fields!BadPhoneFlg.Value <> "G" OR Fields!BadPhoneFlg.Value <> Nothing, "Red","Black"))
If the valuefor bad address > nothing or Badphoneflg <> G or <>Nothing then the color needs to change to red, any of these can be true not all of them. I tried the switch, choose, all of them and if I take one of the fields out then I get it to work but I cant get them to work if they are both like this.
Thanks
Stokh
Three ORs mean that you don′t have to differ between the cases, right ?
=IIF(Fields!BadAddressFlg.Value > Nothing OR Fields!BadPhoneFlg.Value <> "G" OR Fields!BadPhoneFlg.Value <> Nothing, "Red","Black")
Did you already tried that ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
Well you would think so but not in here it doesnt apparently that doesnt work either, it turns them all red
There is only one record out of the 13 returned in the report that meets both badaddress having data in it and badphone have N instead of G so that record should be red. 3 other records only have the N in badPhone so those should be red too, the rest should be all black. Instead it turns them all red!
I thought using Nested Iffs was supposed to evaluate each one seperate and that isnt the case I tried a switch statement too and that doesnt work either.
Stokh
|||I am not quite sure about the > Nothing, did you try the <> Nothing ? As Nothing is the equivalent for NULL I would be careful with direct comparing with it like a numeric value.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
Yeah I tried that to. Thats how the data comes in from the main frame which is not the way it should be but
I tried <> Nothing, Is Nothing, Trim(Fields!BadAddressFlg) you name I tried it. Nothing is working.
Stokh
|||Here is the latest one, now if I take out this part "IIF(Trim(Fields!BadPhoneFlg.Value) <> "G" Or Trim(Fields!BadPhoneFlg.Value) <> Nothing,"Red" " I get the correct ones
=IIF(Trim(Fields!BadAddressFlg.Value) > " ","Red",IIF(Trim(Fields!BadPhoneFlg.Value) <> "G" Or Trim(Fields!BadPhoneFlg.Value) <> Nothing,"Red","Black"))
|||Strange thing. Do you have the chance to send over the sample data (as it is not very much, only those 13). You can create the appropiate data insert statements with the procedure here:http://vyaskn.tripod.com/code.htm#inserts
As soon as you will post them I will create a sample project with it, we will get this thing to work :-)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
Funny, there is only 13 records that are returned for this part there are 1000's of records in the table itself. and cant data privacy doesnt allow me to but I can give you the
appl_cd char 1
debtor_nbr varchar 10
debtor_nm varchar 50
debtor_xref_nm varchar 50
debtor_for nm carchar 50
address_line1 varchar 50
address_line2 varchar 50
city varchar 30
state_cd char 2
zip_cd char 5
zip_plus4_cd char 4
phone_nbr char 10
bad_address_flg char 4
bad_phone_flg char 1
last_update_dt datetime 8
Maybe that will help you not sure though.
Thanks!
|||I got the answer
=iif(trim(Fields!BadAddress.Value) <> "" or (trim(Fields!BadPhone.Value) <> "G" and trim(Fields!BadPhone.Value) <> ""),"Red","Black")
apparently it come off the mainframe with not knowing if they added spaces or something into the fields. Not sure why they do it that way but nonetheless got it done.
Thanks for the help!
Stokh
I already know how you create a stored procedure to add information to a database or retrieve a value for one record. But I don't know how to create a stored procedure that will retrieve many records for a certain querystring value.
Here's my simple stored procedure to show one record:
CREATE PROCEDURE DisplayCity
(
@.CityID int
)
AS
SELECT City From City where CityID = @.CityID
GO
My code for displaying the City:
Sub ShowCity()
Dim strConnect As String
Dim objConnect As SqlConnection
Dim objCommand As New SqlCommand
Dim strCityID As String
Dim City As String
'Get connection string from Web.Config
strConnect = ConfigurationSettings.AppSettings("ConnectionString")
objConnect = New SqlConnection(strConnect)
objConnect.Open()
'Get incoming City ID
strCityID = request.params("CityID")
objCommand.Connection = objConnect
objCommand.CommandType = CommandType.StoredProcedure
objCommand.CommandText = "DisplayCity"
objCommand.Parameters.Add("@.CityID", CInt(strCityID))
'Display SubCategory
City = "" & objcommand.ExecuteScalar().ToString()
lblCity.Text = City
lblChosenCity.Text = City
objConnect.Close()
End Sub
Here's the code I'd like to get help with changing into a stored procedure:
Sub BindDataList()
Dim strConnect As String
Dim objConnect As New System.Data.SqlClient.SQLConnection
Dim objCommand As New System.Data.SqlClient.SQLCommand
Dim strSQL As String
Dim dtaAdvertiser As New System.Data.SqlClient.SQLDataAdapter()
Dim dtsAdvertiser As New DataSet()
Dim strCatID As String
Dim strCityID As String
Dim SubCategory As String
Dim SubCategoryID As String
Dim BusinessName As String
Dim City As String
'Get connection string from Web.Config
strConnect = ConfigurationSettings.AppSettings("ConnectionString")
objConnect = New System.Data.SqlClient.SQLConnection(strConnect)
objConnect.Open()
'Get incoming querystring values
strCatID = request.params("CatID")
strCityID = request.params("CityID")
'Start SQL statement
strSQL = "select * from Advertiser,AdvertiserSubCategory, Categories, SubCategories, County, City"
strSQL = strSQL & " where Advertiser.CategoryID=Categories.CategoryID"
strSQL = strSQL & " and Advertiser.AdvertiserID=AdvertiserSubCategory.AdvertiserID"
strSQL = strSQL & " and AdvertiserSubCategory.SubCategoryID=SubCategories.SubCategoryID"
strSQL = strSQL & " and Advertiser.CountyID=County.CountyID"
strSQL = strSQL & " and Advertiser.CityID=City.CityID"
strSQL = strSQL & " and AdvertiserSubCategory.SubCategoryID = '" & strCatID & "'"
strSQL = strSQL & " and Advertiser.CityID = '" & strCityID & "'"
strSQL = strSQL & " and Approve=1"
strSQL = strSQL & " Order By ListingType, BusinessName,City"
'Set the Command Object properties
objCommand.Connection = objConnect
objCommand.CommandType = CommandType.Text
objCommand.CommandText = strSQL
'Create a new DataAdapter object
dtaAdvertiser.SelectCommand = objCommand
'Get the data from the database and
'put it into a DataTable object named dttAdvertiser in the DataSet object
dtaAdvertiser.Fill(dtsAdvertiser, "dttAdvertiser")
'If no records were found in the category,
'display that message and don't bind the DataGrid
if dtsAdvertiser.Tables("dttAdvertiser").Rows.Count = 0 then
lblNoItemsFound.Visible = True
lblNoItemsFound.Text = "Sorry, no listings were found!"
else
'Set the DataSource property of the DataGrid
dtlAdvertiser.DataSource = dtsAdvertiser
'Set module level variable for page title display
BusinessName = dtsAdvertiser.Tables(0).Rows(0).Item("BusinessName")
SubCategory = dtsAdvertiser.Tables(0).Rows(0).Item("SubCategory")
SubCategoryID = dtsAdvertiser.Tables(0).Rows(0).Item("SubCategoryID")
City = dtsAdvertiser.Tables(0).Rows(0).Item("City")
'Bind all the controls on the page
dtlAdvertiser.DataBind()
end if
objCommand.ExecuteNonQuery()
'this is the way to close commands
objCommand.Connection.Close()
objConnect.Close()
End Sub
It's really no different. If I understand what you mean then you'd want the following stored procedure:
CREATE PROCEDURE DisplayCity
(
@.CatID Int,
@.CityID Int
)
AS
SELECT * from Advertiser,AdvertiserSubCategory, Categories, SubCategories, County, City
where Advertiser.CategoryID=Categories.CategoryID
and Advertiser.AdvertiserID=AdvertiserSubCategory.AdvertiserID
and AdvertiserSubCategory.SubCategoryID=SubCategories.SubCategoryID
and Advertiser.CountyID=County.CountyID
and Advertiser.CityID=City.CityID
and AdvertiserSubCategory.SubCategoryID = @.CatID
and Advertiser.CityID = @.CityID
and Approve=1
Order By ListingType, BusinessName,City
GO|||I actually need help with my Code for displaying the stored procedure and records. I only know how to retrieve one record using a stored procedure.|||Your original question was misleading, then :)
I'd recommend readingthese Microsoft tutorials on performing data access. There are many ways to loop through records and display data so find the one most suitable for you.|||
You would need to do a while statement. If you are using SQL Server then you can do your SQL DataReader. Just do a while loop and tell your code to keep reading until end of records and for each row add it to an array. Then you can bind thay array to a datagrid.
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.
Each user who inserts a new SQL record from the FormView control needs to have their UserID in one of the fields of the record. I have the user ID stored in the Session("UserID") variable. I am having trouble finding the right way to get this done. I have tried using a hidden text box but I can't seem to assign the value. I have tried the Insert Parameters but that will not accept <%# Session("UserID") %> as a DefaultValue. Any ideas would be helpful. Thanks.
Try handling the ItemInserting event of your FormView where you can programmatically set the SqlDataSource.DefaultValue to your Session variable value. For example: protected void FormView1_ItemInserting(object sender, FormViewInsertEventArgs e)
{
SqlDataSource1.InsertParameters["UserID"].DefaultValue = Session["UserID"].ToString();
}|||Worked great with "(" instead of brackets "[". Thanks!|||I'm glad it worked out. My example was in C#. I guess you needed VB sample instead.|||You could of also created a SessionParameter and done it without any code at all.|||
Motley wrote:
You could of also created a SessionParameter and done it without any code at all.
Brilliant!!!
