Friday, June 21, 2013

[T-SQL] MERGE Statement DELETE from TARGET when not in SOURCE 'hanging'

[T-SQL] MERGE Statement DELETE from TARGET when not in SOURCE 'hanging'

MERGE Statement DELETE from TARGET when not in SOURCE 'hanging'

Posted: 20 Jun 2013 11:39 PM PDT

My code runs fine until I try Delete an entry that is in my target table but not in my source. The insert & update all work.[code] MERGE dbo.cart AS c_target USING (SELECT @sessionid as sessionid ,ISNULL(o.free_subsku,0) as subsku ,o.free_sku as mainsku ,ko.cartrsn ,o.qty_free * ko.qty as qty ,COALESCE(sp.price,p.price) as price ,COALESCE(sp.[weight],p.[weight]) as [weight] FROM offer o inner join @cart_offer ko on o.id = ko.offer_id inner join products p on o.free_sku = p.sku left join subproducts sp on o.free_subsku = sp.sku WHERE o.active = 1 and o.offer_type = 'GWP' ) as c_source ON c_target.sessionid = c_source.sessionid and c_target.mainsku = c_source.mainsku and c_target.subsku = c_source.subsku --Issue here?!?! WHEN NOT MATCHED BY SOURCE AND c_target.sessionid = @sessionid THEN DELETE WHEN MATCHED AND c_target.qty <> c_source.qty THEN UPDATE SET c_target.qty = c_source.qty ,c_target.price = c_source.price ,c_target.[weight] = c_source.[weight] WHEN NOT MATCHED BY TARGET THEN INSERT (sessionid,subsku,mainsku,qty,price,[weight]) VALUES (@sessionid,c_source.subsku,c_source.mainsku,c_source.qty,c_source.price,c_source.[weight]); [/code]I know I should attach some test data, but can anyone see anything obviously wrong in my code?Also cart table has about 5mil rows in it.thanks

Update raise error

Posted: 20 Jun 2013 09:21 PM PDT

Hi,Is there any setting in SQL Server that makes an UPDATE statement raise an error if no rows are affected?I'm using Red Gate SQL Data Compare to sync databases but there are some issues with the GUIDs (on a first phase they where NEWID() and not static) and when updating old databases the sync script can update 0 rows.I'd like for the script to raise an error if no rows are affected. Is there any thing to do this, rather than writing code after every update statement checking @@ROWCOUNT?Thanks,Pedro

Do I have to use repetative joins to get values

Posted: 21 Jun 2013 12:21 AM PDT

Hello,I have a query that returns several key values:SELECT * FROM TableXWhich returns:Value1 = 1001Value2 = 9671.....etcNow Value1 & Value2 are stored in the same ValueTable. To get one translated to a textual representation I would join like so:SELECT MRX.Col1 VAT.TextName, MRX.Col3, Col4....FROM TableX MRXINNER JOIN ValueTable VAT ONMRX.Col2 = VAT.Value1This is fine and dandy and gets one key translated to user friendly text but what about the other (Col4)?Do I have to join again..add an OR clause or...?SELECT MRX.Col1 VAT.TextName, MRX.Col3, VAT2.TextName....FROM TableX MRXINNER JOIN ValueTable VAT ONMRX.Col2 = VAT.Value1INNER JOIN ValueTable VAT2 ONMRX.Col4 = VAT2.Value2TIA'JB

Retrives the subject wise highest marks list in a table

Posted: 17 Jul 2011 11:45 PM PDT

Hi All,I have a requirement like, we are having two tables in our database.Table names: student, marklistStudent table values:id studname------------------1 x2 y3 z4 a5 bMarklist table values:id maths physics English---------------------------------1 50 60 702 70 60 403 50 80 704 50 100 705 90 60 70But my requirement is, I need to display the data "subject wise" highest marks for each student.for example:id name highestmark---------------------------------1 x EnglishAny boxy help me how to reach this scenario.Thanks in AdvanceBest regardsRadh

roll up the data as per period between start and enddate

Posted: 20 Jun 2013 06:33 AM PDT

Hi I have a query that returns aggregated data for a day between start and end date . Now My manager says he would send the start and end date and wants to rollup or aggregate the data to either month ,quarter or year as per the dates on the same data as per the parameter period(month,day,year ,quarter) that is sent .Suppose he sends startdate = 06/20/2012 and enddate = 06/20/2013 and period = month then he wants to aggregate the data for every month Until the end date .if period = quarter then he wants to see the aggregate for every 3months data from start to end datebelow is the query ;with Vol_Factdata as (Select CONVERT(char(10),EventStartDate,126) as Eventstartdate, Agg.ProductTypeID, Agg.ProductTypeName, Agg.LoanProgramTypeId, MIN(MetricValue) AS MetricValall, SUM(SampleSize) as SampleSizeall FROM [Reporting].[AggregatedRatesforWidgets] Agg with (NOLOCK) WHERE Agg.EventStartDate >= @ipstartdate and Agg.EventStartDate <=@ipenddate and Agg.LoanProgramTypeID= @ipProducttypeID GROUP BY CONVERT(char(10),agg.EventStartDate,126),Agg.ProductTypeID,Agg.ProductTypeName,Agg.LoanProgramTypeId) Select vcy.EventStartDate as EventStartDate, vcy.ProductTypeID as ProductTypeID, vcy.ProductTypeName as ProductTypeName, vcy.LoanProgramTypeID AS LoanProgramTypeId, 'ALL' as Loanstate, vcy.MetricValall_1year as MetricValue, vcy.SampleSizeall_1year as SampleSize FROM Vol_Factdata vcyCan some one suggest me a better way ?Thanks&RegardsSC

include the outptut of storedprocedure in select

Posted: 20 Jun 2013 04:46 AM PDT

I have stored prcuder like [code="sql"]create proc calcaulateavaerage@studentid intasbegin-- some complecated business and queryreturn @result -- single decimale value value end[/code]and then I want to [code="sql"]create proc the whole resultselect * , ................................ from X where X.value > (calcaulateavaerage X.Id)[/code]it always give error muli part identifier calcaulateavaerage couldn't be bound any idea how to solve that

Remove NewLine Charachters on a Select All.

Posted: 20 Jun 2013 03:20 AM PDT

Hi Guru's I'm trying to run a Select on an entire table Via [quote]Select * From[/quote] However a couple of the columns contain addresses with the NewLine Cahrachter embedded in it which is ruining my export to Excel. I know that I can run a [code="sql"]REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')[/code] on a specific field, my question is how to do this on the entire selection?Thanks.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog