SELECT
PIN,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 1' THEN VALUE ELSE NULL END) AS address1,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 2' THEN VALUE ELSE NULL END) AS address2,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 3' THEN VALUE ELSE NULL END) AS address3,
MAX(CASE WHEN HEADER = 'ACOMPANY' AND PROPERTY = 'Membership Number' THEN VALUE ELSE NULL END) AS MemberNo,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Preferred method of contact*' THEN VALUE ELSE NULL END) AS Tel1,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Tel number (o/h) e.g. 011 2690000' THEN VALUE ELSE NULL END) AS Tel2,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Mobile number e.g. 0821234567' THEN VALUE ELSE NULL END) AS Tel3,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Fax number e.g. 011 2691000' THEN VALUE ELSE NULL END) AS Tel4,
MAX(CASE WHEN HEADER = 'Date Of Birth' AND PROPERTY = 'eg. 04 Jan 1965' THEN VALUE ELSE NULL END) AS DOB,
MAX(CASE WHEN HEADER = 'Email address' AND PROPERTY = 'Email Address' THEN VALUE ELSE NULL END) AS Email,
MAX(CASE WHEN HEADER = 'ID Number' AND PROPERTY = 'ID Number' THEN VALUE ELSE NULL END) AS IDNumber,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Title' THEN VALUE ELSE NULL END) AS Title,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Initials' THEN VALUE ELSE NULL END) AS Initials,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Firstname' THEN VALUE ELSE NULL END) AS Firstname,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Surname' THEN VALUE ELSE NULL END) AS Surname,
MAX(CASE WHEN HEADER = 'ACOMPANY' AND PROPERTY = 'Membership Number' THEN STATUS ELSE NULL END) AS STATUS
FROM
FULLSOURCE
GROUP BY PIN
Now how can I reverse this to insert all the values back to tbl FULLSOURCE from maybe an updated SSOTARGET Tbl. FULLSOURCE looks like this:
I need to do a conditional insert. This is what I have tried, it does not work. What am I doing incorrectly?
IF (SELECT COUNT(*) FROM TBL1 INNER JOIN TBL2 ON TBL1.MODEL_ID = TBL2.MODEL_ID INNER JOIN TBL3 ON TBL1.PRO_TYPE = TBL3.TYPE INNER JOIN TBL4 ON TBL1.PRO_SITE = TBL4.WHDESC) > 0
INSERT INTO TBL5 SELECT TBL1.PRO_SITE, TBL1.MODEL_ID, TBL1.NUM_SLOTS, TBL1.TARGET_DAYS, GETDATE() AS Expr1, TBL3.TYPE_ID, NULL AS Expr2, TBL1.NUM_SLOTS AS Expr3, NULL AS Expr4, NULL AS Expr5, 0 AS RAMP FROM TBL1 INNER JOIN TBL2 ON TBL1.MODEL_ID = TBL2.MODEL_ID INNER JOIN TBL3 ON TBL1.PRO_TYPE = TBL3.TYPE INNER JOIN TBL4 ON TBL1.PRO_SITE = TBL4.WHDESC
UPDATE TBL5 SET TEAMS=0 GO UPDATE TBL5 SET TEAMS = (SELECT NUM_SLOTS FROM TBL6 R1 WHERE (R1.TEAM_ID = TBL5.TEAM_ID) ) WHERE (TEAM_ID = (SELECT TEAM_ID FROM TBL6 R3 WHERE (R3.TEAM_ID = TBL5.TEAM_ID))) GO UPDATE TBL5 SET TOTALTEAMS = TEAMS + RAMP GO
I need to do a conditional because sometimes the select that returns data contains no records.
Hello all. I am inserting data into a table from a linked server (.XLS file). One of the columns deals with dates. In the .XLS file some ofthe dates are invalid (either wrong format or wrond data). What i am looking for is how to insert only the rows where the dates are correct. My basic structure is:
INSERT INTO PENSIONS(EMPLOY_REF, CONTRIB_CHANGE) SELECT CAST(EMPLOY_REF AS VARCHAR(10)), CAST(CONTRIB_CHANGE AS DATETIME) FROM mockdownload_dd...[Pensions$]
I am new to MSSQL and am trying to write a complicated SQL statement that I'm having trouble with. Any help that anyone can offer is much appreciated!
Here is the problem I am tackling:
I have a list of about 5,000 members of our organization stored in the MemberList MSSQL table. I have a separate MSSQL table (CityList) that has approximately 500,000 resident of a city.
I am trying to find matches between MemberList and CityList for the purposes of figuring out which of our members are registered voters.
The tricky part of this problem, is that there is no unique ID (such as a social security number) that is present in each list. Accordingly, I have decided to created several types of matches:
1. NameDOBMatch: Where the FirstName, LastName & DOB fields in MemberList table match the same fields in CityList table.
2. NameAddressMatch: Where the LastName, FirstName & Address fields in the MemberList table match the same fields in the CityList table.
3. DoubleMatch: A combination of the first two matches (i.e. where the LastName, FirstName, DOB, & Address fields in the MemberList table match the same fields in the CityList table).
My goal is to "loop" through the MemberList and CityList tables and to add a new row to a third MSSQL table (MemberMatch) each time one of the aforementioned matches is found. The MemberMatch table has the following fields: 1. MatchID (key) 2. MemberID (Unique ID of member from MemberList table) 3. ResidentID (Unqiue ID of matching member from CityList table). 4. MatchType (value of NameDOBMatch, NameAddressMatch or DoubleMatch, depending on match type).
If anyone could help me create an SQL statement that would accomplish that, I would very much appreciate it!
Is there any way to create a "Conditional Insert Trigger" My Scenario is this; When a user adds an email address to the database, I want to look to see if the email address is like '%@acme-holdings%' and if it is then to change the value to 'Not allowed', otherwise to leave it alone and go ahead with inserting the original email address
I need to dynamic select a column in which insert a vale based on aparameter value, I have this code, but it throws an incorrect syntaxerror.How do I dinamically select a column to insert based on a parameter?Create PROCEDURE dbo.UpdateDetalleOT (@eotId int,)insert into OT (select Casewhen @eotId = 1 THEN OTFechaBorradorwhen @eotId = 2 THEN OTFechaAAsignarend) values ....Best RegardsFabio Cavassinihttp://www.pldsa.com
I've got a case where I am bringing older data into a brand new DB. Unfortunately, there are several versions of the old DB and I won't know ahead of time which one I will be running against. The data's basicaly the same, the table names are the same, but the column schema is NOT. Basiclly, I want to use a conditional on the Insert.
Code Block
CREATE TABLE TestTable (Col1 int NULL, Col2 varchar(50) NULL) GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG= 'TestDB' AND TABLE_NAME ='TestTable' AND COLUMN_NAME= 'Fred')
This results in an "Unknown column" error. What am I missing here and how can I accomplish it. I know that I COULD build a SQL string and Exec it, but that is such a PITA and not very elegant.
I need to do a conditional insert, and I am afraid that if I do it in 2 steps it will create bugs...I have a table called customers and a table called lockscustomers has 3 fields CustomerID, FirstName, and LastNamelocks has fields LockID, CustomerID, UserID, SessionID, and TimeStampI need to check if there are any records in Locks for a CustomerID and if there are none, I need to insert One.This is going to tell my application that this Customer record is locked so no one else can edit it, until the Lock record is deleted.I just don't want to make 2 trips, One to check if the Lock exists, then One to insert the Lock, since this could allow 2 locks to be created if the timing is correct.Any ideas or comments?
I have several places where I need to get the id (primary key) of a resource, inserting a row if the resource does not exist (i.e. an artificial key to be used as an FK for another table). I should probably change this varchar key lookup to use a hash index, but that is beside the point.
So the table is essentially like: CREATE TABLE MyLookup(id int identity primary key nonclustered, mykey varchar(256)); CREATE CLUSTERED INDEX mylookup_cidx_mykey ON MyLookup(mykey);
I see two main approaches for how I can do my get-id-with-insert-if-needed.
(Approach 1) DECLARE @id INT; SELECT @id = id FROM MyLookup WHERE mykey = 'some key value'; IF (@id is null) BEGIN
INSERT MyLookup ('some key value'); SET @id = SCOPE_IDENTITY(); END
(Approach 2) DECLARE @id INT; INSERT MyLookup SELECT 'some key value' WHERE NOT EXISTS (SELECT id FROM MyLookup WHERE mykey = 'some key value'); IF (@@ROWCOUNT = 0)
SELECT @id = id FROM MyLookup WHERE mykey = 'some key value'; ELSE
SET @id = SCOPE_IDENTITY();
From some quick tests in profiler, approach 2 seems to be a bit faster and have lower resource utilization. But I'm not sure if it maybe takes some more aggressive locks even in the unnecessary case where the mykey row value of 'some key value' already exists. Approach 2 also looks cleaner to me, but I don't mind a bit of extra code if it gives me better scalability through less lock contention.
Any tip on what is considered the best practice for a conditional insert like this, or a tip on how to get detailed lock info for a query? The lock info for profiler was all greek to me, it just had a hex value with each lock acquired/released, so I have no idea what it was telling me. Is my only solution to just run exhaustive tests and look at the perf numbers from the black box?
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
---Selection results SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expectedresult FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment
--Now here what i am trying to get, union the below two record to the above "selection Results" for only segment ='VEC_BAL' or segment = 'PPC_BAL'
--2 records SELECT l.segment , l.email , Addr1, City, ST, 999 as Code,'' fname, ''lname from #LIST l where email ='mmm@m.com' SELECT l.segment , l.email , Addr1, City, ST, 888 as Code, '' fname, ''lname from #LIST l where email ='lrk@m.com'
--- expected results please execute below block ( per each segment 2 records will insert if there is segment ='VEC_BAL' or 'PPC_BAL' ). If there is no Vec_Bal or PPC BAL then no additional inserts, if there is only VEC_BAL no PPC_BAL then only 2 records insert SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expctresults1 FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment union
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?
Is there a way in order to execute a subscribed report based on a certain criteria?
For example, let's say send a report to users when data exist on the report else if no data is returned by the query executed by the report then it will not send the report to users.
My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.
I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.
hello friends my one insert code lines is below :) what does int32 mean ? AND WHAT IS DIFFERENT BETWEEN ONE CODE LINES AND SECOND CODE LINES :)Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) Dim cmd As New SqlCommand("Insert into table1 (UserId) VALUES (@UserId)", conn) 'you should use sproc instead cmd.Parameters.AddWithValue("@UserId", textbox1.text) 'your value Try conn.Open()Dim rows As Int32 = cmd.ExecuteNonQuery() conn.Close()Trace.Write(String.Format("You have {0} rows inserted successfully!", rows.ToString())) Catch sex As SqlExceptionThrow sex Finally If conn.State <> Data.ConnectionState.Closed Then conn.Close() End If End Try MY SECOND INSERT CODE LINES IS BELOWDim SglDataSource2, yeni As New SqlDataSource() SglDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString SglDataSource2.InsertCommandType = SqlDataSourceCommandType.Text SglDataSource2.InsertCommand = "INSERT INTO urunlistesi2 (kategori1) VALUES (@kategori1)" SglDataSource2.InsertParameters.Add("kategori1", kategoril1.Text)Dim rowsaffected As Integer = 0 Try rowsaffected = SglDataSource2.Insert()Catch ex As Exception Server.Transfer("yardim.aspx") Finally SglDataSource2 = Nothing End Try If rowsaffected <> 1 ThenServer.Transfer("yardim.aspx") ElseServer.Transfer("urunsat.aspx") End If
I want to know how I can create conditional FROM WHERE clauses like below ..
SELECT X,X,X FROM CASE @intAltSQL > 0 Then Blah Blah Blah END CASE @intAltSQL = 0 Then Blah END WHERE CASE @intAltSQL > 0 Then Blah Blah Blah END CASE @intAltSQL = 0 Then Blah END
Im faced with the following design issue.. on my site there are different profiles: a city profile, a restaurant profile and a user profile. in my DB:City profiles are stored in tbCities cityID int PK shortname nvarchar(50) forumID int FK (...) Restaurant profiles are stored in tbRests restID int PK shortname nvarchar(50) forumID int FK (...) User profiles are stored in tbUsers userID int PK shortname nvarchar(50) forumID int FK (...) as you can see a single ID value (for CityID,restID or userid) might occur in multiple tables (e.g. ID 12 may exist in tbRests and in tbUsers)Each of these profile owners can start a forum on their profile. forumID in each of the above tables is a FK to the PK in tbForums:forumID intforumname nvarchar(50) (...) Now imagine the following: a site visitor searches ALL forums...say he finds the following forums:ForumID Forumname1 you opinion on politics2 is there life in space?3 who should be the next president of the USA? a user may want to click on the forum name to go to the profile the forum belongs to.And then there's a problem, because I dont know in which table I should look for the forum ID...OR I would have to scan all tables (tbCities,tbRests and tbUsers) for that specific forumid,which is time-consuming and I dont want that! so if a user would click on forumID 2 (is there life in space?) I want to do a conditional inner join for the tablecontainingforumID (which may be tbCities,tbRests or tbUsers) select tablecontainingforumID.shortname FROM tablecontainingforumID tINNER JOIN tbForums f ON t.ForumID=f.ForumIDwhere f.ForumID=2 I hope my problem is clear..any suggestions are welcome (im even willing to change my DB design if that would increase effectivity)
I encounter a T-Sql problem related to if conditional processing:The following script execute an insert statement depending on whether column 'ReportTitle' exists in table ReportPreferences. However it gets executed even when ReportTitle column is not present.Could anyone offer some advice? IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0) BeginINSERT INTO dbo.DefaultsSELECT FinancialPlannerID,ReportTitleFROM dbo.ReportPreferencesendGO
I have a stored procedure that performs a search function with params:@username nvarchar(50)@country nvarchar(50)and like 10 more.A user may provide values for these params optionally.So when the @username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)Currently my statement is:select username,country from myUsers whereusername=@username and country=@countryWith this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...What can I do to solve this?Thanks!
Hi, is it possible to do a conditional WHERE in T-SQL? I have a table with a column that consists of a reference that starts with either a single alpha character or two alpha characters followed by four numeric digits (the numeric portion is always unique but the alpha isn’t). E.g. A1234, AB1235, AB1236, C1237, HT1238. What I want to do is select a range of rows based on the numeric portion of this reference column. In other words I want to select say 50 rows starting from row 1000 (rows 1000 to 1050) regardless of whether there is one or two alpha characters preceding the numerics.The Stored procedure I have so far works (using COUNT for testing) for selecting a range of rows that has two alpha's at the start. However, if I simply add an OR to the WHERE to select rows where there is a single alpha in the reference column, when a single alpha reference is found it will fail the first logical check for two alpha's giving an error condition. Therefore, how can I incorporate a conditional WHERE using IF or some alternative method, so that it will also give me all the rows in the number sequence that start with either single or double alpha's within the same SELECT / WHERE statement?
Thanks for any help.ALTER PROCEDURE [dbo].[sp_Test]
( @startRef int, @endRef int )
AS
BEGIN
SELECT Count(*) FROM myTable WHERE ((SUBSTRING(Ref,3,LEN(Ref)-2) BETWEEN @startRef AND (@startRef + @endRef)))
I have an SqlDataSource that uses a value from the query string in the WHERE clause of the sql statement. The sql is something like this: SELECT * FROM myTable WHERE myfield = @myfield and I have the QueryStringParameter setup like this: <asp:QueryStringParameter Name="myfield" QueryStringField="myfield" /> What I need is for the sql statement to return all records in the case that "myfield" is not defined in the query string. How would I implement this? Thanks, Joshua Foulk
Hello all, my update statement works as expected, but lacks some conditional logic. How can I change the statement to not decrement qtyonhand if the quantity is 0? Additionally, I would need to return to the calling application something that would allow me to populate a label with a message to the user.. How can that be accomplished? Here is my sproc:CREATE PROCEDURE [webuser].[cssp_removeItem] @lblID int AS Update cstb_inventoryset qtyonhand = qtyonhand -1where Id = @lblIDGO Here is my app code: Try Dim cmd As SqlCommand = cn.CreateCommand cmd = New SqlCommand("cssp_removeItem", cn) cmd.CommandType = CommandType.StoredProcedure With cmd cmd.Parameters.Add("@lblId", SqlDbType.Int).Value = lblId.Text End With If Not cn.State = ConnectionState.Open Then cn.Open() End If cmd.ExecuteNonQuery() Catch ex As Exception Response.Write(ex.ToString) Finally If Not cn.State = ConnectionState.Closed Then cn.Close() cn = Nothing End If
Hi, [SQL 2005 Express] I would like a DropDownList to be populated differently depending on the selected value in a FormView. If the FormView's selected value (CompanyID) is 2, then the DropDownList should show all Advisers from the relevant Company. Otherwise, the DropDownList should show all Advisers from the relevant Company where the TypeID field is 3. Here is the SQL for case 1: SELECT AdviserID, AdviserName FROM Advisers WHERE (CompanyID = @CompanyID). Here's the SQL for case 2: SELECT AdviserID, AdviserName FROM Advisers WHERE (CompanyID = @CompanyID) AND (TypeID = 3). Here's my best (failed) attempt to get what I want: SELECT AdviserID, AdviserName FROM Advisers WHERE IF @CompanyID = 2 THEN BEGIN (CompanyID = @CompanyID) END ELSE BEGIN (CompanyID = @CompanyID) AND (TypeID = 3) END I've also tried: SELECT AdviserID, AdviserName FROM Advisers WHERE CASE @CompanyID WHEN 2 THEN (CompanyID = @CompanyID) ELSE (CompanyID = @CompanyID) AND (TypeID = 3) END and SELECT AdviserID, AdviserName FROM Advisers WHERE CASE WHEN (@CompanyID = 2) THEN (CompanyID = @CompanyID) ELSE (CompanyID = @CompanyID) AND (TypeID = 3) END I'd be very grateul to know (a) what the correct syntax for this is and (b) if it can be achieved using a parametised query, rather than a stored procedure. Thanks very much. Regards Gary
I'm wondering if one can designate a conditional foreign key that relates to one of many different tables depending on the "type" column in the foreign key's. My goal is to come up with some SQL code that will allow for this in a Create table statement. (By the way I'm using MS SQL-7 but I'm wondering if this can be done in general.)
I have two simple cases below that show illustrate what I'm trying to do: Thanks in advance, -JerryZZ
--------------------------------------------------------- Case 1: The foreign key relation is to a primary key
Table: Invoices -Fields: ---InvoiceID ..... (primary key) ---BilleeID ...... (fkey to Manfacturers.ManfID IF BilleeType=M) ...................(fkey to Distibutors.DistID IF BilleeType=D) ---BilleeType .....(constraint = M or D)
--------------------------------------------------------- Case 2: The foreign key relation is to a unique "not null" non-primary key
Table: InvoicesEmail -Fields: ---InvoiceID ..... (primary key) ---EmailAddress .. (fkey to Manfacturers.EmailAddress IF BilleeType=M) ...................(fkey to Distibutors.EmailAddress IF BilleeType=D) ---BilleeType .....(constraint = M or D)
Hi, ladies and gentelmen! Can you help me with following trouble: I got a table (let it be called SomeTable) in which there's one nullable field (let's call it SomeField) among many others. PRIMARY KEY for SomeTable is of INT IDENTITY type. Business rules are following: only records that have SomeField IS NULL can be deleted, so I need to perform conditional delete (for cases like DELETE SomeTable). I don't like idea about using SP here, so I tried to solve the task by means of trigger. However, when DELETE clause is used within a transaction and there're records affected by the trigger that don't match business rule, trigger uses ROLLBACK TRAN and then deletes only matching rows from target table. Everything works fine with our SomeTable, but not so fine with the transaction, because due to ROLLBACK TRAN statement in trigger body this transaction is rolled back (as it is described in documentation). But I don't wanna ALL my transaction rolled back! So, is there in SQL Server 7.0 any way to rollback only changes that caused trigger to fire? Something like ROLLBACK TRIGGER?
I would like to write the following (pseudo) stored procedure, but am having problem with the syntax:
Create Procedure spSort @IDContract nvarchar(10) @SortOrder int
SELECT * FROM Contracts WHERE IDContract = @IDContract IF @SortOrder = 0 BEGIN ORDER BY ContractDate END IF @SortOrder = 1 THEN BEGIN ORDER BY ShippingPeriod END
The problem is in conditionally setting the sort order. The actual sp is quite complex and I don't really want to have to use two procedures (one for ordering by ShippingPeriod and one for ordering by ContractDate
I am trying to write query that will select calls that only show up when the resolution returned from the case in the sub query is 'y'. When I do the group by some calls will show up twice because of a null and a 'y' resolution return. I want to only select a call if there is a 'y' that shows for all resolutions associated with that call. If there are any null resolutions for the call I don't want it to show up in my returned values. This is what I have so far, but I can't figure out how to make it drop both instances of the callID if one instance is null.
Code:
SELECT C.CallID, P.PrimarySupportGroupID, P.PrimaryTeamName, T1.Resolution, CASE C.CallID WHEN T1.Resolution IS NOT NULL THEN 'Y' ELSE 'N' END FROM HEAT.dbo.CallLog C, (SELECT CallID, CASE WHEN A1.Resolution = '' THEN 'Y' ELSE NULL END AS Resolution FROM HEAT.dbo.Asgnmnt A1) T1, HEAT.dbo.Profile P WHERE C.CallID = T1.CallID AND C.CustID = P.CustID AND (P.PrimarySupportGroupID = 'ATS') AND (P.PrimaryTeamName = 'Beta') GROUP BY C.CallID, P.PrimarySupportGroupID, P.PrimaryTeamName, T1.Resolution
If anyone could help me out with this or at least give me a little information and point me in the right direction I would really appreciate it.
