How To Replace Cursor Based Statements With Set Ba

Nested REPLACE Statements Using Multiple Columns
I need to pass 3 column values and one Formula string into 4 replace statements and output the result in one column.

Nesting them in the usual way doesn't seem to work as that only allows for one column.

My table consits of four columns...PF (numeric), Hours (numeric), TotalNumber INT, and Formula (nvatchar)

My function needs to search and replace the Formula column for instances of all the three number columns and output the formula as a mathmatical formula rather than a string.

Here is what I have so far which works fine if all three columns have a value, but if only one is null then it will retrun NULL and not the other two values.

FUNCTION GetFormula
(@numPF NUMERIC(10,2), @numHours NUMERIC(10,2), @intTotalNumber INT, @strFormula nvarChar(200)) RETURNS nvarchar(200)
AS
BEGIN
DECLARE @strExpression nvarchar(200)

SELECT @strExpression=REPLACE(@strFormula, 'TotalNumber',@intTotalNumber)
SELECT @strExpression=REPLACE(@strExpression, 'PF',@numPF )
SELECT @strExpression=REPLACE(@strExpression, 'Hours',@numHours )
RETURN @strExpression
END


Many Thanks

View Replies !

---

Need SQL To Replace CURSOR
Hello,
I have a 3 million-row table, which has primary key that consists of id_no and addDtime.
The id_no is duplicated with a different addDtime to make the row unique. I need to remove all duplicate id_no rows except the one with the latest addDtime. A cursor on a test server with 1-million rows took 14 hours before somebody switched the box off. Is there any way of using SQL to replace the cursor.
Example of Cursor:

Declare @id_no tid_no = ""
DECLARE UPD_title_cursor CURSOR FOR SELECT id_no FROM updtitle
GROUP BY id_no HAVING COUNT(id_no) > 1
OPEN UPD_title_cursor
FETCH NEXT FROM UPD_title_cursor INTO @id_no
WHILE @@fetch_status =0
BEGIN
BEGIN TRANSACTION
delete updtitle where id_no = @id_no
and addDtime < (select max(addDtime)from updtitle where id_no = @id_no)
if @@error <> 0
BEGIN
ROLLBACK TRANSACTION
END
COMMIT Transaction
FETCH NEXT FROM UPD_title_cursor INTO @id_no
END
CLOSE UPD_title_cursor
DEALLOCATE UPD_title_cursor
Go

View Replies !

---

Cursor Based SQL?
Does anyone have any good references they could recommend on Cursorbased SQL writing? I have to create SQL that can loop though recordssimular to VB loops and I have been told that this is the way to go.Any recommendations would be helpful.

View Replies !

---

Creating WHERE Clauses Based On IF (or CASE) STATEMENTS Transact-SQL
I am trying to create a stored procedure whose where clause isdependent on a parameter.If the parameter @myparam is null or '' then I want the where clause tobe one thing, else I want it to be a completely different thing. I cando it easily using iif but obviously that is not an option here...Here is what I have:Select @ FROM my table WHEREIF @myparam = '' or @myparam is null[MyField] = like '%'ELSEcharindex(@myparam,[MyField]) > 0)I have also tried variations of this and tried it using CASE. I keepgetting syntax errors. Do I have to write multiple SELECT statementsfor every possibility. If I can get this working the query willactually be a bit more complex, although it will follow this basicstructure...in other words, I'd rather not have to write multiplequeries when the only thing that is changing is the WHERE portion...Thanks!Ryan

View Replies !

---

How To Write Set-based SQL Instead Of Cursor
Guys
Here's the scenario

create table data1 (dealid varchar(6) , datex smalldatetime , Tn INT)
insert data1 (dealid , datex , Tn )
values ('12345' , '31-12-2005' , 9999)
insert data1 (dealid , datex , Tn )
values ('12345' , '30-11-2005' , 9999)
insert data1 (dealid , datex , Tn )
values ('12345' , '31-10-2005' , 9999)
insert data1 (dealid , datex , Tn )
values ('98765' , '31-12-2005' , 2)
insert data1 (dealid , datex , Tn )
values ('98765' , '30-11-2005' , 1)
insert data1 (dealid , datex , Tn )
values ('98765' , '30-11-2005' , 0)
select * from data1


I need to update the Tn column from the default 9999 for the 3 rows in this table where the dealid is 12345 based on the value in the datex column so the row with the 'highest ie most recent date' gets a 0. I then need to assign the value 1 to the next highest and so on until all rows (in this case 3) get incrementing integer values. It's easy with a cursor but can't get my head round doing it in a set-based way
Any ideas

View Replies !

---

Cursor, Set Based Approach
I am replacing cursor logic in a SP to a setbased approach to scale better. My setbased approach seems to be better but it runs very fractionaly faster (execution time) than the cursor approach for a single run in test environment. I think resource cost wise, my set based approach should be better. Number of rows iterated thru this cursor is small (0-150). This particular SP is called over 2000 times in production everyday. Is it worth the trouble changing this if we get only marginally benefits, will my set-based approach work better on a server that has lot of activity (lot of connections etc). Our db server runs at about 75-85% cpu usage daily and this particular SP accounts to 13% CPU usage for 2000+ executions.

If the data set involved in cursors is small, is it worth the trouble changing them to set based approaches?
Am I doing right to change this SP to setbased approach.

View Replies !

---

Avoiding Cursor - Want Set Based Solution
Hi there!

Here is my situation:

table 'ReceiptHeader'

IDCustomerIDDateCreated
1225102/06/2002
1332102/09/2002
1444002/15/2002


table 'ReceiptDiscount'

IDDiscountIDReceiptHeaderIDAmount
111210.00
241250.00
311325.00

**a receipt can have multiple discounts**


Table 'ReceiptDetail'

ReceiptHeaderID LineItemIDTotal
121155.33
131145.33
141241.66

**for this example there is only one line item per receipt**



Without using a cursor, i would like to return a result set
like this one below using a set based solution...


ReceiptIDCustomerIDDiscountTotal
1225160.00155.33
1332125.00145.33
144400.00241.66

Thanks,
SF

View Replies !

---

Cursor Looping Versus Set-based Queries
I know this question has been asked. And the usual answer is don't usecursors or any other looping method. Instead, try to find a solutionthat uses set-based queries.But this brings up several questions / senarios:* I created several stored procedures that take parameters and insertsthe data into the appropriate tables. This was done for easy access/usefrom client side apps (i.e. web-based).Proper development tactics says to try and do "code reuse". So, if Ialready have stored procs that do my logic, should I be writing asecond way of handling the data? If I ever need to change the way thedata is handled, I now have to make the same change in two (or more)places.* Different data from the same row needs to be inserted into multipletables. "Common sense" (maybe "gut instinct" is better) says to handleeach row as a "unit". Seems weird to process the entire set for onetable, then to process the entire set AGAIN for another table, and thenYET AGAIN for a third table, and so on.* Exception handling. Set based processing means that if one row failsthe entire set fails. Looping through allows you to fail a row butallow everything else to be processed properly. It also allows you togather statistics. (How many failed, how many worked, how many wereskipped, etc.)?? Good idea ?? The alternative is to create a temporary table (sandboxor workspace type thing), copy the data to there along with "status" or"valdation" columns, run through the set many times over looking forany rows that may fail, marking them as such, and then at the end onlydealing with those rows which "passed" the testing. Of course, in orderfor this to work you must know (and duplicate) all constraints so youknow what to look for in your testing.

View Replies !

---

Declare Cursor Based On Dynamic Query
Hi,

I am declaring the cursor based on a query which is generated dynamically. but it is not working

 

Declare @tempSQL varchar(1000)

--- This query will be generated based on my other conditon and will be stored in a variable

set @tempsql = 'select * from orders'

declare cursor test for @tempsql

open test

 

This code is not working.

 

please suggest

 

Nitin

View Replies !

---

Breakout Records Based On UNIT Total Without Cursor
I have a set of revenue records where there is a UNIT column and a REVCHARGE column. What I need to do is breakout the records into single records where the unit count is > 1 and calc the actual charge:

Ex:

Units REVCHG FIELD_A FIELD_B .....
3 3.00 ABCD EFGH

Needs to be converted to:

Units REVCHG FIELD_A FIELD_B .....
1 1.00 ABCD EFGH
1 1.00 ABCD EFGH
1 1.00 ABCD EFGH

The calc is obvious but how can I do this with a cursor but would like to do it without a cursor if possible? Anybody got an idea?

Thanks.

View Replies !

---

Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE               QTYIN  COST_IN_AMT        COST_OUT_AMT(MOVING AVERAGE)   
1          01/01/2007  PURCHASE            10                1000
2          01/01/2007  PURCHAES              5                1100
3          01/01/2007  SALES                    -5                                     *TobeCalculated
4          02/01/2007  Purchase                20                9000
5          02/01/2007  SALES                  -10                                     *TobeCalculated
5          02/01/2007  purchase                50                 8000 
6          03/01/2007  Sales                    -10                                      *TobeCalculate
7         01/01/2007   Purchase                20                12000
 
I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here)
In order  to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly.
When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly.
The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated.
Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time.
Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?

View Replies !

---

How To Convert Cursor Based Stored Proc In Set Based Simple Stored Proc.
 

Hey All,
I am trying to convert cursor based stored proc in to set based simple statements stored proc. As this stored proc has created alot of performance issues. I am confuse now as I spent my most of time creating this stored proc. Please advise how can I convert this stored proc into set base simple statment.

Thanks in advance.

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [SampleStoredProc]
@Var1  varchar(20),
@Var2  varchar(3),
@Var3       varchar(2) = 'Dummy'           
As

declare @selectlist   varchar(5000)
declare @tableBuild   varchar(1000)
declare @FieldName   varchar(50)
declare @FieldSelect   varchar(500)
declare @FieldTitle   varchar(50)
declare @TableName   varchar(50)
declare @holdTable   varchar(50)
declare @title    varchar(50)
declare @holdTitle   varchar(50)
declare @PageName   varchar(50)
declare @sequence   varchar(100)
declare @extraCriteria   varchar(200)
declare @holdCriteria   varchar(200)
declare @insertSQL   varchar(5000)
declare @ConvertRoutine  varchar(500)
declare @loopCtrl1   bit
declare @loopCtrl2   bit
declare @ConvertSQL   varchar(5000)
declare @PrevValue   varchar(50)
declare @NewValue   varchar(50)
declare @ActionTxt  varchar(1)
declare @Description  varchar(20)
declare @effDate  varchar(10)
declare @transEffDate  varchar(10)
declare @expDate  varchar(10)
declare @lastTransDate  varchar(10)
declare @policyStatus  varchar(2)
declare @reasAmendDesc varchar(50)
declare @policyNumber  varchar(20)
declare @riskState  varchar(20)
declare @PriorPrem  money  
declare @AmendPrem  money  
declare @PremDiff  money  
declare mtcursor cursor for
 select TableName, FieldName, FieldSelectTxt, FieldTitleTxt, SequenceFieldName, ExtraCriteriaTxt, PageTitleTxt, ConversionRoutineTxt from MyTable1
   where Column1 = @Var2
  order by PageDisplaySequenceNbr, TableName, ExtraCriteriaTxt, SequenceFieldName

 open mtcursor

 fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine

 set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr'
 set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)'
 set @loopCtrl1 = 0
 set @loopCtrl2 = 0

 WHILE (@loopCtrl1 = 0)
 begin
  set @holdTable = @TableName
  set @holdCriteria = @extraCriteria
  set @holdTitle = @title

  if @FieldSelect = ''
      set @selectlist = @selectlist + ',' + @FieldName
  else
      set @selectlist = @selectlist + ',' + @FieldSelect

  set @tableBuild = @tableBuild + ',' + @FieldName + ' varchar(50)'

  fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine
  if @@fetch_status <> 0
      set @loopCtrl2 = 1

  if (@TableName <> @holdTable) or (@extraCriteria <> @holdCriteria) or (@title <> @holdTitle) or (@loopCtrl2 = 1)
   begin

    set @tableBuild = @tableBuild + ')'
    set @insertSQL = '
 declare mtcursor2 cursor for
  select FieldName, FieldTitleTxt, ExtraUpdateMatchTxt, PullForUpdateInd, PullForAddInd, PullForDeleteInd, PullForAnyUpdateInd from MyTable1
  where TableName = ''' + @holdTable + '''
      and ExtraCriteriaTxt = ''' + @holdCriteria + '''
      and PageTitleTxt = ''' + @holdTitle + '''
      and Column1 = ''' + @Var2 + '''
      order by FieldDisplaySequenceNbr

 declare @FieldName varchar(50)
 declare @FieldTitle varchar(50)
 declare @ExtraUpdateMatch varchar(500)
 declare @PullUpdate bit
 declare @PullAdd bit
 declare @PullDelete bit
 declare @PullAnyUpdate bit

  open mtcursor2
  fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate

  WHILE (@@fetch_status = 0)
  begin

    if substring(@FieldTitle,1,1) = ''#''
        set @FieldTitle = substring(@FieldTitle,2,len(@FieldTitle) - 1)
                else
                    set @FieldTitle = '''''''' + @FieldTitle + ''''''''

   if @PullAnyUpdate = 1
                  begin
            exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U''''
      from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + ''
        where A.Val1 = ''''O'''' and B.Val1 = ''''U'''''')
                   end
   else
      begin
  if @PullUpdate = 1
             exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U''''
       from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + ''
         where A.Val1 = ''''O''''  and B.Val1 = ''''U'''' and ((A.'' + @FieldName + '' <> B.'' + @FieldName + '') or (A.'' + @FieldName + '' is null and B.'' + @FieldName + '' is not null)
          or (A.'' + @FieldName + '' is not null and B.'' + @FieldName + '' is null)) '')
                  end

   if @PullAdd = 1
  exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', ''''n/a'''', '' + @FieldName + '', ''''A''''       from #tempTable A where Val1 = ''''A'''''')
   if @PullDelete = 1
    exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', '' + @FieldName + '', ''''n/a'''', ''''D''''
      from #tempTable A where Val1 = ''''D'''''')
   fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate
  end

  close mtcursor2
  deallocate mtcursor2'

    exec (@tableBuild + ' insert into #tempTable select ' + @selectlist + ' from ' + @holdTable + ' where Id = ' + '''' + @Var1 + '''' + @holdCriteria + @insertSQL)

    set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr'
    set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)'
   end

    if @loopCtrl2 = 1
       set @loopCtrl1 = 1
  end

  close mtcursor
  deallocate mtcursor

  Delete from MyTable2 where ltrim(rtrim(PreviousValueTxt)) = ltrim(rtrim(EndorsedValueTxt)) and ActionTxt='U' and ID=@Var1 
  declare deletecursor cursor for
 select distinct PageNm from MyTable2 where Id = @Var1 and ActionTxt = 'U'

  open deletecursor

  fetch next from deletecursor into @PageName

  while @@fetch_status = 0
     begin
  if (SELECT count(*) from MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' and PreviousValueTxt <> EndorsedValueTxt ) = 0
       DELETE FROM MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U'
  fetch next from deletecursor into @PageName
     end

  close deletecursor
  deallocate deletecursor

  declare convertcursor cursor for
 select a.PreviousValueTxt, a.EndorsedValueTxt, A.EntrySequenceNbr, A.ActionTxt, b.ConversionRoutineTxt from MyTable2 a
 inner join MyTable1 b
  on a.PageNm = b.PageTitleTxt and a.FieldNm = b.FieldTitleTxt and b.ConversionRoutineTxt <> ''
 where a.Id = @Var1

  open convertcursor

  fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine

  while @@fetch_status = 0
     begin
 set @ConvertSQL = 'declare @PrevConverted varchar(50) declare @NewConverted varchar(50)'
 set @ConvertSQL = @ConvertSQL + ' declare @ConvertInput varchar(50) '
 -- start 33385
 set @ConvertSQL = @ConvertSQL + ' declare @Var3 varchar(2) '
 set @ConvertSQL = @ConvertSQL + ' set @Var3 = ''' + @Var3 + ''''
 --end 33385
 if @ActionTxt = 'A'
  set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = ''' + @PrevValue + ''''
 else
   begin
  set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @PrevValue + ''''
  set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = (' + @ConvertRoutine + ')' 
   end
 if @ActionTxt = 'D'
  set @ConvertSQL = @ConvertSQL + ' set @NewConverted = ''' + @NewValue + ''''
 else
   begin
  set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @NewValue + ''''
  set @ConvertSQL = @ConvertSQL + ' set @NewConverted = (' + @ConvertRoutine + ')'
   end

 set @ConvertSQL = @ConvertSQL + ' update MyTable2 set PreviousValueTxt = @PrevConverted, EndorsedValueTxt = @NewConverted
  where EntrySequenceNbr = ''' + @Sequence + ''''

 exec (@ConvertSQL)

 fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine
    end

  close convertcursor
  deallocate convertcursor

/* LOB-specific data conversions */
  if @Var2 = 'PA '
  --exec PAConfirmCovConversions @Var1 = @Var1
 exec PAConfirmCovConversions @Var1 = @Var1, @Var3 = @Var3
 -- End issue 33385
 
  Create table #pageSeqTable (PageTitle varchar(50), PageSeq int)
  insert into #pageSeqTable
 select distinct PageTitleTxt, PageDisplaySequenceNbr
 from MyTable1
 where Column1 = @Var2

  select PageNm, RowNumber, FieldNm, PreviousValueTxt, EndorsedValueTxt, ActionTxt
  from    MyTable2, #pageSeqTable b
  where Id = @Var1 and PageNm = b.PageTitle
  order by b.PageSeq, RowNumber, ActionTxt desc, EntrySequenceNbr

  select @effDate = convert(char,EffectiveDate,101), @transEffDate = convert(char,TransactionEffectiveDt,101), @expDate = convert(char,LastTransactionEffectiveDt,101),
 @policyStatus = PolicyStatusCd, @reasAmendDesc = ReasonAmendedDes,
 @policyNumber = PolicyNumber,
 @riskState = StateName,
 @AmendPrem = convert(money,PremiumAmount)   /* Case 33385 */
  from SHPlaninfo A, SHSeleReasonAmended B, SHSeleStateCode C
  where Id = @Var1
       AND Val2 = (select max(Val2)
      from SHPlanInfo
      where Id = @Var1)
       AND B.ReasonAmendedCd = A.ReasonAmendedCd
       AND C.StateCode = A.RiskState
Select @PriorPrem = convert(money,PremiumAmount) FROM SHPlanInfo WHERE Id = @Var1 and Val2 = '0' 
Set @PremDiff = @AmendPrem - @PriorPrem            


  select EffectiveDate = @effDate

  select TransactionEffectiveDt = @transEffDate, ExpirationDate = @expDate, LastTransactionEffectiveDt = @lastTransDate

  select AmendXPolStat = @policyStatus

  select ReasonAmendedDes = @reasAmendDesc   

  select PolicyNumber = @policyNumber     

  select RiskState = @riskState      

  select PriorPremium =  @PriorPrem             
  select AmendPremium = @AmendPrem             
  select PremiumDifference = @PremDiff            
Select ClientNumber from SHClient with (nolock) where Id=@Var1 and  ApplicantRecordInd = 1     
delete from MyTable2 where Id = @Var1

return

 

View Replies !

---

Help Cursor Based Stored Procedure Is Getting Slower And Slower!
I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO

View Replies !

---

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View Replies !

---

Variable Type For Fetcing The Cursor Record In Tsql Cursor
what is the equivalent of the %rowtype in oracle for the tsql

View Replies !

---

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.
 

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.
 
here is the stored procedure:
 

Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval

 

GO

 
example useage:
 

EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View Replies !

---

Join Cursor With Table Outside Of Cursor
part 1

Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)

DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]

OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME


WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME

END

CLOSE DBCur
DEALLOCATE DBCur


Part 2

SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS


my question is,
how to join the part 1 n part 2?
is there posibility?

View Replies !

---

ODBC-based To OLE DB-based Data Transfer
I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!

View Replies !

---

Cursor Inside A Cursor
I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors




declare Q cursor for
select systudentid from satrans


declare @id int

open Q
fetch next from Q into @id
while @@fetch_status = 0
begin

declare c cursor for

Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END

From SaTrans , systudent b where satrans.systudentid = b.systudentid

and satrans.systudentid = @id




declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money

set @arbalance = 0
open c
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount

while @@fetch_status = 0
begin

set @arbalance = @arbalance + @amount
set @before = @arbalance -@amount

insert c2000_utility1..tempbalhistory1
select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance
where( convert (int,@amount) <= -50
or @amount * -1 > @before * .02)
and @type = 'P'




fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
end
close c
deallocate c
fetch next from Q into @id

end
close Q
deallocate Q


select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1

View Replies !

---

Client Side Cursor Vs Sever Side Cursor?
I having a difficult time here trying to figure out what to do here.I need a way to scroll through a recordset and display the resultswith both forward and backward movement on a web page(PHP usingADO/COM)..I know that if I use a client side cursor all the records get shovedto the client everytime that stored procedure is executed..if thisdatabase grows big wont that be an issue?..I know that I can set up a server side cursor that will only send therecord I need to the front end but..Ive been reading around and a lot of people have been saying never touse a server side cursor because of peformance issues.So i guess im weighing network performance needs with the client sidecursor vs server performance with the server side cursor..I am reallyconfused..which one should I use?-Jim

View Replies !

---

Report Based On Filtered Query OR Based On Custom Query
adp on SLQ7

What would be wise to do.

I'm creating a report based on two inner joined tables and i've got a total sum field for each line in the report. (Price * Ordered)

This results in the following sql statement:

Code:


sqlStr = "SELECT *, [Table1].Ordered * [Table1].Price AS LineTotal FROM [Table1] INNER JOIN [Table2] ON [Table1].RecieptNumber = [Table2].Number WHERE ([Table1].RecieptNumber = " & varNumber & ")"



I think I have a few options now.

1. Leave the varNumber out of the query, and save it as a regular query. And now filter the report on varNumber.

2. Write the constructed query with createquerydef to eg. "TmpQueryForReport" And set the reports recordsource fixed to TmpQueryForReport.

3. Set the recordsource of the report to constructed sqlStr on Report_open()

---------------------

Option 1, I got this working but when the database grows (and it will) this might get awfully slow.

Option 2
I had this working before i switched to using MSSQL server 7.00
After the switch i thought, this might be a problem with giving db access rights cause the users might need write rights to the db.

Option 3. Fast and easy???

Option 4 Stored procedures???

View Replies !

---

Questions About Memory Based Bulk Copy Operation(InsertRow Count,array Insert Directly,set Memory Based Bulk Copy Option)
Hi~, I have 3 questions about memory based bulk copy.

1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)?
For example, how much insert row at below sample?(the max value of nCount)
for(i=0 ; i<nCount ; i++)
{
    pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData));
}

2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)

3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ?
BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);

-------------------------------------------------------
My solution is like this. Is it correct?

// CoCreateInstance(...);
// Data source
// Create session

 m_TableID.uName.pwszName = m_wszTableName;
 m_TableID.eKind = DBKIND_NAME;

 DBPROP rgProps[1];
 DBPROPSET PropSet[1];

 rgProps[0].dwOptions  = DBPROPOPTIONS_REQUIRED;
 rgProps[0].colid   = DB_NULLID;
 rgProps[0].vValue.vt  = VT_BSTR;
 rgProps[0].dwPropertyID  = SSPROP_FASTLOADOPTIONS;
 rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";

 PropSet[0].rgProperties  = rgProps;
 PropSet[0].cProperties  = 1;
 PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;

 if(m_pIOpenRowset)
 {
  if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
  {
   return FALSE;
  }
 }
 else
 {
  return FALSE;
 }

View Replies !

---

Column Based On Other Column (short Name Based On Name), When To Do The Transformation?
Hi!
 
I am designing a dimension table which will include a short name column based on the (full) name column. For example say Product dimension where I will have ProductName and ProductShortName. ProductShortName will be the first 6 characters of ProductName. I could populate ProductShortName using:


Substring in the select when I select from the original system, e.g. SUBSTR(PRODUCT_NAME, 1, 6) AS ProductShortName

Create a derived column in the SSIS flow which does the same thing

Create the ProductShortName column as a computed column which uses substring on ProductName

Create a trigger that populates ProductShortName based on ProductName when a row is inserted or updated

Create a named calculation in the table in the Analysis Services project's data source view

Create a named query in the Analysis Services project's data source view

I usually use 1, and 5 or 6 would only be used if I only will create reports against the cubes. 3 seems easiest to maintain, so I am thinking about using that one, but maybe it is slow for the data flow as I imagine it must be something like using 4, or when is the column "created" at runtime, i.e. when the table is queried?
Which approach(es) do or would you use? Pros and cons?
 
Thanks!
 
 

View Replies !

---

SQL Replace '-' By ''
Hi all i have a question regarding sql, i want to replace some characters...
 
any knows simply how to do this?
 
I want to replace "999-25000-69" by "9992500069"
 
grtz

View Replies !

---

Replace?
i got a 100k rows column contain first name + last name. but half of them are got comma between first and last name. how can i update and remove all the comma. can anyone provide a statment please thanks so much

View Replies !

---

Replace
can I use a replace on text type field?

View Replies !

---

Using 'IN' To Replace 'EXCEPT'
Hi everyone,

I have wriiten a query that works great in sql server but MS Access does not support EXCEPT. I have to rewrite this query using 'IN' or 'NOT IN' but can not figure it out because it must be compared on all fields. I'm sure this will require multiple subqueries because 'IN' must return only one field but I can not rap my brain around it. Could someone please help with this? Here is the 'EXCEPT' query I know works.


Code Snippet
SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
sh.Balance, sh.Available_Bal FROM
(((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
Participation_Type ON ac.Participation_Id = Participation_Type.ID)
WHERE ac.INDIVIDUAL_ID = 5249
Except
SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
sh.Balance, sh.Available_Bal FROM
(((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
Participation_Type ON ac.Participation_Id = Participation_Type.ID)
WHERE ((ac.participation_id <> 101) AND (sh.share_type > 49 )) AND (ac.Individual_Id = 5249);  This code gets me close using 'IN'.
Code Snippet
SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
sh.Balance, sh.Available_Bal FROM
(((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
Participation_Type ON ac.Participation_Id = Participation_Type.ID)
WHERE (ac.INDIVIDUAL_ID = 5249) AND sh.member_nbr not in
(SELECT sh.member_nbr from Share sh inner join accountindividual ac on sh.member_nbr = ac.member_nbr AND sh.account_nbr = ac.account_nbr
WHERE ac.participation_id <> 101 AND sh.share_type > 49) AND sh.account_nbr not in
(SELECT sh.account_nbr from share sh inner join accountindividual ac on sh.member_nbr = ac.member_nbr AND sh.account_nbr = ac.account_nbr
WHERE ac.participation_id <> 101 AND sh.share_type > 49)  
  

View Replies !

---

How To Replace Having
Dear all,
here is one query,

IF EXISTS (SELECT SUM(DEBIT_AMOUNT) FROM @Ledger
HAVING SUM(DEBIT_AMOUNT)<>SUM(CREDIT_AMOUNT))

i'm trying to tuning myprocedures. i think having is not good enough to use.

how can we replace having in this case.....

thank you very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

View Replies !

---

Using Replace
I have a colunm in a table that has a list of email address.
I want to remove a certain email address from that colunm, but it is in 54 different rows. What is the best way to do this ?

View Replies !

---

Replace Roa
i just wanna replace the last word 'roa' to 'road'
what is wrong with my code. I dont relaly see any problem. But it's replaceing all the 'roa' to 'road'.

declare @table table(ad_str1 varchar(20))
insert @table
select 'street road roa' union all
select 'street street'

select ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))) from @table

update @table
set ad_str1 = replace(ad_str1, ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))), 'road')
where ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))) = 'roa'

select * from @table

View Replies !

---

Replace '
hi

when i execute ...
select * from Members where address = '6257 Rockwell's'
ERROR :

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 's'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string '
'.


Kindly help me how to solve '

View Replies !

---

How To Do REPLACE In SQL Queries
Hi,
I am having a situation where I need to update a column in my SQL table that contains a link to an image file. Basically ...
I have this stored in a column IMAGESRC
  Project/aa11be5d-dd9e-48c8-9d8c-6a972e996b28/ProjectImages/702d_2.jpg I need to change it to this
Project/NEWUSERID/ProjectImages/702d_2.jpg
 How can I accomplish this in SQL???
 
thanks in Advance
Dollarjunkie  

View Replies !

---

Replace, The ' Character, And Yea...
I have the following:----------------- WHILE PATINDEX('%,%',@Columns)<> 0 BEGINSELECT @Separator_position = PATINDEX('%,%',@Columns)SELECT @array_Value = LEFT(@Columns, @separator_position - 1)SET @FieldTypeID = (SELECT FieldTypeID FROM [Form].[Fields] WHERE FieldID = (CAST(@array_Value AS INT)))SET @FieldName = (SELECT [Name] FROM [Form].[Fields] WHERE FieldID = @array_Value)print 'arry value' + CONVERT(VarChar(500), @array_value)print 'FieldTypeID: ' + CONVERT(VARCHAR(500), @FieldTypeID)PRINT 'FieldName: ' + @FieldNameBEGINIF @FieldTypeID = 1 OR @FieldTypeID = 2 OR @FieldTypeID = 3 OR @FieldTypeID = 9 OR @FieldTypeID = 10 OR @FieldTypeID = 7BEGINSET @InnerItemSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[ItemDetailFieldRecords] IDFR WHERE IDFR.ItemDetailID = ID.ItemDetailID AND IDFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[TaskFieldRecords] TFR WHERE TFR.TaskID = T.TaskID AND TFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeID = 4 OR @FieldTypeID = 8 --DropDownList/RadioButtonlistBEGINSET @InnerItemSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[ItemDetailFieldListRecords] IDFLR ON FFLV.FieldListValueID = IDFLR.FieldListValueID WHERE IDFLR.ItemDetailID = ID.ItemDetailID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[TaskFieldListRecords] TFLR ON FFLV.FieldListValueID = TFLR.FieldListValueID WHERE TFLR.TaskID = T.TaskID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeiD = 5 --CascadingBEGINSET @InnerItemSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[ItemDetailFieldCascadingRecords] IDFCR ON IDFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE IDFCR.ItemDetailID = ID.ItemDetailID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[TaskFieldCascadingRecords] TFCR ON TFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE TFCR.TaskID = T.TaskID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeiD = 6 --ListBoxBEGINSET @InnerItemSelect = ' (SELECT i.[CSV] FROM @ItemDetailLV i WHERE i.ID = ID.ItemDetailID AND i.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT it.[CSV] FROM @TaskLV it WHERE it.ID = T.TaskID AND it.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeID = 11 --UsersBEGINSET @InnerItemSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[ItemDetailUserRecords] IDUR ON SU.UserID = IDUR.UserID WHERE IDUR.ItemDetailID = ID.ItemDetailID AND IDUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[TaskUserRecords] TUR ON SU.UserID = TUR.UserID WHERE TUR.TaskID = T.TaskID AND TUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FIelDTypeID = 12 --GroupBEGINSET @InnerItemSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[ItemDetailGroupRecords] IDGR ON SG.GroupID = IDGR.GroupID WHERE IDGR.ItemDetailID = ID.ItemDetailID AND IDGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[TaskGroupRecords] TGR ON SG.GroupID = TGR.GroupID WHERE TGR.TaskID = T.TaskID AND TGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDENDPRINT 'Inner Item Select:' + @InnerItemSelectPRINT 'Inner Task Select:' + @InnerTaskSelectSET @IDSelect = @IDSelect + @InnerItemSelect + ', 'SET @TSelect = @TSelect + @InnerTaskSelect + ', 'SELECT @Columns = STUFF(@Columns, 1, @separator_position, '')END   --------------- That is only part of a large query that writs a SQL Query to a column in a Database. That Query (in the column) is just ran normally so I don't need to compile it each time I want to run it.THe problem I have is  @FieldName might be:     ryan's field. That apostrophe is killing me because the SQL keeps it as ryan's field, not ryan''s field(note the 2 apostrophes).  I cannot do: REPLACE(@FieldName, ''', '''') because it's not closing the apostrophes. Is there an escape character that I can use to say only one: ' ?Would the only solution be to put: ryan''s field into the Database, and just format it properly on the output?  Thanks. 

View Replies !

---

Replace &<br&> Tag In CRLF
Hi all,
I made a migration operation and when I am looking inside database I can see the <BR> tags.
The question is how to write a query that replace all of this occurances of <BR> inside the colum table, with CLRF which is a new line code.
Thanks in advance.
 
 
 

View Replies !

---

Find And Replace
To ensure I don't leave orphans floating around in tables when records get deleted (values from one record might link to values in another) how do I find and possibly replace values in tables?For example, if I have a unit of measure table and want to delete the value "inches", how do I look in other tables to find this value and give the user the option to cancel or clear it out. If I don't it will cause controls bound to that value like the dropdownlist to throw an error.

View Replies !

---

Replace Function In SQL?
Hi,

I have a table with a field called productname, and it has about 5000 rows, and within that about 1000 have a productname that has 'NIB' in the name, ie "My Product NIB DVD" and I have been asked to replace 'NIB' with 'New' ie "My Product New DVD" Can I do this in SQL using an Update statement? Or do I have build something in maybe asp.net to use a replace function to change the name.

Thanks

View Replies !

---

Using Replace Function
I am using MSDE. In this I have a field that contains a desciption. Prior to saving to the db, I replace all vbcrlf's to <br />'s. That works fine when displaying in HTML, but when I display it in a datagrid, I want to replace the <br />'s with vbcrlf's.

I thought I might be able to do it with a replace function in my SQL query, something like "Select ID, replace(Description, '<br />', vbcrlf) as Description". This produces an error that vbcrlf is not a fieldname. So I tried "select replace(Description, '<br />', 'xxx') as Description", and while this did not create an error, neither was the text replaced.

Am I not using the replace function correctly? Is there another way in which I might accomplish the task?


Dim ConnectionString As String = "server='(local)'; trusted_connection=true; database=dbname"
Dim CommandText As String

'Command text is greatly abreviated for this discussion.
CommandText = "Select ID, replace(Description, '<br />', vbcrlf) as Description"

Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(CommandText, myConnection)

Dim ds As New DataSet()
Dim dv as new dataview()
myCommand.Fill(ds)

DataGrid1.DataSource = ds
DataGrid1.DataBind()
...
...
...

View Replies !

---

Find And Replace.
Does anybody know how to search through all stored procedures (in SQL Server 2000) and find and replace text without having to open each one individually in Query Analyzer and doing so one at a time?

That would be so time consuming. I want to be able to change my table names, but I have so many stored procedures allready using the old names. To go and find each name and replacing them is a task I don't want to even try and do.

Thank you to whomever can help.

Alec

View Replies !

---

REPLACE ISSUE..
Hi All,I have the following STRING - 'D or Shorty'Struggling with the syntax required to replace the 'or' with ; withoutalso replacing the 'or' in 'Shorty' with ;e.g.ORIG STRIMG:D or ShortyREQUIRED STRING:D ; ShortyThanks in advance

View Replies !

---

How To Run Replace On All Columns
Here is my replace query and I need to run this on every column in mytable. Right now I manually enter the column name (_LANGUAGES_SPOKEN)but this is time consuming and would like to automate this process asmuch as possible.Update PROFILESET LANGUAGES_SPOKEN = replace(cast(_LANGUAGES_SPOKEN asnvarchar(255)),char(13)+char(10),':')Thanks,JP

View Replies !

---

Create Or Replace
Looking for something analogous to the Create Or Replace option in Oracle. Is there an equivalent command in SQL Server?

View Replies !

---

Replace Data Using Sql
I have a database of about 300,000 records.
The records were imported from a csv file.
One of the fields is duration.

The data in duration are like ths:
1 second: 0:01
26 minutes: 26:00

If i put the format of the field as time, the data are messed up.
0:01 becomes 1 minute.
26:00 becomes 1 day 2 hours.

I currently have duration as text.

How can i use sql or visual basic to replace all the data so that they can have the format "00:00:00"?
(0:01 becomes 00:00:01, 26:00 becomes 00:26:00)

I need the duration in time format in order to be able to make sum calculations.

I will be doing the same calculations every month so i need the above procedure to be able to execute it every time i need to.

Thank you in advace

George

View Replies !

---

Replace Query? Possible?
Is it possible to make a replace query in SQL?

So Find What, Replace With

I have a table with email addresses. But now for more than 500 people it must be changed from user@company.nl to user@COMPANY2.nl

Is this possible?

Thanks in advance!

View Replies !

---

REPLACE Name If Code Like
Hello

for MS SQL 2000

I want to replace a value if a Code LIKE 'A%'

MyTable :
ID
Name
Code

SELECT ID, Name, Number FROM MyTable

but if Code LIKE 'A%' then Name = 'LAN'

how can i do that ?

thank you

View Replies !

---

Replace Funtion
how do i write a replace function that will replace a certain character with a return key (ie what happens when we do Ctrl+return key in SQL Enterprise table... so that the rest of the cell data in the column is on the next line?!

SELECT REPLACE(tasks, '/', '????') AS EXPR1
FROM log_descriptions


what should ???? be?

View Replies !

---

Replace Command
can the command replace use variables ie

replace(@Name,'.com','.com;')

im getting an error with this and i was just wondering if it was just me
Jim

View Replies !

---

Does T-sql Has Replace(in Vb) Function?
I want to perform Replace(searchstring, oldstring, newstring) like function in T-sql as same as in VB..

is there existing one or any other user-defined function?

thanks...

View Replies !

---

SQL To Replace String
Hi,
I need to write a single replace sql as folows:

I have a string consisting of numbers seperated by a space. Some of the numbers are suffixed with a star like this: '1 12* 5 7*'

I need to remove those numbers that are suffixed with a star. In other words, I need an output as follows: '1 5'


any help would be appreciated

Thanks

View Replies !

---