Can I Print The Results Of A Condition Based On The Condition?
For example..
select * from mytable where MyNum = 7
If this brings back more than 1 row, I want to display a message that says,
Print 'There is more than one row returned'
Else (If only 1 row returned), I don't want to print anything.
Can I do this? Thx!
View Complete Forum Thread with Replies
Related Forum Messages:
COndition Spli - Error Date Condition
Dear friends, 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. What is wrong?? Regards, Pedro
View Replies !
Incrementing Based On Condition
Hi, I am facing a challenge and hope some one can help me with the query. I have a school. School have classrooms. Classrooms are divided into various sections (Section A, Section B and so on) . Sections have subsections. Every student is allocated a rollnumber in that section.(Subsection is just for dividng the sections. it has no other use.) Now the student is given a choice to specify his own roll( DesiredRoll) in that section. If two children select the same rollno, then the system internally allocates a trackingno.(There can be multiple roll no as these are allocated manually by admin). Let me demonstrate this: So when the first entry is made in the db, and let us say that the section to be allocated is A, RollNo is 1 and the DesiredRoll is 1 Student 1 Section A Subsection 1 Roll No 1 DesiredRoll 1 TrackingNo 0 The second entry is made in the db and let us say the section to be allocated is A, RollNo is 2 and the DesiredRoll is 2 Student 2 Section A Subsection 1 Roll No 2 DesiredRoll 2 TrackingNo 0 Now let us say there is a 3rd entry the section to be allocated is A, RollNo is 3 but the DesiredRoll is 1. Now since the DesiredRoll has already been taken, we will allocate the DesiredRoll 1, however now the trackingNo will be 1 Let us say there is a 4th entry is made, the section to be allocated is A, RollNo is 4 but the DesiredRoll is again 1. Now since the DesiredRoll has already been taken, we will allocate the DesiredRoll 1, however now the trackingNo will be 2 Similarly this logic will work for different sections. How will I write a query so that I can detect this scenario and increment the tracking no or allocate a tracking no of 0 if there is a new entry made in that section The structure of the table is as follows: IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Student]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Student]( [RID] [int] NOT NULL, [Class] [int] NULL, [Section] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SubSection] [int] NULL, [RollNo] [int] NULL, [DesiredRoll] [int] NULL, [TrackingNo] [int] NULL ) END GO INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (1, 1, N'A', 1, 1, 1, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (2, 1, N'A', 1, 2, 2, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (3, 1, N'A', 1, 3, 1, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (4, 1, N'A', 1, 4, 1, 2) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (5, 1, N'A', 12, 3, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (6, 1, N'A', 12, 4, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (7, 1, N'B', 5, 1, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (8, 1, N'B', 5, 2, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (9, 1, N'B', 5, 3, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (10, 1, N'B', 10, 1, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (11, 1, N'B', 10, 2, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (12, 1, N'B', 10, 3, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (13, 1, N'B', 11, 1, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (14, 1, N'B', 11, 2, 0, 0) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo]) VALUES (15, 1, N'B', 11, 3, 0, 0) Thanks
View Replies !
JOIN Based On LIKE Condition
I'm trying to join two tables based on a like condition. The first table contains the full IP, e.g. '166.27.12.24' and the second contains a 2 octet range, e.g. '166.27', which I need to join. Table 1 -> TRAFFIC (Time, SourceIP) Table 2 -> IP_ROSTER (IP2OctetRange, Administrator) I've tried the following, but it does not seem to work: SELECT TOP 100 SOURCE_IP, r.IP2OctetRange, r.Administrator FROM TRAFFIC LEFT JOIN IP_ROSTER AS r ON SOURCE_IP LIKE RTRIM(LTRIM(IP2OctetRange))+'%'
View Replies !
Returns TABLE Based On A Condition
HI all,In SQL Server, i have a function which will return a table. likecreate function fn_test (@t int) returns table asreturn (select * from table)now i want the function to retun the table based on some condition like belowcreate function fn_test(@t int) returns table asif @t = 1 return (select * from table1)else return (select * from table2)It is not working for me. Please give me your suggesstions. It's very urgent.Thank you in advance....
View Replies !
How To Write Trigger Based On Certain Condition
One of my table called as 'customertable' contains following fields customername, emailid, subscriptionendperiod Example records are: david, david@john.com, 12/20/2005(mm/dd/yyyy format). My question is that: Just one month before subscriptionendperiod that is on 11/20/2005(mm/dd/yyyy) an automatic email should go to david@john.com with the message 'Your subscription period ends on 12/20/2005' How to write trigger for this. Please Note : No ASP code is invloved in this. Only MSSQL coding to be done. Regards
View Replies !
Condition Joins Based On Parameters
I'm using SQL Server 2005 Reporting Services. Apologies if this is in the wrong area. I'm not sure if it's possible but what I want to do is join table dimFactor to factMedia using different fields dependent on the partameter value. i.e from FactMedia fm... if parameter value = 1 INNER JOIN dimFactor df ON df.FactorID = fm.AgeGroup_ID if parameter value = 2 INNER JOIN dimFactor df ON df.FactorID = fm.Gender_ID and so on.. Can this be done and if so, how?? I've tried using CASE statements but it's not having any of it. Help please!
View Replies !
Reordering Rows Based On A Condition
Hi, I have two tables : Students and StuHistory. The structure of the Student table is as follows : IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Student]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Student]( [RID] [int] NOT NULL, [Class] [int] NULL, [Section] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SubSection] [int] NULL, [RollNo] [int] NULL, [DesiredRoll] [int] NULL, [TrackingNo] [int] NULL, [Original_rollno] [int] NULL, [StudentStatus] [int] NULL ) END GO A section has subsections where students are allocated rollno's. Every student has a unique roll no in that subsection. However he is also given a choice to enter his desired roll no. If more than one student choose the same desired roll no in that subsection/section, there is a [TrackingNo] field that then starts keeping a count. For the first unique desired roll no in that subsection/section the tracking no is always 0. [StudentStatus] represents the following : (-1 for deleted, 0 for edited, 1 for newly inserted). After every fortnight, i have to run a batchquery that does the following: 1. all students marked with -1 are moved to a table called StuHistory which has the same structure as that of Student. 2. Now oncethe -1 status students are moved, there will be a gap in the roll no. I want to reallocate the rollnos now, where rollnos = desired roll no taking into consideration the trackingno So if 4 students have chosen the desired roll no as 5 and their current roll no is scattered in a subsection lets say 7, 10, 14,16, then while rearranging they will be together(grouped by subsection/section) and will be allocated roll no's 5,6,7,8. The other students will be moved down based on their desired roll nos. Over here i have to also fill the gaps caused because of the students who were deleted. How do i write query for this? I have been struggling. I thought of posting this as a new post as it was mixed in the previous post. Script : INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (1, 1, N'A', 1, 1, 1, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (2, 1, N'A', 1, 2, 2, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (3, 1, N'A', 1, 3, 1, 1,0,1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (4, 1, N'A', 12, 1, 1, 0,-1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (5, 1, N'A', 12, 2, 1, 1, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (6, 1, N'A', 12, 3, 2, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (7, 1, N'B', 5, 1, 3, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (8, 1, N'B', 5, 2, 3, 1, 0 ,1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (9, 1, N'B', 5, 3, 3, 2, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (10, 1, N'B', 5, 4, 2, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (11, 1, N'B', 5, 5, 2, 1, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (12, 1, N'B', 10, 1, 1, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (13, 1, N'B', 10, 2, 1, 1, 0, 1 ) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (14, 1, N'B', 10, 3, 1, 2, 0, -1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (15, 1, N'B', 10, 4, 2, 0, 0, 1) Thanks.
View Replies !
Using Results From Stored Procedure In WHERE Condition
Hi, I'm trying to write a nested stored procedure, with the outer proc named "spAssociate", and inner proc named "spSales". So far I have created the inner proc CREATE PROCEDURE spSales@userID intASSET NOCOUNT ON SELECT SalesOppID FROM Sales_Opportunities WHERE SalesOppCurrentStatus NOT IN ('Sale Lost','Sales Closed','Sale Closed','Unqualified','Deferred','Dropped') AND OppOwnerUserID = @userIDGO This was successfully created. I wanted to use the return set in the outer proc, which I tried creating as: CREATE PROCEDURE spAssociate(@userID int, @containerType varchar(100), @associateType varchar(100))AS SET NOCOUNT ON SELECT AssociateID FROM AppRelations WHERE ContainerType=@containerType AND ContainerID IN (EXECUTE spSales @userID) AND AssociateType=@associateType GO I get an error "incorrect syntax near execute". How can I use the results from the inner proc for the WHERE condition in my outer proc? Any help is greatly appreciated. Thanks!
View Replies !
Insert Into Temp Table Based On If Condition
hello all,this might be simple:I populate a temp table based on a condition from another table:select @condition = condition from table1 where id=1 [this will giveme either 0 or 1]in my stored procedure I want to do this:if @condition = 0beginselect * into #tmp_tablefrom products pinner joinsales s on p.p_data = s.p_dataendelsebeginselect * into #tmp_tablefrom products pleft joinsales s on p.p_data = s.p_dataendTha above query would not work since SQL thinks I am trying to use thesame temp table twice.As you can see the major thing that gets effected with the condictionbeing 0/1 is the join (inner or outer). The actual SQL is much biggerwith other joins but the only thing changing in the 2 sql's is the joinbetween products and sales tables.any ideas gurus on how to use different sql's into temp table based onthe condition?thanksadi
View Replies !
Check Certain Row Of Record Based On Special Condition
let say i have such data in table ------------------------------- Number | Descriptions 5 | xxxxxxx 4 | xxxxxxx 3 | xxxxxxx 2 | xxxxxxx 1 | xxxxxxx 0 | xxxxxxx <- 5 | xxxxxxx <- 4 | xxxxxxx anyway to select 0 | xxxxxxx 5 | xxxxxxx out? based on the number of next row is 5, and current number is 0. because i need to description data.
View Replies !
Showing An Image Based On A Boolean Condition
I have a table object attached to a dataset, One of the columns is based on a boolean value(bit field) from the database. I have a check picture and an unchecked picture. How can I show the selected image based on the result? links or ideas welcome
View Replies !
How To Make Table Row Invisible Based On Certain Condition
HI I have the following scenario in my report. -The data is displayed in a table -The table groups by one field -Each table row calls a subreport -There is about 6 paramaters in the report -The last paramater of the list of paramters is a multivalue paramater and based on what is selected in the list the corresponding subreport must be shown. -So i use a custom vbscript funtion to determine if a specific value was selected or not. This functionality is working fine. My problem is if the user does not select all the values in the multi select then i want to make the row invisble and remove the whitespace so that there is not a gap between the other subreports which is shown. I can make the subreport invisible inside the row but there is still the white space which does not display very nicly. How can i make the row invisible if the vbscript function that is called returns a false value? Here is the funtion I call -> Code.InArray("ValueToSearchFor", Parameters!MultiValueDropDown.Value) The Function returns a true or false. Thanks.
View Replies !
Getting Column Names And Its Values Based On Condition
Hi, I have a table as follows Table Master { Id varchar(20), Cat1 datetime, Cat2 datetime, Cat3 datetime, Cat4 datetime } and the data in the table is as follows Table Master { Id cat1 cat2 cat3 cat4 ----------------------------------------------- 1 d11 null d13 d14 2 d21 d22 d23 d24 3 NULL d32 d33 d34 4 d41 d42 NULL NULL } I want to retrive column names and its values wheb the ID matches to some value. Can any one please let me know how to do this? Thanks alot ~Mohan
View Replies !
How To Generate Condition Based Aggregate/summary Data
Hi I have a source table with App_Id, App_Type , Tier_Type, Date_Id as columns. I have a destination table with Date_Id, Total_App_Count, Booked_App_Type_Count, Approved_App_Type_Count columns. I need to count the Total number of records , count of records with App_Type = "Booked" and count of records with App_Type = "Approved" and populate in my destination table. What all the steps i need to create in SSIS to get this results ? Thanks Kumaran
View Replies !
How To Make Subreport Visible Based On Parameter Condition?
Hi, I have a subreport added to the main report and I want to make this report visible only when the parameter value is met. Ex, I have a parameter CustName in the main report and want to show the subreport when the custName = xxxxx. There reports are parameter driven not data driven reports. Any help is greatly appreciated. Thanks, Sirisha
View Replies !
Adding A Column Name To A Table In Each Of The Databases Based On A Condition
i have the folowing databases DB1,DB2,DB3,D4,DB5........ i have to loop through each of the databases and find out if the database has a table with the name 'Documents'( like 'tbdocuments' or 'tbemplyeedocuments' and so on......) If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in that database and there can be more than 1 'Documents' table in a database. can someone show me the script to do it? Thanks.
View Replies !
Inserting Values Intoto Only Column Based On A Condition
Hi, I have at table as foolows Table Cat3 { ID, Update datetime } and also have a master table as follows Table Master { ID, Cat1 Datetime, Cat2 datettime } My requirement is to alter themaster table schema i.e to add a column with the name as of the table name i.e Cat3 and will lok lie as foolows Table Master { ID, Cat1 Datetime, Cat2 datettime, Cat3 Datetime } I would like to insert the data to this column. The sample output is as follows Before insertng the data of table ;Cat3' into the Master table Table Master { Id Cat1 Cat2 --------------------------------- 1 D1 D2 2 D2 NULL 3 D3 D4 } And The Cat3 table data is as follows Table Cat3 { ID Update -------------------------- 1 D5 3 D6 4 D7 } The final putput of the master table should be as follows Table Master { Id Cat1 Cat2 Cat3 ------------------------------------------------------ 1 D1 D2 D5 2 D2 NULL NULL 3 D3 D4 D6 4 NULL NULL D7 } Can any one please let me know the query to achieve this Thank you very much for your time and support ~Moahn
View Replies !
Delete Multiple Rows One At A Time Based On A Condition
Hi, I have the following scenario : CustomerDetail customerid customername status app_no [status = 0 means customer virtually deleted] CustomerArchive archiveno [autoincrement] customerid customername status At the end of the month, I have to physically delete customers. I have written two stored procs: proc1 create proc spoc_startdeletion as declare @app_no int select @app_no = (select app_no from customerdetail where status=0) EXEC spoc_insertcustomerarchive @app_no -- After transferrin, physically delete delete from customerdetail where status=0 proc2 create proc spoc_insertcustomerarchive @app_no int as insert into customerarchive(customerid,customername,status) select customerid,customername,status from customerdetail where app_no = @app_no It works fine if there is only one row with status=0, however the problem is that when there are multiple rows in customerdetail with status=0, it returns 'Subquery returned more than one value' How can i transfer multiple rows one by one from the customerdetail to customerarchive and then delete the rows once they are transferred. Vidkshi
View Replies !
Trying To Create A Proc That Will Insert Values Based On A Condition That Is Another Table
Can someone give me a clue on this. I'm trying to insert values based off of values in another table. I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year). Two questions 1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great. 2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated. Thanks in advance, bellow is the code that I have so far: RB Select Results.custID, Results.PledgeLastYr From Results, PledgeInLastYear Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear Insert Into Results(PledgeLastYr) Values ('Y')
View Replies !
HELP With SQL Query: Select Multiple Values From One Column Based On &&<= Condition.
Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation: I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner: (AISC_Shapes_Table) AISC_MANUAL_LABEL W W44x300 300 W42x200 200 (and so on) WT22x150 150 WT21x100 100 (and so on) MT12.5x12.4 12.4 MT12x10 10 (etc.) I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is: Code SnippetSELECT AISC_MANUAL_LABEL, W FROM AISC_Shape_Table WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%')) It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance. Regards, Steve G.
View Replies !
An Easy Way To Reference The Nth Row From A Table Meeting A Condition X, And The Mth Row From The Same Table Meeting Condition Y
Hi 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, Int1 int NOT NULL ) GO CREATE PROCEDURE Initialise AS BEGIN INSERT INTO InitialisedValues (Int1 ) SELECT 2 INSERT INTO InitialisedValues (Int1 ) SELECT 4 INSERT INTO InitialisedValues (Int1 ) SELECT 8 INSERT INTO InitialisedValues (Int1 ) SELECT 16 END GO EXEC Initialise /*=======================================================*/ CREATE TABLE AllRows ( AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY, Int1 int NOT NULL ) GO CREATE TABLE RowsToChange ( RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY, Int1 int NOT NULL ) GO CREATE PROCEDURE InitialiseRowsToChange AS BEGIN INSERT INTO RowsToChange (Int1 ) SELECT 2 INSERT INTO RowsToChange (Int1 ) SELECT 4 INSERT INTO RowsToChange (Int1 ) SELECT 8 INSERT INTO RowsToChange (Int1 ) SELECT 16 END GO EXEC InitialiseRowsToChange GO CREATE PROCEDURE PopulateAllRows AS BEGIN INSERT INTO AllRows (Int1 ) SELECT 1 INSERT INTO AllRows (Int1 ) SELECT 1 INSERT INTO AllRows (Int1 ) SELECT 2 INSERT INTO AllRows (Int1 ) SELECT 3 INSERT INTO AllRows (Int1 ) SELECT 5 INSERT INTO AllRows (Int1 ) SELECT 8 INSERT INTO AllRows (Int1 ) SELECT 13 INSERT INTO AllRows (Int1 ) SELECT 21 INSERT INTO AllRows (Int1 ) SELECT 34 INSERT INTO AllRows (Int1 ) SELECT 55 INSERT INTO AllRows (Int1 ) SELECT 89 INSERT INTO AllRows (Int1 ) SELECT 144 INSERT INTO AllRows (Int1 ) SELECT 233 INSERT INTO AllRows (Int1 ) SELECT 377 INSERT INTO AllRows (Int1 ) SELECT 610 INSERT INTO AllRows (Int1 ) SELECT 987 INSERT INTO AllRows (Int1 ) SELECT 1597 INSERT INTO AllRows (Int1 ) SELECT 2584 INSERT INTO AllRows (Int1 ) SELECT 4181 INSERT INTO AllRows (Int1 ) SELECT 6765 END GO EXEC PopulateAllRows GO SELECT * FROM AllRows GO WITH Temp(OrigID) AS ( SELECT OrigID FROM (SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows) AS FromTable INNER JOIN RowsToChange AS ToTable ON FromTable.RowScore = ToTable.Int1 ) UPDATE AllRows SET Int1=1000 FROM Temp as InTable JOIN Allrows as OutTable ON Intable.OrigID = OutTable.AllRowsID GO SELECT * FROM AllRows GO USE master GO CREATE DATABASE ComplexCertainRowsToChange GO USE ComplexCertainRowsToChange GO CREATE TABLE InitialisedValues ( InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY, Int1 int NOT NULL ) GO CREATE PROCEDURE Initialise AS BEGIN INSERT INTO InitialisedValues (Int1 ) SELECT 2 INSERT INTO InitialisedValues (Int1 ) SELECT 4 INSERT INTO InitialisedValues (Int1 ) SELECT 8 INSERT INTO InitialisedValues (Int1 ) SELECT 16 END GO EXEC Initialise /*=======================================================*/ CREATE TABLE AllRows ( AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY, Int1 int NOT NULL, Type1 int NOT NULL ) GO CREATE TABLE RowsToChange ( RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY, Int1 int NOT NULL, Type1 int NOT NULL ) GO CREATE PROCEDURE InitialiseRowsToChange AS BEGIN INSERT INTO RowsToChange (Int1,Type1 ) SELECT 2, 1 INSERT INTO RowsToChange (Int1,Type1 ) SELECT 4, 1 INSERT INTO RowsToChange (Int1,Type1 ) SELECT 8, 1 INSERT INTO RowsToChange (Int1,Type1 ) SELECT 16, 1 END GO EXEC InitialiseRowsToChange GO CREATE PROCEDURE PopulateAllRows AS BEGIN INSERT INTO AllRows (Int1, Type1 ) SELECT 1, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 1, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 2, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 3, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 5, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 8, 2 INSERT INTO AllRows (Int1, Type1 ) SELECT 13, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 21, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 34, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 55, 2 INSERT INTO AllRows (Int1, Type1 ) SELECT 89, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 144, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 233, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 377, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 610, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 987, 2 INSERT INTO AllRows (Int1, Type1 ) SELECT 1597, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 2584, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 4181, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 6765, 1 END GO EXEC PopulateAllRows GO SELECT * FROM AllRows GO WITH Temp(OrigID) AS ( SELECT OrigID FROM (SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows WHERE Type1=1) AS FromTable INNER JOIN RowsToChange AS ToTable ON FromTable.RowScore = ToTable.Int1 ) UPDATE AllRows SET Int1=1000 FROM Temp as InTable JOIN Allrows as OutTable ON Intable.OrigID = OutTable.AllRowsID GO SELECT * FROM AllRows GO
View Replies !
Condition (if / Else) In SQL?
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.
View Replies !
Where Condition
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.
View Replies !
Where Condition
hi i want to give .text filed of a TextBox to where condition but the result is not correct. The SQL is like following: selStr = "SELECT COUNT(*) FROM Users WHERE (Name = usernameTextBox.Text) and (Password = passwdTextBox.Text)"
View Replies !
Only NOT Condition In CONTAINS
Hi All, I have stored Candidates Resume in binary(Image) format in database. I have a search feature to search resume. For Example: with all these words: ASP.Net, SQL Server, 2 Years without these words: Java Here I get Candidate which CONTAINS("ASP.Net" AND "SQL Server" AND "2 Years" AND NOT "Java") Now I want to search candidates only without these words: Java it means the condition is CONTAINS(NOT Java) is this possible? if yes, how? Thanks in advance.
View Replies !
WHERE Condition
Hi, 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'
View Replies !
No/any Condition
I have a query something like select a, b, c from mytable where a=@prmA 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? Regards, Hakan
View Replies !
And / Or Condition
hi, 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 :) thank you for any suggestions!
View Replies !
CONDITION
Hi,I want to write a SQL stmt.(condition) that checks the following @ActionPLanID > 0 and ActionPlanID exists in table A simultaneously? I was thinking of using IF NOT exists but don't know how to write it. Am I going in the right direction? Thanks, VBJP
View Replies !
Search Condition
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
View Replies !
IN CONDITION QUESTION
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 ?
View Replies !
Copy On Condition
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?
View Replies !
Exclude Condition
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.
View Replies !
MS SQL IF ELSE Condition Checking
Hello,I have Table1 with column Email, which has mail addresses in theformat 'useremailid@'. Few of these Email values are NULL where nomail address was specified.In my View1 I'm using SUBSTRING like... Left(Email,CHARINDEX('@',Email)-1) AS EMAIL_NAME (to cut out the @ sign) FROM Table1 and getjust the usermailidAnyway, what I would like to do is use the above to get all theusermailids and have a condition that checks if Email field IS NULLand if TRUE replaces it with blank value ''So something like IF (Email IS NULL) THEN Email = ''I've never really used IF ELSE in a query and would be gratefull ifsomeone could help me out. I don't want to leave out the NULL valuesusing WHERE Email is NOT NULL, I just want to convert them to an emptystring ''Many thanks in advance :-)Yas
View Replies !
Condition With Group By
Data:PROJ PLAN TOTTIME UNITA P1 10 DAYA P2 10 HOURA P3 1 MONTHWHEN I'M DOING GROUP BY ON PROJAND CALCULATING TOTTIME IT CONSIDER ONE OF THE UNIT I.E. DAY, HOUR, MONTHI WANT TO SUMUP ALL WITH HAVING UNIT CALCULATION ALSO.10 DAY=10 DAYS10 HOUR=1.25 DAYS1 MONTH=20 DAYSTHE RESULT SHOULD BE LIKE THIS:PROJ PLAN TOTTIME (IN DAYS)-------------------------------A ALL 31.25-------------------------------THANKS IN ADV.T.S.NEGIJoin Bytes!
View Replies !
IF Condition In Join??
Dear GroupI'd be grateful if you can send me on the right track in achieving this.I have three tables A,B,C outlined as follows:Table: AField: RowIDField: EntityIDField: TypeIdentifierTable: BField: RowIDField: NameTable: CField: RowIDField: NameLet's assume I've the following records:Table A:1,1,02,1,1Table B:1,SmithTable C:1,XYZCorporationThe table joins are as follows:A.EntityID = B.RowIDA.EntityID = C.RowIDI would like to select all records from Table A and display the Names fromeither Table B or Table C, depending on the Field TypeIdentifier.E.g.: SELECT Name FROM A JOIN B ON (A.EntityID = B.RowID) JOIN C ON(A.EntityID = C.RowID) IF TypeIdentifier = 0 SELECT Name FROM B IFTypeIdentifier = 1 SELECT Name FROM CResultset:SmithXYZCorporationIs this somehow possible?Thanks very much for your time & efforts!Martin
View Replies !
SQL Query Condition
Hi, I am creating a stored procedure which takes a few paramaters. One of which is a string of comma separated codes. At the end of my select query the codes will be used in the where clause. What I'm doing is parsing the comma separated string and populating a temp table called #codes. So my query will look something like this: select * from tableA where tableA.col1 = 'something' and tableA.code in (select * from #codes) and.... However, the code paramater can be null and if this is the case I want the query to be called for all codes - i.e. I effectively want to remove the and tableA.code in (select * from #codes) part of the where clause. Is there any clever way of doin this other than having a if... else... and writing 2 queries - one with the and and one without it? Hope this is clear. Thanks, Wallace
View Replies !
MDX - Exclude Condition
I have to built a query to get the % for all the Region (Americas, Asia and Europe) from a cube. But in these regions some countries are excluded and treated seperate. Like Asia does not include India and Japan. How do I get the ASIA query using an EXCLUDE condition. Please help.
View Replies !
Using Alias In Where Condition
Can we do Select BookNo as Catalog from Books where Catalog = 12356 I need to find the way to use alias in "where" for very complex query Is anyone has way around it ? Thank you
View Replies !
Using Where Condition With Dates
I'm trying to make sql that check date in database Select ... From .... Where ((id=1) and (port=2) and (logdate=#12/31/2001#)); I'm getting 0 records even if its exist. I know the problem is in the "logdate=#12/31/2001#" What is the problem and why the sql ignores it ?
View Replies !
Where Condition Using Variable
Hi, I've come across a funny when using variables within a WHERE clause in a multiple JOIN statement. I have the following tables: Articles table ============== Article_ID int PK IDENTITY(1,1) Title varchar(128) Article_Date datetime Author_ID int FK REFERENCES Authors(Author_ID) Archive char(1) WaitingForConfirm char(1) Authors table ============= Author_ID int PK IDENTITY(1,1) Author_Name varchar(64) TextArticles table ================== ContentRow_ID int PK IDENTITY(1,1) Article_ID int FK references Articles(Article_ID) Content varchar(2048) Below is an extract from a fairly complex stored procedure used to search based on a variable number of values including Author, Title and Content. SELECT Articles.Article_ID , Articles.Title , Articles.Article_Date , Authors.Author_Name FROM Articles LEFT OUTER JOIN Authors ON Articles.Author_ID = Authors.Author_ID LEFT OUTER JOIN TextArticles ON Articles.Article_ID = TextArticles.Article_ID LEFT OUTER JOIN FileArticles ON Articles.Article_ID = FileArticles.Article_ID WHERE Articles.Article_Date BETWEEN @startdate AND @enddate AND (Articles.Archive > 'Y' OR Articles.Archive < 'Y') AND (Articles.WaitingForConfirm > 'Y' OR Articles.WaitingForConfirm < 'Y') AND Articles.Title LIKE @title AND authors.author_name LIKE @author AND TextArticles.Content LIKE @content ORDER BY Articles.Article_Date DESC If any of these variables are passed in as NULL I convert to '%' and any variable that is not null is altered to '%value%'. The problem arises with the @content variable - which is varchar(128). The following variables are passed in: @startdate = 'JAN 01 2000' @enddate = GETDATE() @author = 'smith' - which is changed within the procedure to '%smith%' @title = NULL - which is changed within the procedure to '%' @content = NULL - which is changed within the procedure to '%' The procedure returns 0 rows based on these criteria - even though if I hard- code these values the expected number of rows are returned. I have used various print statements throuout the procedure and the @content variable is always correctly established. I have come across this before with another table/column/join combination and am at a loss to explain it. Any help would be greatly appreciated. Many Thanks . . Greg
View Replies !
Between And In Search Condition
hi, if i use the following query, isn't it supposed to output same amount of records? select... from... where invoice.billcycle in (104,204,304) and select ... from ... where invoice.billcycle between 104 and 304 for some reason, i get 38 from the first and 7 from the second query. thanks for the help in advance
View Replies !
Stuck With OR Condition
Hi, 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. Thanks in advance, Peter
View Replies !
Somewhat Dynamic Where Condition
I'm using sql server 2000. I'd like to create a stored procedure that has a somewhat dynamic where condition. I'd like to do sth like this: ALTER PROCEDURE [dbo].[sp_Situation](@type_data as integer )As ... declare @where_cond varchar(20) if @type_data = 1 set @where_cond = 'A, B, C' else set @where_cond = 'A' select col1, col2, col3... from table_1 where col1 in (@where_cond) A, B and C are single values (col1 contains or A or B or C, not a string 'A, B, C'). Is it possible to do sth like that?
View Replies !
Like Condition In Sql Statement
This is my sql query Select DOJ AS 'JoiningDate' from emp where DOJ like '%2008/09/04%' there are 8 records on this time.But it displays no result . The column is datetime datatype year month date or we need to mention any thing other than this.
View Replies !
If Null Condition
I have a this code SELECT @maxid = MAX(id) FROM Quote now i need to check if @maxid is null.. if so i want to set it as 1. how do i do this
View Replies !
How To Write This Condition?
Hi, I am trying to set up a conditional split. As usual, my books do not explain what I am trying to do :-) flat file source --> conditional split (if Column0 contains a certain string send to error file) In the Conditional Split Transformation Editor I have the following for "condition": FINDSTRING( "my search string", [Column0] ,1 ) TITLE: Microsoft Visual Studio ------------------------------ Error at myPkg [Conditional Split [8259]]: The expression "FINDSTRING( "my search string[Column0] ,1 )" is not Boolean. The result type of the expression must be Boolean. Error at myPkg[Conditional Split [8259]]: The expression "FINDSTRING( "my search string[Column0] ,1 )" on "output "Case 1" (8351)" is not valid. Error at myPkg [Conditional Split [8259]]: Failed to set property "Expression" on "output "Case 1" (8351)". As you can see I want it to search Column0. Can someone please point out my error. Thanks
View Replies !
How To Check A Condition
hi as i am a beginner to ssis. i have tried a lot to solce a logicbut it did not work.the situation is as i have a view which is updated dynamically whenever thebase table gets updated. then from view i hvae to select the new inserted row with the order id as identity column. there is a column in a table as salespersonid i have to check if the value is null or not.if its null then i have to assign a sales personid from the table havind the sales person detail. if its already in the row then i have to create a xml file of that data and send file so,e location. i have created a stored procedure to work on the creation of a xml file. but i hve tried a lot about how to check the value of the salepersonid which may be null or anu number and deceide to do one of the above operation. i have to do it in ssis. i am stuck on it. can any one have any idea.on proceeding it. thanks in advance.
View Replies !
|
TRACKER
