I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...
I need to filter rows in a dataflow...
I created a condition spli to that... maybe there is a better solution...
And the condition is: Datex != NULL(DT_DATE)
(Some DATE != NULL)
[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.
I am trying to write an visibility function to have message shown based on two different IIF conditions:
If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS", otherwize return " NAME CREATED")If behavior is to Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST", otherwize return "NAME SUCCESSFULLY DELETED") I tried the following which doesn't work: =IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAME CREATED"), IIF((UCase(First(Fields!Message.Value, "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")
create view vwdate as select custno, custname, city, date from salestab where date = ????
well, this question mark...i have to fill..
like i want if month is june then i want for whole may month results of custno, custname and city..
if month is july then i want results for whole june month..
means for previous month results i want whatever the date is for that particular month..
Example:
if date='06/15/08' then results will be custno custname city date --------------------------------- 1 AAAA NJ 05/01/08 2 SDS CT 05/04/08 ........................ 05/18/08 .......................... 05/30/08
if date='06/30/08' OR '06/1/08' then also results will be same.. so i want for previous month results..
so can anyone help me to get the correct condition in where clause?
How can i write a condition for like isdate(Fields!DueDate.value)
I tried isdate but i am getting an error message. what is the equivalent for isdate in sql server reporting services formula fields or calculated fields expression.
I am collecting a year parameter from my user and want to use that year as part of a complete date condition.
So I collect the year say '2003' from my user.
A condition must be applied so that the date must be after 01-APR-...and here is where i want to utilize the parameter @Year. So I can apply the same date condition depending on what year we are looking at.
I am currently using the following logic:
(DATEPART(yy, CANCELLATION_DATE) = @Year) AND (DATEPART(mm, CANCELLATION_DATE) >= 04) AND (DATEPART(dd, CANCELLATION_DATE) >= 02)
This causes problems as the date can be only Jan, Feb, Mar, Apr 01 or 02. What I need is to gather all dates prior to Apr 02, with the year paramter to follow.
We originally developed this querty using Oracle so idealy there would exist a pl-sql to t-sql conversion tool out there! Anybody know of one?
Here is how the pl-sql is structured: CANCELLATION_DATE >= TO_DATE('02-APR-'||:"Year",'DD-MON-YYYY') ) ) )
Hi guys. i have a field in a table which datatype is varchar and i m storing datetime and number in it.. but when i try to use select statement like ( between startdate and enddate) or number (= ) operator it's not working..
select * from table where cast(Field as datetime) between @startdate and @enddate
second query that i m trying to exectue is
select * from table where cast(Field as bigint) = @Fieldid
but both are not returning any value...can anyone tells me where i m doing mistake
I've two audit tables, AUDIT_ORDERS and AUDIT_ORDER_LINES.
The AUDIT_ORDERS has these columns: AUDIT_ID, ORDER_ID, AUDIT_DATE and other ones.
The AUDIT_ORDER_LINES has these columns: AUDIT_ID, ORDER_ID, ORDER_LINE_ID, AUDIT_DATE and other ones.
I need to join these two tables in order to select for each order line row the first order having the related audit date lower than or equal to the audit date of the related order line.
I don't want to use the TOP 1 clause or a subquery. I think to complete a such statement:
SELECT OL.Order_Line_ID, O.Order_ID, OL.Audit_Date, O.Audit_Date FROM AUDIT_ORDER_LINES as OL INNER JOIN AUDIT_ORDERS as O on OL.Order_ID = O.Order_ID and O.Audit_Date <= OL.Audit_Date ...
I'd like to get the first row of the Audit_Orders with audit_date <= of the audit_date of the Audit_Order_Lines table by using the join clause.
I have a series of Updates that I perform on 7 tables. I would like to execute these updates on a nighly basis via a job. I have the update statements lined up in a stored proc.
So I have -
Create Proc Update AS SET NOCOUNT ON
DECLARE @RETURNVALUE int DECLARE @ERRORMESSAGETXT varchar(510) DECLARE @ERRORNUM int
set @RETURNVALUE = 0
BEGIN ---Start here
Update table1 Update table2 . . . Update table7
SELECT @ERRORNUM = @@ERROR IF @ERRORNUM = 0 SELECT @RETURNVALUE = 0 ELSE BEGIN SELECT @ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages] WHERE error = @@ERROR RAISERROR (@ERRORMESSAGETXT, 16, 1) SELECT @RETURNVALUE = 1 END RETURN @RETURNVALUE END --- END HERE
---
Is the error checking at the appropriate place? Should I have error conditions after every update? If yes then what condition do I check for?
declare @dte as datetime='2015-04-01' declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0) declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0)); DECLARE @query AS NVARCHAR(MAX);
[code]...
iam getting the ouput correctly my requirement is i want to write a condition here JOIN busdetails b ON m.bus_id = b.bus_id i want to write this statement as JOIN busdetails b ON m.bus_id = b.bus_id and m.uname='lekshmi'
I am developing a scientific application (demographic forecasting) and have a situation where I need to update a variety of rows, say the ith, jth and kth row that meets a particular condition, say, x.
I also need to adjust rows, say mth and nth that meet condition , say y.
My current solution is laborious and has to be coded for each condition and has been set up below (If you select this entire piece of code it will create 2 databases, each with a table initialised to change the 2nd,4th,8th and 16th rows, with the first database ignoring the condition and with the second applying the change only to rows with 'type1=1' as the condition.)
This is an adequate solution, but if I want to change the second row meeting a second condition, say 'type1=2', I would need to have another WITH...SELECT...INNER JOIN...UPDATE and I'm sure this would be inefficient.
Would there possibly be a way to introduce a rank by type into the table, something like this added column which increments for each type:
ID Int1 Type1 Ideal Rank by Type
1 1 1 1
2 1 1 2
3 2 1 3
4 3 1 4
5 5 1 5
6 8 2 1
7 13 1 6
8 21 1 7
9 34 1 8
10 55 2 2
11 89 1 9
12 144 1 10
13 233 1 11
14 377 1 12
15 610 1 13
16 987 2 3
17 1597 1 14
18 2584 1 15
19 4181 1 16
20 6765 1 17
The solution would then be a simple update based on an innerjoin reflecting the condition and rank by type...
I hope this posting is clear, albeit long.
Thanks in advance
Greg
PS The code:
USE
master
GO
CREATE DATABASE CertainRowsToChange
GO
USE CertainRowsToChange
GO
CREATE TABLE InitialisedValues
(
InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,
da = New Data.SqlClient.SqlDataAdapter("SELECT [Products].[Names], Count([ProductList].[Products]) AS [Total] FROM [Products] LEFT JOIN [ProductList] ON [ProductList].[Names] = [Products].[Names] GROUP BY [Products].[Names] ", strConnection) can we use a where condition in the statement.If so how can we use it.
Hi all,I'm building a DataSet on Visual Studio and don't know how to do a condition (if/else) with SQL... I have a search form, with a DropDownList and have 2 options in it: Search by Title or Search by Author. If the "Title" is selected, then the value is "title and if "Author" is selected, then the value is "author".Here is what I have right now for the DataSet, as seperated queries but I think I can combine them to be one single query 1.This will returns the songs that matches the title:SELECT LYRICS_PK, LYRICS_TITLE, LYRICS_TITLE2, LYRICS_WRITER, LYRICS_WRITER2, LYRICS_COWRITER, LYRICS_DATE_ADDED, UserId_FK, LYRICS_APPROVED, LYRICS_TYPE, LYRICS_VIEWS, LYRICS_ADDED_BYFROM t_lyricsWHERE ((@LYRICS_TITLE IS NULL) OR (LYRICS_TITLE LIKE '%' + @LYRICS_TITLE + '%') OR (LYRICS_TITLE2 LIKE '%' + @LYRICS_TITLE + '%')) AND (@LYRICS_TYPE = 'title') 2. This returns the songs that matches the author: SELECT LYRICS_PK, LYRICS_TITLE, LYRICS_TITLE2, LYRICS_WRITER, LYRICS_WRITER2, LYRICS_COWRITER, LYRICS_DATE_ADDED, UserId_FK, LYRICS_APPROVED, LYRICS_TYPE, LYRICS_VIEWS, LYRICS_ADDED_BY FROM t_lyrics
WHERE ((@LYRICS_AUTHOR IS NULL) OR (LYRICS_AUTHOR LIKE '%' + @LYRICS_AUTHOR + '%') OR (LYRICS_AUTHOR2 LIKE '%' + @LYRICS_AUTHOR + '%')) AND (@LYRICS_TYPE = 'author') This is very inefficient because I have 2 queries, and I need to build 2 ObjectDataSources as well as 2 different GridViews to display the results. I think we can do something likeSELECT .... ... FROM t_lyricsif (@LYRICS_TYPE = 'title') DO THE WHERE CLAUSE THAT RETURNS MATCHES WITH TITLEelse if (@LYRICS_TYPE = 'author') DO THE WHERE CLAUSE THAT RETURNS MATCHES WITH AUTHOR But I don't know how to write that in T-SQL.Any help would be greatly appreciated,Thank you very much,Kenny.
i'm working on a query and have discovered something fairly simple regarding "and" / "or" condition.
if I use e.g.
id_product in ('1111','2222')
as a result i should get all products that match id = 1111 and id = 2222.
But if I do it like
id_product in ('1111') and id_product in ('2222')
as a result i get 0 rows returned, where as i want to find invoices that have both products, and not those which have either product 1111 or 2222 or even both :)
If prmA is something like generic all, any, I want this query return value without condition. Is there a way to do this with sql or I should write stored procedure that checks @prmA and all other condition parameters and generate new SQL statement?
In following query, I use three conditions in WHERE caluse. When I use only CreditUnion.Id=@CreditUnionID Then I get the right value set. But when I join other two conditions , i get all the values instead of relevant data for that parameter.
Can anyone say why it happenes?
Code Block SELECT Member.LastName + ' ' + Member.FirstName AS MemberName, CASE WHEN CuStatus = 'Existing' THEN 'Existing' ELSE 'New' END AS MemberType, EnumCUMembershipStatus.UIText AS Status, CDOrder.DecidedOnCU, SysUserLogon.LastName + ' ' + SysUserLogon.FirstName AS CUDecisionOfficer, 'CD' AS ProductType, CreditUnion.Name
FROM Member INNER JOIN CDOrder ON Member.LastCDOrderFK = CDOrder.Id AND Member.Id = CDOrder.MemberFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id INNER JOIN EnumCUMembershipStatus ON Member.CuStatus = EnumCUMembershipStatus.Name INNER JOIN SysUserLogon ON CreditUnion.Id = SysUserLogon.CreditUnionFK
WHERE (CreditUnion.Id = @CreditUnionID) AND (Member.CuStatus = 'Approved') OR (Member.CuStatus = 'Declined')
UNION
SELECT Member_1.LastName + ' ' + Member_1.FirstName AS MemberName, CASE WHEN CuStatus = 'Existing' THEN 'Existing' ELSE 'New' END AS MemberType, EnumCUMembershipStatus_1.UIText AS Status, LoanApplication.DecidedOnCU, SysUserLogon_1.LastName + ' ' + SysUserLogon_1.FirstName AS CUDecisionOfficer, 'Loan' AS ProductType, CreditUnion_1.Name
FROM Member AS Member_1 INNER JOIN LoanApplication ON Member_1.LastLoanApplicationFK = LoanApplication.Id AND Member_1.Id = LoanApplication.MemberFK LEFT OUTER JOIN CreditUnion AS CreditUnion_1 ON Member_1.CreditUnionFK = CreditUnion_1.Id LEFT OUTER JOIN EnumCUMembershipStatus AS EnumCUMembershipStatus_1 ON Member_1.CuStatus = EnumCUMembershipStatus_1.Name LEFT OUTER JOIN SysUserLogon AS SysUserLogon_1 ON LoanApplication.SysUserLogonFK = SysUserLogon_1.Id AND LoanApplication.SysUserLogonCUFK = SysUserLogon_1.Id AND CreditUnion_1.Id = SysUserLogon_1.CreditUnionFK
WHERE (CreditUnion_1.Id = @CreditUnionID) AND Member_1.CuStatus = 'Approved' OR Member_1.CuStatus = 'Declined'
I am an ASP.NET Developer I am using two SQL Server databases, 2005 and sql express.I am using a select statement on an IN CONDITION One table, Table name = SOP10100 resides in a SQL Server 2005 DatabaseThe other table, Table name = ORDER resides on a SQL server express Database I am writing the following sql statement SELECT ORDERNO, CARRIER FROM SOP10100WHERE ORDERNO IN ('ORD000234','ORD000384',....) My question is how many values can I fit on this IN conditionI mean the maximum number of values (upperlimit) Is there a better way to do this Using ASP.NET ?
Hi This is madhavi am working with a project with ASP.NET Using VB.NET.. i have requirement that i have to provide the result based on search condition.... First : For Serach i have to search based on given CITY and CATEGORY....
For this i have written a StoredProcedure like: ****************************************************************************************************************** Create PROCEDURE YellowPages_Search(@city nvarchar(50),@SearchWord nvarchar(200),@Name varchar(50) OUTPUT,@CompanyName varchar(50) OUTPUT,@Address varchar(1000) OUTPUT,@PhoneNo varchar(50) OUTPUT,@MobileNo varchar(50) OUTPUT,@Fax varchar(50) OUTPUT,@Email varchar(50) OUTPUT,@WebSite varchar(50) OUTPUT)AS declare @sql nvarchar(1000)set @sql='select * from YellowPages_Userdetails where city='''+@city + '''and (category like ''%' + @SearchWord + '%'' or subcategory like ''%' + @SearchWord + '%'') ' exec(@sql) GO ************************************************************************************************************************************************************************* Now i want to extend this search condition for LOCATION and SUBCATEGORY means my search condition should include CITY , LOCATION , CATEGORY and SUBCATEGORY (here the location and subcategory may be given or may not be given) so please help me out Thanks in Advance, Madhavi
i have two tables: "Person" and "Year". "Person" can have many "Year" (one to many relation). i want a query which returns all the records from "Person" where "Year" is 2005 but exclude if there is any "Year" with 2004. how can i write that query? any help will be appreciated. i did try <code> SELECT * FROM Person JOIN Year ON Person.Id = Year.PersonID WHERE Year.Year = 2005 AND Year.Year <> 2004 </code> but it doesn't seem to work. i want this query to return records from Person where there is no any year with 2004 but only 2005. If a person has both 2004 and 2005 exclude that person.
i have two tables A and B with the same fields, If the id field of table B equals id field in Table A i need to update th edata for that id row.If the id field doesn;t match then i need to insert a new record in tale A for that id that is i need to perform insertion or updation into table A depending on table B dataCan anyone give me some idea how to start?
I have a sql problem I'm trying to solve. I'm selecting from a table based on a foreign key, and the select returns 2 rows. The table has a column called type, and each row for the foreign key has a different type value. Hopefully the example below can help to explain:
Case 1:
PK | FK | Type | Text -------------------------- 1 | 226 | 0 | some text goes here 2 | 226 | 1 | NULL
Case 2:
PK | FK | Type | Text -------------------------- 3 | 334 | 0 | some text goes here 4 | 334 | 1 | actual text I want to select is in this cell
I'm trying to create a select statement to grab the text for the foreign key I'm looking up. In case 2, I want the text where type=1 but in case 1 I want the text where type=0.
I had started writing it as
select text from table where fk=334 and ( (type=4 and text is not null) or type=0 )
but this returns both rows. What I what is something that I think is more akin to
case a || case b
expression in programming - if case a evaluates as true, use that, otherwise evaluate case b and use if true, otherwise return false.
I hope you can understand what I'm trying to get and any suggestions would be much appreciated.
