Hi:
I have written a SQL statement that accepts a letter and then prints out all the records in a table starting with that letter. I was wondering if there is a way that I could change the query so that if prints out all records if a blank or empty value is passed in?
Here's my query:
ALTER PROCEDURE [dbo].[GetMediaListByFirstLetter]
(
@firstLetter char(1)
)
AS
SELECT Media_ID, OrgName
FROM Media
WHERE UPPER(SUBSTRING(Media.OrgName,1,1)) = @firstLetter
Any help doing this would be greatly appreciated.
Roger
I am creating a report on an asp.net webpage to show how many times a coupon has been used from a set list of items.There are two tables I am using for this query.Table 1 has the list of items or coupons. Table 2 has a column for the item id from Table 1 and a value.
Example: Table 1 id Name 1 Coupon1 2 Coupon2 3 Coupon3
[code]...
My current query will only show coupon1 and coupon2 and the number and value. It will not show coupon2 since it hasn't been used. I would still like to show coupon2 with a number of zero and a value of zero.
Current query is: SELECT Table1.Name, COUNT(Table2.ID) AS CNum, SUM(Table2.Value) AS CValue FROM Table2 JOIN Table1 ON Table2.ID = Table1.ID GROUP BY Table1.Name
I have a set of SSRS reports published on the server. It can be accessed through a web application or through SharePoint. Most of the reports work fine through both - web app and SharePoint. However, one of the report, which returns large amount of data has some problem:
1. It generates fine from web app 2. It generates fine from SharePoint; only if one or two values selected from Filter dropdown 3. However, if all items (about 20 items) selected from Filter dropdown... and click on View Report, it processes for a while and then shows nothing. The page remains blank.
Did some research and felt the problem is with Distributed Cache Service.
Hi have a problem to solve and I hope that this is not a SSRS Bug.
I created a Reports(using SQL Server Project) which has several parameters which values are passed to a SP.
One of these parameter is an Integer and it is an optional value, so if the user fill it is used by the SP, otherwise the SP uses NULL and run anyway.
I starts to define tha parameter:
Datatype = integer
Allow blank value
Available: Non queried
Default: Null
if I want to Preview the report I have to provide an integer to the parameter's field ...
If for instance I set:
Default: Not queried = 0
In the moment I deploy and I use the ReportViewer in my window application the parameter's field is unabled!!
So I tried this solution:
Datatype = integer
Allow blank value
Allow null value
Available: Non queried
Default: Null
In the preview the checkbox: NULL is checked and I click on the View Report.
But when I deploy it,in the ReportViewer in my window application the parameter's field this checkbox is unchecked.
Do I forget something during my setting??I have to control it programmatically??
N.B. By default the user will not user this parameter so the best is that he can click directly on "View Report" without any additional "work" on the parameter!!
I am passing few parameters to the sql function to do some calculations. If the input parameter is null or blank, then I want to set the input parameter to the value zero
I have a visible DateTime parameter on the Report that I would like to make optional.
When I open up the report and try to run it without specifying the date I get a validation error that parameter is required. If I set default value to null - it works, but I have to check for both '' and NULL in my query:
StartDateTime >= @StartDate OR @StartDate='' OR @StartDate IS NULL
I tried setting default parameter using expression ='' but I get an error that 'Default Value' of parameter 'StartDate' doesn't have expected time.
So the question is can I set DateTime parameters' default value to blank?
I build reports using reporting services 2005 sp2. I have 3 parameters---> 3 multi value combo box . However when I tick "allow blank value" within the report parameter properties for this drop menu, report WILL NOT run without making selection.(The xml is ok allow blank value set to true) the reports still require you to enter a value for all parameters in the report when the report can and should be able to be run with no parameter defined. the trick "default values for all of the params to "=String.Empty"" is not good .
I found this article but i can't find microsoft hotfix for it .
Looks like a very simple problem but not geeting it.. I have parameter as ALL and it has corresponding value as <BLANK>,which comes by default. Allow Blank to true
How I can select this parameter as default value in parameter??
Please make sure that if I am taking '' then it is getting treated differently.I don;t want Value of ALL as '' or NULL
We imported approximately 2.9 million records from our mainframe server into our SQL Server but have run into a problem. The data in a few of the fields contains both leading and trailing spaces. An example of the data would be like this, using periods to represent spaces:
What we have:
..1A02938.....
What we need:
1A02938 (no spaces)
Is there some sort of algorithm I can run on the data to remove those spaces? The problem is coming up when trying to perform a SELECT query. We try something like:
SELECT * FROM PCPIPT0 WHERE PANO20 = "1A02938" but we get zero results because of the spaces in the database. The datatype of the filed is char(20) because we need some flexibility on the size of the data stored.
I want to be able to use a query to display all the records in the 6.5 database that have no data in the STATUS field. This is the query I thought would work....."SELECT * from travel_date WHERE status="''"
But, that is not working. Can someone please help me figure out the right way to wrtie this?
I'm in a bit of a jam here and will appreciate any help.
I need the SQL code to replace a record if the record is empty.
For instance, I have about 7 columns containing over 40K records. In the firstname field, some records are blank. I need to replace all the blank firstname fields with this: 'now invalid' (without the quotes)
I want to join the two tables to add the Code of CodeType "C" to the records of NAMES
Result Example IDNameCode 1FIRSTgfd 2SECOND----
I want to have all records from the names with the codetype C, if there is no record with the codetype c for a given ID, the cell should be blank to identify for which ID's the CodeType C is mising.
CREATE PROCEDURE [Get_WirelessProducts_By_Page] @CurrentPage int, @PageSize int, @TotalRecords int output AS --Create a temp table to hold the current page of data --Add and ID column to count the records CREATE TABLE #TempTable ( ID int IDENTITY PRIMARY KEY, ProductID int, ProductCategoryID nvarchar(50), ProductBandwidthKB int, ProductOverridePrice nvarchar(50), UseWirelessOverridePrice bit, DedicationTypeID int, DedicationTypeName nvarchar(50) ) --Fill the temp table with the Customers data INSERT INTO #TempTable ( ProductID, ProductCategoryID, ProductBandwidthKB, ProductOverridePrice, UseWirelessOverridePrice, DedicationTypeID, DedicationTypeName ) SELECT W.ProductID, W.ProductCategoryID, W.ProductBandwidthKB, W.ProductOverridePrice, W.UseWirelessOverridePrice, W.DedicationTypeID, D.DedicationTypeName FROM tblWirelessProducts W INNER JOIN tblDedicationTypes D ON W.DedicationTypeID = D.DedicationTypeID --Create variable to identify the first and last record that should be selected DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@CurrentPage - 1) * @PageSize SELECT @LastRec = (@CurrentPage * @PageSize + 1) --Select one page of data based on the record numbers above SELECT ProductID, ProductCategoryID, ProductBandwidthKB, ProductOverridePrice, UseWirelessOverridePrice, DedicationTypeID DedicationTypeName FROM #TempTable WHERE ID > @FirstRec AND ID < @LastRec --Return the total number of records available as an output parameter
SELECT @TotalRecords = COUNT(*) FROM tblWirelessProducts GO Here is the relevant VB Code: Dim parmReturnValue As SqlParameter 'conProducts is already open cmdProducts = New SqlCommand("Get_WirelessProducts_By_Page", conProducts) cmdProducts.CommandType = CommandType.StoredProcedure cmdProducts.Parameters.Add("@CurrentPage", intCurrentPage) cmdProducts.Parameters.Add("@PageSize", dgrdProducts.PageSize) parmReturnValue = cmdProducts.Parameters.Add("@TotalRecords", SqlDbType.int) parmReturnValue.Direction = ParameterDirection.Output conProducts.Open dgrdProducts.DataSource = cmdProducts.ExecuteReader()
If Not IsDBNull(cmdProducts.Parameters("@TotalRecords").Value) then Response.Write(cmdProducts.Parameters("@TotalRecords").Value) End IfThe rows are returned correctly, but the output parameter "@TotalRecords" doesn't return anything. Any ideas what I'm doing wrong? Thanks in advance for your help.
1, 2 and 3 parameter are text inputs. 4th is multi-value parameter and 5th is again a text input. I need to Pass a blank parameter to my 5th parameter.
1) I tried the below Expression in the "UploadedEnt" DataSet Properties and not in the "Main" DataSet.
I have taken the actual sql query (file) and I have just placed the select statement.
DataSet Name: UploadedEntselect distinct UploadEnt from ( SELECT DISTINCT col1, col2... ....) Ent
Order by 1OR logic has been applied in the Tablix Properties Filters as an expression.=Fields!value1.Value like Parameters!value1Param.Value Or Fields!value2.Value like Parameters!value2Param.Value Or Fields!value3.Value like Parameters!value3Param.Value Or Fields!value4.Value = Parameters!value4Param.Value(0) Or Fields!UploadEnt.Value = Parameters!UploadedEntParam.ValueFilter: Expression = TRUE2) I tried applying NULL check box which works perfectly but I do not want to apply that here.How to Pass a Blank Parameter?
Is it possible to enable parameter of type date to be blank (not null)?
I created parameter of type date, gave it no default value, when I pressed the preview tab I got error message "The property 'DefaultValue' of report parameter 'DateParamName' doesn't have the expected type" Thanks in advance!
SELECT SOURCE, TRANSDATE, LOCATION, DESCRIPTION, MOACTIVETIMESTAMP, MOINACTIVETIMESTAMP FROM CTS_Missing_Data_Report_VW WHERE LOCATION = @Location AND SOURCE = @Source AND (TRANSDATE BETWEEN @StartDate AND @EndDate) ORDER BY Transdate desc, Location
So the user can enter a value for Location and Source and select the date range.BUT I also want the user to be able to put in nothing for the Location and Source so the query would return everything for that date range.
So if they did this the query would be
SELECT SOURCE, TRANSDATE, LOCATION, DESCRIPTION, MOACTIVETIMESTAMP, MOINACTIVETIMESTAMP FROM CTS_Missing_Data_Report_VW WHERE (TRANSDATE BETWEEN @StartDate AND @EndDate) ORDER BY Transdate desc, Location
I have set the parameters @Location and @Source to "Allow blank value" in the datasets for the location and source I have :
SELECT NULL AS Source UNION SELECT DISTINCT RTRIM(LTRIM(SOURCE))AS Source FROM CTS_OPS_SOURCE_LOCATION_TBL_VW ORDER BY SOURCE
So a blank will show on the drop down and when I run the query for the Query Designer in the Dataset Properties the results does show a blank record for the first record.BUT when I Run the report there are no blanks in the drop downs for the location or source. And there is no '<blank>' selection in the drop down either. And the drop down insist the user selects a value from both of the drop downs.
I have a Multivalue parameter with 4 available values and have 4 columns in the report that correspond to each of these values.I apply column visibility to each of the 4 columns with the following expression (the number changes for each column
What I want to do is that if I select a KPI and the Column value is NULL then to hide the row.Obviously if you select multiple KPIs and only 1 of the columns has null value then I wouldn't want the Row hidden.
I am attempting to update a sql db using the update and parameter code in VB.net 2003 through MSDE for a web application. It updates changed data OK, but if the textbox value is deleted, the code does not update the sql db. I am new to this, and I'm sure it is something simple. Here is some sample code.
With cmdCategoriesUpdate .Parameters("@Field1Tag").Value = txtFld1.Text .Parameters("@Field2Tag").Value = txtFld2.Text End With cmdCategoriesUpdate.ExecuteNonQuery() SqlConnection1.Close()
I wanted to remove duplicate records from SSRS report. I set the "Hide Duplicates" to True. It is now working, But i am getting the space between the two records, which i want to get rid of. How to get rid of extra spaces between two records ( Please find the details below).
While converting Crystal reports to Reporting services I noticed that the existing crystal reports puts todays date as default value for the date parameter. Users also want it that way. I was unable to find a way to put a default value for a parameter in SSRS. I am a newbie in this so may be I missed something.
I've got two tables.. the first table carried a ProductID, and amongst other things a TradePrice
The other tbl carries a ProductID, a IndivPrice and a CustomerID
The second tbl lists prices for products for indiv Customers.
My Query needs to bring back ALL the products from the first tbl...
It also needs to show the TradePrice for that product.
I need to join my query to the second tbl...
And finally, if the second tbl has a price for that product AND the customerID is the same as one I pass into the query.. show that price also..
So here's my first query:
SELECT dbo.Products.ProductID, ProductName, ProductTradePrice, IndivPrice, dbo.Trade_PriceLists.CustomerID AS PLCustomerID FROM dbo.Products LEFT OUTER JOIN dbo.Trade_PriceLists ON dbo.Products.ProductID = dbo.Trade_PriceLists.ProductID WHERE (ProductType = 'Trade' OR ProductType = 'Both') AND (Replace(Lower(ProductBrand),' ','') = 'brandname') AND (CustomerID IS NULL OR CustomerID = 'teste' OR CustomerID = '') ORDER BY TradeOrder
I thought that would work, but what happens is that, if that particular customer has no indiv prices set.. then it only shows the ones that have no records at all in that second tbl..
So unless there is a record for a particular product in that second tbl and it doesn't have a CustomerID assigned to (which would never happen as that tbl is only every for indiv customer prices) then it doesn't show.
See? - The 2nd product should not get an indiv price as although it's in that second tbl, the customerID assigned to it is different. The 4th product should not get an indiv price as it's not in that second tbl at all.
however, with my query above I'd only get Products 1and 3... and if I did a query on a customer with no indiv prices I'd only get product 4 as it's not in the indiv at all...
I have a report that pulls data for the following date parameters:
DECLARE @STARTDATE datetime, @ENDDATE datetime SET @STARTDATE = GETDATE()-3 SET @ENDDATE = GETDATE() Select * from tablename where reportdate between @STARTDATE and @ENDDATE
This query works fine. I wanted to show the parameters chosen in a text box in the report so the user knows the date range the report was run. I tried using the following: "Discharge Date between " & Parameters!STARTDATE.Value & " and " & Parameters!ENDDATE.Value but I am getting a forward dependancy error. I have also tried setting a default value but can not get the correct code to subtract three days from Now()
my query is.. Select j.jobSubject,e.eOrganization ,jv.JobClick,j.jobID from dbo.tbl_Jobs jinner join dbo.tbl_Employer e on e.mId=j.jobCreatedByIDinner join dbo.tbl_JobView jv on jv.JobID=j.jobID order by jv.JobClick desc This query returns 1 to many records
But I need the query should return 0 to many record . .yes I have already know inner join does not handle my problem so plz suggest me which type of join would solve my problem
I have three tables Accounts, History and Dates . What I need to do is display all the accounts from History (900) records and compare them to the accounts in Accounts table pull all the matching records based on a certain date range , but If there is no record in the History table for this period I still need to display the account from Accounts and some text saying that there is no record matching for this period.
Account History 11 22 33 4NO information for this month 55 SELECT C.ACCOUNT, CASE WHEN C.ACCOUNT = LEFT(H.NUMBER,8) THEN LEFT(H.NUMBER,8) END FROM ACCTS C LEFT OUTER JOIN HISTORY H ON C.ACCOUNT = LEFT(H.NUMBER,8) INNER JOIN DATES D ON h.PERIOD = D.CUR_PERIOD GROUP BY C.ACCOUNT, H.NUMBER
This will give me all the matching records for the period but I need somehow to show all the accounts even if they don't have records for this period.
I have a table with 5 columns, let say ID,PersionID, Date, Type,Qty and source data looks like this
ID  PersonID   Date           Type      Qty Â
1     1       01/01/2011      Accept     5          2     1       01/01/2011      Accept     5  3     2       02/01/2010      Accept     10             4     2       02/01/2010      Deny       20  5     3       02/01/2012      Accept     15
[Code] .....
Output should look like this..look for only Type=Accept until deny is reached. After Deny,if there is a Accept ignore it.
ID PersonID   Date           Type        Qty 1   1       01/01/2011      Accept       5     (show only one Accept row=1 becoz Type is Accept and date is same,Qtyis same) 3   2       02/01/2010      Accept       10    (show Accept row=3,ignore deny row)
5   3       02/01/2012      Accept       15    (show Accept row=5)
6   4       05/05/2012      Accept       25    (show Accept rows=6,7 and ignore Deny & Accept rows = 8,9)
7   4       07/08/2012      Accept       20         11  6       01/01/2011      Accept       5     (show Accept rows=11,12 because Qty is different) Â
12  6       01/01/2011      Accept       15
Create Sample Table (ID int null, PersonID Int null, Date Datetime null , Type varchar(10) null, Qty int null)
Insert into sample values (1 ,1,'01/01/2011','Accept',5),
(2,1,'01/01/2011','Accept',5),  (3,2,'02/01/2010','Accept',10),             (4,2,'02/01/2010','Deny',20),  (5,3,'02/01/2012','Accept',15),  (6,4,'05/05/2012','Accept',25),  (7,4,'07/08/2012','Accept',20), (8,4,'07/08/2012','Deny',5), (9,4,'09/23/2012','Accept',23), (10,5,'09/08/2012','Deny',12), (11,6,'01/01/2011','Accept',5),          (12,6,'01/01/2011','Accept',15)
I have two tables, Employee and Calls. They are joined on an Employee field. In my where clause I have a value specified that only returns specific calls. What I would like to have happen is to have the query return all Employee records regardless if any records from the Calls table is present for that employee. I want something that looks like this:Employee # of Calls Employee A 5 Employee B 0 Employee C 10
When I apply a WHERE clause to the Calls table I get this:Employee # of Calls Employee A 5 Employee C 10
I tried a LEFT OUTER JOIN without success. Any suggestions?
Dataset "FromTimeDimension" is a report parameter and has values (yyyymm)= 200601,200602,200603,2000604....... I would like to always have the default for this parameter to always be the latest month...ie November is the most recent month in our cube, so I would want the parameter to default to "200611". However, when I try to use the "last" function, the error says that aggregate function cannot be used.
Hi -- I'm starting an ASP.NET 2.0 application which contains a page with a checkbox and gridview control on it. In its default state the gridview displays all the records from a table pulled from a SQL Server database (via a SqlDataSource object). When the user checks the checkbox, I want the gridview to display only the records where one of the columns is not null. But I've been unable to construct the WHERE clause of the SQLDataSource object correctly. I see that I can hard-code the SqlDataSource object so that the column to be filtered is always NULL or always NOT NULL. But I want this filtering to be more dynamic such that the decision to show all or non-null records happens at run-time. Should I be using two SqlDataSource objects -- one for the NOT NULL condition and one for the "all records" condition? Then when the user checks the checkbox, the gridview would be configured to point to the appropriate SqlDataSource object. (???) Seems like a bit of overhead with that approach. I'm hoping there's a more elegant way to get this done. Please let me know if you need more information. Thanks in advance. Bill
Hello.I realize that this question has been asked before, but I still can't get it to work. Below are some links that might be of help:http://forums.asp.net/p/1159666/1913519.aspx#1913519http://forums.asp.net/p/1161930/1924264.aspxhttp://forums.asp.net/p/1116601/1732359.aspx#1732359http://forums.asp.net/t/1104718.aspxhttp://forums.asp.net/p/1096290/1655706.aspx#1655706http://forums.asp.net/p/1110162/1707952.aspx#1707952 Basically, I need a DropDownList to display only projects for which the logged in user is assigned as leader. The [Projects] table contains an integer ProjectId, a string ProjectName, a uniqueidentifier ProjectLeader, and other fields. Can someone help me with the SQL query and code? * Here is the definition of the SqlDataSource: <asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDB.MDFConnectionString %>" SelectCommand="SELECT [ProjectId], [ProjectName] FROM [Projects] WHERE ([ProjectLeader] = @Leader)" OnSelecting="SqlDataSource5_Selecting"> <SelectParameters> <asp:Parameter Name="Leader" Type="Object" /> </SelectParameters> </asp:SqlDataSource> * Here is the definition of the SqlDataSource5_Selecting method: protected void SqlDataSource5_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters("@Leader").Value = loggedInUserId; } where loggedInUserId is a global variable of type System.Guid. It has been evaluated in the Page_Load event to as: loggedInUserId = (System.Guid)Membership.GetUser().ProviderUserKey; Now the first problem I encounter is that when I run the page, the compiler complains and says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." The second problem is when I insert the line: SqlDataSource5.SelectParameters("Leader").DefaultValue = loggedInUserId; in page_Load. The compiler again says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." I've spent a long time trying to figure it out, but could not solve it. I would appreciate it if someone can help me out. Thank you very much.
I have a query below to show all the records with joining these two tables.
SELECT DISTINCT B.BF_ORGN_CD, B.LEV5, A.BF_ACTY_CD FROM BF_ORGN A INNER JOIN BF_ORGN_CNSL_TBL B ON A.CD=B.BF_ORGN_CD WHERE A.BF_ACTY_CD IS NOT NULL ORDER BY B.BF_ORGN_CD,A.BF_ACTY_CD
My goal is only to show all the duplicate records.
Bf_ORGN_CD LEV5 BF_ACTY_CD AC_21234_2 AC_21200_1 402 AC_21236_2 AC_21200_1 402 AC_21238_2 AC_21200_1 402 AC_29000_1 AC_29000_1 802 ---> NOT SHOW (ONLY 1 RECORD) AC_29988_1 AC_29988_1 801 ---> NOT SHOW (ONLY 1 RECORD)
HiI'm migrating from Access til MySQL.Works fine so far - but one thing is nearly killing me:I got the count of total records in a variabel - (antalRecords)I got the count for the Field Q1 where the value value is = 'nej'Now I just need to calculate how many % of my records have the value 'nej'I access this worked very fine - but with MySQL ( and ASP) I just cant getit right!!! I go crazy ....My code looks like this :strSQL="SELECT COUNT(Q1) AS Q1_nej FROM Tbl_evaluering " &_"WHERE Q1 = 'NEJ' "set RS = connection.Execute(strSQL)antal_nej = RS("Q1_nej")procent_nej = formatNumber((antal_nej),2)/antalrecords * 100Hope ...praying for help ...Please ;-)best wishes -Otto - Copenhagen
