Help with Update Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with Update Statement

Author

Topic

cpower
Starting Member

9 Posts

Posted - 2009-01-21 : 10:31:02

I have the following query:
Update dbo.[Or_Processing]
Set
LEDGER_NAME = occ.OR_LED,
ACCOUNTING_DATE = REPLACE(CONVERT(CHAR(11),PERIOD, 106),' ','-'),
CURRENCY_CODE = 'EUR',
ACTUAL_FLAG = 'A',
USER_JE_CATEGORY_NAME = 'Conversion',
USER_JE_SOURCE_NAME = 'Navision Legacy',
CURRENCY_CONVERSION_DATE = REPLACE(CONVERT(CHAR(11),PERIOD, 106),' ','-'),
USER_CURRENCY_CONVERSION_TYPE = 'USER',
ORIGINATING_BAL_SEG_VALUE = occ.OR_COY,
CONCATENATE_COA_VALUE = (occ.OR_COY +'.'+ occ.OR_CC +'.'+ occ.OR_LOB +'.'+ oacc.OR_ACC +'.'+ '0000' +'.'+ '0000'),
SEGMENT1 = occ.OR_COY,
SEGMENT2 = occ.OR_CC,
SEGMENT3 = occ.OR_LOB,
SEGMENT4 = oacc.OR_ACC,
SEGMENT5 = '0000',
SEGMENT6 = '0000',
--ENTERED_DR = ,
--ENTERED_CR = ,
PERIOD_NAME = RIGHT(REPLACE(CONVERT(CHAR(11),PERIOD, 106),' ','-'),8),
FUNCTIONAL_CURRENCY_CODE = 'EUR'
FROM dbo.[Or_Processing]op INNER JOIN dbo.[OracleACCMap] oacc
on OACC.LEG_ACC = op.GL_CODE
INNER JOIN dbo.[OracleCCMap] occ
on OCC.LEG_CC = op.DEPT_CODE

The problem I have is that I need to update the ENTERED_DR and ENTERED_CR columns (commented out above) from another column in the same table called BALANCE. The BALANCE column contains debit and credit values. I need to move the debit figures to the ENTERED_DR column and the credit values to the ENTERED_CR column but can figure aout the best way.

Should look like this:

Balance, ENTERED_CR, ENTERED_DR
-1000, -1000, [BLANK]
-250, -250, [BLANK]
345, [BLANK], 345
-15, -15, [BLANK]
850, [BLANK], 850

Can anyone help? Thanks in advance.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-21 : 10:43:01

How do u differentiate a value in a balance column as debitvalue or credit value in parent table...??

cpower
Starting Member

9 Posts

Posted - 2009-01-21 : 10:46:06

All credit values are prefixed with '-' and debit values do not have a prefix. The problem is I am not sure how to integrate a case statment onto the lines.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-21 : 10:49:59

try this

Update dbo.[Or_Processing]
Set
LEDGER_NAME = occ.OR_LED,
ACCOUNTING_DATE = REPLACE(CONVERT(CHAR(11),PERIOD, 106),' ','-'),
CURRENCY_CODE = 'EUR',
ACTUAL_FLAG = 'A',
USER_JE_CATEGORY_NAME = 'Conversion',
USER_JE_SOURCE_NAME = 'Navision Legacy',
CURRENCY_CONVERSION_DATE = REPLACE(CONVERT(CHAR(11),PERIOD, 106),' ','-'),
USER_CURRENCY_CONVERSION_TYPE = 'USER',
ORIGINATING_BAL_SEG_VALUE = occ.OR_COY,
CONCATENATE_COA_VALUE = (occ.OR_COY +'.'+ occ.OR_CC +'.'+ occ.OR_LOB +'.'+ oacc.OR_ACC +'.'+ '0000' +'.'+ '0000'),
SEGMENT1 = occ.OR_COY,
SEGMENT2 = occ.OR_CC,
SEGMENT3 = occ.OR_LOB,
SEGMENT4 = oacc.OR_ACC,
SEGMENT5 = '0000',
SEGMENT6 = '0000',
ENTERED_DR = substring(balance,1,charindex('-',balance)-1),
ENTERED_CR = substring(balance,charindex('-',balance)+1,len(balance)),
PERIOD_NAME = RIGHT(REPLACE(CONVERT(CHAR(11),PERIOD, 106),' ','-'),8),
FUNCTIONAL_CURRENCY_CODE = 'EUR'
FROM dbo.[Or_Processing]op INNER JOIN dbo.[OracleACCMap] oacc
on OACC.LEG_ACC = op.GL_CODE
INNER JOIN dbo.[OracleCCMap] occ
on OCC.LEG_CC = op.DEPT_CODE

cpower
Starting Member

9 Posts

Posted - 2009-01-21 : 11:08:44

No Joy. I got the following error:

The data type decimal is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

The BALANCE, ENTERED_DR & ENTERED_CR columns should all be datatype = decimal.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-21 : 11:13:35

try with this

ENTERED_DR = substring(convert(varchar(max),balance),1,charindex('-',convert(varchar(max),balance))-1),
ENTERED_CR = substring(convert(varchar(max),balance),charindex('-',convert(varchar(max),balance))+1,len(convert(varchar(max),balance)))

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-21 : 11:17:00

quote:
Originally posted by cpower

No Joy. I got the following error:

The data type decimal is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

The BALANCE, ENTERED_DR & ENTERED_CR columns should all be datatype = decimal.

can you show me the sample data stored in Balance column?? How u have stored '-' symbol in Decimal Field??

cpower
Starting Member

9 Posts

Posted - 2009-01-21 : 11:18:25

Incorrect syntax near max.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 11:22:02

seems like this is what you want


Update dbo.[Or_Processing]
Set
LEDGER_NAME = occ.OR_LED,
ACCOUNTING_DATE = REPLACE(CONVERT(CHAR(11),PERIOD, 106),' ','-'),
CURRENCY_CODE = 'EUR',
ACTUAL_FLAG = 'A',
USER_JE_CATEGORY_NAME = 'Conversion',
USER_JE_SOURCE_NAME = 'Navision Legacy',
CURRENCY_CONVERSION_DATE = REPLACE(CONVERT(CHAR(11),PERIOD, 106),' ','-'),
USER_CURRENCY_CONVERSION_TYPE = 'USER',
ORIGINATING_BAL_SEG_VALUE = occ.OR_COY,
CONCATENATE_COA_VALUE = (occ.OR_COY +'.'+ occ.OR_CC +'.'+ occ.OR_LOB +'.'+ oacc.OR_ACC +'.'+ '0000' +'.'+ '0000'),
SEGMENT1 = occ.OR_COY,
SEGMENT2 = occ.OR_CC,
SEGMENT3 = occ.OR_LOB,
SEGMENT4 = oacc.OR_ACC,
SEGMENT5 = '0000',
SEGMENT6 = '0000',
ENTERED_DR = case when balance > 0.00 then balance end,
ENTERED_CR = case when balance < 0.00 then balance end,
PERIOD_NAME = RIGHT(REPLACE(CONVERT(CHAR(11),PERIOD, 106),' ','-'),8),
FUNCTIONAL_CURRENCY_CODE = 'EUR'
FROM dbo.[Or_Processing]op INNER JOIN dbo.[OracleACCMap] oacc
on OACC.LEG_ACC = op.GL_CODE
INNER JOIN dbo.[OracleCCMap] occ
on OCC.LEG_CC = op.DEPT_CODE

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-21 : 11:22:08

I think you are not using sql server 2005 and later versions

try with this

ENTERED_DR = substring(convert(varchar(8000),balance),1,charindex('-',convert(varchar(8000),balance))-1),
ENTERED_CR = substring(convert(varchar(8000),balance),charindex('-',convert(varchar(8000),balance))+1,len(convert(varchar(8000),balance)))

cpower
Starting Member

9 Posts

Posted - 2009-01-21 : 11:24:40

A few lines from the first two columns of the table are below:

GL_CODE BALANCE
6125 -1560.63
6115 129.32
7145 250
2136 1666.76
7275 117.09
5428 1460.23
2210 189.18
5645 -24323.69
7520 -2.63

Table was created before the update statement as per:

Create Table [Or_Processing]
([GL_CODE]Varchar(20),
[DEPT_CODE]Varchar(20),
[PERIOD]datetime,
[BALANCE]Decimal(38,20),
LEDGER_NAME VARCHAR(30),
ACCOUNTING_DATE DATETIME,
CURRENCY_CODE VARCHAR(15),
DATE_CREATED DATETIME,
ACTUAL_FLAG VARCHAR(1),
USER_JE_CATEGORY_NAME VARCHAR(25),
USER_JE_SOURCE_NAME VARCHAR(25),
CURRENCY_CONVERSION_DATE DATETIME,
USER_CURRENCY_CONVERSION_TYPE VARCHAR(30),
CURRENCY_CONVERSION_RATE Integer,
ORIGINATING_BAL_SEG_VALUE VARCHAR(25),
SEGMENT1 VARCHAR(25),
SEGMENT2 VARCHAR(25),
SEGMENT3 VARCHAR(25),
SEGMENT4 VARCHAR(25),
SEGMENT5 VARCHAR(25),
SEGMENT6 VARCHAR(25),
CONCATENATE_COA_VALUE VARCHAR(100),
ENTERED_DR Decimal(38,20),
ENTERED_CR Decimal(38,20),
ACCOUNTED_DR DECIMAL,
ACCOUNTED_CR DECIMAL,
PERIOD_NAME VARCHAR(15),
FUNCTIONAL_CURRENCY_CODE VARCHAR(15))
Insert Into dbo.[OR_Processing]([GL_CODE],[DEPT_CODE],[PERIOD],[BALANCE])
select [GL_CODE],[DEPT_CODE],[PERIOD],SUM([BALANCE])
From dbo.[Or_Staging]
Group BY [GL_CODE],[DEPT_CODE],[PERIOD]
7043 201.58

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 11:43:10

quote:
Originally posted by raky

try with this

ENTERED_DR = substring(convert(varchar(max),balance),1,charindex('-',convert(varchar(max),balance))-1),
ENTERED_CR = substring(convert(varchar(max),balance),charindex('-',convert(varchar(max),balance))+1,len(convert(varchar(max),balance)))

why are you converting to varchar for simply distingushing between negative and positive values?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 11:43:53

quote:
Originally posted by cpower

A few lines from the first two columns of the table are below:

GL_CODE BALANCE
6125 -1560.63
6115 129.32
7145 250
2136 1666.76
7275 117.09
5428 1460.23
2210 189.18
5645 -24323.69
7520 -2.63

Table was created before the update statement as per:

Create Table [Or_Processing]
([GL_CODE]Varchar(20),
[DEPT_CODE]Varchar(20),
[PERIOD]datetime,
[BALANCE]Decimal(38,20),
LEDGER_NAME VARCHAR(30),
ACCOUNTING_DATE DATETIME,
CURRENCY_CODE VARCHAR(15),
DATE_CREATED DATETIME,
ACTUAL_FLAG VARCHAR(1),
USER_JE_CATEGORY_NAME VARCHAR(25),
USER_JE_SOURCE_NAME VARCHAR(25),
CURRENCY_CONVERSION_DATE DATETIME,
USER_CURRENCY_CONVERSION_TYPE VARCHAR(30),
CURRENCY_CONVERSION_RATE Integer,
ORIGINATING_BAL_SEG_VALUE VARCHAR(25),
SEGMENT1 VARCHAR(25),
SEGMENT2 VARCHAR(25),
SEGMENT3 VARCHAR(25),
SEGMENT4 VARCHAR(25),
SEGMENT5 VARCHAR(25),
SEGMENT6 VARCHAR(25),
CONCATENATE_COA_VALUE VARCHAR(100),
ENTERED_DR Decimal(38,20),
ENTERED_CR Decimal(38,20),
ACCOUNTED_DR DECIMAL,
ACCOUNTED_CR DECIMAL,
PERIOD_NAME VARCHAR(15),
FUNCTIONAL_CURRENCY_CODE VARCHAR(15))
Insert Into dbo.[OR_Processing]([GL_CODE],[DEPT_CODE],[PERIOD],[BALANCE])
select [GL_CODE],[DEPT_CODE],[PERIOD],SUM([BALANCE])
From dbo.[Or_Staging]
Group BY [GL_CODE],[DEPT_CODE],[PERIOD]
7043 201.58

did you try my suggestion?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-21 : 11:56:13

quote:
Originally posted by visakh16

quote:
Originally posted by raky

try with this

ENTERED_DR = substring(convert(varchar(max),balance),1,charindex('-',convert(varchar(max),balance))-1),
ENTERED_CR = substring(convert(varchar(max),balance),charindex('-',convert(varchar(max),balance))+1,len(convert(varchar(max),balance)))

why are you converting to varchar for simply distingushing between negative and positive values?

Initially he told that credit values are prefixed by a '-' sign and debit values are not.. so i misunderstood that it is a varchar field..Later he gave sample data in balance column..and told datatype of Balance column..

cpower
Starting Member

9 Posts

Posted - 2009-01-21 : 19:43:48

Thanks visakh16. Your solution was exactly what I was after. Seems so easy when you get told. Thanks to everyone else who replied. Much appreciated.

Regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources