How To Write Set-based SQL Instead Of Cursor

How To Write SQL Query And Not Cursor ?
Please help me to get the required result:
For each IDS in table1
- change the ids to numbers (eg. for '1,2,3' get the numbers (IntValue) 1, 2 & 3)
- in table2, find the maxVal for each number
- disply the table1..ids, number, table2..maxVal & table1..idsDesc,
order by table1..ids, table2..maxVal & IntValue

I have 2 tables, over milin records each. The Simplified versions of the tables looks like that:

create table table1 (ids varchar(100), idsDesc varchar(100))
go
insert table1 select '1,2,3', 'Description 1'
union all select '2,3,4', 'Description 2'
union all select '1,7', 'Description 3'
union all select '16,3,8', 'Description 4'
union all select '2,5,6,1', 'Description 5'
go

create table table2 (ids int, maxVal int)
go
insert table2 select 1, 10
union all select 2, 6
union all select 3, 12
union all select 4, 11
union all select 5, 66
union all select 6, 4
union all select 7, 3
-- union all select 8, 5 -- no value for 8
union all select 9, 6
union all select 16, 12
go

I have also function that returns table variable of numbers delivered from given string:
create function dbo.fn_StrToIntValues ( @str varchar(1000) )
returns @numsTbl table (IntValue int not null)

The command
select * from dbo.fn_StrToIntValues('1,2,33')

Returns --> intValue
1
2
33

Can I use SQL query and not cursor to get the following result ?

Required Output :
ids IntValue maxVal idsDesc
--------- -------- ------- ---------------
'2,5,6,1' 5 66 'Description 5'
'16,3,8' 16 12 'Description 4'
'2,3,4,9' 3 12 'Description 2'
'1,2,3' 3 12 'Description 1'
'1,7' 1 10 'Description 3'

View Replies !

---

How To Put Condition In Select Statement To Write A Cursor
col1          col2 col3   col4
36930.60   145    N   . 00
17618.43   190   N    . 00
6259.20    115    N    .00
8175.45     19    N     .00
18022.54   212   N    .00
111.07      212   B     .00
13393.05   67   N     .00
In above 4 col
if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
here col2 values are unique.

Can any one reply for this..............

View Replies !

---

Does Cursor Convert Table To Read/write?
Hello,

Any help here much appreciated.

I am using sql server 2000 to perform address cleansing. there is a point in my scripting when a table i pass values to becomes read/write.

i suspect this is when i run a cursor through the table.

Is anyone able to confirm for me whether running a cursor changes a table's properties?

Many thanks.

Tim

Ps as the table seems to be read/write it is harder to tell if NULLs are in the table and this is messing with joins I have further down the track.

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 !

---

How To Write Trigger Based On Certain Condition
One of my table called as 'customertable' contains following fields

customername, emailid, subscriptionendperiod

Example records are:

david, david@john.com, 12/20/2005(mm/dd/yyyy format).

My question is that: Just one month before subscriptionendperiod that is on 11/20/2005(mm/dd/yyyy) an automatic email should go to david@john.com with the message 'Your subscription period ends on 12/20/2005'

How to write trigger for this.

Please Note : No ASP code is invloved in this.
Only MSSQL coding to be done.


Regards

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 !

---

How To Replace Cursor Based Statements With Set Ba
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]
@Var1varchar(20),
@Var2varchar(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 @ActionTxtvarchar(1)
declare @Descriptionvarchar(20)
declare @effDatevarchar(10)
declare @transEffDatevarchar(10)
declare @expDatevarchar(10)
declare @lastTransDatevarchar(10)
declare @policyStatusvarchar(2)
declare @reasAmendDescvarchar(50)
declare @policyNumbervarchar(20)
declare @riskStatevarchar(20)
declare @PriorPremmoney
declare @AmendPremmoney
declare @PremDiffmoney
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) '

set @ConvertSQL = @ConvertSQL + ' declare @Var3 varchar(2) '
set @ConvertSQL = @ConvertSQL + ' set @Var3 = ''' + @Var3 + ''''

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

if @Var2 = 'PA '

--exec PAConfirmCovConversions @Var1 = @Var1
exec PAConfirmCovConversions @Var1 = @Var1, @Var3 = @Var3


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)
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 = @PremDiffSelect ClientNumber from SHClient with (nolock) where Id=@Var1 and ApplicantRecordInd = 1
delete from MyTable2 where Id = @Var1

return

View Replies !

---

Select Criteria Based On Date - Cleaner Way To Write?
Code:

SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS +
AUG_CURR_CREDITS + AUG_CURR_DEBITS +
SEP_CURR_CREDITS + SEP_CURR_DEBITS +
OCT_CURR_CREDITS + OCT_CURR_DEBITS +
NOV_CURR_CREDITS + NOV_CURR_DEBITS +
DEC_CURR_CREDITS + DEC_CURR_DEBITS +
JAN_CURR_CREDITS + JAN_CURR_DEBITS +
FEB_CURR_CREDITS + FEB_CURR_DEBITS +
MAR_CURR_CREDITS + MAR_CURR_DEBITS +
APR_CURR_CREDITS + APR_CURR_DEBITS +
MAY_CURR_CREDITS + MAY_CURR_DEBITS +
JUN_CURR_CREDITS + JUN_CURR_DEBITS) as CURR_AMT



I need to sum these amounts running from July to the month prior to whatever the current month is. So if it was August, it would only be

Code:

SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS) as CURR_AMT



Is there a cleaner (shorter) way to iterate through the twelve months than either writing the query 12 times in an IF statement, or 12 CASE statements? This is only part of a query that joins several tables (not shown).

Any suggestions on the best way to write this would be valued.

View Replies !

---

Write A Stored Procedure Based On Recursive Data.
Hello,  I am hoping someone can help me in this.  I am looking to write a stored procedure that will return  the heirarchy of an organization.  I will display how the heirarchy might look and then list the tables involved.
 
John Smith

- Jacob Jones
- Lisa Thompson
- Samuel Barber

- Paul Smith
- John Jackson
 
Ok, so Jacob, Lisa, an Samuel report up to John Smith.  Paul and John Jackson report up to Samuel Barber.
 
Here are the tables:
 
Users holds the user_id, first_name, last_name, and reports_to_user_id.
User_Roles holds the user_id, role_type_id
Role_Types holds the role_type_id, and the type (which could be Administrator, Standard, Guest) for example.  In addition, Role_Types also has ranking which must be taken into consideration as well.  1 being the top rank and 9 being the lowest.
 
Thanks very much in advance,
Saied

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 !

---

How Do I Write Multiple Pipeline Buffer To Multiple Targets Based On A Calculated Value In The Pipeline Buffer
The scenario is as follows: I have a source with many rows. Each row has a column called max_qty_value. I need to perform a calculation using another column called qty. This calculation is something similar to dividing qty/(ceiling) max_qty_value. Once I have that number I need to write an additional duplicate row for each value from the prior calculation performed. For example, 15/4 = 4. I need to write 4 rows to the same target table as in line information for a purchase order.

 

The multicast transform appears to only support fixed and/or predetermined outputs. How do I design this logic in SSIS to write out dynamic number of rows to a target table.

 

Any ideas would be greatly appreciated.

 

thanks

John

View Replies !

---

Backup Master Key, Cannot Write Into File 'c: Empmaster'. Verify That You Have Write Permissions, That The File Path Is Valid.
Hi,

 

I tried to backup the master key by the following syntax :

     OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'

     BACKUP MASTER KEY TO FILE = 'c: empmaster'  ENCRYPTION BY PASSWORD = 'mypassword'

but it failed and i got the following message:

Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

NB: I am using the "sa" user to execute this command.

I know that we have a security permission issue , but where and how ?

 

Regards,

Tarek Ghazali

SQL Server MVP

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 !

---

How To Write This SQL
 How to write this SQLSelectCommand="SELECT DISTINCT TblOrder.CustomerUID, TblOrder.OrderHiddenID, TblPayment.PaymentAmount, TblPayment.Result, TblOrder.OrderID FROM TblOrder CROSS JOIN TblPayment WHERE (TblOrder.CustomerUID = @IsCustomerID) AND (TblOrder.OrderHiddenID = @IsHiddenID) AND (TblPayment.Result = 'Pending')"How to get latest order id from this.I need to combine it with above sql.I mean i want to select above records but based on max orderid record.such as select latest records from ....above SQL where max orderid

View Replies !

---

Need Help To Write SQL
Hi,I
have a hotel reservation system.I need to implement Check availability
(Room checking) function for the project.But I dont understand how do I
start and write SQL for this.Here is table structureTblRooms------------RoomsIDRoomNameNoteunitPriceSeasonalOffersTblReservation----------------ReservationIDArrivalDateDepartureDateArrivalFromFlightNoPurposeOfVisitTblRoomsInventory-------------------RoomsInventoryIDTotalRoomsBookedRooms Should I add more fields or table to implement this or this is enough .please any body can help me 

View Replies !

---

How To Write To SQL
I'm sure this is really basic. I've created a simple form that on submittal, I would like the text boxes to be submitted to the database. I have successfully created the Datasource and it connects ok, but what code would i use to submit one text box for email address and a simple submit button. Is there an easy way to do this with VWDE?
 Thanks

View Replies !

---

How To Write This
Hello,
I have MyTable with ID, IsYesNo fields
ID is duplicated so I need perform select on MyTable with the following conditions:
1. Select all the ID distinct where IsYesNo=’Yes’ first
2. Then select all the ID distinct where IsYesNo=’No’ if ID is not in the first selection
Combine these two and return the result
 

View Replies !

---

How To Write This Sql
I have table T1 with fields T1.ID, T1.CheckBoxCol
 
The T2 tabel has the same ID as T2.ID and T2.CheckBox1, T2.CheckBox2, T2. CheckBox3
 
Now, I need to check T1 and
if T1.CheckBoxCol=1 then set T2.CheckBox1=1
else if T1.CheckBoxCol=2 then set T2.CheckBox2=1
else if T1.CheckBoxCol=3 then set T2.CheckBox3=1
 
for each T1.ID
 
How can I do this?
 

View Replies !

---

How To Write This SQL
I have a table T1
ID Desc
1 aaaa
1 bbbb
1 cccc
2 aaaa
2 cccc
3 cccc
 
Now, I need to group by ID and make Desc column as follows in a new table
ID Desc
1 aaaa, bbbb, cccc
2 aaaa, cccc
3 cccc
 
How can I do this?
 

View Replies !

---

How To Write This?
I need to compare two column in two different tables but I do not know how to write this query.

Example:


Table1
-------------
EmployeeSSN
EmployeePayRate

Table2
-------------
EmployeeSSN
EmployeePayRate

I need the result set to look like this

Result Set

-----------------------------------------------------
EmployeeSSN T1Rate T2Rate Same?
-----------------------------------------------------
111-11-1111 8.95 8.95 True
222-22-2222 9.95 8.95 False
333-33-3333 8.95 NoMatch False
444-44-4444 NoMatch 8.95 False

and so on....

How do I write this in Transact-SQL also using a function to return the "NoMatch" and True or False statements?

Thanks,
Roger

View Replies !

---

Is There A Better Way To Write This SQL ?
I wanted to get some advice on a report I am generating using t-SQL.... Basically I am wondering if there is a more efficent way to write this query. The performance, so far is not too bad (MUCH faster than the report ran using inline SQL no sprocs and response.write tables). I am binding this entire SQL result to a datagrid for presentation in a browser.

Here's the storedProc...


proc usp_PromotionSummary
@PromotionTypeID int
AS


SELECT p.PromotionCode, p.Name PromoName, p.promotionid,
MAX(l.BlastDate) AS BlastDate, SUM(l.Quantity) AS Quantity,

(SELECT COUNT(t.TrackingID)
FROM Tracking t
INNER JOIN promotion_list pl on t.promotion_list_id = pl.promotion_list_id
WHERE pl.promotionid = p.promotionid and t.pageid = 1) as Hits,

-----------------------------
(CASE SUM(l.Quantity)
WHEN 0 THEN 0
ELSE
cast((SELECT COUNT(t.TrackingID)
FROM Tracking t
INNER JOIN promotion_list pl on t.promotion_list_id = pl.promotion_list_id
WHERE pl.promotionid = p.promotionid and t.pageid = 1) as decimal) / SUM(l.Quantity) * 100
END) AS PercHitsOfQty,

-----------------------------
(SELECT sum(l.cost)
FROM List l
INNER JOIN Promotion_List pl
ON l.Listid = pl.listid
WHERE pl.promotionid = p.promotionid) as TotalCost,

-----------------------------
(CASE(SELECT COUNT(t.TrackingID)
FROM Tracking t
INNER JOIN promotion_list pl on t.promotion_list_id = pl.promotion_list_id
WHERE pl.promotionid = p.promotionid and t.pageid = 1)
WHEN 0 THEN 0
ELSE
(cast((SELECT sum(l.cost)
FROM List l
INNER JOIN Promotion_List pl
ON l.Listid = pl.listid
WHERE pl.promotionid = p.promotionid) as decimal) /

(SELECT COUNT(t.TrackingID)
FROM Tracking t
INNER JOIN promotion_list pl on t.promotion_list_id = pl.promotion_list_id
WHERE pl.promotionid = p.promotionid and t.pageid = 1))

END) AS CostPerHit,

-----------------------------
(SELECT COUNT(t.TrackingID)
FROM Tracking t
INNER JOIN promotion_list pl on t.promotion_list_id = pl.promotion_list_id
WHERE pl.promotionid = p.promotionid and t.pageid = 1 and t.submittedyn = 1) as Submits,

-----------------------------
(CASE (SELECT COUNT(t.TrackingID)
FROM Tracking t
INNER JOIN promotion_list pl on t.promotion_list_id = pl.promotion_list_id
WHERE pl.promotionid = p.promotionid and t.pageid = 1)
WHEN 0 THEN 0
ELSE
cast((SELECT COUNT(t.TrackingID)
FROM Tracking t
INNER JOIN promotion_list pl on t.promotion_list_id = pl.promotion_list_id
WHERE pl.promotionid = p.promotionid and t.pageid = 1 and t.submittedyn = 1) as decimal) /

(SELECT COUNT(t.TrackingID)
FROM Tracking t
INNER JOIN promotion_list pl on t.promotion_list_id = pl.promotion_list_id
WHERE pl.promotionid = p.promotionid and t.pageid = 1) * 100

END) AS PercSubmitsOfHits,

-----------------------------
(select count(distinct cast(ch.contactid as nvarchar(50)) + CAST(ch.promotion_list_id as nvarchar(50)))
from history h
inner join registrationhistory rh ON h.registrationhistoryid = rh.registrationhistoryid
inner join contacthistory ch ON h.contacthistoryid = ch.contacthistoryid
inner join promotion_list pl ON ch.promotion_list_id = pl.promotion_list_id
WHERE pl.promotionid = p.promotionid and rh.contactid <> 0) as Registrants,

-----------------------------
(CASE (SELECT COUNT(t.TrackingID)
FROM Tracking t
INNER JOIN promotion_list pl on t.promotion_list_id = pl.promotion_list_id
WHERE pl.promotionid = p.promotionid and t.pageid = 1 and t.submittedyn = 1)
WHEN 0 THEN 0
ELSE
cast((select count(distinct cast(ch.contactid as nvarchar(50)) + CAST(ch.promotion_list_id as nvarchar(50)))
from history h
inner join registrationhistory rh ON h.registrationhistoryid = rh.registrationhistoryid
inner join contacthistory ch ON h.contacthistoryid = ch.contacthistoryid
inner join promotion_list pl ON ch.promotion_list_id = pl.promotion_list_id
WHERE pl.promotionid = p.promotionid and rh.contactid <> 0) as decimal) /

(SELECT COUNT(t.TrackingID)
FROM Tracking t
INNER JOIN promotion_list pl on t.promotion_list_id = pl.promotion_list_id
WHERE pl.promotionid = p.promotionid and t.pageid = 1 and t.submittedyn = 1) * 100
END) AS PercRegOfSubmits,

-----------------------------
(CASE SUM(l.Quantity)
WHEN 0 THEN 0
ELSE
cast((select count(distinct cast(ch.contactid as nvarchar(50)) + CAST(ch.promotion_list_id as nvarchar(50)))
from history h
inner join registrationhistory rh ON h.registrationhistoryid = rh.registrationhistoryid
inner join contacthistory ch ON h.contacthistoryid = ch.contacthistoryid
inner join promotion_list pl ON ch.promotion_list_id = pl.promotion_list_id
WHERE pl.promotionid = p.promotionid and rh.contactid <> 0) as decimal) / SUM(l.Quantity) * 100
END) AS PercRegOfQty,

-----------------------------
(CASE (select count(distinct cast(ch.contactid as nvarchar(50)) + CAST(ch.promotion_list_id as nvarchar(50)))
from history h
inner join registrationhistory rh ON h.registrationhistoryid = rh.registrationhistoryid
inner join contacthistory ch ON h.contacthistoryid = ch.contacthistoryid
inner join promotion_list pl ON ch.promotion_list_id = pl.promotion_list_id
WHERE pl.promotionid = p.promotionid and rh.contactid <> 0)
WHEN 0 THEN 0
ELSE
(cast((SELECT sum(l.cost)
FROM List l
INNER JOIN Promotion_List pl
ON l.Listid = pl.listid
WHERE pl.promotionid = p.promotionid) as decimal) /

(select count(distinct cast(ch.contactid as nvarchar(50)) + CAST(ch.promotion_list_id as nvarchar(50)))
from history h
inner join registrationhistory rh ON h.registrationhistoryid = rh.registrationhistoryid
inner join contacthistory ch ON h.contacthistoryid = ch.contacthistoryid
inner join promotion_list pl ON ch.promotion_list_id = pl.promotion_list_id
WHERE pl.promotionid = p.promotionid and rh.contactid <> 0))
END) AS CostPerReg




FROM Promotion p
INNER JOIN Promotion_List pl ON p.promotionid = pl.promotionid
INNER JOIN List l ON pl.ListID = l.ListID
WHERE p.PromotionTypeID = @PromotionTypeID

GROUP BY p.PromotionCode, p.Name, p.promotionid



Basically I am using sub-queries to generate totals, calculations, and cost stats. The idea is to display statistics based on a PromotionType. Basic data structure as follows:

-1 PromotionType per Promotion
-A Promotion can have multiple lists
-Promotion_List table marries the Promotions and Lists to generate a unique Promotion_List_ID which uniquely defines the Promotion/List relationship.
- Tracking table tracks Hits, Submits, ContactID, and Promotion_List_ID
- Contacts are placed in Contact, Reg, Permiussion tables, then replicated to History tables (RegHistory, ContactHistory, PermissionHistory) which are married by the History table which stored unique ID identifying which _History tables relate to eachother.

Is there a more efficent way to write this query?
Is it "bad practice" to do the calculations inside the sProc?
Thoughts or ideas?

Thanks in advance.... i can prodivde more info if necessary...

-e

View Replies !

---

SQL To Write?
Hey peeps, fishkake's back, and he's more clueless than ever!

OK, after a few days of wrestling with books and experimenting, I now know all about reading SQL. Well, what that means is I understand Select From Where etc etc.

How do I write data? I have a reference guide, if somebody could give me literally a few high-level commands that are to do with writing data in a similar way to reading it with the SELECT statement, it would be very helpful...

This site is just making me lazy now...

View Replies !

---

Help To Write A Qry
Many thanks in advance for anyone that can help me write this qry:

To summarise - we have a database that links components to services. Components can have a 1 to many relationship with services. The components are held in a table:

dbo.components

compID compName compType
1 srv1 1
2 srv2 1
3 srv3 1

We also have a services table which hold all our various services we own:

dbo.services

svcID svcName
1052 svc1
1053 svc2
1054 svc3

We then have a tbl that shows the links between components and services

dbo.compUses

svcID compID
1052 310
1052 400
1053 122
1256 134

I would like to find out through the qry what components are currently NOT linked to a service. This will allow me to find out what components have no relationships.

This is in SQL2005.

Have I explained this well enough? Any help would be much appreciated!

View Replies !

---

Hot To Write This SP
Hey all.. Sorry I have never written a SP in my life.
so this would be a newbie question for sure.
Using sql server 7.

Here is my table field setup with sample values all fields are numeric
except date

weekending size effic dailycap workdays planutil demand
variance
5/172002 8 80 85 5 244
102 142

A file with source data will be made available in comma delimitted format to
supply a new weekending value, size and demand.

What I will need is first the dts to bring that in. then I am assuming a
stored procedure to be run (this is why I am here)
to add the data from the comma del. file into the table. If the data EXISTS
I would want it to UPDATE the values that are in the dest table but run a
calculation first which would be the planutil minus demand then the result to
be updated intot the record.
if the record from the tab del. file does not exisst in the dest table then
insert it.


another words the logic i have in mind
read the data from the temp tample (where the file gets imported into)
see if the record exists in the live table is it does update it with the calculation of planutil minus demand
if not create the new record in the live table.
I need it to compare..

Someone help me with some code
i thank you kindly in advance

p.s. And good books dedicated to stored procedures??

View Replies !

---

How To Write This?
 

Hello all!
I'm triyng to update a NULL value to another value.  I know I can do this, just can't seem to write it out right.
 
Something like
UPDATE Part_Order
SET Ser = 'NA'
WHERE Ser isnull
 
I know thats not right, but I know I'm close.
Any hints?
 
TIA!
 
Rudy

View Replies !

---

How To Write Log?
Hi There,

I have been working in ssis and i have a task to write a log of the all events in daily basis.

I mean writing a log for the entire package for each tasks.

Can some one help me how to do it?

Thanks

 

 

View Replies !

---

How To Write This Sql
I have table T1 with fields T1.ID, T1.CheckBoxCol

The T2 tabel has the same ID as T2.ID and T2.CheckBox1, T2.CheckBox2, T2. CheckBox3

Now, I need to check T1 and
if T1.CheckBoxCol=1 then set T2.CheckBox1=1
else if T1.CheckBoxCol=2 then set T2.CheckBox2=1
else if T1.CheckBoxCol=3 then set T2.CheckBox3=1

for each T1.ID

How can I do this?

View Replies !

---

How To Write This SQL
I have a table T1
ID Desc
1 aaaa
1 bbbb
1 cccc
2 aaaa
2 cccc
3 cccc
 
Now, I need to group by ID and make Desc column as follows in a new table
ID Desc
1 aaaa, bbbb, cccc
2 aaaa, cccc
3 cccc
 
How can I do this?
 

View Replies !

---

How To Write An UDF And Run It
how to write an UDF and run it

View Replies !

---

Best Way To Write This
Hi,
I have to implement a search functionality. In the various filters for the search, Store Number is one such filter.
The user should be able to enter range values for store numbers.
Like 1500-1600. So this should filter for all the stores between 1500 and 1600.
Similarly, all these also should be valid.
1550,1600
1550
1550 - 1580,6000,8000
etc.
I have function which identifies the commas or dashes and seperates out the store number and returns a string like
Stores.Storenumber in(1555,1600)
Store.StoreNumber between 1555 and 1600 etc...
i generate a sql at run time and append this piece and then execute the sql.
I have one of the query below.

declare @strQuery varchar(max)
declare @strConcat varchar(10)
declare @strAppend varchar(max)
set @strAppend=''
set @StrConcat ='And '

if @IsAdmin is null-- Not a Admin
set @StoreId =(select StoreNumber from Stores where Store_Id = @StoreId)

set @strQuery='
Select
(Select StoreNumber from Stores where Store_id=d.DestinationId) as StoreNumber,
CartonNumber,
ActualReceiptDate as [Scan Date],
isnull(Sum(QtyShipped),0) as [Total Units],
b.BatchNumber
from
Carton c
left outer join
CartonDetail Cd on Cd.Carton_Id = c.Carton_ID
inner join Batch b on b.General_Id = c.Carton_Id and b.BatchType=''Warehouse'' and b.TranTable=''Carton''
inner join Document d on d.Document_ID = c.document_Id
inner join Stores st on st.Store_ID = d.SourceID and st.StoreType =5
inner join Stores on Stores.Store_ID = d.DestinationID
inner join Codelist cl on cl.Codelist_Id = c.CartonStatus_ID
inner join Codes on Codes.Code_ID = cl.Code_id and Codes.CodeType=''Cartons Status Code''
where not c.cartonNumber is null '

if not (@StoreId) is null
begin
set @strAppend = @strConcat + '(' + dbo.DecodeStoreNo(@StoreId) + ')'
End

if not (@DateFrom) is null and not (@DateTo) is null
begin
set @strAppend = @strAppend + @strConcat + '(convert(varchar(50),c.ActualReceiptDate,101) between ''' + @DateFrom + ''' and ''' + @DateTo + ''')'
End

if not (@CartonNumber) is null
Begin
set @strAppend = @strAppend + @strConcat + '(c.CartonNumber = ''' + cast(@CartonNumber as varchar) + ''')'
End

if not (@Status) is null
Begin
set @strAppend = @strAppend + @strConcat + '(cl.Codevalue = ''' + @Status + ''')'
End


set @strAppend = @strAppend + ' group by
d.DestinationId,CartonNumber , ActualReceiptDate , b.batchnumber
order by ActualReceiptDate'

set @strQuery = @strQuery +@strAppend
execute(@strQuery)
This query takes time, if there a little over 1000 records.
I wanted to know, if there is any way to optimize this query? or any other way in which the above can be accomplished.

I hope i was able to explain my query fairly. Please let me know otherwise.

Thanks
Renu.

View Replies !

---

How Do I Write This
Good Afternoon all,
I was wondering how to write the TOP 5 records using Relational Algebra.

Dallr

View Replies !

---

How To Write And Execute?
 
Hi all,
      i want to write a stored procedure to get the user details from userprofile table based on the time schedule from another table called reminders.
   the structure of reminder table is called
       jobtype - type of job that is sending mails to user or sending mails to employee like that
       frequency - how frequenctly sending mails that is daily or weekly or monthly
       weekday    -  if the above option is weekly then we have to take which day on weekly to send mail
       dayofmonth - if the frequency option is monthly then we have to take this field for which day on month
      jobtime - At what time to send mails it may be daily or weekly or monthly
so i want to write the stored procedue based on above values to execute some task( that is getting userdetails and sending mails).
please help me.
Thanks!

View Replies !

---

Please Help Me To Write A Sql Query
I have two table named tbl_Scale and tbl_NGTrDAMaster
tbl_Scale(ScaleID,ScaleName,ScaleLB,ScaleUB,ScaleSI1,ScaleSI2,ScaleSI3) here scale id is prim key
tbl_NGTrDAMaster(TrDaId,ScaleID,CityTypeID,DAAmount) no prim key
and we get CityTypeID from xml databinder.......
In my form thr is two drop down list one for scale name and another for city type id
this is the data form  tbl_NGTrDAMaster
 17 1 1 555 18 3 1 777 19 3 1 999 8 1 1 777 5 5 1 34634 20 1 1 52352 27 1 1 6666 23 5 1 12412 12 2 1 235235 13 3 1 456456 14 5 1 1000000 15 4 1 60000 16 5 1 90 24 5 1 25123 25 5 1 13124 26 5 1 12412
but i am expecting only one combination of set.....
like 1-1,1-2,1-3,1-4.......but if reenter 1-1 thn we have to restrict that....
please help me....
i am in big trouble......Thanx in advance
If my qes is not clear for everyone...
plz tell me....
i try my lebel best for understand my prob to u.....

View Replies !

---

Write SQL With / Problem
hi,all,I have a table called "docs", it has a collum  named "dirname"the table data like this
docs table
id   dirname1    c14/sharedoc/forums2    c14/sharedoc/forums3    c15/sharedoc/forums...
now I need get dirname equal 'c14/sharedoc/forums' data,so I write sql like this
select * from docs where dirname='c14/sharedoc/forums'
but,this return nothing.
I guest "/"  was been filter,so can someone write correct sql?
 

View Replies !

---

Write Into File Using SQL.
I need how we can write into a file result of query in SQL.
i need to write into a file the details we get after executing a query.
 

View Replies !

---

How To Write Query For This Using C#?
Hi,I have included here my webform here.i need some assistance here with code.my webform contains two parts.the 1st part is office info and the 2nd part is client info.i also have two table named office_info and client_info.1st part is populated from the table office_info as soon as the office name is chosen from the dropdownlist.in my scenario,when user selects officename from dropdownlist,then textboxes correspondingto address and email gets populated by the related data from table office_info.2nd part is client info.here there are 3 textboxes(for name,age,address) to collect the data from the client using the form.these data gets posted to new row in table client_info as soon as user clicks on the save button.Now my actual question starts here.when user selects the option from the dropdonwlist the office info displays,now when he fills the client info part and clicks the save button,i want all the data to go to the table client_info in such a way that all the data fromthe client info part plus the id of the office also go along with it.eg: when user clicks the save button.i want data to get submitted in table client_info in this way.(id,name,age,address,off_row_id) (1,jack,25,US,1) here off_row_id is the id from the below table.my table office_info is like this (id,off_name,address,email)                                         eg(1,xyz,ny,xyz@xyz.com)    well can anyone tell me how to write query to do insert,edit,update,delete query in this case using c#  and sql?here is the scenario <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"></script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server">    <title>Untitled Page</title></head><body>    <form id="form1" runat="server">    <div>        Office Info:<br />        <hr />        <br />        Office name:        <asp:DropDownList ID="DropDownList1" runat="server" Width="63px">            <asp:ListItem>ABC</asp:ListItem>            <asp:ListItem>XYZ</asp:ListItem>        </asp:DropDownList><br />        <br />        &nbsp;Address:        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />        <br />        email:        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />        <br />        <hr />        </div>        Client info:<br />        <br />        &nbsp;name:        <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br />        <br />        &nbsp;age: &nbsp;        <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br />        <br />        &nbsp;address:<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox><br />        <br />        <br />        <hr />        <asp:Button ID="Button1" runat="server" Text="save" />        <asp:Button ID="Button2" runat="server" Text="cancel" />    </form></body></html> thanks.jack.      

View Replies !

---

How To Write Sql Query With Asp.net(C#)?
 hello everyone. i want to know how asp.net works with sql database. can i have a link to the article where i can perform from basic to advance sql query using asp.net(C#)? (in context of vwd 2005 and sql express ) thanks. jack. 

View Replies !

---