Need To Remove Extra Spaces In Query Output

Sep 15, 2006

I'm using the following command:

osql -E -n -d testdb -i testquery.qry -o "c:Scriptsoutput.txt" -h-1 -w 500 -s ","

With the following query (testquery.qry):

SET NOCOUNT ON
SELECT table1.column5, table2.column9
FROM table1, table2
WHERE table1.column4 = table2.column4
AND table1.column1 != "NULL"
ORDER BY table1.column5

All of the columns are cast as char up to 50 characters.

Even if only a field has a few characters, I get a lot of extra white space in my output. I want to get rid of those trailing spaces. I've tried SET ANSI_PADDING OFF, RTRIM(), and CAST(x AS VARCHAR(y)). I still get the same output. What am I doing wrong, what am I missing?

View 4 Replies


ADVERTISEMENT

Create Output File In A Specific Layout - Extra Spaces Between Fields

Mar 17, 2014

I'm trying to create an output file in a specific layout. For some reason my output file is adding an extra 10 spaces between the Account Number and the Check Number in the statement below. The rest of the output file looks fine. Where the extra 10 spaces are coming from? I need 1 Filler Space between these fields.

SELECT DISTINCT
CASE p.PaymentMethodID WHEN 10 THEN 'I' WHEN 60 THEN 'V' WHEN 50 THEN 'S' ELSE 'I' END
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 20 - LEN(ba.AccountNumber))+ CONVERT(CHAR(20), ba.AccountNumber))
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 18 - LEN(p.CheckNumber)) + CONVERT(VARCHAR(18), p.CheckNumber))

[Code] .....

View 2 Replies View Related

SQL Server 2008 :: How To Remove Spaces From Output

Jun 23, 2015

When I save my output (from a query I ran) to a text file, there seems to be rows of spaces. Is there a way i can just kill off any spaces at the end of my query? Like rtrim or something?

View 9 Replies View Related

Adding Spaces To Query Output -- Really Stuck!!

May 10, 2001

I want to add 3 spaces at the end of each row for the result of a query.

The initial query being output to a text file (USING DTS) is:

select colA, colB =
CONVERT( CHAR(15), TableX.ColB )
from TableX
------------------------------------------------------------------------

BUT WHEN I EXPORT THE FOLLOWING (USING DTS), I GET THE SAME RESULTS

select colA, colB =
CONVERT( CHAR(18), TableX.ColB )+char(32)+char(32)+char(32) -- ' '
from TableX

Replacing the char(32) with ' ' (3 spaces in quotes) doesn't help.

Any ideas of how to append the 3 spaces after each record when exporting to
a text file using DTS??

Thanks

View 2 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

Remove Extra Characters

Feb 17, 2005

I have the following sql statement:

SELECT FTE_CLASS_GROUP_NBR_DSCR
FROM dbo.DLIST_FTE_CLASS_GROUP
WHERE FTE_GRP_ID IS NOT NULL

This is an example of the result returned:

9999/00 Some lenghty text is displayed after the numbers

I want to trim everything after 9999/00

Is there a way to use rtrim to remove the characters after the numbers or another method?

Thanks,
-D-

View 1 Replies View Related

Remove Extra Record

Mar 20, 2007

I have a table sample:

DECLARE@Sample TABLE (Merchant VARCHAR(1), OldOrder INT, OrderType VARCHAR(20),
NewOrder INT, cdate datetime)

INSERT@Sample
SELECT'a', 1, 'new', 1,'1/1/2003' UNION ALL
SELECT'a', 2, 'renewal',2,'8/2/2003' UNION ALL
SELECT'a', 3, 'renewal', 3, '2/1/2004'UNION ALL
SELECT'a', 4, 'renewal', 4,'11/1/2004' UNion ALL
SELECT'a', 5, 'renewal' ,5, '3/8/2006'UNION ALL
SELECT'a', 6, 'new', 1, '3/1/2004' UNION ALL
SELECT'a', 7, 'new', 1,'12/3/2005' UNION ALL
SELECT'a', 8, 'renewal',2, '8/2/2005'UNION ALL
SELECT'a', 9, 'renewal', 3,'9/2/2006'UNION ALL
SELECT'a', 10, 'renewal',4, '10/2/2006'UNION ALL
SELECT'a', 11, 'renewal', 5, '11/2/2006' UNION ALL
SELECT'b', 1, 'new', 1,'8/2/2003'UNION ALL
SELECT'b', 2, 'new', 1,'9/2/2003'UNION ALL
SELECT'b', 3, 'new', 1,'8/2/2005'UNION ALL
SELECT'c', 1, 'new', 1,'8/2/2004'UNION ALL
SELECT'c', 2, 'renewal',2,'8/2/2005' UNION ALL
SELECT'c', 3, 'renewal',3,'9/2/2005' UNION ALL
SELECT'd', 2, 'new',1 ,'8/2/2005'UNION ALL
SELECT'd', 3, 'renewal',2, '4/2/2006'UNION ALL
SELECT'd', 4, 'renewal',3, '6/2/2006'UNION ALL
SELECT'd', 4, 'renewal',4,'9/2/2006'

I want to remve all records serials startign with type 'new' (neworder 1) which is later than ,1/1/2005' and subsquncial renewals
The desire result should be:

MerchantOldOrderOrderTypeNewOrdercdate
a1new12003-01-01 00:00:00.000
a2renewal22003-08-02 00:00:00.000
a3renewal32004-02-01 00:00:00.000
a4renewal42004-11-01 00:00:00.000
a5renewal52006-03-08 00:00:00.000
a6new12004-03-01 00:00:00.000
b1new12003-08-02 00:00:00.000
b2new12003-09-02 00:00:00.000
c1new12004-08-02 00:00:00.000
c2renewal22005-08-02 00:00:00.000
c3renewal32005-09-02 00:00:00.000
Thanks.
Jeff

View 1 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

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 Remove Blank Spaces In Records??

Jun 23, 2005

We imported approximately 2.9 million records from our mainframe server
into our SQL Server but have run into a problem.  The data in a
few of the fields contains both leading and trailing spaces.  An
example of the data would be like this, using periods to represent
spaces:

What we have:

..1A02938.....

What we need:

1A02938  (no spaces)

 Is there some sort of algorithm I can run on the data to remove
those spaces?  The problem is coming up when trying to perform a
SELECT query.  We try something like:

SELECT * FROM PCPIPT0 WHERE PANO20 = "1A02938"  but we get zero
results because of the spaces in the database.  The datatype of
the filed is char(20) because we need some flexibility on the size of
the data stored.

Any assistance would be greatly appreciated.

View 5 Replies View Related

Remove Leading Zeroes And Keep Spaces

Sep 27, 2001

I have a char(12) field that was loaded like '000000000101' I need to change the data to be ' 101'. Is there a way to do this and preserve the number and keep the leading spaces?
Thanks

View 3 Replies View Related

How To Remove White Spaces Between Words

May 30, 2008

What is the Select statement to remove white spaces between words?

View 4 Replies View Related

Need Your Help To Remove Spaces In The Column Entries Using SQL

Oct 21, 2006

Hi all,I am new to these so plz never mind if this is funny.here is my problem :Table : moodyColumn : TitleNew column : NospaceI have data in "Title" column of many rows which are normal sentence.My requirment is to remove the "white space", +, | , ., / , ! @, $, %etc special characters and fill it by ( hyphen) and put it in new"Nospace" ColumnExample :I have : Hurray ! I won the GameNeeded : Hurray-I-won-the-GameCan any body helpme in getting an SQL Query for this if possibleThanks in Advance

View 4 Replies View Related

Transact SQL :: Remove Trailing Spaces From A Column

Aug 31, 2015

The table I have is:

CREATE TABLE [dbo].[FTE2015](
[Firm Number] [varchar](50) NULL,
[w9] [varchar](50) NULL
) ON [PRIMARY]
GO

select * from dbo.FTE2015
Firm Number w9
709485""   0
040898A" 12.5
709502"" 2.4
041382"" 0.4
709503"" 0.3
709681""  4.9

How do I remove the trailing blanks? I tried RTRIM but it does not work.

SELECT RTRIM([Firm Number])
FROM dbo.FTE2015;
(No column name)
709485""
040898A"
709502""
041382""
709503""

How can I resolve this? The [Firm Number]column is not of a fixed length.

View 11 Replies View Related

Transact SQL :: LTRIM And RTRIM Does Not Remove Spaces

Oct 25, 2012

I'm not sure about why I'm not able to remove spaces even after trimming them. Below is the result of query I'm usning.

select distinct LTRIM(RTRIM(Promotion_Code)) Promotion_Code
--, count(Promotion_code)
from dbo.Marketing_Promotion_Tb
where Promotion_code  like '%1BTPIZZA%'

Result :

Promotion_Code   Length
1BTPIZZA                  10
1BTPIZZA                   8

View 12 Replies View Related

Remove Alpha Chars && All Spaces In Field [UPDATED]

Apr 5, 2007

Hello,



I need to strip out all alpha chars and spaces in a given field and return only the numbers.

I've tried =CInt(Fields!Info.Value) and get an unexplained error. If the data was formatted consitantly I could simply do a RTrim or Right, but the number strings are not the same, some have spaces as in phone numbers (1 800 555 1212) or don't have a leading 1. Most instances are correct for my purpose (8005551212).



Any help would be appreciated.



UPDATE: Using the Replace function =Replace(Fields!Info.Value, " ","") gets me almost there. Now I should be able to use a Right, 10 function to return my desired value. Is it possible to combine these two funtions together?

View 4 Replies View Related

How To Remove The Blank Spaces Between Subreports In Sql Server Reporting Service?

Feb 14, 2007

In Sql server reporting service the blank spaces or white spaces are coming in between the subreports, when we place the subreports in the main reports.

If any one know how to remove the blank spaces between the subreports, please reply me. Its very urgent.

View 2 Replies View Related

Reporting Services :: How To Remove Blank Spaces After Removing Duplicated Records

Aug 18, 2015

I wanted to remove duplicate records from SSRS report. I set the "Hide Duplicates" to True. It is now working, But i am getting the space between the two records, which i want to get rid of. How to get rid of extra spaces between two records ( Please find the details below).

View 5 Replies View Related

Create TRIGGER Remove White Spaces From A Fields In Table-scan And Fix

Apr 21, 2008

hi
i have table i use it for update insert
and the users use this table from a grid on the web
and i need to prevent from white space in the fields in table
so how to
create TRIGGER remove white space from a fields in table scan and fix it ?





Code Snippet
SELECT TRIM(fieldname)
, LTRIM(fieldname)
, RTRIM(fieldname)
, LTRIM(RTRIM(fieldname))
FROM tablename





Code Snippet
WHERE (LTRIM(RTRIM(fieldname)) = 'Approve')




Code Snippet
replace(@text,' ','')







create TRIGGER on update insert and not to damage the text in the all fields
TNX

View 21 Replies View Related

SQL Server 2008 :: Cleanup History Has Extra Output Message

Apr 16, 2015

Oldest Day till I want to Purge[30 days of Old Data] : 03-17-2015

Running Cleanuphistory Procedure On Server:

NO of records are qualifying for deletion:0

Deleted Sysmail MAil Items, Old history: 1
Deleted Sysmail Log Old history: 1
0 history entries purged. <<<<<<< ----------------------- [HOW IT IS COMING ?? ??]
Deleted SQL JOB Old HISTORY: 1

*** END OF PROCEDURE. ***

[Code] .....

View 6 Replies View Related

Trailing Spaces On Output Parameter Of SQL Task - Different Behavior On Different Machines

Dec 20, 2007



Hello Everyone,

I've seen many entries about trailing spaces but have not found one like this.

In the Control Flow I am using an "Execute SQL Task" to populate some SSIS local variables (type string) by: (1) executing a SQL stored proc with output variables (type varchar(100)) to (2) be mapped to the local variable name (the parameter mapping Data Type is VARCHAR).

One of these mapped outputs is used as a path for subsequent operation in the Control Flow. At execution the sproc fires, populating the local variable with the path but with trailing spaces out to 255. Later in the "Script Task" when that path is used I receive an error telling me that the path is too long, and something about 260 or 246 characters.

Here's the oddity. I have two desktop environments running XP and a server environment (server 2003). This package runs just fine on the server - no trailing space issue, no need to trim. But on both my desktops I get the errors. By adding trim statements I can get back the correct path, but varchars should not be including trailing spaces, and the sproc return variable is a varchar (100).

I know this soulds like numerous other posts which indicate the solution is to trim, but I think the question I am asking is why does it work on the server but not the desktop? Is the SSIS variable type string experiencing a bug on different OS's?
Not to further complicate the issue but it used to work on my laptop, but through a horrible sequence of events I had to reload the studio in which case the error started to happen on that too.

View 6 Replies View Related

Output Buffer Remove Row?

Feb 6, 2007

I am using a script component to create the output buffer dynamically. I use the Outputbuffer.AddRow() call. I then set all the fields I want, and its added to the output and later inserted into the database. If a field value fails it causes an error, but the record is partially inserted upto the point where the set field command caused the error. So if I set 10 fields, and it fails on field 5 it inserts data for the 5 fields that worked and nulls into the others.

As a result I have a try catch clause, and if it fails I want to cancell the addition of the new row. Is there a command like RemoveRow(), rollback, etc that can be used to not insert the record in error?

Sample code..

Try

PaymentOutputBuffer.AddRow()

PaymentOutputBuffer.Sequence = pi + 1

PaymentOutputBuffer.RecordID = Row.RecordID

PaymentOutputBuffer.PaymentMethod = PaymentArray(pi)

Catch e As Exception

PaymentErrorOutputBuffer.removecurrentrow(??)

End Try

View 9 Replies View Related

Remove Leading Zeros From Output

Feb 25, 2014

What is the best way to change an output of P0123 to 123? i.e. drop the letter 'P' and also any leading zeros. We have a report that outputs terminal ID's which range from P0001 through to P0536.

I can drop the 'P' easily enough, but how I can drop the P000 from terminal ID P0001 for example.

View 2 Replies View Related

SQL 2012 :: BCP Output - Remove Milliseconds Part

Apr 18, 2014

I The requirement is to unload all columns data into csv file using bcp with pipe delimiter, but the condition is to remove milliseconds part of a datetime column.

Ex:
2014-02-19 17:12:14.967 remove .967 from data while unloading into csv.

View 1 Replies View Related

Extra Line Between Header And Data In Query?

Mar 24, 2015

I need to have an automated process to generate and send out csv data on a set schedule. Using the sp_send_dbmail routine is working great except for one small problem I am having trouble getting around.

I am specifying a query to be run and including the results as a file attachment. The customer wants a csv file with column names.

The attachment always includes a 'separator line' of dashes between the column header and the actual data. Is there any way to eliminate this line of dashes? When I use @query_result_header = 0 flag, it removes the line of dashes, but the column names are also removed as well.

I want to keep the column names, but just remove the line of dashes.

View 1 Replies View Related

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.

For example,the output of the Aggregate Transformation may be 960216 ,but the

'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.

I'm sure the Group By of the Aggregate Transformation is right!



But ,when I set the "keyscale" property of the transformation,the results match!

In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.

Thanks for your advice.

View 2 Replies View Related

SQL Server 2008 :: Send Emails To Individuals In Query Results And Extra?

Jul 15, 2015

I have a set of results based on a query that displays a learner, their workplace assessor and both their email addresses.

I want to send a standard request email (using db_mail) to each learner's email address which specifies who their individual assessor is and their current employer. One per learner.
I then want to email each assessor and in this email detail their learners (and their employer details) that I have emailed the request too.

View 1 Replies View Related

Query Produces Jumbled Output / Output Not In Sequence

Jul 23, 2005

Hi!Server info -Win2K3 Server +SP1 with 1 GB Memory and 1.5 GB Virtual MemorySQL Server 2000 Enterprise Edition + SP3 running on this.Required result -Create a SQL Script that will contain a set of create, update, insert& delete statements (about 17500 lines including blank lines) thatcan be run on different databases seperatelyHow we do this -We have a SP - that creates a temporary table and then calls anotherSP that actually populates the temporary table created by the first SP*Samples of both SPs are below -PROBLEMThe result is directed to a file -However when the query is run it runs through the entire script but'Jumbles' the outputRunning the same scripts on a copy of the database on other machineswork fine and the size of the outfiles is exactly the sameI have increased the page size to 2.5 GB and restarted the server.Running the sp now generated the correct output a few times but gotjumbled as before after a few more users logged in and activity on theserver increased.Another interesting point is that the output is jumbled exactly thesame way each time. It seems the sql executes correctly and writesthe output in chunks only writting the chunks out of sequence - butin the same sequence each time.e.g. of expected resultInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table3Values ................Update RefGenSet Last = 1234Where RefGenRef = 1JUMBLED OUTPUTInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table2Values ...Values ...Update RefGenSet Last = 1234Where RefGenRef = 1Insert into Table3Values ................Insert into Table1Values c, d, e, 21, 12....Insert into Table2----------------------------------------Sample of First Script - STATDATA_RSLT**************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET NOCOUNT ONGOCREATE PROCEDURE StatData_rsltASCREATE TABLE #tbl_Script(ScriptText varchar(4000))EXEC TestStatData_intSELECT t.ScriptTextFROM #tbl_Script tGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************************Sample of CALLED SP - TestStatData_int*******************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE TestStatData_intASDECLARE @AttrRef int,@TestID int,@PrtTestRef int,@AttrType tinyint,@EdtblSw tinyint,@NmValRef int,@SrtTypeRef int,@AttrStr varchar(20),@TestStr varchar(20),@PrtTestStr varchar(20),@AttrTypeStr varchar(20),@EdtblStr varchar(20),@NmValStr varchar(20),@SrtTypeStr varchar(20),@TestRef int,@Seq int,@PrtRef int,@Value varchar(255),@TermDate datetime,@AttrID int,@DefSw tinyint,@WantSw tinyint,@TestRefStr varchar(20),@SeqStr varchar(20),@PrtStr varchar(20),@TermDateStr varchar(255),@AttrIDStr varchar(20),@DefStr varchar(20),@WantStr varchar(20),@LanRef int,@LanStr varchar(20),@Code varchar(20),@Desc varchar(255),@MultiCode varchar(20),@MultiDesc varchar(255),@InhSw tinyint,@InhStr varchar(20),@InhFrom int,@InhFromStr varchar(20),@Lan_TestRef int,@ActSw tinyint,@ActSwStr varchar(20)SELECT @Lan_TestRef = dbo.fn_GetTestRef('Lan')INSERT INTO #tbl_ScriptVALUES('')-- Create tablesINSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRefint, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRefint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES('CREATE TABLE #tbl_TestAttr(AttrRef int, TestRef int, Seq int,PrtRef int, AttrType tinyint, Value varchar(255), TermDate datetime,AttrID int, DefSw tinyint, WantSw tinyint, ActSw tinyint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Codevarchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDescvarchar(255), InhSw tinyint, InhFrom int)')INSERT INTO #tbl_ScriptVALUES ('')-- insert Test valuesDECLARE Test_cursor CURSOR FORSELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,l.NmValRef, l.SrtTypeRefFROM Test lOPEN Test_cursorFETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestStr = ISNULL(CAST(@TestID as varchar), 'NULL'),@PrtTestStr = ISNULL(CAST(@PrtTestRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@EdtblStr = ISNULL(CAST(@EdtblSw as varchar), 'NULL'),@NmValStr = ISNULL(CAST(@NmValRef as varchar), 'NULL'),@SrtTypeStr = ISNULL(CAST(@SrtTypeRef as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +@PrtTestStr+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +@SrtTypeStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefENDCLOSE Test_cursorDEALLOCATE Test_cursorDECLARE TestAttr_cursor CURSOR FORSELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,le.Value,le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSwFROM TestAttr leWHERE le.WantSw = 1AND le.ActSw = 1OPEN TestAttr_cursorFETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestRefStr = ISNULL(CAST(@TestRef as varchar), 'NULL'),@SeqStr = ISNULL(CAST(@Seq as varchar), 'NULL'),@PrtStr = ISNULL(CAST(@PrtRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@Value = ISNULL(@Value, 'NULL'),@TermDateStr = ISNULL(CAST(@TermDate as varchar), 'NULL'),@AttrIDStr = ISNULL(CAST(@AttrID as varchar), 'NULL'),@DefStr = ISNULL(CAST(@DefSw as varchar), 'NULL'),@WantStr = ISNULL(CAST(@WantSw as varchar), 'NULL'),@ActSwStr = ISNULL(CAST(@ActSw as varchar), '1')SELECT @Value = '''' + @Value + ''''WHERE @Value <> 'NULL'INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +@SeqStr+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr+', '+ @ActSwStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwENDCLOSE TestAttr_cursorDEALLOCATE TestAttr_cursorDECLARE AttrName_cursor CURSOR FORSELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,e.MultiDesc, e.InhSw, e.InhFromFROM AttrName e, TestAttr leWHERE e.LanRef = 0AND e.AttrRef = le.AttrRefAND le.WantSw = 1AND le.ActSw = 1OPEN AttrName_cursorFETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@LanStr = ISNULL(CAST(@LanRef as varchar), 'NULL'),@Code = ISNULL(@Code, 'NULL'),@Desc = ISNULL(@Desc, 'NULL'),@MultiCode = ISNULL(@MultiCode, 'NULL'),@MultiDesc = ISNULL(@MultiDesc, 'NULL'),@InhStr = ISNULL(CAST(@InhSw as varchar), 'NULL'),@InhFromStr = ISNULL(CAST(@InhFrom as varchar), 'NULL')SELECT @Code = REPLACE(@Code, '''',''''''),@Desc = REPLACE(@Desc, '''','''''') ,@MultiCode = REPLACE(@MultiCode, '''','''''') ,@MultiDesc = REPLACE(@MultiDesc, '''','''''')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],MultiCode, MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +''',' + @InhStr + ', ' + @InhFromStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromENDCLOSE AttrName_cursorDEALLOCATE AttrName_cursor-- Do update TestAttr dataINSERT INTO #tbl_ScriptVALUES ('UPDATE le')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' le.TestRef = t.TestRef,')INSERT INTO #tbl_ScriptVALUES (' le.PrtRef = t.PrtRef,')INSERT INTO #tbl_ScriptVALUES (' le.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' le.Value = t.Value,')INSERT INTO #tbl_ScriptVALUES (' le.TermDate = t.TermDate,')INSERT INTO #tbl_ScriptVALUES (' le.AttrID = t.AttrID,')INSERT INTO #tbl_ScriptVALUES (' le.DefSw = t.DefSw,')INSERT INTO #tbl_ScriptVALUES (' le.WantSw = t.WantSw,')INSERT INTO #tbl_ScriptVALUES (' le.ActSw = t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM TestAttr le, #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('WHERE le.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Update AttrNameINSERT INTO #tbl_ScriptVALUES ('UPDATE en')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' en.Code = te.Code,')INSERT INTO #tbl_ScriptVALUES (' en.[Desc] = te.[Desc],')INSERT INTO #tbl_ScriptVALUES (' en.MultiCode = te.MultiCode,')INSERT INTO #tbl_ScriptVALUES (' en.MultiDesc = te.MultiDesc,')INSERT INTO #tbl_ScriptVALUES (' en.InhSw = te.InhSw,')INSERT INTO #tbl_ScriptVALUES (' en.InhFrom = te.InhFrom')INSERT INTO #tbl_ScriptVALUES ('FROM AttrName en, #tbl_AttrName te')INSERT INTO #tbl_ScriptVALUES ('WHERE en.AttrRef = te.AttrRef')INSERT INTO #tbl_ScriptVALUES (' AND en.LanRef = te.LanRef')INSERT INTO #tbl_ScriptVALUES (' AND te.LanRef = 0')-- Do update Test the dataINSERT INTO #tbl_ScriptVALUES ('UPDATE l')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' l.TestID = t.TestID,')INSERT INTO #tbl_ScriptVALUES (' l.PrtTestRef = t.PrtTestRef,')INSERT INTO #tbl_ScriptVALUES (' l.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' l.EdtblSw = t.EdtblSw,')INSERT INTO #tbl_ScriptVALUES (' l.NmValRef = t.NmValRef')INSERT INTO #tbl_ScriptVALUES ('FROM Test l, #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('WHERE l.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')--DELETE where just updatedINSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t, Test l')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = l.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM te')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE te.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Insert TestAttrINSERT INTO #tbl_ScriptVALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,AttrType,Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestRef, t.Seq, t.PrtRef, t.AttrType,t.Value, t.TermDate, t.AttrID, t.DefSw, t.WantSw, t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('')-- AttrNameINSERT INTO #tbl_ScriptVALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],MultiCode,MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],te.MultiCode, te.MultiDesc, ')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN 0')INSERT INTO #tbl_ScriptVALUES (' ELSE 1 END,')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN NULL')INSERT INTO #tbl_ScriptVALUES (' ELSE 0 END')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))INSERT INTO #tbl_ScriptVALUES ('')-- Insert new rowsINSERT INTO #tbl_ScriptVALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,t.EdtblSw, t.NmValRef, t.SrtTypeRef')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_Test')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_TestAttr')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_AttrName')-- Update RefGenDECLARE @RefGenReflast int,@RefGenRefStr varchar(10)SELECT @RefGenReflast = lastFROM RefGenWHERE RefGenRef = 1SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES('')INSERT INTO #tbl_ScriptVALUES('UPDATE RefGen')INSERT INTO #tbl_ScriptVALUES ('SET Last = ' + @RefGenRefStr)INSERT INTO #tbl_ScriptVALUES ('WHERE RefGenRef = 1')INSERT INTO #tbl_ScriptVALUES ('')GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************RegardsGlenn

View 5 Replies View Related

How To Trim Spaces In The Query Result

Oct 31, 2007



Hello guys,

Iam showing the query results in the asp.net grid. Iam getting a long string with more than 1 space between some words.
How i can trim the spaces of that column result.

I tried RTRIM and LTRIM but these functions need two individual strings with spaces either left or right.

Help me.

View 15 Replies View Related

Can’t Understand The Query Execution Interval, For Every One Criteria It Takes Extra 60 Seconds. Help Please?,

Aug 17, 2006

Can€™t understand the Query execution interval, for every one criteria it takes extra 60 seconds. Help please?,  







Query A

SELECT TOP 15 * FROM CMD4 AS T1 WHERE T1.ST IN

(SELECT max(T2.ST) FROM CMD4 AS T2 WHERE T1.SERIAL=T2.SERIAL

AND T2.sd='20060817') AND sd='20060817'

--AND STOCKNAME like 'A%'

ORDER BY D_CUM desc;

The above query take 2 seconds to return result







Query B

SELECT TOP 15 * FROM CMD4 AS T1 WHERE T1.ST IN

(SELECT max(T2.ST) FROM CMD4 AS T2 WHERE T1.SERIAL=T2.SERIAL

AND T2.sd='20060817') AND sd='20060817' AND STOCKNAME like 'A%' ORDER BY D_CUM desc

This query same as above but " AND STOCKNAME like 'A%'" Criteria only added but it takes 01 minute 43 seconds to return result. why it is like that where i need to adjust the query i have no idea. please help

View 1 Replies View Related

Using Query Parameter When Data Cell Has Leading Spaces

Feb 21, 2008



In SQL Reporting I want the user to pass a parameter to the report; the parameter is a employee number. Easy right, you write a where statement like where table_name.employee_number =@employee_number. When the user runs the report they€™re asked to type in the number.

My problem is the dB stores the employee number with leading spaces padding the number out to 9, so if the user types in 1 they get jack, but if they type in €˜ 1€™ they get the right employee. I can€™t roll it out that way

I€™d do something in the where clause so the user could just type in 1 & it would auto pad out to 9 spaces. I€™ve tried using the €˜LIKE€™ clause & I€™ve tried using concatenate but so far neither works well.

I'd appreciate any advice

Thanks -- Vince

View 3 Replies View Related

Writing A Query To Find Results With Any Spaces Prior To Value

May 13, 2008

I have a table that contains a column called hardness. It is a varchar 50 and actually contains numbers. I did not create the database so not sure why they choose varchar vs. number. I need to write a query that will return all results with a space prior to the number in this column. I am having difficulty and would greatly appreciate some help. Thanks!

View 6 Replies View Related

How To Remove Partially Duplicate Rows From Select Query's Result Set (DB Schema Provided And Query Provided).

Jan 28, 2008

Hi, 
Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. 
I have three tables namely – Forums,Topics and Threads  in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- 
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
ORDER BY t.topicid ASC,th.lastpostdate DESC 
Whose result set is as below:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
1
Java Overall
a@b.com
2008-01-27 14:44:29.000
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0  
On modifying the query to:- 
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid)
ORDER BY t.topicid ASC,th.lastpostdate DESC 
I get the result set as below:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
4
Swings
p@q.com
2008-01-27 15:12:51.000

I want the result set as follows:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0  I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. 
The scripts for creating the tables and inserting test data is as follows in an already created database:- 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO 
CREATE TABLE [dbo].[Forums] (
            [forumid] [int] IDENTITY (1, 1) NOT NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO 
CREATE TABLE [dbo].[Threads] (
            [threadid] [int] IDENTITY (1, 1) NOT NULL ,
            [topicid] [int] NOT NULL ,
            [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [replies] [int] NOT NULL ,
            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO 
CREATE TABLE [dbo].[Topics] (
            [topicid] [int] IDENTITY (1, 1) NOT NULL ,
            [forumid] [int] NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Forums] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [forumid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Threads] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [threadid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Topics] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [topicid]
            )  ON [PRIMARY]
GO  
ALTER TABLE [dbo].[Threads] ADD
             FOREIGN KEY
            (
                        [topicid]
            ) REFERENCES [dbo].[Topics] (
                        [topicid]
            )
GO 
ALTER TABLE [dbo].[Topics] ADD
             FOREIGN KEY
            (
                        [forumid]
            ) REFERENCES [dbo].[Forums] (
                        [forumid]
            )
GO  
------------------------------------------------------ 
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum'); 
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion'); 
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
 

View 7 Replies View Related







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