Add Spaces To Select Paramter

Oct 30, 2007



Hi all,

I have a stored procedure that one of the select parameters is a NCHAR (250)

I need to select and RTRIM() is, and add 3 spaces

I've tried many options, but each time I get the full trimmed version of the column

This is what I've tried:


SELECT (RTRIM(c.customer_name) + space(3)) as CustomerName

SELECT (RTRIM(c.customer_name) + ' ') as CustomerName
SELECT (RTRIM(c.customer_name) + replicate(' ',3)) as CustomerName



Any suggestions?

Cheers

View 8 Replies


ADVERTISEMENT

Custom Parameter For Select Paramters - How To Use The Value Of Another Field As The Paramter Default.

Mar 5, 2008

SELECT     ArticleID, ArticleTitle, ArticleDate, Published, PublishDate, PublishEndDateFROM         UserArticlesWHERE     (ArticleDate >= PublishDate) AND (ArticleDate <= PublishEndDate)
When I use the above on a GridView I get nothing.  Using SQL manager it works great.
I don't know how to pass the value of the ArticleDate field as a default parameter or I think that's what I don't know how to do.
I am trying to create a app that I can set the dates an article will appear on a page and then go away depending on the date.
 Thanks for any help!
 

View 1 Replies View Related

SQL 2012 :: Select Rows With Blank Spaces

Feb 12, 2014

I got two columns DEATHDATE and STATUS

I have to update STATUS Column as INVALID when data in DEATHDATE column as follows

Example of INAVLID DEATH DATE : 1221, 12xt88,12#10, 1, ABC, #@ , etc

Do nothing when the DEATHDATE is VALID like

Examples of Valid/accepatable DEATHDATE: 09122012, 091212 ,******,********, 000000,00000000

How can i achieve this.

View 1 Replies View Related

Pass A Paramter

Dec 27, 2007

I have created this sp to and i cant figure out how to use the top 3 as a paramter. I want to be able to use any # like top1,2,3,4,5 etc. I not sure how to compose the syntax i have my sp below.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_moveBackupTables]
@serverName varchar(50) --Parameter
AS
BEGIN

declare @tableName varchar(50) --Variable

--Declare Cursor
DECLARE backup_Cursor CURSOR FOR
select name from adventureworksdw.dbo.sysobjects
where name like 'MyUsers_backup_%' and xtype = 'U'
and name not in(select top 3 name from adventureworksdw.dbo.sysobjects
where name like 'MyUsers_backup_%' and xtype = 'U' order by name desc)

OPEN backup_Cursor

--Move to initial record in the cursor and set variable(s) to result values
FETCH NEXT FROM backup_Cursor
INTO @tableName

--Loop through cursor records
WHILE @@FETCH_STATUS = 0
BEGIN
--dynamically build create table
Declare @SQL varchar(2000)
Set @SQL = 'CREATE TABLE ' + @serverName + '.dbo.' + @tableName + '(
[Id] [numeric](18, 0) NOT NULL,
[Field1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Field2] [numeric](18, 0) NOT NULL,
[Field3] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Field4] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Field5] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_1' + @tableName + '] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@SQL)

--Insert values into new dynamically created table
Declare @backupTableRecords varchar(1000)

Set @backupTableRecords = 'Insert into ' + @serverName + '.dbo.' + @tableName + ' SELECT * FROM AdventureWorksDW.dbo.'+ @tableName

exec(@backupTableRecords)

--Drop old table
Declare @dropTable varchar(200)
set @dropTable = 'drop table adventureworksdw.dbo.' + @tableName

exec(@dropTable)

--Move to the next record in the Cursor and set variable(s) value(s)
FETCH NEXT FROM backup_Cursor INTO @tableName
END
CLOSE backup_Cursor
DEALLOCATE backup_Cursor
END

View 2 Replies View Related

How To Add Another Row To Paramter List Which From

Jan 29, 2008

Hi,

I want to know how to add another row to paramter lists which from a stored procedure in the report. Is it possible? I don't want to use Multi-value becaue User only have one select.

Thanks,

View 3 Replies View Related

Problem Getting Output Paramter

May 15, 2008

 
I keep running a sproc that sets an output paramter, but when I try to retrieve it, it says it is empty Dim con As New SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("QuotesDataBase").ConnectionString)
Dim command As New SqlCommand("quotes_Quotes_Search", con)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New SqlParameter() With {.ParameterName = "query", .DbType = DbType.String, .Direction = ParameterDirection.Input, .Value = query})
command.Parameters.Add(New SqlParameter() With {.ParameterName = "startRowIndex", .DbType = DbType.Int32, .Direction = ParameterDirection.Input, .Value = startRowIndex})
command.Parameters.Add(New SqlParameter() With {.ParameterName = "maximumRows", .DbType = DbType.Int32, .Direction = ParameterDirection.Input, .Value = maximumRows})
Dim param As New SqlParameter
param.ParameterName = "TotalResults"
param.Direction = ParameterDirection.Output
param.DbType = DbType.Int32
command.Parameters.Add(param)
command.Connection = con
con.Open()
Dim reader As SqlDataReader = command.ExecuteReader
totalResults = param.Value 

View 4 Replies View Related

Passing Paramter To Sp_ExecuteSql

Apr 21, 2006

I am executing a stored procedure from within other procedure with EXEC SPname Command. I have read that we should use sp_ExecuteSql system stored procedure in place of EXEC command because it will catch the execution plan whereas executing a statement or a stored procedure will not catch the execution plan. Now i am trying to execute my stored procedure as

Execute Sp_ExecuteSql @parameterName from within another stored procedure
where @paramter is an integer (but internet says that sp_executesql only accepts nvarchar/ntext datatype).So i am not able to really execute my stored procedure with sp_ExecuteSql. Am i missing out something..is there some prodedure to do this task???
ANY HELP WILL BE GREATLY APPRECIATED.





THANKS

View 5 Replies View Related

Using ExecutionInstanceGUID As Query Paramter

Jun 15, 2006

Hi all,

I'm stuck here trying to use the system variable ExecutionInstanceGUID.

During a first DataFlowTask I store the ExecutionInstanceGUID in a Table.

Further in my Control Flow I want to get all rows with the current InstanceGuid, my query is pretty simple:

SELECT ColumnA
FROM D_Data
WHERE InstanceGUID = ?

In the parameter mapping collection I have added a parameter which maps to System::ExecutionInstanceGUID

When I execute my package I have the following error:
"Message: component "OLE_SRC_SData" (1282) failed the pre-execute phase and returned error code 0xC0010001."

I tried to map the parameter to another variable and it works properly, I don't see what's wrong using ExecutionInstanceGUID like this.

Any help will be appreciated.

Sébastien.

View 3 Replies View Related

Oupput Paramter From A Stored Procedure

Sep 5, 2000

Hi

I have a problem in validating the user ..if the user is not a valid user
it shouldn't execute the stored procedure ..but it should quit the SP and
return a message to the frontend application.The SP look something like this..
when I execute the below SP I get the following ERROR

Server: Msg 3902, Level 16, State 1, Line 0
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Not a Valid USER
Server: Msg 3902, Level 16, State 1, Procedure test, Line 118
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Not a Valid USER

HERE IS THE SP For REFERENCE


CREATE PROCEDURE test
@userid varchar(25),
@revenue_date datetime,
@veropt numeric(25)
AS
set nocount on
DECLARE @version NUMERIC(25)
DECLARE @stg_version NUMERIC(25)
DECLARE @record_count NUMERIC(25)
DECLARE @temp_Count NUMERIC(25)
DECLARE @Stg_Count NUMERIC(25)
DECLARE @Count_Temp_Exep NUMERIC(25)
DECLARE @Temp VARCHAR(20)

DECLARE C1 CURSOR FOR
SELECT userid FROM CEB_REVENUE_RECOG_USERID
OPEN C1
FETCH NEXT FROM C1 INTO @Temp
WHILE @@fetch_status = 0
BEGIN
IF @Temp <> @Userid
PRINT 'Not a Valid USER'
---IF THE USER IS NOT A VALID USER IT SHOULD RETURN A MESSAGE TO A FRONTEND APPLICATION SAYING THAT HE IS NOT A VALID USER
--AND IT SHOULD NOT EXECUTE FURTHER CODE
ELSE

BEGIN TRANSACTION one

if exists (select * from sysobjects where id = object_id(N'[dbo].[CEB_REVENUE_RECOG_TEMP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CEB_REVENUE_RECOG_TEMP]

CREATE TABLE [dbo].[CEB_REVENUE_RECOG_TEMP] (
[VERSION] [numeric](15, 0) NOT NULL ,
[STAGING_VERSION] [numeric](15, 0) NOT NULL,
[OPPORTUNITY_ID] [varchar] (15) NULL ,
[CURRENT_GRADE] [varchar] (15) NULL ,
[AMOUNT] [real] NULL ,
[NEGOTIATED_AMOUNT] [real] NULL ,
[ANTICIPATED_AMOUNT] [real] NULL ,
[START_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[REC_STATUS] [varchar] (1) NULL,
[PRIOR_YTD] [real] NULL ,
[MTD_REVENUE] [real] NULL ,
[YTD_REVENUE] [real] NULL ,
[DEFERRED] [real] NULL ,
[DAY1] [numeric](5,0) NULL,
[DAY2] [numeric](5,0) NULL,
[DAY3] [numeric](5,0) NULL,
[percentage] [numeric](5,0) NULL,
[YTDDAYS] [numeric](5,0) NULL
)

SELECT @version = max(version) from CEB_REVENUE_RECOG_AUDIT
SELECT @Stg_Count = Count(*) FROM CEB_STG_REVENUE_RECOG
IF @version IS NULL
BEGIN
SELECT @version = 1
END
ELSE
BEGIN
SELECT @version = @version +1
END


IF @veropt = 0
BEGIN
SELECT @stg_version = max(staging_version) from CEB_STG_REVENUE_RECOG_AUDIT
IF @stg_version IS NULL
BEGIN
SELECT @stg_version = 1
END
ELSE
BEGIN
SELECT @stg_version = @stg_version +1
END

INSERT INTO CEB_REVENUE_RECOG_AUDIT
(date, USERID, version, staging_version, starttime,REVENUE_DATE,STATUS)
values
(getdate(), @USERID, @version,@stg_version,getdate(),@revenue_date,'P')

INSERT INTO CEB_STG_REVENUE_RECOG_AUDIT (date, USERID, staging_version, starttime) values(getdate(), @USERID, @stg_version, getdate())



---DELETE FROM CEB_STG_REVENUE_RECOG_CEB
---EXEC 'Stored Procedure of Carlos'
SELECT @record_Count = count(*) From CEB_STG_REVENUE_RECOG
INSERT INTO CEB_STG_REVENUE_RECOG_HISTORICAL
SELECT @stg_version,OPPORTUNITY_ID,OPPORTUNITY_NAME, INSTITUTION_ID, INSTITUTION_NAME, ELVIS_ID,PROGRAM_ID,PRODUCT, OPPORTUNITY_TYPE,
OPPORTUNITY_STATUS, CURRENT_GRADE,MEMBERSHIP_YEAR,NEGOTIATED_AMOUNT,NE GOTIATED_DATE,ANTICIPATED_AMOUNT,START_DATE,
END_DATE,RENEWAL_MONTH,JOIN_DATE,MEMBERSHIP_PERIOD ,ORIG_NEG_AMOUNT,PREV_NEG_AMOUNT,EST_AMOUNT, PR_SAL_LAST_NAME,
PR_SAL_FST_NAME,PR_SAL_MID_NAME,CREATION_TIMESTAMP ,LAST_UPD_BY,REC_STATUS
FROM CEB_STG_REVENUE_RECOG

INSERT INTO CEB_REVENUE_RECOG_TEMP(VERSION,STAGING_VERSION, OPPORTUNITY_ID,CURRENT_GRADE,NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT,START_DATE,END_DATE)
SELECT @version,@stg_version, OPPORTUNITY_ID,CURRENT_GRADE,NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT,START_DATE, END_DATE
FROM CEB_STG_REVENUE_RECOG

--DELETE FROM CEB_STG_REVENUE_RECOG_CEB

UPDATE CEB_STG_REVENUE_RECOG_AUDIT SET ENDTIME = getdate(), RECORD_COUNT = @RECORD_COUNT
WHERE STAGING_VERSION = @stg_version
END

ELSE
BEGIN
INSERT INTO CEB_REVENUE_RECOG_AUDIT
(date, USERID, version, staging_version, starttime,REVENUE_DATE,STATUS)
values (getdate(), @USERID, @version,@stg_version,getdate(),@revenue_date,'P')

INSERT INTO CEB_REVENUE_RECOG_TEMP(VERSION, STAGING_VERSION,OPPORTUNITY_ID,CURRENT_GRADE, NEGOTIATED_AMOUNT,
ANTICIPATED_AMOUNT,START_DATE,END_DATE)
SELECT @version,@stg_version, OPPORTUNITY_ID,CURRENT_GRADE,NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT,START_DATE, END_DATE
FROM CEB_STG_REVENUE_RECOG_HISTORICAL WHERE STAGING_VERSION = @veropt

SELECT @record_count = COUNT(*) FROM CEB_REVENUE_RECOG_TEMP
UPDATE CEB_REVENUE_RECOG_AUDIT set ENDTIME = getdate(), STATUS = 'C' where VERSION = @version
END
COMMIT TRANSACTION one

BEGIN TRANSACTION two

UPDATE CEB_REVENUE_RECOG_TEMP SET PRIOR_YTD = 0, YTD_REVENUE=0, MTD_REVENUE=0, AMOUNT = 0, deferred=0

UPDATE CEB_REVENUE_RECOG_TEMP SET REC_STATUS = 'B' WHERE LEN(END_DATE)>=10

UPDATE CEB_REVENUE_RECOG_TEMP SET REC_STATUS = 'P', END_DATE = start_date + 364 WHERE (LEN(END_DATE) = 0 OR END_DATE IS NULL)

UPDATE CEB_REVENUE_RECOG_TEMP SET CEB_REVENUE_RECOG_TEMP.PERCENTAGE = CEB_REVENUE_RECOG_GRADES.percentage
from CEB_REVENUE_RECOG_TEMP INNER JOIN CEB_REVENUE_RECOG_GRADES
ON CEB_REVENUE_RECOG_TEMP.CURRENT_GRADE = CEB_REVENUE_RECOG_GRADES.GRADE

UPDATE CEB_REVENUE_RECOG_TEMP SET CEB_REVENUE_RECOG_TEMP.PERCENTAGE = 100
WHERE CEB_REVENUE_RECOG_TEMP.percentage IS NULL

UPDATE CEB_REVENUE_RECOG_TEMP SET amount = CEB_REVENUE_RECOG_TEMP. ANTICIPATED_AMOUNT * percentage / 100
from CEB_REVENUE_RECOG_TEMP WHERE REC_STATUS = 'P'

UPDATE CEB_REVENUE_RECOG_TEMP SET amount = negotiated_AMOUNT
from CEB_REVENUE_RECOG_TEMP WHERE REC_STATUS = 'B'

/*--TODAY'S ADDITION--*/

INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION, OPPORTUNITY_ID,CURRENT_GRADE, NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE,EN D_DATE,'06',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE OPPORTUNITY_ID IS NULL OR OPPORTUNITY_ID = ' '
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE OPPORTUNITY_ID IS NULL OR OPPORTUNITY_ID = ' '


INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION, OPPORTUNITY_ID, CURRENT_GRADE, NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT, START_DATE, END_DATE, '02',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date > @REVENUE_DATE
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date >@REVENUE_DATE

INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION, OPPORTUNITY_ID,CURRENT_GRADE, NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE,EN D_DATE,'03',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date IS NULL
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date IS NULL

INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION,OPPORTUNITY_ID,CURRENT_GRA DE,NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE ,END_DATE, '01',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date > End_Date
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date > End_Date

INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION,OPPORTUNITY_ID,CURRENT_GRA DE,NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE ,END_DATE, '04',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE AMOUNT < 0
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE AMOUNT < 0

INSERT INTO CEB_REVENUE_RECOG_YTD(OPPORTUNITY_ID, MYTD_REVENUE, YTDDAYS)
SELECT OPPORTUNITY_ID, 0 ,0
FROM CEB_REVENUE_RECOG_TEMP
WHERE OPPORTUNITY_ID
NOT IN (SELECT OPPORTUNITY_ID FROM CEB_REVENUE_RECOG_YTD)

UPDATE CEB_REVENUE_RECOG_TEMP SET PRIOR_YTD = CEB_REVENUE_RECOG_YTD.MYTD_REVENUE, YTDDAYS = CEB_REVENUE_RECOG_YTD.YTDDAYS
FROM CEB_REVENUE_RECOG_TEMP inner join CEB_REVENUE_RECOG_YTD
ON CEB_REVENUE_RECOG_TEMP.OPPORTUNITY_ID = CEB_REVENUE_RECOG_YTD.OPPORTUNITY_ID

UPDATE CEB_REVENUE_RECOG_TEMP SET day1 = datediff(day,start_date,@revenue_date)+1 - YTDDAYS, day2 = datediff(day,start_date,END_DATE) + 1 - YTDDAYS
UPDATE CEB_REVENUE_RECOG_TEMP SET DAY3 = DAY1
UPDATE CEB_REVENUE_RECOG_TEMP SET DAY3 = DAY2 WHERE DAY1 > DAY2

INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION,OPPORTUNITY_ID,CURRENT_GRA DE,NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE ,END_DATE, '05',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE day2 = 0
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE day2 = 0

UPDATE CEB_REVENUE_RECOG_TEMP SET mtd_revenue = Day3 * (amount - PRIOR_YTD) / day2
UPDATE CEB_REVENUE_RECOG_TEMP SET mtd_revenue = amount - prior_ytd WHERE amount < prior_ytd



INSERT INTO CEB_REVENUE_RECOG_TEMP(VERSION,STAGING_VERSION,OPP ORTUNITY_ID,CURRENT_GRADE,MTD_REVENUE, NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT,
START_DATE,END_DATE,REC_STATUS,AMOUNT,YTDDAYS,DAY2 ,PRIOR_YTD)
SELECT @version, @stg_version,CEB_REVENUE_RECOG_EXCEPTION.OPPORTUNI TY_ID,CURRENT_GRADE, (CEB_REVENUE_RECOG_EXCEPTION.AMOUNT-CEB_REVENUE_RECOG_YTD.MYTD_REVENUE),
NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE, END_DATE,REC_STATUS,AMOUNT,CEB_REVENUE_RECOG_YTD.Y TDDAYS,0,MYTD_REVENUE
FROM CEB_REVENUE_RECOG_EXCEPTION INNER JOIN CEB_REVENUE_RECOG_YTD
ON CEB_REVENUE_RECOG_EXCEPTION.OPPORTUNITY_ID = CEB_REVENUE_RECOG_YTD.OPPORTUNITY_ID
WHERE CEB_REVENUE_RECOG_EXCEPTION.VERSION = @version AND CODE = '05' AND
(CEB_REVENUE_RECOG_YTD.MYTD_REVENUE - CEB_REVENUE_RECOG_EXCEPTION.AMOUNT) <> 0

SELECT @Temp_Count = COUNT(*) FROM CEB_REVENUE_RECOG_TEMP

/*--TODAYS ADDITION--*/
UPDATE CEB_REVENUE_RECOG_TEMP SET NEGOTIATED_AMOUNT = 0, ANTICIPATED_AMOUNT = 0 WHERE NEGOTIATED_AMOUNT IS NULL AND ANTICIPATED_AMOUNT IS NULL


UPDATE CEB_REVENUE_RECOG_TEMP SET ytd_revenue = mtd_revenue + PRIOR_YTD, DEFERRED = AMOUNT - mtd_revenue - PRIOR_YTD
UPDATE CEB_REVENUE_RECOG_TEMP SET DEFERRED = 0 WHERE YTD_REVENUE> =amount


INSERT INTO CEB_REVENUE_RECOG_HISTORICAL(VERSION,STAGING_VERSI ON,OPPORTUNITY_ID,CURRENT_GRADE,AMOUNT,NEGOTIATED_ AMOUNT,ANTICIPATED_AMOUNT,
START_DATE,END_DATE,REC_STATUS,REVENUE_DATE,PRIOR_ YTD,MTD_REVENUE,YTD_REVENUE,DEFERRED,REMAINING_DAY S,DAYS_IN_PERIOD,PERCENTAGE,ADJUSTMENT_FLAG)
SELECT VERSION,STAGING_VERSION,OPPORTUNITY_ID,CURRENT_GRA DE, AMOUNT,NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT,START_DATE,END_DATE,
REC_STATUS,@revenue_date,PRIOR_YTD, MTD_REVENUE, YTD_REVENUE,DEFERRED,DAY2,0,100,'A'
FROM CEB_REVENUE_RECOG_TEMP

--DELETE FROM CEB_REVENUE_RECOG_TEMP
UPDATE CEB_REVENUE_RECOG_AUDIT SET ENDTIME = getdate(), RECORD_COUNT = @RECORD_COUNT, STATUS = 'C' WHERE VERSION = @version

UPDATE CEB_REVENUE_RECOG_YTD SET MYTD_REVENUE = MYTD_REVENUE + CEB_REVENUE_RECOG_TEMP.mtd_revenue,
CEB_REVENUE_RECOG_YTD.YTDDAYS = CEB_REVENUE_RECOG_YTD.YTDDAYS + CEB_REVENUE_RECOG_TEMP.DAY3
FROM CEB_REVENUE_RECOG_YTD INNER JOIN CEB_REVENUE_RECOG_TEMP
ON CEB_REVENUE_RECOG_YTD.OPPORTUNITY_ID = CEB_REVENUE_RECOG_TEMP.OPPORTUNITY_ID
WHERE CEB_REVENUE_RECOG_TEMP.VERSION = @version


/*--INSERTING RECORD INTO EXCEPTION SUMMARY----*/
/*--TODAYS ADDITION--*/

INSERT INTO CEB_REVENUE_RECOG_EXCEPTION_SUMMARY VALUES(@version,'00','Total Records in Temp',@Temp_Count)

INSERT INTO CEB_REVENUE_RECOG_EXCEPTION_SUMMARY
SELECT @version,CEB_REVENUE_RECOG_EXCEPTION.CODE,DESCRIPT ION ,COUNT(CEB_REVENUE_RECOG_EXCEPTION.CODE) as RECORD_COUNT
FROM CEB_REVENUE_RECOG_EXCEPTION INNER JOIN CEB_REVENUE_RECOG_EXCEPTION_CODES ON
CEB_REVENUE_RECOG_EXCEPTION.CODE = CEB_REVENUE_RECOG_EXCEPTION_CODES.CODE
WHERE VERSION = @version GROUP BY CEB_REVENUE_RECOG_EXCEPTION.CODE,DESCRIPTION


INSERT INTO CEB_REVENUE_RECOG_EXCEPTION_SUMMARY VALUES(@version,'91','Total Records in Staging',@Stg_Count)

SELECT @Count_Temp_Exep = SUM(record_count) from CEB_REVENUE_RECOG_EXCEPTION_SUMMARY
where code < '90' and VERSION = @version

INSERT INTO CEB_REVENUE_RECOG_EXCEPTION_SUMMARY VALUES(@version,'92','Record Count',@Count_Temp_Exep)


INSERT INTO CEB_REVENUE_RECOG_CLOSED_PERIOD VALUES(@revenue_date)

UPDATE CEB_REVENUE_RECOG_AUDIT SET Status = 'X' WHERE VERSION = @version



COMMIT TRANSACTION two

FETCH NEXT from C1 INTO @Temp
end
CLOSE C1
DEALLOCATE C1


THANKS
VENU

View 2 Replies View Related

Input Paramter As Part Of OPENDATASOURCE

Jan 26, 2007

I want to use an input parameter as my filename, but I get a synataxerror message. Howerve, when I hard code the filename the proc compilessuccessfully.Thanks for any help. I'm using SQL Server 2005LTR_90,LTI_ELIG_pct,LTI_REC_pct,LOW_SALARY,HIGH_SALARY FROM OPENDATASOURCE("Microsoft.Jet.OLEDB.4.0","Data Source=C:inetpubwwwrootORC_Beta_Companies"' + @infilename +'"Extended Properties=Excel 8.0")...[summary_data$]Syntax message:Msg 102, Level 15, State 1, ProcedureimportExcelSpreadSheetIntoeNavigator_DataORC, Line 244Incorrect syntax near 'Microsoft.Jet.OLEDB.4.0'.

View 2 Replies View Related

Passing Multiple Values To A Paramter

Apr 20, 2007

Hi,



I'm trying to pass multiple values to a single parameter from a report to a second report. For instance I want to pass the values a user selected in the original report, such as the countries a user select under a Country filter, and once the second report is called, I want that report to filter on those same countries, right now I can only pass one of the values selected to the second report. If someone can let me know if this is possible it'd be much appreciated, thanks in advance.

View 1 Replies View Related

Adding Total To Paramter Drop Down List

Apr 30, 2008


I was trying to write an expression someting like this.

(CASE WHEN (GroupVar2 IN('CBank','DTC', 'EDirect')) THEN GroupVar2 ELSE 'InstLend' END) AS COALESCE(GroupVar2,'Total') AS GroupVar2


In GroupVar2 column, following values are available;


CBank
DTC
EDirect
InstLend
Inst-Load

I use this for a parameter in my report. I want to consider inst-Load as the same as InstLend. In drop down menu , I should see only InstLend. When I select it, I should get summation of InstLend and Inst-Load.
Also I should see 'Total' as one of the available value. So when I select total it should give me summation of all of above.

Can anyone help me to write this corretly?
Thanks

View 1 Replies View Related

Issue With Executing Dynamic Sql With Inbound Paramter As Varchar Striong

Aug 4, 2007

Hi All:

I am trying to execute a dynamic sql, the dynamic sql makes use of an inbound paramter defined as varchar.

When I try to execute it fails, because it does not plavce the inbound paramter in quotes.

Any help would be appreciated.

In the bound search as an eaxmple can be" 'NY'

@P_SEARCH_VALUE='NY'

SET @V_SQL_FILTER = N' WHERE STATE = '+@P_SEARCH_VALUE

SET @V_SQL=@V_BASE_SQL+@V_SQL_FILTER

EXEC sp_executesql @V_SQL

Here is the v_sql out put:

SELECT TOP 100 * FROM V$ZIPCODE_LOOKUP_ALL WHERE STATE = NY

As you can see the sql will fail because the NY is not in quotes.

I tried using '@P_SEARCH_VALUE''' and other forms but could not get it work.

View 6 Replies View Related

SPACES

Sep 24, 2001

Please how can remove spaces on a date field and a text field. THanks.

View 1 Replies View Related

Spaces In A Sql Server

Nov 19, 2007

i collected the users information without using any trim fucntion(i have implemented now)
but the data which has been already posted into my server has text with some white spaces at the beginning of data
now how to remove this white spaces in this column in online server data.
the data is something like this
mycolumn 
data
  data
data
    data
 
so how to remove white spaces in the above column

View 3 Replies View Related

Add SPACES In Data

Apr 22, 2008

Hi
i have 1 query please guide me,
SELECT P.ProjectName, REPLACE(SPACE(TU.TaskOutlineLevel), ' ', '__') AS dash,TU.TASKISSUMMARY AS TASKSUMMARY,
here i want to add  BLANKS but not working so i have to show add spaces using  __ here any idea i can add SPACES or '  ' here.
please let me know if any. basically i want to align the data in my GRIDVIEW
Thanks in advance
Parth

View 4 Replies View Related

Remove Spaces

Apr 12, 2006

How can you remove spaces in the middle of a string, RTRIM and LTRIM does not work

View 1 Replies View Related

White Spaces

Jan 8, 2002

Creating a text file using DTS, is there a function/way to take out white
spaces from columns. Example:
'1234 ','567 ' would come in text as
'1234','567'

Thanks in advance.

View 2 Replies View Related

Spaces On An Insert

Jan 7, 2003

Hi,

I am building my insert statements dynamically and am finding that there are spaces after certain integer fields.

example Insert Table A (col1, col2, col3)
values (1 ,'2',3 )

If col1 and col3 are integer fields, is there any affect to either how the value is stored or how it will be retrieved when the table is used in a join. By inspecting the values in the table, it seems fine. Do I need to worry?

Thanks,

Jim

View 1 Replies View Related

Trailing Spaces

Apr 28, 2003

If I run SELECT Len(' ') it returns 0, if SELECT Len('a ') it returns 1
I need this to return the correct length including the space that on the end. I thought it was an ansi_padding problem but even turning padding on results in a 0 length. Any ideas? Thanks!

Todd

View 2 Replies View Related

Erasing Spaces!

Apr 28, 2004

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

I am tring to join two tables. There is one problem of course. There is one column I would be able to join the two tables by. This column would be Loc_Code. The only problem is that both columns are not exactly the same. They look like this:

Table 1 Table 2
Loc_Code Loc_Code
A 12345 A12345
A 12346 A12346
A 12347 A12347
A 12348 A12348

I need to erase the spaces that exists in the Loc_Code column in table 1 so that I can join with table 2.


All help would be appreciated.

View 9 Replies View Related

Trimming Spaces

Mar 9, 2006

I need to trim what looks like two spaces from a field.

Example:

" 601274" needs to be "601274". Does anyone know the syntax?+

View 9 Replies View Related

Remove Spaces

Jan 25, 2007

is there a way to do remove spaces from a string if its they are in deifferent places on each row

View 1 Replies View Related

How To Ignore Spaces?

Jan 31, 2008

I'm writing a store procedure to accept search strings from user on my site. Currently, this is what I have.




Code Snippet
@schoolID int = NULL,
@scholarship varchar(250) = NULL,
@major varchar(250) = NULL,
@requirement varchar(250) = NULL
--@debug bit = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT * FROM [scholarship]
WHERE ([sectionID] = @schoolID OR @schoolID IS NULL)
AND ([schlrPrefix] LIKE '%' + @scholarship + '%' OR [schlrName] LIKE '%' + @scholarship + '%' OR [schlrSufix] LIKE '%' + @scholarship + '%' OR @scholarship IS NULL )
AND ([Specification] LIKE '%' + @major + '%' OR @major IS NULL )
AND ([reqr1] LIKE '%' + @requirement + '%' OR [reqr2] LIKE '%' + @requirement + '%' OR [reqr3] LIKE '%' + @requirement + '%' OR [reqr4] LIKE '%' + @requirement + '%' OR [reqr5] LIKE '%' + @requirement + '%' OR @requirement IS NULL )





The problem is, somtimes the search doesn't work if there is a space behind or infront of the search string. I wonder if there is away to ignore any spaces and go right into whatever character comes next or after. If so, how do I implement that?

View 7 Replies View Related

Getting Spaces In A Column

Mar 7, 2008

I have a single column value like this. I want to get the charindex value whereever it is a single space.


'6 REP5426 15936 022708 107/0000 33003301985 BAILEY AMY NICOL 11454 PP 25.00 000-00 00110080 A O '

Thanks.

View 5 Replies View Related

Trailing Spaces - Such A Pain...

Dec 31, 2006

Hi All...  I'm using a SQL Server 2005 database.  I've noticed that columns that are declared as "char" and that have a fixed size tend to put trailing spaces at the end of the data when I pull it out.  I guess I can understand why...  But it's a pain dealing with it.   As I'm bringing my application up, I can see spaces all over the place - I just havent gotten around to doing anything about it yet.  What's the easiest/best way to get rid of those spaces.  Geez, it'd be real cool if I could put something in the SELECT statement.  Any thoughts?  Thanks much!!  -- Curt
 

View 1 Replies View Related

Find Leading Spaces

Aug 23, 2001

What is the best way to check for leading spaces in your table, using ltrim?
Such as TableA(name, city)
the data in TableA Smith Dallas
John New York
Greg Richmond
David Chicago
Return only David.

View 1 Replies View Related

Spaces In SQL 7 Database Names

Mar 7, 2000

Hey all,

Hope I can get some help here.

Have a view that has to reference a table in another database on the same server. This isn't the problem. I am just using Databasename..tablename in the from statement. Get the results required in testing.

The problem is that in production the table name has a space character. i.e. 'accounting info' would be the table name. I have not been able to figure out the proper syntax to capture this properly to reference in the from statement.

I have tried enclosing the name in ' ', " ", [], (), {} and just about every combination I can come up with.

Any help in this would be great, and changing the databse name isn't an option at this time.

Thanx,
Chris

View 3 Replies View Related

Replace Null With Spaces

Sep 4, 2003

Hi all smart people,

Can someone please help me with how to replace null values with spaces in select statement.

I have to have a select statement which will select all the data but the field contain null value it has to replace with spaces. How can I do that ?

Thanks,

View 7 Replies View Related

Extra Spaces In Rows

Jul 10, 2006

I have some fields in my database that are char(50)

I use an insert to put some text into the fields (using perl)
when I look at the fields I find that spaces are used to "pad out" the text to the field size.

This didn't happen with MySQL and PHP, is there any way to disable this?

View 5 Replies View Related

Error In Replace With Spaces

Jun 2, 2004

Hi !
I'm trying to put a space after . and , in varchar fields.

I've tried

UPDATE Resultados
SET RespuestaAbierta = REPLACE(RespuestaAbierta, ',', ', ')

but nothing happened

What's wrong ??

Thanks and sorry if my english isn´t so good.

Eduardo (from Argentina)

View 9 Replies View Related

HELP! How To Reduce The Unused Spaces ..

Aug 9, 2004

HI all,

I have a database that allocated:

Data: 7300 MB
Log: 2000 MB

But only used

Data: 5500MB
Log: 50MB

How can I free the unused space in the transaction log because
the database is getting too big.

Thank you for your suggestion.

View 1 Replies View Related

All Spaces In A CHAR(5) Column

Jan 31, 2005

i'm going nuts with this, i suppose i will crack it eventually, but i thought i'd ask around here, seems like all the smart SQL Server guys hang out here

(i'm an SQL guy, not an SQL Server guy)

how does one place 5 spaces into a CHAR(5) column?
create table testzeros
( id smallint not null primary key identity
, myfield char(5)
)
insert into testzeros (myfield) values (' 1')
insert into testzeros (myfield) values (' 11')
insert into testzeros (myfield) values (' 111')
insert into testzeros (myfield) values (' 1111')
insert into testzeros (myfield) values ('11111')
insert into testzeros (myfield) values (' ')

select id
, myfield
, len(myfield) as L
from testzerosno matter what i do, id=6 shows up with L=0, just like an empty string

i've even tried inserting 4 spaces and a non-blank character, which enters just fine, just as you would expect, but when i update the value and replace the non-blank character with a blank, all 5 spaces collapse back to an empty string

is there some kind of server setting like SET ALL_SPACE_EQUALS_EMPTY_YOU_IDIOT to OFF or something?

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved