[SQL Server] exception handling: how would you do it? |
- exception handling: how would you do it?
- Combining fields of matching records
- Auto Increment Alphabet
| exception handling: how would you do it? Posted: 02 Oct 2013 08:44 AM PDT Hi all,I have a 3-part UNION ALL query. Sometimes, one of the tables are not available, meaning I can't connect to it.Currently, if one part of the query fails, the whole thing fails. How do I trap all possible exceptions, so even if any 1 or 2 parts fail, the rest will return a result.Here is the test data:[code="sql"]create table A (col_1 varchar(1), col_2 int);insert into A values('a',100);create table B (col_1 varchar(1), col_2 int);insert into B values('b',200);create table C (col_1 varchar(1), col_2 int);insert into C values('c',300);SELECTa.col_1 AS td, a.col_2 AS [td align=RIGHT]FROM( select col_1, col_2 from A union all select col_1, col_2 from B union all select col_1, col_2 from C ) aFOR XML RAW('tr'), elements[/code]How would you do it?Thanks, |
| Combining fields of matching records Posted: 02 Oct 2013 04:16 AM PDT I have a table that contains several fields, and some of the records are identical except for one of those fields. I'm trying to write a query to return a single row that contains each field plus a combination field representing the concatenated values that were unique to each row.For example: FIELD1 | FIELD2 | FIELD3 | FIELD4ROW1 | ABC DEF GHI JKLROW2 | ABC DEF GHI MNOAnd I want the output to look like:OUTPUT | ABC DEF GHI JKL,MNOI can do this in .NET code, but I was hoping to do this in the SQL. Is it possible?I've found this example, but haven't been able to make it work correctly and don't really understand all of it:SELECT p.ASSIGNNUM,p.DESC,p.STARTDATE,LEFT(el.EmpList,LEN(el.EmpList)-1)FROM TableParent pCROSS APPLY (SELECT EMPLOYEENUM + ',' AS [text()] FROM TableChild WHERE ASSIGNNUM =p.ASSIGNNUM FOR XML PATH(''))el(EmpList) |
| Posted: 27 May 2010 05:20 AM PDT Hi All,I need to generate a query that'll prefix my form series like the below AAABAC...AZBABBBC...BZCACBCC...CZ........ZAZB...ZZAAA...........Basically, I need to increment my form prefix with the next alphabet and if Z had already been reached to increment the first letter i.e. A to BOn my part I've developed a logic for 2 character series. But struggling to make it a dynamic option to go to AAA & once it reaches ZZZ to AAAA.Here's my attempt[code="plain"]DECLARE @FP VARCHAR(3)DECLARE @FP1 VARCHAR(1)DECLARE @FP2 VARCHAR(1)SET @FP = 'AAA'SET @FP1 = SUBSTRING(@FP,LEN(@FP)-1,1)SET @FP2 = SUBSTRING(@FP,LEN(@FP),1)SELECT @FP, @FP1, ASCII(@FP1), @FP2, ASCII(@FP2)IF(ASCII(@FP2)=90)BEGINSET @FP2='A' SET @FP1=CHAR(ASCII(@FP1)+1)IF(ASCII(@FP1)>90)BEGINSET @FP1='A'SET @FP='A'+@FP1+@FP2ENDELSEBEGINSET @FP=@FP1+@FP2ENDENDELSE IF(ASCII(@FP2)<90)BEGINSET @FP2=CHAR(ASCII(@FP2)+1)SET @FP=@FP1+@FP2ENDSELECT @FP, @FP1, ASCII(@FP1), @FP2, ASCII(@FP2)[/code]I hope someone helps. Ankit Mathur |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment