How To Output Data To Different Column
Mar 12, 2013
l am fresh in SQL...
select hierarchy.hiername,devicefail.deviceid
,sum(DATEDIFF(minute,started,ended)) as duration
,100 - SUM(datediff(minute,started,ended))/(672 * 60.0000)*100 AS Uptime from devicefail
LEFT JOIN device ON device.deviceid = devicefail.deviceid
LEFT JOIN hierarchy ON device.hierlevel = hierarchy.hierlevel
where devicefail.started >= '2013-02-01 00:00:00'and
devicefail.ended <='2013-02-28 23:59:59'
and devicefail.componentid like 201 or devicefail.componentid like 0
group by devicefail.deviceid,hierarchy.hiername,devicefail. componentid
order by hiername
the above output as:
hiernamedeviceidduration Uptime
Airp 95412092548.10267857
Airp 95411891953.07787699
Airp 9542187295.35714286
Airp 9542155296.15079365
How could I achieve the result as following:
hiernamedeviceidduration1 duration2 Uptime1 Uptime2
Airp 954120925 18919 48.10267857 53.07787699
Airp 954218721552 95.35714286 96.15079365
View 1 Replies
ADVERTISEMENT
Dec 1, 2011
I want to display the following output where integer value is column data.
{[Advertiser].[AdvertiserKey].&[4000],
[Advertiser].[AdvertiserKey].&[4001],
[Advertiser].[AdvertiserKey].&[4002],
[Advertiser].[AdvertiserKey].&[4003],
[Advertiser].[AdvertiserKey].&[3661],
[Advertiser].[AdvertiserKey].&[3662]
}
View 4 Replies
View Related
Sep 21, 2006
Here is what I get. Is this an install problem or is this how this software works??
===================================
Error at Data Flow Task [Data Conversion [720]]: An output cannot be added to the outputs collection.
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC020800F (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.InsertOutput(DTSInsertPlacement eInsertPlacement, Int32 lOutputID)
at Microsoft.DataTransformationServices.Design.Controls.ComponentMetaDataTreeView.AddOutput()
View 9 Replies
View Related
Apr 27, 2008
Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:
rowtype| ID | value
A | 123 | alpha
B | 123 | beta
C | 123 | delta
A | 124 | some val
B | 124 | some val 2
C | 124 | some val 3
etc...
where for each ID, I have 3 rows that are associated with it and with different corresponding values.
I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.
i.e. each ID iteration will do something like:
insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha')
insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta')
insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta')
etc..
After my loop, I will just do a select * from #someTempTable
Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005
View 2 Replies
View Related
Dec 10, 2007
Hi Friend,
I am stuck with a problem and need your help. As we know, all columns that go to error flow of flat file source connection are displayed as a single column e.g. FlatFileSourceErrorOutputColumn, but my requirement is to extract the first column value from this FlatFileSourceErrorOutputColumn, my data is dilimeted by "|" pipe operator. I have created a script component to deal with this. However if we take FlatFileSourceErrorOutputColumn column as input column in script component, it comes as BLOB data. I wrote below code in transformation script component to extract BLOB data from column in string form and then do a Left function search to take first column out.
When I am running this script component I am getting '??????????' question marks as a result in Row.Pname.
Can anyone please help me understand if I am doing anything wrong in this script or suggest a better way to take the data out?
I appreciate your help.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
'
Dim Error_Data As String
Dim Column_1 As String
Dim Len As Integer
Dim Buffer As Byte()
Buffer = Row.FlatFileSourceErrorOutputColumn.GetBlobData(0, CInt(Row.FlatFileSourceErrorOutputColumn.Length))
Error_Data = System.Text.Encoding.Unicode.GetString(Buffer)
Len = Error_Data.IndexOf("|")
Column_1 = Left(Error_Data, Len - 1)
Row.Pname = Column_1
End Sub
Thanks,
Kul
View 5 Replies
View Related
Dec 8, 2006
I am working on an OLAP modeled database.
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
View 9 Replies
View Related
Mar 8, 2008
I have a data flow that consists of
OLE DB source which calls a stored proc that returns a result set
data conversion
Excel destination
I am in design mode in Business Intelligence studio. My excel destination (with an Excel Connection) shows no sheet name though I have an execute SQL task before the data flow to create the excel table called SHEET1. Needless to say, there are no output columns visible to do any mappings. I did go to the ExcelConnection to set the OpenRowset Property to SHEET1 but it seems to have no effect.
I can do the export in SQL Server Management studio and that works fine, but it is basic and does not meet my requirements. I have to customize the package to allow dynamic Excel filenames based on account names and have to split my result set into multiple excel sheets because excel 2003 has a max of 65536 rows per sheet. Also when I use the export wizard, I have the source as a table and eventually the source has to be a stored proc with input parms.
What am I missing or doing wrong? Thanks in advance
View 6 Replies
View Related
Sep 27, 2005
I have two queries I would like to combine the output on. they are as follows:
Select substring(WrkSta.[Name],1,2) 'Location'
,count (aexe.ReturnCode) as '# Patched'
from WrkSta, AeXEvt_AeX_SWD_Execution aexe
where WrkSta.WrkStaId=aexe.WrkStaId
and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%'
or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%'
or WrkSta.[Name] like 'ET%')
and (aexe.returncode='0' or
aexe.returncode ='3010')
and aexe.AdvertisementName like 'MS05-035-043%'
group by substring (WrkSta.[Name], 1,2)
---
Returns :
Location # Patched
EP 102
ES 1986
ET 19
AB 174
SU 6
EM 506
and the second one:
Select substring(WrkSta.[Name],1,2) 'Location'
,count (coll.WrkStaId ) as '# Workstation'
from WrkSta join AeXNSCollectionMembership coll on WrkSta.WrkStaId=Coll.WrkStaId
where coll.CollectionGuid = '38F5DAFC-E09D-49A5-A0FD-370983CA7596'
and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%'
or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%'
or WrkSta.[Name] like 'ET%')
group by substring (WrkSta.[Name], 1,2)
---
returns:
Location # Workstations
EP 178
ES 2299
ET 24
AB 215
SU 13
EM 582
What I need is :
Location # Workstations # Patched
EP 178 102
EI 2299 1986
ET 24 19
AB 215 174
SU 13 6
EM 582 582
No mater how I try to do a join to do this in a single query I end up with what looks like a cross-join and # workstations and # Patched jump to huge numbers. I obviously am having a problem understanding how to set up the select statements so that I can do this in one query or am I following the wrong direction and should be trying something else?
Once again I appreciate your help in advance....
View 1 Replies
View Related
Jun 29, 2007
Hello,I was wondering if anyone can help me figure something out.Is it possible to do a querey in MS SQL server and have the resultsreturned so that each result in each row is preceeded by the columnname?eg. instead of usual output -colName1, colValue1,colName2,colValue2,colName3,colValue3 ?Also I would like to only have this for certain columns ie in theabove example only for columns 2 and 3Thank you! :-)Yas
View 4 Replies
View Related
Apr 23, 2007
I would like to get the actual name of the column that has the error. Using the ErrorColumn (int value) I thought there would be some type of lookup collection based on the input (like column names)- if there is, can someone tell me how to get to it?
I have my error output writing to a stored proc, but instead of "32226" as the column name, I need to have the actual name of the column. I am going from Flat File to OLE DB Destination. I have a Script Component getting the output to write to my sproc, and I just need to get the column name.
Suggestions?? Thanks
View 19 Replies
View Related
May 3, 2006
Hi,
I am dumping varchar(40) column from a OLE DB Source to Excel file but it is failing due this error
[Excel Destination [239]] Error: Column "ItemDescription" cannot convert between unicode and non-unicode string data types.
How can I resolve this issue?
Thanks
View 8 Replies
View Related
Mar 12, 2007
I have a Data Flow Task that extracts some data using a DataReader Source and loads it to a Raw File Destination. I am getting the following error message:
[DataReader Source [2357]] Error: The value was too large to fit in the output column "LASTCOL" (2558).
I thought that using a Raw File Destination would avoid this type of problem. How can I resolve this issue?
View 5 Replies
View Related
Aug 17, 2007
using sql database, i have a smallmoney column. when i enter an amount, 50.00 for example, i have 50.0000 displayed. is there a way to only have 50.00 displayed?
same with the smalldatetime, is there a way to limit the display to "mm/dd/yyyy" without the "hh:mms am"
View 2 Replies
View Related
Dec 22, 2014
I would like to change the output of a column into a hyperlink however I am not sure that is possible without some further post-processing. I am aware I would have to do:
Code:
select
'<a href="' + URL + '" target="_blank" nav="web">' + tbl_ID.ID + '</a>'
from tbl_ID
however this obviously just exports the text
<a href="[URL]" target="_blank" nav="web">[ID]</a>
This would work fine if I was exporting it into some sort of HTML table however this doesn't work with excel for example. Is there a way to make this possible or would I have to create a macro in excel to add the url with the ID separately?
View 3 Replies
View Related
May 5, 2008
Hi,
I'm importing data from and oracle database to an SQL one through a SSIS package, I'm getting this error:
"The output column "earned_hours" has a precision that is not valid. The precision must be between 1 and 38".
the package runs but returns this column as NULL values
earned_hours is of type "NUMBER" in oracle (some of the values are decimals), I tried making it numeric(x,y),float or decimal(x,y), but I'm still getting the same results.
does anybody know why is this happening or have a solution for this error?
Thanks
View 5 Replies
View Related
Dec 10, 2013
I use bcp command to output to excel, it works. But I want to format the excel, some column width are too small,user need adjust the column width, otherwise it shows ######.
How can I set columns width when I use bcp output to excel.
Also, can bcp command output to multiple excel sheets and add report title in each excel sheet?
View 2 Replies
View Related
Sep 14, 2006
Is there a way to supress output on one column in a SP, using data from the same row?
Like This:
SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP)AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility, Active
FROM dbo.tblResidents
But which returns null for some of the columns if DOT is not null?
DOT is the Termination Date, so the only columns that have any meaning once there is data in the DOT column are DisReason and DisSummDue. Also, if DOT *is* null, then the above columns also have no meaning.
I tried several variations of the following, but I can't figure it out
CREATE PROCEDURE [dbo].[spTesting] AS
BEGIN
SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP) AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, Facility
FROM dbo.tblResidents A
WHERE DOT IS NULL
UNION
SELECT Last, First, DOP, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility
FROM dbo.tblResidents I
END
GO
----------------
-Stephen
View 4 Replies
View Related
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
Jul 3, 2006
Hi,
I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,
Select SerialNumber From SerialNumbers Where OrderID = @OrderID
If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.
Please help me.
Thanks in advance.
View 13 Replies
View Related
Jun 20, 2007
When configuring error output, I want everything that is good in the row to make it to the destination, and then the offending column that is causing an error to be set to NULL, and then sent to the destination as well. In addition, I want to take the offending column's data, and route it over to an error holding table. I know about the ability to redirect the whole row, but I just sort of want to redirect just that column. For example....
Have a table with 5 columns
col1 int null,
col2 int null,
col3 char(3) null,
col4 bit null,
col5 int null
My data flow loads data from a flat file and has a record that looks like this
1 5 ABC R 3
I want the row to make it to the destination as follows....
1 5 ABC NULL 3
Then the offending data needs to go over to my error table
err# errcolumn errdata errdesc
1 col4 R Could not convert "R" to bit data type
Any way to do this?
View 5 Replies
View Related
Dec 7, 2006
I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311
I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.
I am new to SSIS.
thanks! -ZZ
View 8 Replies
View Related
Apr 2, 2007
Hi,
Iam redirecting the error output of a OLEDB destination component to a script component. My aim is to create a HTML report having the information about the bad records, the error occuring in the rows and the column name that fails. The error output provided two new columns i.e the errorcode and errorcolumn , the errorcolumn value for a bad record gives the linage id for the column, is there a way to derieve the name of the column by using the lineage id?
Regard,
pritesh
View 5 Replies
View Related
Jan 19, 2007
Hello,
I have an ODBC connection manager to a Progress database. In that database there is a column declared as a string of 10 characters long.
However, some data in this column is actually up to 15 characters long.
This makes my DataReader Source fail everytime I try to run my package because it sets the output column like this :
Datatype : Unicode string [DT_WSTR]
Length : 10
Is there any way to solve this without changing the datatype in the Progress database (that is beyond my control) ?
tanks in advance ...
View 13 Replies
View Related
Feb 12, 2002
have
select isntuser from syslogins
output
isntuser
--------
1
want
select isntuser as [Joe]+char(13) +[Blowwwwwwww] from syslogins
Joe
Blowwwwwww
--------
1
any idea ?
View 1 Replies
View Related
Mar 8, 2004
Hello Everyone,
Hope someone will be able to help me out here.
I have a text file exported from my DTS package and it requires an '!' as a custom column delimiter.
Does anyone have any idea how I can use the '!' mark instead of the Tab or Vertical Bar as my Column Delimiter?
Would appreciate any suggestions.
Thanks,
Kay
View 3 Replies
View Related
Mar 31, 2008
Currently running Sql Server 2005Is it possible to issue the delete command and capture the affected rows asxml types that will be stored in an audit table with an xml column?Something along the lines of:delete from source_tableoutput(deleted.*into audit_table (xml_audit_column)for xml auto)where source_table.column = @delete_value
View 1 Replies
View Related
May 16, 2007
I have a very weird problem.
I ran a SQL statement to get the data from two tables using join. These two tables have a column in the same name €śID€?.
SQL=€?select * from table1 a, table2 b where a.ID=b.ID€?
Set RS=DB.Execute(SQL)
The statement worked. But when I output the records in my ASP pages, I got an error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal€?
This is the line caused error:
<%= RS(€śa.ID€?) %>
It seemed that SQL Server recordset doesn€™t carry the table aliens. If I removed a. from ID the error was gone but it gave me the value of b.ID.
How to solve the problem? Thank you.
View 4 Replies
View Related
Sep 11, 2007
I have a derive column( sequence) transformation in data flow , i am trying to assign this column to a variable , so that i can use it in the SQLtask control flow... how can i do this? can you show me some examples?
View 10 Replies
View Related
Jun 8, 2006
Hi,
I have a problem using the odbc datasource reader to execute a sql command on a progress database. My query is something like:-
select max(id), sum(amount) from my_table
OR
select a, b, c, recid(my_table) from my_table
which produces external columns and output columns with no name. The progress sql doesn't support using aliases on column names and setting validateexternalmetadata to false and manually naming the input and output parameters in the 'Advanced Editor' doesn't seem to work either. I either get the error 'The name for output column "" is blank and columns can not be blank' or if I add my own column names in the input and output parameters it fails in the pre-execute phase saying it can't find a column in the datasource with name 'myalias'
I can get around the aggregate functions by transfering all the data and doing the aggregate on the local server but I also need to call functions such as recid() which I can't work around. SQL2000 DTS ignored these things and matched as best it could where SQL 2005 IS seems overly strict.
Has anyone encountered similar problems and does anyone have any ideas? I'm currently at a loss :(
View 3 Replies
View Related
Apr 12, 2006
Hi All,
I'm trying to transfer data from DB2 Database to SQL Server 2005.
Well, i used the OLE DB Source, the Data Conversion Component and the OLE DB Destination component.
I have five Data flows with this configuration above. But I am receiving an error message from one of them.
Please check below the error message:
"[Source Table TARTRATE [1]] Error: The value was too large to fit in the output column "ADJ_RATE_PCT" (60). "
"[Source Table TARTRATE [1]] Error: The "component "Source Table TARTRATE" (1)" failed because error code 0xC02090F8 occurred, and the error row disposition on "output column "ADJ_RATE_PCT" (60)" specifies failure on error. An error occurred on the specified object of the specified component."
Could you please help me with this issue?
Thanks in advance.
Thiago
View 7 Replies
View Related
Apr 10, 2008
Hello All,
I am migrating data from one database to another. I am using Multicast to seperate (legal street,legal mail and legal city) and (mail_street,mail_state,mail_zip,mail_city) also later after UNION of the above I am doing two lookups as I had to get contact ID and Customer ID from other two tables. In UNION i am matching (Mail street legal street) and so on.
I am getting double the data in the output. my input data is 1000000 and im gettin 2000000.
What could be the reasons. Please help me out.
Thank You
View 5 Replies
View Related
Jun 5, 2001
I'm using isqlw to generate delimited text files from scripts. There are several SET options available so that you can restrict the output to just the data in the script, for example SET NOCOUNT ON.
I can't find a similar SET option to suppress the column headings, although there is a tickbox that allows you to do this in Query Analyzer (Query -> Current Connection Options -> Advanced -> Print Headers)
DTS packages appear to support this, yet I can't find the setting from a dts file.
Thanks,
Paul
View 1 Replies
View Related
Feb 22, 2005
I'm executing the following...
select COL1, min(COL2) from TABLE group by COL1
the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed.
I was hoping a simple
"delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)"
would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect?
this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so.
tia
a
View 2 Replies
View Related
