I am trying to eliminate the extra space in columns that have been returned from a DB query...the tables have character lengths of 40, I would like to return just the necessary characters.
Thanks in advance.
Mark.
Did you try rtrim(ltrim(columnname) in the query?
cheers,
Andrew
|||Thanks Andrew,
rtrim(columnname) did it.
Cheers,
Mark.
Hi
I have a column in the db with serial number data, on a export that I am doing the data has to be 10 digits long the problem is not all of them are eg
12234
122334343
1234234567
how can i get it to look like this adding an 0 to the front to make the row 10 digits
0000012234
0122334343
1234234567
Thanks
SELECT REPLICATE('0',10 - LEN(CONVERT(VARCHAR(10),Column1))) + CONVERT(VARCHAR(10),Column1)
HTH,
Babu
|||
select right('000000000' + convert(varchar(10),ColumnName),10)
example
declare @.i int
select @.i = 12345
select right('000000000' + convert(varchar(10),@.i),10)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Alternatively perform the formatting in your front end application|||If the number is to be stored as a number, then this is the best advice. I wouldn't suggest it if this is a value that will be used many different places and never be used in a math equation. For that I would store it in a character string and prepend the zeros.
The real problem comes in all of the different places it is used (reports, data warehouse,etc.) Someone has to format it, and you don't want the user to have to use some UI function to format it. You might do the formatting on the way to the DW and to a reporting data store, but to me it begs the question of the nature of the data. If the nature of the data is a code that happens to be all numbers (but would perform just as well in the application if it was not all numbers, ie 'asd02020' would not change the application as opposed to '000022020', then store it as a character, format it when you save it, and get it over with :)
How can i make a sum (concatenation) of strings of one column in a table.
for example i have a table like this
field1 field2
1 abc
1 bcd
2 sdf
2 sdd
I want to get these strings added group by field 1
Thanks
This cannot be done in a single query unless you employ some tricky hack. There is no obvious and reliable solution to do this in a single query. You'll have to use a cursor and a table variable for this purpose.
|||Hi,
with .Net 2.0 it is very easy. You can write "User defined aggregate" called for example StringConcat and use it in your queries like this
SELECT dbo.StringConcat(field2)FROM MyTableGROUP BY field1
and here is the code for your aggregate
C#
using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Text;using System.IO;using System.Runtime.InteropServices;[Serializable][SqlUserDefinedAggregate( Format.UserDefined,// user-defined serialization IsInvariantToDuplicates =false,// duplicates matter IsInvariantToNulls =true,// don't care about NULLs IsInvariantToOrder =false,// order matters (ignored) IsNullIfEmpty =false,// don't yield NULL if empty set MaxByteSize = 8000)]// maximum size in bytespublic struct StringConcat : IBinarySerialize{private StringBuilder sb;public void Init() {this.sb =new StringBuilder(); }public void Accumulate(SqlString s) {if (s.IsNull) {return;// skip NULLs }else {this.sb.Append(s.Value); } }public void Merge(StringConcat Group) {this.sb.Append(Group.sb); }public SqlString Terminate() {return new SqlString(this.sb.ToString()); }public void Read(BinaryReader r) { sb =new StringBuilder(r.ReadString()); }public void Write(BinaryWriter w) {if (this.sb.Length > 4000)// limit sb to 8000 bytes w.Write(this.sb.ToString().Substring(0, 4000));else w.Write(this.sb.ToString()); }}// end StringConcatVB
Imports SystemImports System.DataImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerImports System.TextImports System.IOImports System.Runtime.InteropServices' user-defined serialization' duplicates matter' don't care about NULLs' order matters (ignored) ' don't yield NULL if empty set' maximum size in bytes is 8000<Serializable(), _ SqlUserDefinedAggregate( _ Format.UserDefined, _ IsInvariantToDuplicates:=False, _ IsInvariantToNulls:=True, _ IsInvariantToOrder:=False, _ IsNullIfEmpty:=False, _ MaxByteSize:=8000)> _Public Structure StringConcatImplements IBinarySerializePrivate sbAs StringBuilderPublic Sub Init()Me.sb =New StringBuilder()End Sub Public Sub Accumulate(ByVal sAs SqlString)If s.IsNullThen Return' skip NULLsElse Me.sb.Append(s.Value)End If End Sub Public Sub Merge(ByVal GroupAs StringConcat)Me.sb.Append(Group.sb)End Sub Public Function Terminate()As SqlStringReturn New SqlString(sb.ToString())End Function Public Sub Read(ByVal rAs BinaryReader) _Implements IBinarySerialize.Read sb =New StringBuilder(r.ReadString())End Sub Public Sub Write(ByVal wAs BinaryWriter) _Implements IBinarySerialize.WriteIf Me.sb.Length > 4000Then' limit sb to 8000 bytes w.Write(Me.sb.ToString().Substring(0, 4000))Else w.Write(Me.sb.ToString())End If End SubEnd Structure' end StringConcat
I hope this helps.
Let me know if this worked for you.
|||thanks, But I am looking for something within the sql
|||Hi,
After compiling the class into a DLL, you can import the DLL as a SQL Server assembly using either the Visual Studio 2005 Deploy option or manually using the CREATE ASSEMBLY statement and CREATE AGGREGATE statement as is shown in the following listing:
CREATE ASSEMBLY StringConcatFROM'C:\StringConcat.dll'GOCREATE AGGREGATE StringConcat(@.inputnvarchar)RETURNSnvarcharEXTERNALNAME StringConcat.StringConcatGO
Using CLR objects (functions, aggregates...) for string manipulations or complex calculations is way faster than using nested queries or Cursors.
I have used this aggregate in my projects and it works fine.
|||Hi, jiju-kj-
Of course it is possible to achieve this within the SQL. But you will need nested cursors the first cursor will iterate through distinct values in field1 and the inner cursor will iterate through all values from field2 for the current value in field1. I don't recommend you using cursors. But will decide which way is better for you. Let me know if you have problems with creating the cursors.
Cheers,
Paul
sql
I've got a quandry - I have a detailed database that handles advert
orders between a design agency and printers / magazines etc.
I want to add up the total spent by the client and put the results to a
field.
I've actually done that using a query table in access - it should be
quite simple as i can bind the 'total amount' to my table - the only
thing it does not currently do is filter the total based on the month
selected.
For example if you look at
http://www.daneverton.com/dg2data/months/2006-12.asp
The data here is filtered by the issue equaling Dec-2006
The actual order total is 13,622 but the column is showing the total
for all entries to date (a years worth = 422,048)
I'm sure that there is only a basic tweak required but i'm banging my
head over what to do
The sql is "SELECT * FROM monnodraught, q_monodraught_total WHERE
[Issue / Edition] LIKE ? ORDER BY Publication ASC"
Any help gladly received.Hi Dan,
What is the SQL behind: q_monodraught_total ?
Quote:
The sql is "SELECT * FROM monnodraught, q_monodraught_total WHERE
[Issue / Edition] LIKE ? ORDER BY Publication ASC"
I'm thinking you might benefit from a SELECT SUM... query
SELECT SUM(fieldname) FROM table WHERE condition ORDER BY fieldname; SELECT SUM(Age) FROM Persons WHERE Age>20good luck ;o)
Cheers,
Douglas
------------------------
"Dan" <mail@.daneverton.comwrote in message news:1164792444.626863.242620@.h54g2000cwb.googlegr oups.com...
Hi
I've got a quandry - I have a detailed database that handles advert
orders between a design agency and printers / magazines etc.
I want to add up the total spent by the client and put the results to a
field.
I've actually done that using a query table in access - it should be
quite simple as i can bind the 'total amount' to my table - the only
thing it does not currently do is filter the total based on the month
selected.
For example if you look at
http://www.daneverton.com/dg2data/months/2006-12.asp
The data here is filtered by the issue equaling Dec-2006
The actual order total is 13,622 but the column is showing the total
for all entries to date (a years worth = 422,048)
I'm sure that there is only a basic tweak required but i'm banging my
head over what to do
The sql is "SELECT * FROM monnodraught, q_monodraught_total WHERE
[Issue / Edition] LIKE ? ORDER BY Publication ASC"
Any help gladly received.
Hi,
I'm creating a martix report that must have overall averages at the end of each row and column. I've added a list with a textbox to cater for the row totals but this doesn't work for the columns (unless there's some way of displaying the list horizontally...?) Adding a new item within the matrix doesn't work as it won't accept aggregate functions. There must be a way of doing this quite easily but I can't figure it out.
Any ideas or suggestions would be much appreciated.
Thanks,
Aidan
Turns out the solution is very simple - all you have to do is right click on the group header cell and select 'subtotal' from the pop-up menu. You can change the properties of the subtotal cell by clicking on the green triangle in the corner of the cell when viewing the properties window. It made more sense when I realised that the pivot cell must have an aggregate value - I was aggregating in my stored procedure so it took a while to figure it out...
Hope this helps someone at some point.
Aidan
|||Thanks for pointing out the "SubTotal" setting for the column totals. However my question is regarding the Row Totals.
My report format is roughly like so
Category1 Category2 Category3 ... CTotal
Day1 10 20 30 60
Day2 11 22 33 66
Day3
...
RTotal 21 42 63 X
Basically I'm querying for rows by Day and have a GROUP BY for Category for certain type of records and am displaying the COUNT() on each day (e.g. 10, 20, 30)
So theCTotal is easily accomplished using the "SubTotal" setting. The problem is I cannot get RTotal to work. I at a point where I'm thinking of doing theRTotal in my query itself using a temp table for the original data, then doing a SUM of all Category COUNTS vertically and then doing a UNION to give me the bottom row. Needless to say, this would so lame of me I will not able to show my face to anyone.
So if you can set me straight on how you managed to do the Row Totals using a List Region, I would sure appreciate it. For the life of me, I cannot get it to work.
Best.
You should be able to use the same subtotal function by right-clicking the the cell with your (Day1, Day2, Day3) Date Value (usually the second cell from the top in the leftmost column).
I know that I can add a column to a mining structure and/or model more than once using XMLA. However, I am having a problem doing the same using DMX. I now that DMX does not support data binding and that it relies on the INSERT INTO... construct to essentially map the structure columns to the incoming comluns from the data source. I tried to use "AS" like I would in SQL but it doesn't seem to like it.
Can anyone confirm that this can only be done using XMLA and not DMX? If I am mistaken, it would be greatly appreciated if you could provide an example on how to do this using DMX.
For background on why I would want to add the same column to a model more than once please read this post.
Thanks.
It should be fairly straightforward, is this not working?
CREATE MINING STRUCTURE Foo
(
CustID LONG KEY,
Age LONG CONTINUOUS,
AgeDisc LONG DISCRETIZED)
INSERT INTO Foo(CustID, Age, AgeDisc)
OPENQUERY(MyDataSource,"SELECT CustID, Age, Age as Age2 FROM MyTable")
I know that I can add a column to a mining structure and/or model more than once using XMLA. However, I am having a problem doing the same using DMX. I now that DMX does not support data binding and that it relies on the INSERT INTO... construct to essentially map the structure columns to the incoming comluns from the data source. I tried to use "AS" like I would in SQL but it doesn't seem to like it.
Can anyone confirm that this can only be done using XMLA and not DMX? If I am mistaken, it would be greatly appreciated if you could provide an example on how to do this using DMX.
For background on why I would want to add the same column to a model more than once please read this post.
Thanks.
It should be fairly straightforward, is this not working?
CREATE MINING STRUCTURE Foo
(
CustID LONG KEY,
Age LONG CONTINUOUS,
AgeDisc LONG DISCRETIZED)
INSERT INTO Foo(CustID, Age, AgeDisc)
OPENQUERY(MyDataSource,"SELECT CustID, Age, Age as Age2 FROM MyTable")
so a table that looked like:
ID TITLE CLASS_ID
1 "Hello" 137
3 "Goodbye" 587
19 "Whatever" 1028
could return a result set that looked like:
ID TITLE CLASS_ID URL
1 "Hello" 137 "hardcodedfilename.aspx?id=1"
3 "Goodbye" 587 "hardcodedfilename.aspx?id=3"
19 "Whatever" 1028 "hardcodedfilename.aspx?id=19"
I have looked through my SQL book, and scanned the usual help files and google search options, but I haven't seen an example of this. Can it be done, and if so, how?
Thanks in advance for your help.
roger
There are a few approaches, two different ways are shown below.
Chris
DECLARE @.URLTemplate VARCHAR(100)
SET @.URLTemplate = '"hardcodedfilename.aspx?id=**"'
SELECT ID,
TITLE,
CLASS_ID,
REPLACE(@.URLTemplate, '**', CAST(ID AS VARCHAR(10))) AS URL
FROM ...
--or
DECLARE @.prefix VARCHAR(100)
SET @.prefix = '"hardcodedfilename.aspx?id='
SELECT ID,
TITLE,
CLASS_ID,
@.prefix + CAST(ID AS VARCHAR(10)) + '"' AS URL
FROM ...
|||Thank you for your quick response! SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END
now, it looks like:
ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN XML
DECLARE @.prefix VARCHAR(100)
SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + " " as "@.url",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END
But, I get an error:
Msg 156, Level 15, State 1, Procedure fn_WPMTREE, Line 18
Incorrect syntax near the keyword 'FOR'.
Any suggestions? And thanks again for your help.|||
I've made a couple of corrections, see the example below.
I wasn't sure whether you needed the trailing space in the following:
CAST(ID AS VARCHAR(10)) + ' ' as "@.url",
If not, then simply replace the above with the following:
CAST(ID AS VARCHAR(10)) as "@.url",
Chris
CREATE FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
DECLARE @.prefix VARCHAR(100)
SET @.prefix = 'rightframe.aspx?s='
RETURN (
SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + ' ' as "@.url",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE
)
END
|||That works beautifully!I can't thank you enough.
Now, all I have to do is get an INNER JOIN working in this, and I am in good shape!
Again, Thank you.|||
HI Chris,
I have a similar question. I have a query that I use for a letter that I create. I'm attaching the query below. I want to add the text "CRM" next to the output result for "AgentDesc". So if the output of "AgentDesc" is "Director"; then I want it to display as CRM Director. Am sure this is easy enough for you..:
Thanks
SELECT dbo.tblOffer.*, CRM_SQL_ADMIN.DtFormat(dbo.tblOffer.OfferDt, 'mm dd, yyyy') AS OfferDate, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.FName)
+ ' ' + CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.LName)) AS AnalName, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblAgent.FName)
+ ' ' + CRM_SQL_ADMIN.NZ(dbo.l_tblAgent.LName)) AS AgentName, dbo.l_tblAgent.AgentDesc AS AgentDesc,
CRM_SQL_ADMIN.vwOfferAgent_Names.DCAName AS DCAName, CRM_SQL_ADMIN.vwOfferAgent_Names.MCAName AS MCAName,
CRM_SQL_ADMIN.vwOfferAgent_Names.DIRName AS DIRName, CRM_SQL_ADMIN.vwOfferAgent_Names.RMName AS RMName,
CRM_SQL_ADMIN.vwOfferAgent_Names.SReps AS SReps,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'PayTerms' AND [OPTION] = [tblOffer].[PayTerms]) AS PayTerms_Desc, dbo.l_tblCust.CustName AS CustName,
dbo.l_tblCust.StAddress AS StAddress, dbo.l_tblCust.City AS City, dbo.l_tblCust.State AS State, dbo.l_tblCust.ZipCode AS ZipCode,
dbo.l_tblCust.GPO AS GPO, dbo.l_tblCust.IsTargetCust AS IsTargetCust,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'ProgBen' AND CONVERT(bit, [OPTION]) = [tblOffer].[ProgBen]) AS ProgBen_Desc,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'ProgCrit' AND CONVERT(bit, [OPTION]) = [tblOffer].[ProgCrit]) AS ProgCrit_Desc, dbo.tblOffer.SpecProgType AS SpecProg,
CRM_SQL_ADMIN.DtFormat(CRM_SQL_ADMIN.EndOfQtr(dbo.tblOffer.OfferDt), 'mm dd, yyyy') AS EndOfQtr,
CRM_SQL_ADMIN.DtFormat(CRM_SQL_ADMIN.WeekDayAdd(- 4, CRM_SQL_ADMIN.EndOfQtr(dbo.tblOffer.OfferDt)), 'mm dd, yyyy') AS EndOfQtrLess3d,
OSS.Tot_Qty AS Tot_Qty, CRM_SQL_ADMIN.GetUSDNo00(OSS.Tot_Purch) AS Tot_Purch, CRM_SQL_ADMIN.GetUSDNo00(OSS.Tot_Savings)
AS Tot_Savings
FROM dbo.l_tblCust INNER JOIN
dbo.tblOffer ON dbo.l_tblCust.CustNum = dbo.tblOffer.CustNum LEFT OUTER JOIN
dbo.l_tblBusAnalyst ON dbo.tblOffer.BusAnalystID = dbo.l_tblBusAnalyst.EmpNum LEFT OUTER JOIN
CRM_SQL_ADMIN.vwOfferAgent_Names ON dbo.tblOffer.OfferID = CRM_SQL_ADMIN.vwOfferAgent_Names.OfferID LEFT OUTER JOIN
dbo.l_tblAgent ON dbo.tblOffer.AgentID = dbo.l_tblAgent.AgentID INNER JOIN
CRM_SQL_ADMIN.vwOfferSums_Simple OSS ON dbo.tblOffer.OfferID = OSS.OfferID|||
Change:
dbo.l_tblAgent.AgentDesc AS AgentDesc,
to:
( 'CRM ' + dbo.l_tblAgent.AgentDesc ) AS AgentDesc,
|||Gr8...That works...Thanks a lot Arnie for your help...so a table that looked like:
ID TITLE CLASS_ID
1 "Hello" 137
3 "Goodbye" 587
19 "Whatever" 1028
could return a result set that looked like:
ID TITLE CLASS_ID URL
1 "Hello" 137 "hardcodedfilename.aspx?id=1"
3 "Goodbye" 587 "hardcodedfilename.aspx?id=3"
19 "Whatever" 1028 "hardcodedfilename.aspx?id=19"
I have looked through my SQL book, and scanned the usual help files and google search options, but I haven't seen an example of this. Can it be done, and if so, how?
Thanks in advance for your help.
roger
There are a few approaches, two different ways are shown below.
Chris
DECLARE @.URLTemplate VARCHAR(100)
SET @.URLTemplate = '"hardcodedfilename.aspx?id=**"'
SELECT ID,
TITLE,
CLASS_ID,
REPLACE(@.URLTemplate, '**', CAST(ID AS VARCHAR(10))) AS URL
FROM ...
--or
DECLARE @.prefix VARCHAR(100)
SET @.prefix = '"hardcodedfilename.aspx?id='
SELECT ID,
TITLE,
CLASS_ID,
@.prefix + CAST(ID AS VARCHAR(10)) + '"' AS URL
FROM ...
|||Thank you for your quick response! SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END
now, it looks like:
ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN XML
DECLARE @.prefix VARCHAR(100)
SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + " " as "@.url",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END
But, I get an error:
Msg 156, Level 15, State 1, Procedure fn_WPMTREE, Line 18
Incorrect syntax near the keyword 'FOR'.
Any suggestions? And thanks again for your help.
|||
I've made a couple of corrections, see the example below.
I wasn't sure whether you needed the trailing space in the following:
CAST(ID AS VARCHAR(10)) + ' ' as "@.url",
If not, then simply replace the above with the following:
CAST(ID AS VARCHAR(10)) as "@.url",
Chris
CREATE FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
DECLARE @.prefix VARCHAR(100)
SET @.prefix = 'rightframe.aspx?s='
RETURN (
SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + ' ' as "@.url",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE
)
END
|||That works beautifully!I can't thank you enough.
Now, all I have to do is get an INNER JOIN working in this, and I am in good shape!
Again, Thank you.
|||
HI Chris,
I have a similar question. I have a query that I use for a letter that I create. I'm attaching the query below. I want to add the text "CRM" next to the output result for "AgentDesc". So if the output of "AgentDesc" is "Director"; then I want it to display as CRM Director. Am sure this is easy enough for you..:
Thanks
SELECT dbo.tblOffer.*, CRM_SQL_ADMIN.DtFormat(dbo.tblOffer.OfferDt, 'mm dd, yyyy') AS OfferDate, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.FName)Change:
dbo.l_tblAgent.AgentDesc AS AgentDesc,
to:
( 'CRM ' + dbo.l_tblAgent.AgentDesc ) AS AgentDesc,
|||Gr8...That works...Thanks a lot Arnie for your help...sqlHello all, i have 2 sql tables, and they each contain a column with bigint values. What i want to do is add up the values from both table, and then display it as 1 number. Supposing i have the following
table name: DownloadTable
fileName |DownloadSize|
file1 | 45
file2 | 50
file3 | 20
--------------
second table
table name: VideoTable
fileName | VideoSize |
file1 | 40
file2 | 60
file3 | 20
------------
Now, i want this to output 120, wich is the sum of the sum of the values of table 1 and 2 I've already tried the following:
SELECT SUM(DownloadTable.DownloadSize) + SUM(VideoTable.VideoSize) FROM DownloadTable, VideoTable
However, when i ran this, it gave me a huge number, that was far from accurate from what it was suppose to output. Can anyone help me?
Thanks in advance
Regards,
What is the expected output?|||in my real applicaion, the expected value was 3280. However, i got 37624.|||Based on the values you provided how do you arrive at 3280? Is there any business-forumula?|||well thats the thing. I dont know what i need to do to arrive at the expected value. And when i try to retrieve the sum of the tables indiidually, it works, and i get the expected output. But when it comes time to add up the values of those 2 tables, i get into trouble.
And what is a business-formula?
Thanks for your replies
|||
hervens:
Hello all, i have 2 sql tables, and they each contain a column with bigint values. What i want to do is add up the values from both table, and then display it as 1 number. Supposing i have the following
table name: DownloadTable
fileName |DownloadSize|
file1 | 45
file2 | 50
file3 | 20
--------------
second table
table name: VideoTable
fileName | VideoSize |
file1 | 40
file2 | 60
file3 | 20
------------
Now, i want this to output 120, wich is the sum of the sum of the values of table 1 and 2 I've already tried the following:
SELECT SUM(DownloadTable.DownloadSize) + SUM(VideoTable.VideoSize) FROM DownloadTable, VideoTable
However, when i ran this, it gave me a huge number, that was far from accurate from what it was suppose to output. Can anyone help me?
Is that your exact SQL statement? You've not specified which columns to join on, so your result set contains a row for every possible combination of every column in each table.
Instead, try something like this:
DECLARE @.Total BIGINT SELECT @.Total = SUM(DownloadSize) FROM DownLoadTable
SELECT @.Total = @.Total + (SELECT SUM(VideoSize) FROM VideoTable)
Hello tmorton, thx for your reply. However, when i pressed "test query" button in the dialog box to try to run it, the following error message poped up in a message box.
"The variable name "@.Total" has already been declared. Variable names must be unique within a query batch or stored procedure"
I also tried modifying the variable name total, but got the same error.
Im really sorry about this, im not much of an sql programmer. Just read a tutorial or 2 about it. Oh, and i copied the code you provided exactly
|||Try This Out......select t = sum(DownloadSize) + (select sum(VideoSize) from VideoTable) from DownloadSize
I think this should work for u.|||Wow, thank a lot Dhaliwal. Everything is working perfectly right now.
I was wondering if it was possible to add an identity column to a flat file data source as it is being processed in a data flow. I need to know the record number of each row in the file. Can this be done with the derived column task or is it possible to return the value of row count on each row of the data?
Any help on this is greatly recieved.
Cheers,
Grant
Generating Surrogate Keys
(http://www.sqlis.com/default.aspx?37)
-Jamie
|||
You may have more than one option here; I would try with a script task in control flow that uses a variable to generate and adds it as a column to the pipeline.
Create a SSIS variable Int32. eg MyIdentityColSeed that is set to zero.
Then, in your data flow, after the flat file source create a script task with 1 output column (named for example MyIdentityCol) and use and script like this:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.MyIdentityCol = Variables.MyIdentityColSeed + Counter
Counter += 1
End Sub
Even when the code is very simple; I did not tested it, so take the time to debug it.
|||Rafael, Jamie,Thanks for both your posts on this matter. I'm looking into Rafael's suggestion at present and see that the only way to access variables in the data flow section is within the PostExecute phase. That problem with this is that it doesn't seem to update the variable as i move through the rows in the dataflow. Is this because of how SSIS has been developped. Can you not set a variable anywhere else within the script. I'll look at Jamie's suggestion once this one has been exhausted.
Thanks,
Grant|||
The Pre and Post execute limitation is just that. It also makes more sense as you only need to read and write the variables pre/post, which is faster than accessing them for every row. Use local variables in the actual main execution code.
You may want to take a quick look at the Row Number Transformation, it may do exactly what you want with seed and increment settings already- http://www.sqlis.com/default.aspx?93
|||Got Rafaels suggestion to work. Didn't need to use a variable in the end just a counter that was initialized on the preexecute event which was incremented for each row. Many thanks for everyone's help.Yet more new things learned today :) .
Thank you all.
Grant|||
Grant Swan wrote:
Rafael, Jamie, Thanks for both your posts on this matter. I'm looking into Rafael's suggestion at present and see that the only way to access variables in the data flow section is within the PostExecute phase. That problem with this is that it doesn't seem to update the variable as i move through the rows in the dataflow. Is this because of how SSIS has been developped. Can you not set a variable anywhere else within the script. I'll look at Jamie's suggestion once this one has been exhausted.
Thanks,
Grant
Errr...both Rafael's and my suggestions were exactly the same
Glad you got it working anyhow!
-Jamie
|||
Grant,
I think Jamie is right; the approaches are identical. I do not keep records of web resources I used; so I was unable, like Jamie, to post a link to the original sorce. I guess a owe some credits to author of the script
Rafael Salas
|||Jamie,Sorry, I'd had a previous link open from the same website and was looking at this which had some resemblence to what i wanted to do:
http://www.sqlis.com/default.aspx?93
As apposed to the link i'd clicked from your post. Apologies and i'll mark your post as an answer also.
Its been a bad day and i've got so many SSIS things in my head :)
Cheers,
Grant|||
Jamie, Sorry, I'd had a previous link open from the same website and was looking at this which had some resemblence to what i wanted to do:
http://www.sqlis.com/default.aspx?93
As apposed to the link i'd clicked from your post. Apologies and i'll mark your post as an answer also.
Its been a bad day and i've got so many SSIS things in my head :)
Cheers,
Grant
Ha ha. Cool. I'm not bothered about getting my "answer count" up, as long as the thread has at least one reply marked as the answer. thanks anyway tho!
-Jamie
Hi all,
I there a way to check if the column is set to NOT NULL is equal to true, if not set to true i have to set it to true before adding my primary key or else i'll get an error message.
in my SP i have a ALTER TABLE table1 ADD PRIMARY KEY (colum1)
My question is, how can i check if the column is set to not null is false and how can i update to set it true?
Thanks.
Teo,
You can use the DMVs sys.tables and sys.columns to determine if a column is nullable.
select t.name, c.name, c.is_nullable
from sys.tables t join sys.columns c
on t.object_id = c.object_id
where t.name = 'fact'
Secondly, to change a column to NOT NULL if it is, use ALTER TABLE ... ALTER COLUMN.
ALTER TABLE dbo.fact ALTER COLUMN dim1_id INT NOT NULL
Regards,
Galex
|||Why you want to check for NULL. without checking it just set it to not null
ALTER TABLE table1 alter column col1 int not null
ALTER TABLE table1 ADD PRIMARY KEY (col1)
it will not retun any errors even it is already set to not null