DTS And Fixed Widths

Dec 2, 2003

Hi all.





I'm trying to export some tables in fixed width format for a client that needs it this way. The first time I did this, using Import/Export in Enterprise Manager, integer fields were 12 chars long. However, when I saved this as a DTS and ran it again later, it made integers 4 chars in the text file (which matches the lengths of the datatypes you see in enterprise manager.)





Why is this difference there? What do I need to do differently? Also, is there a list of how wide each data type is when exported as fixed delimited text?





thanks,


Craig

View 2 Replies


ADVERTISEMENT

Error With Fixed Widths

Mar 7, 2008

hi when I try to export data to a fixed width txt file I get the following error:

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "postcode" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

even though my sql for postcode is LEFT(postcode,8) and the field length is 8 !
do you now why ?

View 1 Replies View Related

Transact SQL :: Convert Non Fixed Rows To Non Fixed Columns Dynamically?

Oct 5, 2015

I have a table with 3 columns  (ID Int , Name Varchar(25), Course Varchar(20))

My source data looks like below

ID      Name        Course
1        A                Java
1        A                C++
2        B                Java
2        B                SQL Server
2        B                .Net
2        B                 SAP
3        C                 Oracle

My Output should look like below...

ID      Name       Course(1)     Course(2)         Course(3)     Course(4)  

1        A                 Java            C++
2        B                 Java            SQL Server .Net             SAP
3        C                 Oracle

Basically need t-sql to Convert non fixed rows to non fixed columns...

Rule: IF each ID and Name have more than 1 course then show it in new columns as course(1) course(2)..Course(n)

Create SQL:

Create table Sample (ID Int null , Name  Varchar(25) null, Course Varchar(20) null)

Insert SQL:

INSERT Sample (ID, Name, Course)
          VALUES (1,'A','Java'),
                 (1,'A','C++'),
                 (2,'B','Java'),
                 (2,'B','SQL Server'),
                 (2,'B','.Net'),
                 (2,'B','SAP'),
                 (3,'C','Oracle')

View 12 Replies View Related

Bar Charts - Possible To Set Max Widths Of Columns ?

Jul 9, 2007



Hi,



Is it possible to fix the max width of the columns ?? Sometime the report only generates 1 or 2 columns and it looks a bit odd as they're stretched to the width of the page.



Also, when we have a large number of culumns, say 20 odd, the legend text get's real squashed up and un readable, are there any tips/tricks so i can have more flexibility over what's displayed ?



thanks



Steve

View 2 Replies View Related

Help Importing File With Varying Column Widths

Nov 7, 2007

True story. Thanks. Dont think that fixed or ragged is going to work for me though. File does not seem to be structured.


I have what appears to be a fixed length file that I would like to import using SSIS. However it does not look loike the spacing between fields is constant. Is there a way to say from character 1 to 10 is X, and 11 to 15 is Y, etc etc? Below is a couple of rows that all pertain to the same record.

01000000225672101242253 55232101242253 5TKTT / 0101 FFVV 5235MCYSNR/CE MUSSETT/BRIAN.A.MR 20071017U7700002277000022 U00000000ZAJNB JNB ZA 00000000 00000000 00000000 SITII Y06 405
02000000235672101242253 55232101242253 5ICE 20071017 1407000017OCT 17OCT
03000000245672101242253 55232101242253 5 PLZPLZ ZAR 1000 ZAR 72ZAZAR 140ZVZAR 562XTZAR 1774 ADT PENALTY APPLIES
04000000255672101242253 55232101242253 50000017740000000100000ZAR200000000000 0000000000000000 0000000000000000 00000000000000000000000000000000 000000000000000000000000000000000ZA 00000007200ZV 00000014000EV 00000001600YR 00000054600 00000000000 00000000000 00000000000 00000000000 00000000000
05000000265672101242253 55232101242253 51 OPLZ DUR CE CE 0633 0633 K K 18OCT18OCT18OCT1625 1740 OK KOW 2 DUR PLZ CE CE 0602 0602 B B 20OCT20OCT20OCT1000 1115 OK BOW
07000000275672101242253 55232101242253 5CCIK5221 000001774005221591015192982 0308M067781 00000000000 00000000000 00000000000
08000000285672101242253 55232101242253 509PLZ CE DUR550CE PLZ450ZAR1000 END CE XT16EV546YR 1 2 3 4

What do you reckon is the best option? SUBSTRING in a Dervied Column? Below is an example of the text.

View 5 Replies View Related

Table Column Widths Size Differently When Emailed To Outlook

May 13, 2007

I am able to get reports going with tables sized properly. They look fine on the ReportServer website and I adjust the column widths so that the headings and data look nice. When I set up a subscription to be delivered by "Report Server E-Mail," though, the table formattings get completely distorted.



In particular, I have two tables, with some column headers being two short words (e.g. Max Height). When rendering on the site, I adjust the columns so the full column header is visible on one line. When I receive the email and read it in Outlook, the header row is now about twice as tall and everything is scrunched together. Both the headings and the data in the fields do not format the same as on the website.



The two tables tend to actually have the exact same width in the email version, although occasionally they are a little different (in the web version one is about half as wide as the other). I have tried just making the columns bigger and that has not worked. I've tried making the font sizes smaller, which didn't work. If I do that, leaving the columns the same width, the email version just gets scrunched into a smaller area with the same text-wrapping problems.



If I open the email in a browser (in a web mail interface) the report renders perfectly as on the site.



I have almost all the default settings, and haven't been messing around with page sizes and things like this (except after, to see if that would fix the problem).



Any ideas, similar experiences, or suggestions? If there is a book I should read or any reference you could point me to in order to figure this out would be helpful. I haven't been able to understand this either using web searches or the two SQL reporting services books I have.

View 4 Replies View Related

Make Subtotal Column Widths Bigger Than Normal Columns?

Feb 12, 2007

The reason I say this is because a subtotal of a dollar amount will take up more space than other values. Right now, I'm forced to make all columns the same larger width because it appears to be all wrapped into 1 column width setting. I can try to change the value of the subtotal column, "matrixcolumn4", but it reverts to the other value after I press enter to apply the changes.

View 1 Replies View Related

Has This Been Fixed In SP1?

May 1, 2006

Can someone from MS tell me if this has been fixed in SP1?

http://blogs.conchango.com/jamiethomson/archive/2005/10/11/2261.aspx

Thanks

Jamie

View 3 Replies View Related

FIXED THE PROBLEM I AM GETTING

Jan 22, 2005

Hello Friends

I am very glad to tell all u ppl that i ve got the solution to my problem at last
I ve just posted my problem(few mins back) in which i was getting problem with connecting to the ASP.NET Web application. I have made a new user on sql through SQL Enterprise Manager as <machinename>ASPNET, and my problem gets solved

sorry to bother u all


Regards
BSS

View 1 Replies View Related

Add A Fixed Value (integer)

May 30, 2006

philippe writes "Hello ,
i am just starting with sql...

I have a table: Table_user

Inside a column user_no (integer)
with value like 35678 (about 8000 rows)
I would like to modifie all rows of this Table_user.user_no
with a value of 60 at the begining of each value.
For example : before 35678 will be 6035678
Its'a concatenation,but it's an integer value .
so do i need to convert tehm to varchar first

I will appreciate some tips...."

View 5 Replies View Related

Possible To Have A Fixed Value As Foreign Key?

Aug 15, 2007

Hi everyone,

I'm trying to make a hierarchy in which some nodes are locked, preventing children nodes from being added.

The hierarchy table points to itself (the parent entry). I want to secure this from the DB side. Initially, no nodes have parents but can attach themselves to one.

I'd like my FK contraint to be like:
CONSTRAINT FK_hierarchy FOREIGN KEY (parent_id, 1) REFERENCES nde_recursion(hierarchy_id, allow_children)

This is so the parent_id can only be set if that entry allows children.

Here's my approach. Any better ideas? I feel like I'm adding an extra field (parent_allow) when I shouldn't.


CREATE TABLE hierarchy(
hierarchy_idINTIDENTITY(1,1) UNIQUE NOT NULL,
allow_childrenBITDEFAULT 1 NOT NULL,

parent_idINT,-- NULL is for root nodes
parent_allowBITDEFAULT 1 NOT NULL,

CONSTRAINT PK_hierarchy PRIMARY KEY (hierarchy_id, allow_children),
CONSTRAINT FK_hierarchy FOREIGN KEY (parent_id, parent_allow) REFERENCES nde_recursion(hierarchy_id, allow_children),
CONSTRAINT CK_hierarchy CHECK ((nde_status = 'Normal' AND parent_id IS NULL) OR (nde_status != 'Normal' AND parent_id IS NOT NULL)),
CONSTRAINT CK_hierarchy2 CHECK (parent_allow = 1)
)

View 5 Replies View Related

Fixed Header

Mar 1, 2007

Hello!

I have a report that the users reach from an url. In the url I have rc:parameters = false. That part works fine. But when I use rc:parameters = false, the property fixed header doesn't seem to work. If I run the report from the report server it works fine, but not from the url.

Any ideas?

/C





View 2 Replies View Related

Fixed Header

Feb 15, 2008

Hi

I have a table in a report which has the FixedHeader property set to true. What happens is that when I try to look for a text using the find option, the found text gets hidden under the header row, which then I have scroll back to see that. The user may think that the text was not found. Any solution to be able to bring the found item to somewhere the user can see?

Thank you

View 7 Replies View Related

Fixed Decimal Convertion

Dec 6, 2007

I am trying to show latitude and longitude with 5 decimal points. Now its showing (for example: 55.744025477, -4.1256633333333 etc.). How do I get  data in 5 decimal points? Your help with example would be appreciated.  aspx code:  <asp:GridView  ID="GridView1" runat="server" DataSourceID="odsGPS" AllowPaging="true" AllowSorting="true"     AutoGenerateColumns="False" CellPadding="1" CellSpacing="1" BackColor="White" GridLines="None"     BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" PageSize="20" Width="100%" Font-Size="8pt"     OnLoad="GridView1_Load" >        <Columns>            <asp:TemplateField HeaderText="Show">                <ItemTemplate>                    <asp:CheckBox ID="CheckBox2" onclick="MarkerForThisRow(this);" ToolTip="Click to show on map." runat="server" OnCheckedChanged="CheckBox2_CheckedChanged" />                </ItemTemplate>                <ItemStyle HorizontalAlign="Center" />                <HeaderStyle HorizontalAlign="Center" />            </asp:TemplateField>                                  <asp:BoundField DataField="Latitude"  HeaderText="Latitude ( ° )"  >                <ItemStyle HorizontalAlign="Center"  />                <HeaderStyle HorizontalAlign="Center" />            </asp:BoundField>            <asp:BoundField DataField="Longitude"  HeaderText="Longitude ( ° )" >                <ItemStyle HorizontalAlign="Center" />                <HeaderStyle HorizontalAlign="Center" />            </asp:BoundField> 
 

View 9 Replies View Related

SQL Query With Fixed Width

Oct 31, 2005

 I have a request to produce a SQL report that will be produced with fixed column width. For example see below Position    Element    LengthField    Format 1-2          Column1    2                Alphanumeric 3-4          Column2    2                Alphanumeric 5-13        Column3    9                Chars Any idea how I can produce the report specified above? Thanks

View 1 Replies View Related

Fixed Db_owner Role

Nov 1, 1999

I have a server that was upgrade to 7 several months ago. The cmptlevel was set to 7 once upgraded. I have a developer group as dbo_owner role on a database and now on certain tables (not all) they can add fields and save. But then go back in and remove the field then try to save they get the error message "- Unable to modify table. ODBC error:[Microsoft][ODBC SQL Server Driver][SQL Server]SETUSER permission denied, database 'XXX',owner 'dbo'."

View 1 Replies View Related

Bigint And Int64 - When Fixed?

Nov 22, 2006

I am attempting to build our first set of packages populating or DW. All of our source system primary keys are bigints (on the main tables at least). SSIS seems to have a problem dealing with bigint values, i.e. having to assign variables as doubles rather than int64, having to cast returning bigint values as float, or bigint sproc output parameters as double. It is all a bit messy (and possibly the most frustrating part of SSIS for me) - does anyone know if this problem is due to be fixed in any forthcoming release?

View 2 Replies View Related

Add Zeros For The Fixed Column

Mar 24, 2008



I would like to add right number of 0 to make the data length of varchar (14)
For example,
before after
1768 -- > 0000001768.000
23456.78 --> 0000023456.780
123679.876 --> 0000123679.876



what is the best way to do this?

View 7 Replies View Related

Generate Fixed Output

Mar 21, 2008

I have table like below

ID AMT
1001 1234.560
1001 34.560
1001 134.000
1002 45.000
1002 3456.000
1003 5678.999


I need to create a fixed length data file..For example, ID char(6) and amt num(10.3) and sum(23.3)

It should be group and order by ID : 01 Represent ID line and 02 represent amt ( there can be multiple amt records) and 03 represent sum of amt.
01 + ID
02 + AMT
03 + Sum(AMT)


The output should look like this.. How do this either using a sql or cursor?

011001
021234.560
0234.560
02134.00
031403.12
011002
0245.000
023456.000
033501.000
011003
025678.999
035678.999

View 4 Replies View Related

Select Fixed Number Of Rows

Jun 14, 2004

A table has a column of int type. I need to select a fixed number of rows for each value. For example, if data in that column (c) are 5, 6, 7, and the number I want to select is 2, then I need 2 rows from c=5, 2 from c=6, and 2 from c=7. How to write that query? Any idea?

Thanks.

View 6 Replies View Related

DTS & Fixed Length Text Files

Apr 13, 2001

I created a package to import records from a fixed length 700 byte text file to a table in a SQL Database. I used the wizard to set it up and note the byte where each column ends. I need to customize the process, as the name of the text file will change each night, so I want to be able to set the file name from the VB front end app.

I have tried modifying the Datasource property of the DTS connection to the flat file, without success. I have also tried setting a global variable for the datasource property in DTS, and assigning that from VB, similarly without luck.

Do I have to create a custom package in code from VB? If so, how do indicate where each column in the text file ends? If I can customize the existing package, is there a specific reference that I need to set in my project that will let me control the value of the global?

TIA

View 2 Replies View Related

DTS And Fixed Field File As Destination

Jun 11, 2001

Hi,

I try to use Data Transaction Service (SQL Server 7) to copy information from an Sql Server Table to an text file (fixed field). When I run the process, no problem, the text file is created and a Ok message appears. But when I look in my text file, every time a field was NULL or empty in the source table, the following fields are not aligned !

Somebody knows what the problem ?

Thanks

Vincent

Ps: I'm french so excuse me for my english

View 1 Replies View Related

Fixed Decimal Places Without Rounding

Mar 1, 2001

What is the best way to force a 2 digit decimal place without rounding?
For example select price*UOM returns
47.1294
3.255
.5
8
.49

What i want to be returned is
47.12
3.25
8.00
.50
.49

Thanks,

Jim

View 3 Replies View Related

Exporting To A Flat Fixed File

Oct 6, 2004

I have a request from a vendor to export data out of my SQL Server 2K database view to a 'flat fixed file'.

What kind of file is this exactly, not a .csv ? Does EM have the capabilities through the DTS wizard, by choosing the output to a text file and fixed width ?

Thank you

View 6 Replies View Related

Fixed Length File Output

Feb 5, 2008

I need to write data into a fixed column length file and was wondering the best (most efficient) way to tackle this. For example, the first few pieces of the report I'm working on now would be:

PacketID - Starting position 1, Field length 9
TransactionID - Starting position 10, Field length 9
Group number - Starting position 19, field length 10
PID/SSN - Starting position 29, field length 10

For the PID/SSN, if I have a PID it'll be 10 digits and fill the field length, if I don't I use SSN which is only 9 digits and enter a space as the 10th digit. Obviously if I don't have certain pieces of information I'll just need spaces of the specified length to satisfy the file format. I'm using SQL 2005. Thanks in advance for any help provided.

View 4 Replies View Related

Fixed Length Text File

Sep 23, 2014

I have approximately 13 columns. Each Column has a start position and end position.. I created this in a table and defined the position, it's still not working for me.

FiceCode char(6), -- starting position 1, field length 6
StateStudID char(10), -- starting position 7, field length 10
CampusStudID char(10), -- starting position 17, field length 10
LastName char(25), -- starting position 27, field length 25

[Code] .....

I need a text output file that will define each start position.I also used: right(replicate('0',25) + cast(last_name as char(25)), 25) in my sql statement.when I add the first_name, I can't get it to start in position 52.

View 1 Replies View Related

Insert Fixed Values And From Another Table?

May 1, 2014

I have a table (tblCustomer) with three fields (customer_id, s_id, s_string)

I want to fill in this table with two fixed values ??and customer_id from another table.

Every customer that starts at P in tblMainCustomer.customer_nr should be entered in table tblCustomer.

Select customer_id from tblMainCustomer where customer_nr like 'P%'

Customer_id taken from tblMainCustomer and s_id, s_string these fixed values ??that are equal for each customer.

These fixed values ??are:

100-----Gold
101-----Steel
1002----Super Copper

Example: If I have a client who has has a customer_id 45 so it will be like this in tblCustomer:

customer_id----s_id----s_string
-------------------------------
45-------------100-----Gold
45-------------101-----Steel
45-------------102-----Super Copper

I am stuck, how do I do this the best way?

View 6 Replies View Related

Index Corruption Not Getting Fixed By DBREINDEX

Jan 30, 2006

Rob writes "We have had an on-going problem in our production database. It appears to all be index corruption. We've opened a PSS with Microsoft, but I figured I'd post here also. We're getting 604,605 errors as well as DBCC CHECKDB errors. All reports are that either REPAIR_REBUILD or REPAIR_FAST will fix the problem. Since putting the database into single_user mode is not an option at this time, we've just been rebuilding the problem indexes with DBCC DBREINDEX. This typically fixes the problem. Microsoft PSS says they believe that it's hardware problems. We're in the process of updating firmware on our storage device. (MSA 1000) This is a clustered machine. Tonight, our index rebuilds completed successfully, but a DBCC CHECKTABLE failed with 8951 and 8955 errors with the REPAIR_FAST option being supplied. I've rebuilt all the indexes on the table, but the DBCC CHECKTABLE still reports with errors. As a test, I DTS'd a copy of this table to make sure that I would at least be able to run a select * on it. That worked okay. My question is the following: Does the repair option for DBCC CHECKDB/TABLE do something that DBCC DBREINDEX doesn't? Thanks in advance! Rob P.S. This is a Siebel environment. :("

View 3 Replies View Related

Fixed A Few Things Still Having A GROUP Error

Feb 16, 2007

Hello,

Here is the code as it now stands:


select TECHN, RESCODE,
(CASE
WHEN TECHN IS NOT NULL AND RESCODE IS NOT NULL AND COMPLET IS NULL THEN 'TOTAL RESCODE'
when TECHN IS NOT NULL AND RESCODE IS NULL THEN 'TOTAL TECH'
ELSE CONVERT(VARCHAR, CAST(COMPLETE AS DATETIME), 105) END) COMPLETE, DESCRIPTION, RES_NAME, ADDRESS, JOB, TYP, NUM, OPR, sum(QTYW) QTY, sum(NCommissionAMT) CommissionAUT
FROM
(select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 1, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser.dbo.Parser_ocr
Inner Join Parser.dbo.Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN Parser.dbo.RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 1, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 1, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 4, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 4, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 4, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 7, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT (DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 7, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 7, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 10, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 10, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 10, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 13, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 13, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 13, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 16, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 16, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 16, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT)
group by rollup(TECHN, RESCODE,(COMPLET, DESCRIPTION, RES_NAME, ADDRESS, JOB, TYP, NUMB, OPR))
order by 1, 2,3, 5;


Msg 156, Level 15, State 1, Line 150
Incorrect syntax near the keyword 'group'.

Could the error message be in the INNER JOIN?

What do I need to check for next.

Thanks for eveyrone's help so far.



Kurt

View 20 Replies View Related

Fixed Length Text Field

Dec 4, 2007

How do I create a text file with fixed length fields from a SQL table using a stored procedure.

View 4 Replies View Related

Fixed Column Output- Looping

Mar 20, 2008

I have table like below

ID AMT
1001 1234.560
1001 34.560
1001 134.000
1002 45.000
1002 3456.000
1003 5678.999


I need to create a fixed length data file..For example, ID char(6) and amt num(10.3) and sum(23.3)

It should be group and order by ID : 01 Represent ID line and 02 represent amt ( there can be multiple amt records) and 03 represent sum of amt.
01 + ID
02 + AMT
03 + Sum(AMT)


The output should look like this.. How do this either using a t-sql or SSIS?

011001
021234.560
0234.560
02134.00
031403.12
011002
0245.000
023456.000
033501.000
011003
025678.999
035678.999

View 6 Replies View Related

SSIS Fixed Width File

May 2, 2006

I am attempting to import a fixed width file into a SS2005 table and am having problems when importing a date that has no value in it. The table will allow nulls.

The date is in dd/mm/yyyy foramt and when there is no date then there are 10 spaces. When transforming the data I TRIM the data down using a derive transform script so all there is, is an empty string. When the file attempts to load I get the following message:

[OLE DB Destination [2238]] Error: There was an error with input column "paid_date" (2306) on input "OLE DB Destination Input" (2251). The column status returned was: "The value could not be converted because of a potential loss of data.".

How can it potentially lose data when there is nothing to lose?

I need some way of converting the empty string into a null. Has anyone got any ideas for me?

View 1 Replies View Related

BCP Fixed Width File Import

Oct 19, 2007

I have a fixed width file that I only need a middle field imported.

The field I need starts at character 116 and is 15 character wide. What should my format file look like?



I have a format file that can import a field before it but this is data I don't need. How do you skip the first field?

View 3 Replies View Related







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