CCC-Ravens
asked on
Remove spaces between select statements
I have a MSSQL Query That returns a file that I export as a .udf file that can be imported into my food service software but I get a break where I don't want one and can not get rid of the space without editing the file.
Here is the Query
!RecordCount:162
!BeginFormat
!Odyssey_PCS, 1, 6, "IDNUMBER"
!Odyssey_PCS, 2, 6, "CARDNUMBER"
!Odyssey_PCS, 14, 20, "LASTNAME"
!Odyssey_PCS, 34, 21, "FIRSTNAME"
!EndFormat
!BeginData
107951 Test Student1
110004 Test Student2
108332 Test Student3
!EndData
I need the break between !BeginData and Test Student1 gone as well as
the break between Test Student 3 and !EndData
Any help here would be greatly appreciated!
Here is the Query
SET NOCOUNT ON
SELECT '!Version: 4.2' +CHAR(13)+
'!RecordCount:'+
cast((SELECT count(*)
FROM STUDENT_MASTER INNER JOIN
NAME_MASTER ON STUDENT_MASTER.ID_NUM = NAME_MASTER.ID_NUM
WHERE (STUDENT_MASTER.ROOM_TYPE = '1s') OR
(STUDENT_MASTER.ROOM_TYPE = '2s,') OR
(STUDENT_MASTER.ROOM_TYPE = '3s,') OR
(STUDENT_MASTER.ROOM_TYPE = '4s,') OR
(STUDENT_MASTER.ROOM_TYPE = '5s,') OR
(STUDENT_MASTER.ROOM_TYPE = '6s')
) as varchar)
+CHAR(13)+
'!BeginFormat' +CHAR(13)+
'!Odyssey_PCS,' + ' ' + '1,' + ' ' + '6,' + ' ' + '"IDNUMBER"' +CHAR(13)+
'!Odyssey_PCS,' + ' ' + '2,'+ ' ' + '6,'+ ' ' + '"CARDNUMBER"' +CHAR(13)+
'!Odyssey_PCS,' + ' ' + '14,' + ' ' + '20,' + ' ' + '"LASTNAME"' +CHAR(13)+
'!Odyssey_PCS,' + ' ' + '34,' + ' ' + '21,' + ' ' + '"FIRSTNAME"' +CHAR(13)+
'!EndFormat'+CHAR(13)+
'!BeginData '
SELECT STUDENT_MASTER.ID_NUM, NAME_MASTER.LAST_NAME, NAME_MASTER.FIRST_NAME
FROM STUDENT_MASTER INNER JOIN
NAME_MASTER ON STUDENT_MASTER.ID_NUM = NAME_MASTER.ID_NUM
WHERE (STUDENT_MASTER.ROOM_TYPE = '1s') OR
(STUDENT_MASTER.ROOM_TYPE = '2s,') OR
(STUDENT_MASTER.ROOM_TYPE = '3s,') OR
(STUDENT_MASTER.ROOM_TYPE = '4s,') OR
(STUDENT_MASTER.ROOM_TYPE = '5s,') OR
(STUDENT_MASTER.ROOM_TYPE = '6s')
ORDER BY NAME_MASTER.LAST_NAME, NAME_MASTER.FIRST_NAME
SELECT '!EndData' AS ' '
!Version: 4.2!RecordCount:162
!BeginFormat
!Odyssey_PCS, 1, 6, "IDNUMBER"
!Odyssey_PCS, 2, 6, "CARDNUMBER"
!Odyssey_PCS, 14, 20, "LASTNAME"
!Odyssey_PCS, 34, 21, "FIRSTNAME"
!EndFormat
!BeginData
107951 Test Student1
110004 Test Student2
108332 Test Student3
!EndData
I need the break between !BeginData and Test Student1 gone as well as
the break between Test Student 3 and !EndData
Any help here would be greatly appreciated!
Extra line end is added after every query.
You can get around this using combination of command line tools osql and findstr. Print the results to file with osql and remove the line endings with findstr.
Example:
osql -E -d Yourdb -iYourQuery.sql -h-1 -w500 -n -b | findstr /V /r /c:"^$"
>output.txt
You can get around this using combination of command line tools osql and findstr. Print the results to file with osql and remove the line endings with findstr.
Example:
osql -E -d Yourdb -iYourQuery.sql -h-1 -w500 -n -b | findstr /V /r /c:"^$"
>output.txt
ASKER
OUT PUT SHOULD BE.....
__________________________ __________ __________ __________ __________ __________ __________ _____
!Version: 4.2
!RecordCount:343
!BeginFormat
!Odyssey_PCS, 1, 6, "IDNUMBER"
!Odyssey_PCS, 2, 6, "CARDNUMBER"
!Odyssey_PCS, 14, 20, "LASTNAME"
!Odyssey_PCS, 34, 21, "FIRSTNAME"
!EndFormat
!BeginData
999999 Test Student1
888888 Test Student2
777777 Test Student3
!EndData
__________________________
!Version: 4.2
!RecordCount:343
!BeginFormat
!Odyssey_PCS, 1, 6, "IDNUMBER"
!Odyssey_PCS, 2, 6, "CARDNUMBER"
!Odyssey_PCS, 14, 20, "LASTNAME"
!Odyssey_PCS, 34, 21, "FIRSTNAME"
!EndFormat
!BeginData
999999 Test Student1
888888 Test Student2
777777 Test Student3
!EndData
My script above does the trick. Check that.
ASKER
Your cmd seems to work but it is not creating the output file for me.
ASKER
Ok fixed the output problem, But it is still not quite right. it is showing up as...
__________________________ __________ __________ __________ __________ __________ __________
!Version: 4.2
!RecordCount:352
!BeginFormat
!Odyssey_PCS, 1, 6, "IDNUMBER"
!Odyssey_PCS, 2, 6, "CARDNUMBER"
!Odyssey_PCS, 14, 20, "LASTNAME"
!Odyssey_PCS, 34, 21, "FIRSTNAME"
!EndFormat
!BeginData
104006 Test Student1
109746 TEST Student2
110976 Test Student3
!EndData
The space at the beginning has to go away and the dead space before each student cannot be there.
<--!Version: 4.2
!RecordCount:352
!BeginFormat
!Odyssey_PCS, 1, 6, "IDNUMBER"
!Odyssey_PCS, 2, 6, "CARDNUMBER"
!Odyssey_PCS, 14, 20, "LASTNAME"
!Odyssey_PCS, 34, 21, "FIRSTNAME"
!EndFormat
!BeginData
<----104006 Test Student1
<----109746 TEST Student2
<----110976 Test Student3
!EndData
__________________________
!Version: 4.2
!RecordCount:352
!BeginFormat
!Odyssey_PCS, 1, 6, "IDNUMBER"
!Odyssey_PCS, 2, 6, "CARDNUMBER"
!Odyssey_PCS, 14, 20, "LASTNAME"
!Odyssey_PCS, 34, 21, "FIRSTNAME"
!EndFormat
!BeginData
104006 Test Student1
109746 TEST Student2
110976 Test Student3
!EndData
The space at the beginning has to go away and the dead space before each student cannot be there.
<--!Version: 4.2
!RecordCount:352
!BeginFormat
!Odyssey_PCS, 1, 6, "IDNUMBER"
!Odyssey_PCS, 2, 6, "CARDNUMBER"
!Odyssey_PCS, 14, 20, "LASTNAME"
!Odyssey_PCS, 34, 21, "FIRSTNAME"
!EndFormat
!BeginData
<----104006 Test Student1
<----109746 TEST Student2
<----110976 Test Student3
!EndData
Hmm, works for me, creates the output.txt defined at the end of the script.
ASKER
I got it to create the file but the format is just not quite correct. It has to be exact.
it must be exactly...
__________________________ __________ __________ __________ __________ __________ __________ __
!Version: 4.2
!RecordCount:343
!BeginFormat
!Odyssey_PCS, 1, 6, "IDNUMBER"
!Odyssey_PCS, 2, 6, "CARDNUMBER"
!Odyssey_PCS, 14, 20, "LASTNAME"
!Odyssey_PCS, 34, 21, "FIRSTNAME"
!EndFormat
!BeginData
104006 Test Student1
109746 Test Student2
110976 Test Student3
!EndData
it must be exactly...
__________________________
!Version: 4.2
!RecordCount:343
!BeginFormat
!Odyssey_PCS, 1, 6, "IDNUMBER"
!Odyssey_PCS, 2, 6, "CARDNUMBER"
!Odyssey_PCS, 14, 20, "LASTNAME"
!Odyssey_PCS, 34, 21, "FIRSTNAME"
!EndFormat
!BeginData
104006 Test Student1
109746 Test Student2
110976 Test Student3
!EndData
You should wrap this part into one string:
SELECT STUDENT_MASTER.ID_NUM, NAME_MASTER.LAST_NAME, NAME_MASTER.FIRST_NAME
->
SELECT
CAST(STUDENT_MASTER_ID_NUM AS CHAR(10)) + CAST(NAME_MASTER.LAST_NAME AS CHAR(15))+ CAST(NAME_MASTER.FIRST_NAM E AS CHAR(20))
With correct CHAR lenghts you should be able to align the columns?
SELECT STUDENT_MASTER.ID_NUM, NAME_MASTER.LAST_NAME, NAME_MASTER.FIRST_NAME
->
SELECT
CAST(STUDENT_MASTER_ID_NUM
With correct CHAR lenghts you should be able to align the columns?
ASKER
I am sorry but you lost me on that last post.
I mean that the problem is now in this part of the code:
So you should replace the columns with one string where you are able to align them correctly (I presume the 'data' columns should be fixed width?)
In that case the SELECT part should be replaced with something like this - where you are able to align the columns exactly.
SELECT STUDENT_MASTER.ID_NUM, NAME_MASTER.LAST_NAME, NAME_MASTER.FIRST_NAME
FROM STUDENT_MASTER INNER JOIN
NAME_MASTER ON STUDENT_MASTER.ID_NUM = NAME_MASTER.ID_NUM
WHERE (STUDENT_MASTER.ROOM_TYPE = '1s') OR
(STUDENT_MASTER.ROOM_TYPE = '2s,') OR
(STUDENT_MASTER.ROOM_TYPE = '3s,') OR
(STUDENT_MASTER.ROOM_TYPE = '4s,') OR
(STUDENT_MASTER.ROOM_TYPE = '5s,') OR
(STUDENT_MASTER.ROOM_TYPE = '6s')
ORDER BY NAME_MASTER.LAST_NAME, NAME_MASTER.FIRST_NAME
SELECT '!EndData' AS ' '
So you should replace the columns with one string where you are able to align them correctly (I presume the 'data' columns should be fixed width?)
In that case the SELECT part should be replaced with something like this - where you are able to align the columns exactly.
SELECT
CAST(STUDENT_MASTER_ID_NUM AS CHAR(10)) +
CAST(NAME_MASTER.LAST_NAME AS CHAR(15))+
CAST(NAME_MASTER.FIRST_NAME AS CHAR(20))
ASKER
I have tried this as well but the leading spaces are still there
Strange, I cannot reproduce.
ID_NUM is int - not char? And doesn't contain spaces?
LTRIM(CAST(STUDENT_MASTER. ID_NUM AS CHAR(10)) would fix that
ID_NUM is int - not char? And doesn't contain spaces?
LTRIM(CAST(STUDENT_MASTER.
ASKER
Yeah and it's weird because the spaces are not there when I run the SQL in SQL Server management studio. But the line breaks are there. Your script gets rid of the line breaks but adds the spaces.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect That did the trick.
you can use for formatting output : line ending characters; Carriage Return ( CR - CHAR(13) ) and Line Feed (LF - CHAR(10) ).
also Replace sql function to remove "extra" space chars
http://msdn.microsoft.com/en-us/library/ms186862(v=sql.105).aspx
Control character
Value
Tab
char(9)
Line feed
char(10)
Carriage return
char(13)