as begin
SET NOCOUNT ON
DECLARE @DEBIT_ID INT
DECLARE @CREDIT_ID INT
DECLARE @servicedamount decimal(18,2)
declare @bal_Serviced_Amt decimal(18,2)
[Code] ....
table is like this
------------------------
DEBIT.ID, DEBIT.ACCOUNT_NO,DEBIT.SERVICED_AMT,CREDIT.ID,CREDIT.TRAN_AMT,CREDIT.SERVICED_FLAG,credit.Serviced_Amt
1456050866223983.0006418110.000300000.00
1456050866223983.0006419110.000500000.00
1456050866223983.0006447110.0002800000.00
1456050866223983.0006510110.0003100303.00
[Code] ....
I need result, I have to subtract
(DEBIT.SERVICED_amt - CREDIT.TRAN_AMT) and set CREDIT.SERVICED_FLAG =1 ,
if DEBIT.SERVICED_amt = CREDIT.TRAN_AMT then set debit.SERVICED_FLAG =1
Using with cursor. It will check row by row , i have written stored procedure , but flag is not updated .
I want to put the fetch results of a cursor to a temporary DB for manipulation, Im selecting all columns from the table in the cursor and the number of total columns is unknow.
STATIC Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor declare ll cursor global static for select name, salary from ag open ll fetch from ll
while @@FETCH_STATUS=0 fetch from ll update ag set salary=200 where 1=1
close ll deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
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
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
Is there any way, to query the above table which returns me all the 1's inside col3 until it finds any other number than 1 + the previous row preceding the 1 for eg., my 1st set would be 1IN NULL 2FOR1 2nd set would look something like this 3Small 4 4World1 5Hj 1 6NJ 1 7Welcome1 3rd set 8So4 9We1 10Are1 4th Set 12OIJIOJPOJPJO7 13OIJOIJ1
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?
Have an XML file that i load in to a Dataset. works fine, it builds its own scheme perfectly. I loop through that data to load a check list which also works wonderfully. two questions based on that. 1) can i add a column after the fact? I want to basically update the the info in the dataset to store if the record was checked in the check list. if not, i can manipulate one of the already defined columns, but i would rather not. 2) what is the best way to update data with in the dataset? Never really done anything but pull data from one. how do i locate the correct row to update ("select name from tbl where name = " + checklist.items[index].tostring(); update row)
sorry for the fairly basic question. i appreciate the help. Justin
I am using the follwing query for extracting the country name and city in a COLUMN [Destination Name] in Destinations table. The Data in the table looks like:
CANADA - Toronto United States- ARIZONA France Argentina United States (USA)- ARIZONA ........ ........
The folowing query is producing the required results upto soem extent but without using -1 in subtracting the one value of CHARINDEX. The error is:
Server: Msg 536, Level 16, State 4, Line 1 Invalid length parameter passed to the substring function. The statement has been terminated.
QUERY ----- select
Left( Destinations.[Destination Name], charindex("-", Destinations.[Destination Name])-1 ) as test into temp2 from destinations
CAN ANYBODY HELP me in extracting the city an dcoutry name. I also want to delete the name in () like (USA).
I have a table that has about 12 fields and 700000 records. My client is going to send me 4 fields out of the 12 fields (1 of them will be primary key) in a text format. What is the best way I can get them updated in the table?
I was given a script that was supposed to take a name field that was separated by commas and normalize it into last, first and middle name. My data looks like below in one fieldname called longname
I am trying to break the string that looks like this
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation. 2007-05-06 07:36:21.28 server All rights reserved. 2007-05-06 07:36:21.28 server Server Process ID is 292.
into three separate strings to look like this
col1 col2 col3 2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation. 2007-05-06 07:36:21.28 server All rights reserved. 2007-05-06 07:36:21.28 server Server Process ID is 292.
I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.
I am trying to break the string that looks like this
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation. 2007-05-06 07:36:21.28 server All rights reserved. 2007-05-06 07:36:21.28 server Server Process ID is 292.
into three separate strings to look like this
col1 col2 col3 2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation. 2007-05-06 07:36:21.28 server All rights reserved. 2007-05-06 07:36:21.28 server Server Process ID is 292.
I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.
How i m Manipulate String , I have (ABC,XYZ,EFG) this string know i want to break this string 1 by 1 and modify and then rearrange in same form ang Get
Hi I have a field which is a file path like 'C:avde8393948.txt'
I want to separate them into folder and filename now the filename is always the same length, so i can use RIGHT to get the filename, but i prefer a method that from the right detects the 1st occurance of and everything after is the filename,
I have two tables TableA and TableB as shown in the example below. I will have to Update TableA based on the data in TableB. If a member in TableB has an EmployerId different from the one in TableA where the EffectiveDate in TableB between the EffectiveDate and ExpirationDate in TableA, then it should void the row in TableA and create rows as shown in the example below (Please refer to the 1st record and the last three records).
I would like to get some suggestions on how to do this efficiently. I am not looking for queries, but I need some ideas...
I have a table with one column as a date field in the form of mm/dd/yyyy and I would like to create a new column on a report as 'Days Open' using the column with random dates in the past and subtracting it from the current system date. Can anyone provide any assistance. I'm very new to SQL Server. I know in Oracle u can use 'sysdate'
The logic is like the min(effdt) as effdt, min(effdt) - 1 as expdt of the next id2 from the source. Nut the problem comes when the same ID2 comes later after a different id2, so just a group by on id1, id2 does not suffice. If it is the last record then the expdt is that month's end date. Can someone throw me some light on what kind of logic I should be using to accomplish this.
I would like to drop the leading 0x on a binary value so I can do abitwise operation.Here is simplified code:select right(0x88186000,8)I expected to get back 88186000, this was not the case. The commandreturned some wierd characters.Am I missing something?
We have some rows that we need to do some tricky string manipulationon.We have a UserID column which has userid entries in the formatfirstname.lastname and i need to change each entry tolastname.firstnameCan this be done by some script?Thanks so much for your help.Sid
I need to to get the result of the function GETDATE and converted to a simpler "mm/dd/yyyy" format in order to compare the results to another date in a table. In ACCESS the function DATE returns the format of 'mm/dd/yyyy' since I need to work with date ranges without a need for this application 'HH:MM:SS'
I have try 'TRANSFORM(GETDATE,'mm/dd/yyyy') but I keep getting errors.
I am not sure what I am doing wrong? Any help is appreciated since I need to work in SQL Server 2000.
I am having problem in string manipulation in SSIS - Derived Column Transformation. I am trying to extract the OU names from Active Directory objects into a SQL table.
Assume that a distinguish name (DN) of an object as below: CN=John, Doe,OU=Users,OU=SubOU,OU=ParentOU,DC=domain,DC=company,DC=com
How can I manipulate the above string so that I get: ParentOU/SubOU/Users
col1 col2 col3 1 IN NULL 2 FOR 1 3 Small 4 4 World 1 5 Hj 1 6 NJ 1 7 Welcome 1 8 So 4 9 We 1 10 Are 1 11 Resp 3 12 OIJIOJPOJPJO 7 13 OIJOIJ 1 14 LKJ 2 15 IJ 1
Is there any way, to query the above table which returns me all the 1's inside col3 until it finds any other number than 1 + the previous row preceding the 1
for eg., my 1st set would be 1 IN NULL 2 FOR 1
2nd set would look something like this 3 Small 4 4 World 1 5 Hj 1 6 NJ 1 7 Welcome 1
I have a scenario while creating a report where I need to maintain the same number of rows for a column, even if the data grows or shrinks for that column
example
Each Scenario is grouped by ID
Scenario 1
columnA
Row1 X X Row2 X Row3 X Row4 XYZ
Scenario 2
columnA
Row1 XA Row2 XYZ
In both scenario's I need to control from front end
Is it possible to INSERT, UPDATE, DELETE data in this type of relationship? If so, how in VS2005? Not having issues with SELECT, even without joins. Thanks
I have a web page called homepage.aspx created in Visual Studio 2005 using VB. It contains some hyperlinks on the left side. I have a database created in SQL Server 2005. I need to add, delete, edit, update data in the database directly from this same page. How can I achieve this?. i.e is it possible to, say, have a form on a section of this same web page that would allow me to directly manipulate/modify and update the database? (If you may give code, you may use abstract names for the database, tables and column and/or field names-I will understand). Many thanks in advance.
Given any date, how can I change that date to be one of the next 3 year cycle dates. These are the fixed cycle dates (always on Sep 1 on those set years) 9/1/1997 9/1/2000 9/1/2003 etc.
For example, if the supplied date was January 4, 1998, it would convert to September 1, 2000 October 31, 2000 -> September 1, 2000 Decemb 12, 2005 -> September 1, 2006 January 1, 1995 -> September 1, 1997
