Setting Comparison Operator Depending On Parameter Value?
Apr 6, 2006
Hello,
Is it possible to set a comparison operator using a parameter value?
The code below shows what I'm after;
declare @co char(1)
declare @date datetime
set @co = '<'
set @date = '02/02/2002'
select * from recipe where date @co @date
I would use an if statement perform two seperate statements depending on the value of co, but this is only one of 13 statements where i need to have different combinations of comparision operators.
thanks
View 2 Replies
ADVERTISEMENT
Jun 22, 2006
I want to create a column alias to represent the comparison of twocolumns (ie a boolean result of True or False). A simple example is:Select VehicleFinanceID, SalePrice > PurchasePrice As isProfit[color=blue]>From VehicleFinance[/color]but I get an error 'Incorrect syntax near >'Books online states that the select_list can contain column_name orexpressionAn expression is a column name, constant, function, any combination ofcolumn names, constants and functions connected by an operator[color=blue]> is a binary operator[/color]So why do I get this error.Incidentally, if I use an arithmetic operator such as +, there is noproblem.
View 5 Replies
View Related
Aug 31, 2007
How do you make the "Like" comparison operator ignore case in an expression??
Expression looks like this:
Code SnippetParameters!CompanyFilterOp.Value = "%",Fields!company.Value like "*" & Parameters!Company.Value & "*"
BTW, the expression above is part of a switch expression, and is at the table level.
Data contains "General Industry" in Company column from database.
User enters "indust" in Company Parameter text box, and result is no data found.
User enters "Indust" in Company Parameter text box, and data is returned.
Thanks in advance for your time in responding.
View 3 Replies
View Related
Apr 18, 2008
I need to make my site aware of which server_name it is loading from so it uses a different connection string. (have dev + prod servers for web/sql)Currently my connection string is in web.config as follows: <connectionStrings> <!-- Development and Staging connection string --> <add name="myconnection" connectionString="server=myserver; user id=mysuer; password=mypassword; database=mydatabase" /> </connectionStrings> I need to make sure the 'name' is the same for both connection strings since that is how the rest of my site looks for it. However, I'm not sure how to get both in here with some sort of 'if/then' statement to determine which one to use.I've heard it could be done in global.asax with something similar to the code below, but I dont know how to assign a 'name' to a connection string for that type of setup. Sub
Session_OnStart ServerName =
UCase(Request.ServerVariables("SERVER_NAME")) IF ServerName = "prod.server.com" THEN ...Set Prd string... ELSE ...Set Dev string... END IF End
Sub
View 8 Replies
View Related
Mar 3, 2015
[Microsoft][SQL Server Native Client 11.0][SQL Server]INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.(42000,1934) Procedure(trgDDLChangeLog).
View 3 Replies
View Related
May 5, 2008
I am trying to merge 2 pieces( i.e procedures , or stored proc) of sql together.
My simple QueryA
SELECT colA, colB, colC, colD
FROM tableA
WHERE
colD IS NOT NULL
My simple QueryB
SELECT colA, colB, colC, colD
FROM tableA
WHERE
colC IS NOT NULL
I am trying to merge these 2 pieces if sql together by passing a input parameter which will decide which query to run. So if I pass an input parameter QueryA , it will run QueryA. If I pass an imput parameter QueryB, it will run QueryB.
Essentially both my queries are the same besides the where condition. Is there a way to merge it into one query (and not use if conditions and make my storedproc long) and apply the where condition depending on what input parameter is passed in ?
I know it can be done using dynamic SQL construction. But any other ways ?
Also can someone also give in the solution in PL/SQL.
Thanks a bunch.
Jaffery.
View 7 Replies
View Related
Jan 13, 2015
This is for Microsoft Server SQL 2012.
I'm trying to create a WHERE clause that will have different results depending on a parameter that is entered. For example, if you put in a number, it will only calculate the rows where the column ID matches that number. However, if you put in 0, which doesn't exist in that column ID, it will instead calculate all the data in the table.
So the below would be a very basic idea of what I'm trying to do, but I'm not sure how to do it with proper syntax.
WHERE IF ID=0, THEN do this
ELSE do this AND ID=#
View 2 Replies
View Related
Sep 14, 2006
My data table contains a column (a foreign key) with numbers from 0 and up. (it could be null or a number. I used ISNULL to convert it.) I want to be able to select in three ways. 1) equals zero (or is null)2) greater than zero (or is not null)3) equal to a specific number.How do I do this?My application setup is Presentation Layer, BLL, DAL and Sql Server.I don't want to create another Query because this is only part of a larger query with other parameters.If I could just do something like @IDOperator @IDValue in the Filter of the Sql it would be great.
View 2 Replies
View Related
Jan 16, 2008
I am using SQL server 2005 SP2.
I have a view created in Sql database by name vw_CETARIS_Suppliers.
CWS_fn_MVParam: This function takes a parameter for cities (xx,yy,zz)
and returns a table with the values as individual cities. - If I
execute this from t-sql, it is working perfectly. Also I created a dataset with the query
SELECT Param FROM CWS_fn_MVParam(@pCity,', ' )
This is executing properly and the dataset is getting all the vlaues from the concatenated parameter (for example 'xx,yy,zz' is returning a table of values
xx
yy
zz.
My SRS report has a multi-value parameter called @pCity. The
following query is given under the data tab in SRS report.
IF @pCity=' All'
SELECT CetarisSupplierCode, CetarisSupplierName, CetarisAddress1,
CetarisAddress2, CetarisCity, CetarisProvState, CetarisPostalZip,
CetarisCountry,
CetarisCurrency, CetarisCompany
FROM vw_CETARIS_Suppliers
ELSE
begin
SELECT CetarisSupplierCode, CetarisSupplierName, CetarisAddress1,
CetarisAddress2, CetarisCity, CetarisProvState, CetarisPostalZip,
CetarisCountry,
CetarisCurrency, CetarisCompany
FROM vw_CETARIS_Suppliers
WHERE CetarisCity IN (SELECT Param FROM CWS_fn_MVParam(@pCity,', ' ))
end
When I refresh the dataset and run for a All,single city or multiple
cities, it returns data. No problem.
When I preview the report for single city and 'All' value it returns data perfectly.
But when I select two or more cities I
am getting an error.
An expression of non-boolean type is specified in a context where a
condition is expected, near ','
Incorrect syntax near the keyword ELSE
Can anybody help me please?. I tried different ways but was not successful in finding out the problem.
Thank you for all your help in advance,
View 15 Replies
View Related
Jan 24, 2007
Hi all,
I have couple of paramters with operator 'range(inclusive)' in my report(Agains datacube), and I always get failure when I choose value with them. here's a example( 3 such parameters on my report).
...
From date To Date
...
From age To Age
...
From Salary To Salary
@salary is the last one.
when i choose a value for 'From Salary' or 'To Salary' from the dropdown list, it runs correctly. But if I choose value for 'from age' or 'To age' or 'From Date' or 'To Date', it fails due to
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'DimTimesheetTimesheetNumber'. (rsErrorExecutingCommand)
Range operator ( : ) operands have different levels; they must be the same.
BTW, all the default values for these parameters are 'all', since it allows only 'all' to select when I set up the parameters, and I tried to change the default value, but it caused errors about the parameter dependency.
Any idea and suggestion will be appreciated!
Jone
View 1 Replies
View Related
Nov 27, 2007
I searched this forum however I could not find an answer to my question. I'm sure it has been asked before but...
In my parameter if the user does not select anything, null, then it returns all the records.
Second question if he selects "All" then it returns all records.
If this is 1st grade stuff please forgive me. I'm pretty new, learning as the needs arise.
Thanks
CardGunner
View 12 Replies
View Related
May 23, 2007
I am trying to use the code below to set a formview datasource parameter in the page_load section of a user control (ascx file): public void Page_Load(object sender, EventArgs e){ formview_datasource.SelectParameters.Add("@department_id", "e62bbc7d623f44a68e101cba90e839s3");} However I am getting the following error: Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@department_id'.
So it would seem that page_load in my user control isn't being called? I'm not sure why or how to work around it. Has anyone else experienced anything like this or can give me some pointers on where I am going wrong?
ThanksBrad
View 3 Replies
View Related
Feb 20, 2008
I have a question regarding Execute SQL Task as I combined one statement like select count(*) from destination table, and insert into error table (source count, error count, destination count) values (?,?,?).
If I use two Execute SQL Task, it should work. I was wondering that is it possible to combine select and insert into one Execute SQL Task direct input. How to approch this?
View 6 Replies
View Related
Apr 10, 2007
Hello all.
I am on the verge of being able to do exactly what I want, but just can't seem to find the right combination of things to do it. I'm sure all of you wonderful folks will be able to point it out to me immediately, but I've been looking at it too long or something....
I have a record of individual sales with the state, and quarter of the sale.
sale_id state quarter
001 NY 2005Q1
003 WI 2006Q2
etc.
I create a report with a matrix to show count(sale_id) with Quarter as the column group and State as the row group. This works fine.
Now what I want to do is to get percentages based on quarterly sales. In other words, what percent of sales for 2005Q1 in NY vs. all sales in 2005Q1. So I create a second dataset (called total) with an SQL query like so:
SELECT count(sale_id)
FROM data_table
WHERE quarter = @QueryQuarter
Now, back in the matrix I want to use the column that we're in (2005Q1, 2005Q2, etc.) as the value that is passed to this query.
This is a simple concept, but I can't seem to figure out the correct call to pass the column group to the query as the parameter.
Thank you for any pointers you might be able to give. As I said, I'm right on the verge and just can't quite get it.
cmk
View 3 Replies
View Related
Jan 27, 2006
I want to set defaults for my multi-valued report parameter MONTH so that when the report starts, it automatically selects all the months prior to the current month (effectively creates a YTD report). However, using RS2005, I can't seem to figure out how to do this. I can create an IIF expression in 12 different value entries in the report parameters that returns the month based on the system date, but the first time I pass blanks, null or anything except a valid parameter, it clears the entire parameter list when the report displays.
Does anyone have any suggestions for auto-populating multiple values in a parameter at runtime where one or more of the parameter values may be empty? Checking "Allow Null" or "Allow Blank" doesn't fix this problem.
I tried to pass all the values in a single value entry on the report parameters page, but can't find the syntax that will allow this. I'm not sure if it will let you do that anyway...
Trent
View 3 Replies
View Related
Jan 21, 2008
we're trying to get a better understanding of how RS behaves when parameters are being set. We see quirky behavior that is a little difficult to describe. Right now we assume that if the revolving green circle (with the phrase "Report is being generated" beneath it) doesnt appear, the report really wasnt rendered properly, even if the report region changes.
One peculiarity that seems pretty consistent is on reports we've prototyped with "from" and "to" date parameters. It seems that when we set one date (doesnt matter which is 1st) things progress normally, ie no "report clearing event" occurs as a result of setting cursor focus in the calendar control and changing its value. The report region doesnt change from what showed previously. But trying to set focus on 2nd (doesnt matter if its "from" date or "to" date, just that its the 2nd date being set) always seems to trigger some kind of event that 1) doesnt allow focus to be on that text box, 2) blanks out the report region including headings. Only after this "event" occurs, can we set focus on the 2nd date, change the value and click the "view report" button for rerendering.
We see similar types of behavior with other types of parameters that include multi value dropdowns and booleans. The toughest part of this is trying to explain it to our users. On some parameters, the event always occurs every time they are changed. On other parameters, it appears that the event only occurs if another parameter was changed beforehand.
I believe we've even seen headings with no data rendered, thinking temporarily that no rows were returned, just to find out that by clicking the "view report" button there really was data to be reported based on current filters. Unfortunately I cant reproduce this scenario when I want to.
View 3 Replies
View Related
Jan 26, 2007
Hi all,
Does sombody have experience on dynamically set or change the default value of a report parameter?
Assuming: report parameters p1, p2, p3, p4 have been set up(and have their default value 'all') with the creation of the report1; report browseing is through reportviewer that embedded in the web application; datasource is datacube
What I want to do: based on the login user of the my web application, set default value of p1 as the user's username.
What I did is:
Microsoft.Reporting.WebForms.ReportParameter reportParam = new Microsoft.Reporting.WebForms.ReportParameter("P1","Mary");
ReportViewer1.ServerReport.SetParameters(new Microsoft.Reporting.WebForms.ReportParameter []{ reportParam });
what I got when reveiw the report:
P1 has no default value, and p2, p3, p4 greyed and could not choose value from them.
Any idea and suggestion will be pre-appreciated!
Jone
View 1 Replies
View Related
Jun 17, 2008
Hi everybody,
Is there a way to set SelectParameter for SQLDataSource in ASPX file using System.Configuration.ConfigurationManager.AppSettings["SiteID"]) ?
Thanks a lot in advance.
View 8 Replies
View Related
Aug 21, 2007
How can I set the multivalue parameter list's default option to <Select All> ?
View 2 Replies
View Related
Feb 19, 2007
Hi,
I'm pretty new to ASP.NET and VB, but I'm working on a project which is essentially a staff directory. Using VS 2005, I've setup a basic grid view which connects to an object which connects via a data layer to a SQL database with Name, Surname, Email, Extension No and Department fields.
I have it working so that if a user enters a name and surname for example, it will return all records with either the name matching or surname matching input parameters. What I want to do is to set up a SQL query which is:
SELECT * FROM records WHERE (Name LIKE @Name) AND (Surname LIKE @Surname) AND (Email LIKE @Email) AND (Dept LIKE @Dept)
so that if the user only enters the first name and surname for example as above, it ONLY returns the record which matches the first name and surname and not all records with either/or. One way I've thought of to do this is to convert the empty fields to "Nothing" so that it fullfils the search parameter for the empty fields. If someone can explain how to do this, or can suggest a better way, I'd be grateful.
Cheers,
Tom
View 3 Replies
View Related
Jun 3, 2015
I have a report with a subscription enabled and the default values that are selected for the report frequently change. I have our report server locked down so that the users can't change the defaults, but I now want to empower them to maintain this on their own. Here is my dilemma. When you have the available parameters set up to pull from a query, the defaults on the report server have to be keyed in manually, which is not an option. The only way to get a check box there, is to explicitly specify the available values.I need my available values to be database driven and I need to be able to select my defaults on the report server using check boxes.
View 5 Replies
View Related
Oct 24, 2007
Hello,
I have a report which displays a customers invoice, in both the companys local currency, and the customers local currency.
The report language is "English (United Kingdom)"
The fields showing customers currency language setting is set to something else, i.e. "France (French)" to display the Euro currency.
The application handles 34 currencies, the query returns the language string, ("France (French)"), to allow the report to bind its language setting to the querys output.
However, it doesn't work, a normal textbox will display the correct country name string, but Reporting Services cannot bind the language setting to a query result. So I also tried setting it as a report parameter, but no joy either (all currencys revert to USD).
I'm using =First(Fields!curFormat.Value, "myDataSet") to bind the 'language' setting, the result of this expression returns "France (French)", which is a valid option for this language setting, as it's in the drop down list.
Rather than create 34 seperate reports for each currency, are there any suggestions on how to bind a fields language setting to a query result?
View 3 Replies
View Related
Aug 3, 2015
I have a multi value parameter called "Location" and this depends on another multi valued parameter value. The default value for the parameter "Location" comes from the another another multi valued parameter. Now say when the default value is set for the parameter Location like the below:
The Location parameter data set has values from the Query and default values has been set as shown below:
=Iif(array.IndexOf(Parameters!Program.Value,"A")>-1,nothing,"N/A")
I get an error on preview saying that . The Default Expression for the report parameter "Location" contains error:
Unable to cast object of type 'System.String' to type 'System.Array'.
View 2 Replies
View Related
Jun 2, 2004
I get this error when I attempt to read from a datareader using the following sql statement:
Dim mysql As String = "SELECT PayrollTrans.PayrollID, Employees.[EmpFirstName] & ' ' & " _
& " Employees.[emplastname] AS FullName, Employees.[City] & ', ' & Employees.[State] & ' ' & Employees.[zip] AS CityState " _
& " , PayrollTrans.Date, PayrollTrans.EmployeeID, PayrollTrans.RegHours, " _
& " PayrollTrans.OTHours , PayrollTrans.RegPay, PayrollTrans.OTPay, " _
& " PayrollTrans.FedTax, PayrollTrans.FICATax, PayrollTrans.MedicareTax, " _
& " PayrollTrans.ESCTax, PayrollTrans.StateTax, PayrollTrans.ESCEMPTax, " _
& " PayrollTrans.FUTATax, PayrollTrans.NetPay, Employees.EmployeeID, " _
& " Employees.Address1, Employees.Address2, Employees.SSAN, " _
& " Employees.PayType, Employees.RegPayRate, Employees.OTPayRate, " _
& " Employees.MaritalStatus, Employees.FedExemption, Employees.StateExemption, " _
& " Employees.Active, Employees.SelectforPay, Employees.PayDate " _
& " FROM PayrollTrans, Employees where PayrollTrans.EmployeeID = Employees.EmployeeID;"
my reader command list as follows:
Dim objCM As New SqlClient.SqlCommand(mysql, SqlConnection1)
Dim objDR As SqlClient.SqlDataReader
objDR = objCM.ExecuteReader
Any ideas on where I am going wrong?
Thanks in advance
View 3 Replies
View Related
May 18, 2004
I am getting a error message saying: Invalid operator for data type. Operator equals boolean AND, type equals datetime.
I traced the pointer to @gdo and @gd, they are both dates!
INSERT INTO AdminAlerts values (CURRENT_USER, 'UPDATE', getDate(), @biui, 'Updated booking, ID of booking updated: ' & @biui & ', Booking date and time before/after update: ' & @gdo & '/' & @gd & ', Room number before/after update: ' & @rno & '/' & @rn & ' and Customer ID before/after update: ' & @cio & '/' & @ci)
If I cut that two dates out it works fine.
Could someone tell me the syntax to include a date in a string :confused:
View 3 Replies
View Related
Oct 8, 2007
Hello,
I am very new to SQL and ran into a problem using Access. I hope you can help me here.
The question is the following:
I have to tables:
Table 4x4: CommodityCode(string)/NeedofBU(high,low)
Table ASDBComplete: CommodityCode/Manufacturer/Rating(red,green,yellow)
What I want to get as a result is to display all ComodityCodes that have a 'red' rating and a 'high' NeedOfBU. So far no problem. But now there usually is more than one manufacturer for one CommodityCode. What I need now is that the CommodityCode is not displayed if there actually exist a Manufacturer with 'green' or 'yellow' rating.
How would you do that?
What I have so far is:
SELECT [4x4].[CommodityCode], [4x4].NeedofBU, ASDBComplete.CommodityCode, ASDBComplete.Rating, ASDBComplete.Manufacturer
FROM 4x4 LEFT JOIN ASDBComplete ON [4x4].[CommodityCode] = ASDBComplete.CommodityCode
WHERE ((([4x4].NeedofBU)="high") AND ((ASDBComplete.Rating)="red"));
But this gives me all the Commodities with red ratings. Even if there is a supplier with a green or yellow rating.
I would need somting like:
if exists 'commodityCode.XY with 'manufacuturer rating = green OR yellow' do not display commodityCode.xy
I really appreciate your help
View 8 Replies
View Related
Jul 20, 2005
Hello.I have one serious problem with COUNT in TSQL.I use MS SQL Server 2000.I would like to count rows depending on data in it.What I meat is:I have fields like:region | month | year | some_count |-------|-------|------|-------------|LA | 1 | 2003| 4 |LA | 2 | 2003| 2 |LA | 3 | 2003| 1 |LA | 4 | 2003| 6 |VV | 1 | 2003| 3 |VV | 2 | 2003| 7 |VV | 4 | 2003| 20 |VV | 6 | 2003| 3 |BB | 2 | 2002| 1 |etc...And what I would like to get from it is:region | jan2003 | feb2003 | mar2003 | apr2003 | may2003 | june2003 | etc...-------|----------|---------|-----------|----------|----------|-----------|--LA | 4 | 2 | 1 | 6 |0 | 0 | 0VV | 3 | 7 | 0 | 20 | 0| 3 | 0BB | 0 | 1 | 0 | 0 |0 | 0 | 0etc...Is it possible to do it in views? if yes then how?I could use temp tables for each month but it is not solution when I have 3yearsand more later then it will be round 40 temporary tables.Thanks for any response.Regards,Casper
View 3 Replies
View Related
Feb 25, 2008
hello
sorry to distub you again
in fact i'm a beginner and i don't really see how i can do some tasks
when i display a report i want the reader to choose a client, and when he chooses a client, he can choose in the second parameter list, the missions that correspond to THIS client
so i have 3 datasets
one in which i get all my data needed
and i have a filter in where part of query
Code Snippet
where client=@client
and mission=@mission
and i filter on these 2 values
in the second dataset , i gather all the clients i have by doing this query
Code Snippet
select distinct name from client
and i have this third dataset where i gather all the missions corresponding to one client
and here i write
Code Snippet
select mission_label from client
where name=@client
the two fields are in the same time and in this third dataset i apply a filter on Parameters!client.value
but when i execute the report, the second parameter doesn't work, it gives me no choice of mission for a client, it doesn't return anything while when i execute the query in the dataset i have these values
how can i do this please?
thanks a lot in advance
and sorry or bothering with such questions
View 4 Replies
View Related
Apr 30, 2004
I'm trying to execute a different SELECT statement depdning on a certain condition (my codes below). However, Query Analyzer complains that 'Table #Endresult already exists in the database', even though only one of those statements would be executed depending on the condition. Any ideas as to a work around? I need the result in an end temporary table.
IF @ShiftPeriod = 'Day'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkDays = 1
ELSE IF @ShiftPeriod = 'Night'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkNights = 1
ELSE IF @ShiftPeriod = 'Evenings'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkEvenings = 1
ELSE
SELECT * INTO #EndResult FROM #NursesAvailable
View 1 Replies
View Related
Apr 23, 2008
Would like to have Identity Inserted wrt a date column
Eg:
ProdDate ID Details
============================
2008.04.01 1 afafafaf
2008.04.01 2 GAFSGHFGF
2008.04.02 1 GAGJAGSDH
2008.04.02 2 QYTYTT
2008.04.03 3 QYTWRRT
At present it is not an Auto increment , I check for the Maximum ID and add One to the next Inserted record. Is there any simpler method?
View 13 Replies
View Related
Jun 4, 2008
I want to insert the rows automatically depending on the cell value in column from another table.
Like if the value of cell "blabla" is 4 it automatically insert the 4 rows in my table with values.
Is it possible in TSQL?
View 13 Replies
View Related
Oct 5, 2007
Anybody knows how to apply an inflation factor depending on the date to a measure
The Inflation Factor is (Current Month Rate / X Month Rate)
So lets say for current year the rates are
Jan 121.64
Feb 121.98
March 122.244
April 122.171
For example
If I want to calculate for March I would show a column for January February and March, the column for January would be multiplied by (122.244/121.64) the column for February would be multiplied by (122.244/121.98) and march by one (122.244/122.244)
But in April the factors would be different I would have 4 columns, January February March and April, January amounts would be multiplied by (122.171/121.64) February by (122.171/121.98) March by (122.171/122.244) and April by one (122.171/122.171)
Im using SSAS 2005
Thanks in advance
Isaac
View 1 Replies
View Related
Jul 23, 2005
Hello all,I have two tables - Projects and ProjectStructTable Projects contains master records of the projects, ProjectStructallows to define a project herarchie and contains the fieldsPrjStructId, ProjectId, PrjStructName, ..., ParentIdPrjStructParent contains a reference to the parent or to itselves ifrecord is top-level-record for a project.I try to create a trigger on table Projects (INSERT) whichautomatically creates the top-level-entry in ProjectStruct but Ididn't succed.Tried to use (several variations similar to)INSERT INTO ProjectStruct (ProjectId, PrjStructName, ParentId)SELECT prjProjectId, 'top-level',IDENT_CURRENT('ProjectStruct'))FROM INSERTEDbut this inserts a reference to the last inserted record. Why thishappens is pretty clear to me, but I found no way to get the referenceto the identity column of the record currently inserted.Is there a way to do this?
View 7 Replies
View Related