I have a customer table with say id, firstname, lastname. I also have another table called phone number that contains multiple phone numbers based on the customer id (id, customerid, phonenumber).
Ok I want to output a record like this
firstname, lastname, phone1, phone2, phone3
I need one record per customer with as many phone numbers as they may have, I could always limit the numbers to 3 or better yet return a null if there are less then the predefined number of phone numbers.
If I try a simple join I get multiline output which is no good for me.
SELECT firstname, lastname, phonenumber
FROM customer, phone
WHERE phone.customerid = customer.id
I am a sql near novice and have a problem in a project I am working on. The exact details of the tables would be too confusing to explain here so I'll use a hypothetical case,
I have a table with 4 columns reccid (int,pk), apples (int),pears(int),oranges (int), lemons (int), totfruit (int), totcitrus(int)
I have a trigger that whenever I update apples,pears,oranges or lemons columns the totfruit and totcitrus are updated with the totals. --------------------- after update as BEGIN SET NOCOUNT ON; update fruit set totcitrus=lemons + oranges, totfruit=apples+pears+lemons+oranges END ---------------------
This works fine for single row updates, but if I run a stored procedure against the table such as;
update fruit set lemons=lemons*2
Then I ger an error message of Subquery returned more than 1 value.
I know I havent written the trigger correctly for multirow updates but dont understand enough to see what I have to do, any advice would be very welcome, also can anyone recommend a suitable book.
In transact-Sql (Sql Server 2000), this "REPLACE" keyword means something different. What it meant in MySQL is that if there is an existing row that has the same primary key value as one of the new rows being inserted, it will replace that old row with the new row.
This meant in MySQL is that if there is an existing row that has the same primary key value as one of the new rows being inserted, it will ignore that new row's insert and keep the values of the old row.
I am trying to implement trigger which can handle multirow updates and which is running on replicated table. So I want it never fails as trigger failure brakes replication.
So:
CREATE TRIGGER on_person_update
BEGIN
-- create temp table
-- populate temp table with Inserted values (I do not need Deleted as PK never change)
COMMIT TRAN
-- Am I right that this insures updates on replicated table will never be rollback after this commit?
BEGIN TRAN A
-- Make a checkpoint here to be able to rollback at any time to this point if something wrong inside loop.
SAVE TRAN MyTran
-- Start looping in temp table
-- RUN DML statement to make neccesary changes for each record in temp table
-- Does it make any sense to do this (IF @ERR below)? When I am trying in DML insert string value into integer column it never gets to IF statement - terminates straight away.
-- Reason why I think I need it as this trigger might be called by another trigger and top level trigger will get an error and can make a decision based on this.
IF @ERR <> 0 BEGIN ROLLBACK TRAN MyTran RAISERROR('Insert or Update failed in on_person_sls_update trigger with error: %s', 16, 1, @ERR) RETURN END
-- End looping temp
-- Do I need here COMMIT TRAN A or trigger will make commit anyway?
END
Why all of this?
Data changed on distributor and arrive to subscriber as a transaction.
We have a trigger on replicated table which will update replicated table in any way but after that it will update another database on subscriber.
This trigger should be able to handle multirow updates.
When this trigger updates another database it runs DML which fires other triggers so they become nested, if I am right. Our trigger should always accept changes from distributor as if it fails replication brakes but after data saved in temp table none or all changes have to be made.
May be I am copmpletely wrong with this template - hope somebody will help.
This is the Trigger which is not working properly during Update, no anyrecord is going to be updated so pls help.I am updating the requisition table when any update in quantity inpodetails tableCREATE TRIGGER trig_updateRequistion ON ERP.DBO.TranPurchaseOrderDetailINSTEAD OF UPDATEASIF UPDATE(Quantity)BEGINUpdate RequisitionSlipDetailset RequisitionSlipDetail.PoQuantity =(Select PoQuantity from RequisitionSlipDetail whereItemCode=(Select CAST(i.ItemCode as nvarchar(20)) from inserted as i)andRSlip_No=(Select CAST(i.RSlip_No as int) from inserted as i))-((Select Quantity from TranPurchaseOrderDetail where Purchase_OrderNo=(Select CAST(i.Purchase_OrderNo as nvarchar(20)) from inserted as i))- (Select CAST(i.Quantity as int) from inserted as i))where RequisitionSlipDetail.ItemCode = (Select CAST(i.ItemCode asnvarchar(20)) from inserted as i) and RequisitionSlipDetail.RSlip_No =(Select CAST(i.RSlip_No as int) from inserted as i)Update TranPurchaseOrderDetail setTranPurchaseOrderDetail.Quantity =(Select CAST(i.Quantity as int) from inserted as i)where TranPurchaseOrderDetail.Purchase_OrderNo = (SelectCAST(i.Purchase_OrderNo as nvarchar(20)) from inserted as i)andTranPurchaseOrderDetail.ItemCode = (Select CAST(i.ItemCode asnvarchar(20)) from inserted as i)andTranPurchaseOrderDetail.PurchaseDetailId =(Select PurchaseDetailId from TranPurchaseOrderDetail wherePurchase_OrderNo = (Select CAST(i.Purchase_OrderNo as nvarchar(20))from inserted as i))END
Another question for all you SQL experts, I have a lot of them. I am trying to select from a table wher some conditions need to be met based on an employee ID. What I am doing is when the rank is a 1,2, or 3 I pick up the text description of that rank. Can I make it so that I get the ID only once and all the text descriptions are on the same line. Here is the sql script along with my current output and my desired output.
--SQL SCRIPT__
select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3')
--CURRENT OUTPUT--
empID Rank Skills ------- ---- -------------------------------------------------- 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer 2924 3 Create Spreadsheet 2924 3 Joining Tables in a Query
--DESIRED OUTPUT--
empID Rank Skills ------ ---- ------------ 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet
In my previous post I asked how to do the bottom question. I got a response to use a cursor, now I made an attempt to use a cursor but I still get the same response. Any help will be greatly appreciated.
--CURRENT OUTPUT--
empID Rank Skills ------- ---- -------------------------------------------------- 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer 2924 3 Create Spreadsheet 2924 3 Joining Tables in a Query
--DESIRED OUTPUT--
empID Rank Skills ------ ---- ------------ 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet
--Here is the cursor script.--
Declare @skills varchar(255),@skills2 varchar(255),@message varchar(255),@empID varchar(255), @Rank varchar(255) DECLARE emp_skills CURSOR For select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924'
DECLARE emp_skills2 CURSOR For select B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' OPEN emp_skills OPEN emp_skills2 FETCH NEXT FROM emp_skills into @empID, @Rank, @skills FETCH NEXT FROM emp_skills2 into @skills2 WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = @skills2 FETCH NEXT FROM emp_skills2 into @skills2 Print @empID + ' '+ @Rank + ' ' + @message
FETCH NEXT FROM emp_skills into @empID, @Rank, @skills
End CLOSE emp_skills DEALLOCATE emp_skills CLOSE emp_skills2 DEALLOCATE emp_skills2
--Previous Post--
Another question for all you SQL experts, I have a lot of them. I am trying to select from a table wher some conditions need to be met based on an employee ID. What I am doing is when the rank is a 1,2, or 3 I pick up the text description of that rank. Can I make it so that I get the ID only once and all the text descriptions are on the same line. Here is the sql script along with my current output and my desired output.
--SQL SCRIPT__
select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3')
--CURRENT OUTPUT--
empID Rank Skills ------- ---- -------------------------------------------------- 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer 2924 3 Create Spreadsheet 2924 3 Joining Tables in a Query
--DESIRED OUTPUT--
empID Rank Skills ------ ---- ------------ 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet
Hi friends, I want to select only filename column of sp_helpdb's output.But I don't know how should I do this? Other way is doing a direct select from sysfiles or sysdatabases but as you know microsoft doesn't recommand this way. Also I can select that filed in my application level but it is strange for me: Of course sql server should has a proper way to do a select from output curosr of this procedure,Doesn't it? -Thanks in advance
I'm trying to get the data out of the database in a columed format, I would be quite comfortable doing this if 'in' and 'out' where there own field. The output i'm trying to get is:
name |data |data |
test1 |123 |456 test2 |123 |456 test3 |123 |456
in an ideal world i'd like to be able to have the header the name of the field data - eg
name |in |out |
test1 |123 |456 test2 |123 |456 test3 |123 |456
I can use - select name,field1data from table where field1 in ("in","out")but this returns each piece of data as a new row - eg:
hi guys!it's very very simple question for you mighty sql DBAs. but very hardfor a developer like me who is very very new to MS SQL.anyways the problem is i want to use one SPs out to in the SELectstatement. here is an example :select * from sp_tables tablename like 'syscolumns'please note that this is just an example. i'm using different SP but iwant to use in the same way.if anybody has anything to say. please write to me. i would be glade toread your repliesThanks,Lucky
Here is my problem: I have a strored procedure that inserts some records
into one table and then selects some records from another table at the end.
The stored procedure takes several parameters, first one of them is marked as
OUTPUT. I'm using it to return an id of the inserted record. The procedure is called from asp.net code with first parameter set as ParameterDirection.InputOutput (tried with just Output as well). Now for the problem: if the the select statement at the end returns 0 records everything works and i my first parameter contains the @@IDENTITY value from the insert statement like it is supposed to.
If the select statement at the end returns 1 or more records my output parameter is not updated at all and contains the same value as before the procedure was run. All the records are inserted correctly.
if i try to return the @@identity as a plain select statement instead of through the parameter i get System.DBNull.
I hope you can shed some light on this for me. Here is my stored procedure:
INSERT INTO INQUIRY (LibraryName, ContactName, Address, City, State, Zip, Phone, Email, Comment) VALUES(@libraryName, @contactName, @address, @city, @state, @zip, @phone, @email,@comment)
--i tried including this statement at the end as well but that did not do the
--trick either
select @inquiryId=@@IDENTITY FROM INQUIRY
set nocount on declare @separator_position int -- This is used to locate each separator character declare @objectId varchar(200) -- this holds each array value as it is returned
if(@productIds is not null) begin while patindex('%,%' , @productIds) <> 0 begin select @separator_position = patindex('%,%' , @productIds) select @objectId= left(@productIds, @separator_position - 1) INSERT INTO PRODUCT_INQUIRY_LOOKUP (ProductId,InquiryId) VALUES(@objectId, @inquiryId) select @productIds = stuff(@productIds, 1, @separator_position, '') end end set nocount off
Select Distinct Email from vPRODUCT_CONTACT WHERE ProductId in (Select ProductId From Product_Inquiry_Lookup Where InquiryId=@inquiryId)
I have a stored procedure where in a cursor I create a dynamic select statement. If I have a non-zero result set (check @@rowcount) I output a message. The problem is I do not know how to supress the output of the execute of the dynamically created select statement. All I want going to output is the message. I know ISQL has this function, and so does EM. How do I do it totally within a stored procedure.
I'm looking for some good hints and tips for reprogrammin an old VB module I just found.
Basically what it does, is receive an input parameter (an int), does a select [name row] from Names where Name_id = [input parameter] and turns this into a string if multiplenames appear.
E.g. result set: John, Josh, Jock turns it into string "John Josh Jock".
So its piece of cake creating a stored procedure selecting data on the base of an input parameter. Select X from Y where Z = @input... the trick is, I don't know how to do arrays in TSQL as in VB.
In the VB edition I create an array, load the names into it, I do a count on how many row the select returns and then a simple for... next adding the names to the string.
Any good examples on how to do this in a sql-server stored proc?
Thanks,
Trin
P.S. This is what I have pieced together this far:
DECLARE medlemcursor CURSOR FOR SELECT [MCPS Kode] FROM DW.dbo.names(NOLOCK) WHERE number = @number
OPEN medlemcursor
FETCH NEXT FROM medlemcursor INTO @tempinstt WHILE (@@FETCH_STATUS <> -1) BEGIN SET @instrument = @instrument + @tempinstt + '-' FETCH NEXT FROM medlemcursor INTO @tempinstt END
CLOSE medlemcursor DEALLOCATE medlemcursor
SELECT @instrument GO
Just doesn't seem to work, returns NULL, even though I've checked that the cursor SELECT statement actually returns data,
Hi All !Is it possible to get rid of these dash symbols which are underliningthe column name when recordset is returned after query execution ?For example, using isql.exe:SELECT 'blah'goproduces the following results:----blahWhat I want to achieve is justblahI know that SET NOCOUNT ON switches the "X row affected" thing. Buthow about column headers ?Thanks for your time,Seeker
HiI have a dynamically constructed sql query that I want to execute, e.g.exec('select * from ' + @tablename)(1) Can I suppress the output somehow if this returns no values?(2) Can I use the result of this query in another query somehow? e.g.select(3) Can I control the size of the columns in the output somehowThanksF
I have a dynamic database that will be periodically queried to selectthe data from a blob field. This blob data field is text of a variablelength. The data will be selected using an id field and a date range.There will be multiple blob fields returned that I would like to outputinto a txt file in a local folder.I have the blob fields showing up as text in the field and not areferring link. Can someone point me to an output to text solution?Thanks
In my program i have function that will get one value from Database. Here i want to assign the output of the sql query to a local variable. Its like select emp_id into Num from emp where emp_roll=222; here NUM is local variable which was declared in my program. Is it correct.? can anyone please guide me..?
I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible?
Do I need just a select statement to do this? What is the difference between the output and select statements?
the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed.
I was hoping a simple "delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)"
would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect?
this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so.
Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:
rowtype| ID | value A | 123 | alpha B | 123 | beta C | 123 | delta A | 124 | some val B | 124 | some val 2 C | 124 | some val 3 etc...
where for each ID, I have 3 rows that are associated with it and with different corresponding values.
I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.
i.e. each ID iteration will do something like: insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha') insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta') insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta') etc..
After my loop, I will just do a select * from #someTempTable
Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005
Hello, I am facing a problem in a SELECT clause which i cannot solve. In my SQL table ("myTable") i have a few columns ("Column1", "Column2", "TypeColumn"). When I select different columns of the table, instead of getting the value of TypeColumn, i would like to get a boolean indicating whether its value is a certain string or not. For example, the TypeColumn accepts only a number of selected strings: "AAA", "BBB", "CCC". when i do a select query on the table, instead of asking for TypeColumn i would like to ask a boolean value of 1 if TypeColumn is "AAA" and 0 if TypeColumn is "BBB" or "CCC". Also, i would like to make this query while I am also fetching the other columns. And i would like to use one query to get all that. I thought something like thsi would work:
SELECT Column1 AS Col1, Column2 AS Col2, IF(TypeColumn = "AAA", 1, 0) AS Col3 FROM myTable
but this doesn't work in SQL 2005! Is it possible to do something similar in SQL 2005 using one query only? i am trying to avoid multiple queries for this.
I have a routine that generates an HTML email and sends it just fine, but one of the columns ends up with 4 decimal places for a column datatype of money. How can I get the script to output only 2 decimal places for the amount column from the select statement?
Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.
Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)
Example: SELECT 'Asset', 'House', TotalPrice * -1 FROM Accounts WHERE AvgAmount > 0 UNION SELECT 'Balance', 'Cover', TotalPrice FROM Accounts WHERE AvgAmount > 0
What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0
I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it.
