Transact SQL :: FOR XML Statement
Sep 4, 2015
FOR XML Statement I should use to convert data in the below table to generate xml in the given format? Can it be done with a single statement?
Event | Condition | Group |Item |Answer
=======================================
New New GroupA Item1 Y
New New GroupA Item2 Y
New New GroupA Item3 N
New New GroupB Item6 N
New New GroupB Item10 Y
New New GroupB Item30 N
[code]....
View 8 Replies
ADVERTISEMENT
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
May 30, 2006
I need an SQL statement that will allow me to strip out duplicate records based upon a data condition.
Table
PPN_I CPN_I Qty_Required ECN_02
1. ItemA ItemB 1 4506
2. ItemA ItemB 1 3209
3. ItemA ItemB 1 901
4. ItemA ItemD 1 null
5. ItemA ItemD 0 null
6. ItemA ItemF 3 5609
7. ItemA ItemG 1 null
8. ItemB ItemA 1 1725
Okay - I have two conditions I need to enforce
1. A duplicate record is defined as a record where PPN_I and CPN_I have identical ID's. My new DB doesn't allow duplicate records. In order to 'strip' out the unwanted records - I need to enforce a couple of data conditions:
I delete both records for this case only - if qty required =1 and a duplicate record has qty required = 0 then both get deleted. In the above example - records 4 & 5 both get deleted. In all other duplicate record scenarios - I want to keep the record with the highest ECN_02 number. So, records 2 & 3 get deleted because they are duplicate (same PPN_I and CPN_I id's) and their ECN_02 is less than the highest value (4506). If no duplicate record exists - no action taken (records 6,7,8).
So, records 4 & 5 - both get deleted. Records 1,2 & 3 become one record (highest ECN_02) records 1 stays - records 2 & 3 deleted.
8 records become 4.
Any thoughts? How do I 'loop thru the table and identify duplicate records and then enforce some kind of rule?
Any assistance would be appreciated,
Thanks in advance.
View 2 Replies
View Related
Mar 2, 2007
Hi all,
I have been struggling with the below transact sql user defined function. I want to use a transact sql variable in an "in" statement. I don't get any results and I am not sure if I am receiving an error or not.
Code:
DECLARE @myval varchar(50),@username varchar(50)
DECLARE @rolelist varchar(2000)
SET @rolelist = ''
SET @myval = 'user a,user b'
select @myval = ''''+ replace(@myval,',',''',''') + ''''
print @myval
DECLARE User_Cursor CURSOR FOR
select distinct eusername
from euser
where eusername in (@myval)
OPEN User_Cursor
FETCH NEXT FROM User_Cursor INTO @username
SET @myval = @username
SET @rolelist = @username
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rolelist =+ @rolelist + ',' + @username
FETCH NEXT FROM User_Cursor INTO @username
END
CLOSE User_Cursor
DEALLOCATE User_Cursor
print @myval
print 'rolelist' + @rolelist
GO
I am at a loss any suggestions would be greatly appreciated.
View 4 Replies
View Related
Oct 27, 2015
I have a pivot SQL like below SQL Server 2012
SELECT Production_Date, Production_Order, LogicalVat KPI_Category,
'Probiotic Amt Consumed' KPI_Data1_Name,'RC Amt Consumed' KPI_Data2_Name
FROM
( SELECT Production_Date , NULL Production_Order,
LogicalVat, ReportValue ReportValue
FROM BIReports.dbo.r_VatMake
WHERE Production_Date between '10/27/2015' and '10/27/2015'
[code].....
Now the attributes changed into like below where the number after RC is dynamic and I can't use the query above anymore
Example:
RC Amt Consumed - RC 6
RC Amt Consumed - RC 7
RC Amt Consumed - RC 8
Probiotic attribute changed into like below where number after PROB is dynamic
Probiotic Amt Consumed - PROB 15
Probiotic Amt Consumed - PROB 16
Probiotic Amt Consumed - PROB 17
View 5 Replies
View Related
Sep 25, 2015
I've been beating my head against this for a bit and haven't been able to figure this out I want to pull 1 set of values between a date time range if @Report = '1' but if @Report = 2 or 3 I want it to drop 3 of the parameters so it will pull all items. by dropping the where parameters for @BeginRangeDate and @LookOutDate and L.COLineStatus. My report works great if I go in and run Report 1 with the date parameters in the where statement and also works great for Reports 2, 3 if I REM out those 3 items in the Where statement. Here is what I currently have
CASE@Report
WHEN'1'THEN
WHEREL.RequestedShipDate>=@BeginRangeDate
ANDL.RequestedShipDate<=@LookoutDate
ANDDO.ForecastDate>=@ForecastDate
[Code] .....
View 7 Replies
View Related
Jul 20, 2005
I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad
View 4 Replies
View Related
Sep 17, 2015
I have a stored procedure that runs (SQL Server 2012 (SP1) Standard Ed) daily and I never had any problem with this stored procedure. However, there is MERGE statement on the stored procedure and I see an error saying that the MERGE statement failed..Here are the stored procedure and error message:
-- FlushQueue
CREATE PROCEDURE [dbo].[FlushQueue] (@RowCount as int = 10000)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
[code]....
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows. [SQLSTATE 42000] (Error 8672). The step failed.
Table definition:---CREATE TABLE [dbo].[ImportDefinitions]
(
[NodeName] [varchar](20) NOT NULL,
[ProcedureName] [varchar](100) NOT NULL,
[FilePrefix] [varchar](20) NOT NULL,
[ImportDelay] [int] NOT NULL CONSTRAINT [DF_ImportDefinitions_ImportDelay] DEFAULT ((0)),
[code]...
View 13 Replies
View Related
Jun 16, 2015
When I run this update statement, it updates the proper badgenumbers but it only updates them to 1 when I did a count? As the data displays some of the results should be more than 1. Why did this occur?
Declare
@count int,
@Assignment varchar(100),
@fullname varchar(100),
@timeworkedtoday decimal(18,2),
@badgeNum varchar(50),
@ticket varchar(50)
[Code] ....
View 5 Replies
View Related
May 20, 2015
Display customized data based on customized where statement from UDT.
The UDT is a parameter inside of a stored procedure.
Problem:
A parameter from a stored procedure is
@communication communications readonly
This parameter is a User-Defined Table Types (UDT) It contains criteria based on end-user's selection from a filtration functionality from a webpage.
Four example of filtration critera based on four end-users' selection that is located inside of a table below.
Each UDT table contains different criteria:
Number Criteria
------ --------
1 Phone
3 Email
Number Criteria
------ --------
1 Phone
2 Cellphone
3 email
Number Criteria
------ --------
4 None
Number Criteria
------ --------
2 Cellphone
1 Phone
Is it somehow possible to use the criteria's value as a column name in the where statement? I want to filtrate the data of the table datatable based on id, name and the UDT's criteria.
I was enable to apply the criteria inside of a variable by looping the UDT's table but the next thing is to paste it in the where statement after "id=1 and name" below
select *
from datatable
where id = 1 and name = 'Cost'
How should I do it?
[URL] .....
create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
[Code] .....
View 4 Replies
View Related
Jul 22, 2015
I would like to change
WHERE dbo.IP_ADMISSION.DischDate >= GETDATE()-7 AND dbo.IP_ADMISSION.DischDate < GETDATE()
to capture Monday to Sunday data only irrespective of what day I run the report
View 16 Replies
View Related
Apr 29, 2015
SELECT FirstSet.Country,FirstSet.[Month]
,ABC.ABC1
,DEF.DEF1
FROM (
SELECT [Answer Text]'Country',interview_start 'Month',[ID respondent],
[Code] ....
I didn't find whats problem with this code. Actually I try to create a select statement with with cte select statement. In cte clause my output ok but when I try to receive that output from write another select statement then its show error.
Msg 102, Level 15, State 1, Line 276
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 315
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 351
Incorrect syntax near the keyword 'as'.
View 8 Replies
View Related
Jun 23, 2015
How I can get the following Desired SELECT STATEMENT with the increment of Max(serial_no)+1. Date will be in quotation in desired SELECT STATEMENT. All data is same. Just changing the serial_no which will be Max(serial_no)+1.
Create table #seq (serial_no numeric, Scode numeric, Sdate datetime, SErr char(10))
insert into #seq values (1,1002,'10/10/2015','SEDT')
insert into #seq values (2,1005,'12/10/2015','PEFT')
insert into #seq values (3,1004,'1/1/2015','QEGT')
insert into #seq values (4,1008,'1/2/2015','TEWT')
insert into #seq values (5,1007,'3/10/2015','REDT')
Result from the above query
serial_no Scode Sdate SErr
1 1002 10/10/2015 SEDT
2 1005 12/10/2015 PEFT
3 1004 1/1/2015 QEGT
4 1008 1/2/2015 TEWT
5 1007 3/10/2015 REDT
Desired SELECT STATEMENT Result with increment of serial_no (Max(serial_no+1)
Select 6, 1002, '10/10/2015', 'SEDT'
Select 7, 1005, '12/10/2015', 'PEFT'
Select 8, 1004, '1/1/2015', 'QEGT'
Select 9, 1008, '1/2/2015' ,'TEWT'
Select 10, 1007, '3/10/2015', 'REDT'
View 3 Replies
View Related
Aug 9, 2015
In the below query, I can get the individual/single group by columns as well as multiple but I cannot control the order in which I would like to group by the data.
So lets say I want to group the data by OS->browser->browser_version(just one example) then I cannot achieve that as the order of OS column comes later in the query.
I know one option would be to write a dynamic SQL but i dont want to do that because of performance reasons. Any other way this can be achieved?
select
case when @include_browser =
1 then browser_name end as browser_name,
case when @include_browser_version
= 1 then browser_version end
as browser_version,
[Code] ....
View 4 Replies
View Related
Oct 1, 2015
I keep getting an error of error in statement near = on this update statement. What is incorrect?
Update abcd
Set CAST(field1 As varchar(4000)) = 'Computers and 2-in-1s'
where CAST(field1 As varchar(4000)) = 'Computeres and and 2-in-1s'
View 11 Replies
View Related
Apr 20, 2015
I want to run an update command where the user types in a CSV value and the query runs. If I simulate 1 number it works, but if I put in 2 variable it returns nothing (but doesn't fail).
declare @SITE_ID int
declare @txtSchedule varchar (500)
set @SITE_ID=1
set @txtSchedule='5,6'
select * from Schedules WHERE SITE_ID=@SITE_ID and WEEK IN(@txtSchedule .
View 3 Replies
View Related
Sep 24, 2015
I use Order By Used with Case Statement and may be this is the reason for which i am getting error
Windowed functions can only appear in the SELECT or ORDER BY clauses.
Here is my complete sql where i use Row_Number function for pagination with sorting.
DECLARE @StartIndex INT
DECLARE @EndIndex INT
DECLARE @SortColumn VARCHAR(MAX)
DECLARE @SortDirection VARCHAR(MAX)
SET @StartIndex = 1
[Code] ....
View 7 Replies
View Related
Apr 21, 2015
I created this unique codes and
I need all [FRMDAT] field set to ‘12/31/2014’ in the MKLOPT table, where the [JOBCOD] in the VALUE list BELOW have a [FRMDAT] that is currently (greater than) > ‘12/31/2014’
VALUE LIST
PH00059
PH02775
PH03051
PH03305
PH03336
PH03342
PH03371
PH03992
PH03993
PH03994
View 2 Replies
View Related
Oct 9, 2015
I've created multiple select statement to pull in varies fields from a table and one of those fields is Fiscal_Year, however, after the select statement I'm adding a Where clause:
Where Fiscal_Year = '2013'
And I keep getting a syntax error??
View 3 Replies
View Related
May 21, 2015
I have this script which generates droptable tablename.
select top 10000 'drop table '+name, * from sys.tables (nolock) where name like '%4[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' order by create_date
I would like to generate 'GO' after each drop statement.
View 3 Replies
View Related
Oct 20, 2015
Is it possible to combine a CASE statement with two different columns to retrieve data into one result column? In one column it has multiple JobCode, but this needs to be divided. The only way I can see to do this is take the data from another column to get the results.Ex. JobCode - has one code for several job descriptions (there are about 30), but all within this code cannot have the same level of access. So I need to divide them out, and put them in one column for AccessLevel.
JobTitle - has one code for one job, (but there are over 100).I want to pull from both columns to get the results I need to assign appropriate access level in one column.
Case JobCode (they all have same job code, but everyone in this job code should not have same access)
When '45' Then '1' (Principal, Asst. Prin, or any Administrator, Counselors)
When '25' Then '2' (this could be teacher, etc. )
Case JobTitle (this is how access should be)
When '12345' Then '1' (this is Administration only)
[code]....
View 12 Replies
View Related
Jun 3, 2006
Hi there can anyone help me to create a SQL Insert Statement. I dont know SQL at all.
To explain, I have two web pages. search.asp and results.asp.
search.asp has the following.
Form Name: searchForm
Set to: Post
Action: results.asp
Text Field: Keyword
Drop Down: CategoryTable
Drop Down: Location
UserName Session Variable: MM_UserName
results.asp has the following SQL which pulls all the results.
SELECT SupplierName, Location, ShortDescription, TimberSpecies, CategoryTitle, Country, CustomerType
FROM Query1
WHERE TimberSpecies LIKE '%MMColParam%' AND CategoryTitle LIKE '%MMColParam2%' AND Location LIKE '%MMColParam3%' AND CustomerType = 'Trade/Retail'
ORDER BY CategoryTitle, TimberSpecies ASC
The database & form I want to insert into.
tblSearcheResults
idSearch (AutoNumber)
location (Text) "Want to insert the 'Location' result here"
category (Text) "Want to insert the 'CategoryTable' result here"
user (Text) "Want to insert the UserName Session Variable result here"
result (Text) "Want to insert the 'Keyword' result here"
Please ask if u need more info.
Mally
View 1 Replies
View Related
Nov 11, 2015
I am trying to run the below but I get an error of 'Incorrect syntax ')'' --- I have tried every angle I can think of around the parens to fix this but nothing I do is working.
UPDATE abcdefg
SET [Date] = GETDate(),
[readytogo] =
(
CASE WHEN [customername] NOT IN (Select [customername] from [server].[database].[dbo].[view])
THEN 'Yes'
ELSE
'Needs Verification'
[code]....
View 5 Replies
View Related
Apr 20, 2015
Im working on Partition purge process, where I need to specify following statement:
SET @cmd = 'ALTER PARTITION FUNCTION ' + @function_name + '() MERGE RANGE (@range)'
EXEC (@cmd);
SET @cmd1 = 'ALTER DATABASE '+ db_name()+ ' REMOVE FILE ' + @partition_file
EXEC (cmd1);
I want to put this statement in Begin Tran /Commit statement but getting error that it is not allowed. "ALTER DATABASE statement not allowed within multi-statement transaction"..what are my options to rollback in case there is a failure.
View 4 Replies
View Related
Oct 15, 2015
I'd like to get results from ZTest_Contract being my result set, and would like to combine the subquery (which gets the Max) into the primary view ZTest_Contract.
CREATE VIEW [dbo].[ZTest_Contract] AS
Select
M.CUSTNMBR,
M.ADRSCode,
M.Contract_number,
M.MaxWSCONTSQ,
M.Equipment_id,
[Code] ......
View 3 Replies
View Related
Jul 29, 2015
In the following t-sql 2012 merge statement, the insert statement works but the update statement does not work. I know that is true since I looked at the results of the update statement:
Merge TST.dbo.LockCombination AS LKC1
USING
(select LKC.comboID,LKC.lockID,LKC.seq,A.lockCombo2,A.schoolnumber,LKR.lockerId
from
[LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
[Code] ...
Thus can you show me some t-sql 2012 that I can use to make update statement work in the merge function?
View 3 Replies
View Related
Nov 12, 2014
I was using Type 2 for one of our Fact table.... and need to put a flag to know which one is the Current record... I couldn't able to figure how to implement logic in the merge statement... This is an example Query ....I was using like this for my fact table...
Basically I need to track CustomerName and City... So I need a Currentflag (Y) for latest record....
MERGE INTO [dbo].[TargetCustomer] AS TRG
USING [dbo].[MyCustomers] AS SRC
ON TRG.[CustomerID] = SRC.[CustomerID]
AND TRG.[CustomerName]=SRC.[CustomerName]
AND TRG.[City]=SRC.[City]
[Code] .....
View 7 Replies
View Related
Sep 29, 2015
Is there any method to get the T-SQL command/ statement using DML triggers?.
i.e. While we insert a record using INSERT Statement, is there any possible way to get the T-SQL Statement using DML trigger for AFTER INSERT.
View 4 Replies
View Related
Oct 7, 2015
In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.
Merge test.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber
from
[Inputtb] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
[code]...
Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?
View 10 Replies
View Related
Mar 28, 2012
I'm using sql 2008 and triying to build a dynamic sql script to split the records 50/50.I know using newid() with order by clause selects randomly but how should I build the select statement to split the data 50/50 so i don't need to run the script manually everytime ?
View 10 Replies
View Related
May 7, 2015
I have written the following code:
SET NOCOUNT ON
DECLARE @RowCount int; SET @RowCount = 0;
Begin Try
Begin Transaction
--------------------------------------------------------
-----Table Name: AlertsStaticRecord_Archive
-----Column Name: AlertID
--------------------------------------------------------
ALTER TABLE [AlertsStaticRecord_Archive] ALTER COLUMN [AlertID] int NOT NULL;
[Code] .....
But, when I execute these batch, I am getting error:
Msg 8111, Level 16, State 1, Line 11
Cannot define PRIMARY KEY constraint on nullable column in table 'AlertsStaticRecord_Archive'.
Msg 1750, Level 16, State 0, Line 11
Could not create constraint. See previous errors.
Because, the first ALTER statement is not getting executed.
View 4 Replies
View Related
Jun 4, 2015
I have used the below update query. However, its updating only the first value. Like its updating AB with volume when c.Type = ABC, similarly for CD. Its not updating based on the 2nd or the next case condition.
Update XYZ Set AB = a.Amt * (CASE WHEN c.Type = 'ABC' THEN (c.volume)
WHEN c.TYPE = 'DEF' THEN (c.volume)
WHEN c.Type = 'GHI' THEN (c.volume)
Else 0
END),
CD = CASE WHEN c.Type = 'MARGIN' THEN '4105.31'
WHEN c.Type = 'ABC' THEN '123.1'
WHEN c.Type = 'DEF' THEN '234.2'
WHEN c.Type = 'GHI' THEN '567.1'
END
from table1 a join table2 b
on a.Cust = b.Customer
join table3 c
on b.account = c.account and b.channel =c.channel
Why its not working properly? But if i use Select statement instead of update query its working properly.
View 18 Replies
View Related
Jul 28, 2015
I am trying to add the letters 'MS' in front of value while using a case statement. If Dispo = 2 I want it to pull back 'Inactive', else I want it to pull back the Value with MS in front (eg. "MS14"). The data in the value column are numbers. Would I use a CONCAT? If so where does that need to go?
Case when dispo = 2 then 'Inactive' else cast(Value as varchar(11)) end ,
View 27 Replies
View Related
