concatenate problem

Hi All,

I am facing problem when i try to concatenate two columns. I have text in one column and numeric data in other field, and in want to update field with text datatype and wants to put '-field2'(field with numeic data) as suffix in text data field.

Result should be "field1-field2"

Any help will be appericiated

ThanksOriginally posted by Devinder Gera
Hi All,

I am facing problem when i try to concatenate two columns. I have text in one column and numeric data in other field, and in want to update field with text datatype and wants to put '-field2'(field with numeic data) as suffix in text data field.

Result should be "field1-field2"

Any help will be appericiated

Thanks
If I read that right you want:

Select field1 + convert(varchar, field2)

You can use that in an INSERT or UPDATE statement.

HTH, Saint|||If I read that right you want:

Select field1 + convert(varchar, field2)

You can use that in an INSERT or UPDATE statement.

HTH, Saint [/SIZE][/QUOTE]

Hi Saint,

Thanks for your reply. I tried this but it works fine in select statement but in update it gives different results.

Following is result in select statement and thats what i want after update
07535494187886-1
07535494187886-2
07535494187886-3
07535494187886-4
30834281804606-1
09462976809022-1
09462976809022-2
37882735916006-1

But actually after update i am getting following result

07535494187886-1-1-1-1-1-1-1-1
07535494187886-2-2-2-2-2-2-2
07535494187886-3-3-3-3-3-3
07535494187886-4-4-4-4-4
30834281804606-1-1-1-1
09462976809022-1-1-1
09462976809022-2-2
37882735916006-1

Any idea why its so.

Thanks|||Originally posted by Devinder Gera
If I read that right you want:

Select field1 + convert(varchar, field2)

You can use that in an INSERT or UPDATE statement.

HTH, Saint

Hi Saint,

Thanks for your reply. I tried this but it works fine in select statement but in update it gives different results.

Following is result in select statement and thats what i want after update
07535494187886-1
07535494187886-2
07535494187886-3
07535494187886-4
30834281804606-1
09462976809022-1
09462976809022-2
37882735916006-1

But actually after update i am getting following result

07535494187886-1-1-1-1-1-1-1-1
07535494187886-2-2-2-2-2-2-2
07535494187886-3-3-3-3-3-3
07535494187886-4-4-4-4-4
30834281804606-1-1-1-1
09462976809022-1-1-1
09462976809022-2-2
37882735916006-1

Any idea why its so.

Thanks [/SIZE][/QUOTE]

No worries guys it started working. I was just updating at wrong time. I changed the location of update now its working fantastic

Thanks a million Saint

Concatenate Multiple Rows?

I can't figure out how to write an SQL query that concatenates one field from a list of records having other parameters in common. It is easier to show than explain. Take the data set:

Spec T_R Section
A008 23w 1
A008 23w 2
A008 23w 4

I need a query that returns a single record/row like this:

Spec T_R Section
A008 23w 1, 2, 4

Any help would be appreciated.I've had this problem more times than I can count. While I was writing my SQL Tutorial (http://www.bitesizeinc.net/index.php/sql.html), I ran across this function for MySQL :

group_concat(field)

Which concatenates the grouped results into a string. If you are using Oracle, you'll need a stored procedure...

-Chrissqlsql

Concatenate Multiple Records Into One Field

Someone please help!. I am trying to create a view in SQL Server 2000 to use for a report but I am having problems with concatenating multiple values into one field. In my example below I am trying to list all records in my queried table in columnA then concatenate a list of all other records that share the same value in column B of the queried table into another field. If there are no other matches for a row in columnA then I would leave the corresponding field in columnB blank. Thanks in advance.

TABLE
ColumnA ColumnB
1......A
2......B
3......C
4......A
5......A
6......B
7......C
8......D
9......C
10.....E

EXPECTED OUTPUT
ColumnA ColumnB
1......4,5
2......6
3......7
4......1,5
5......1, 4
6......2
7......3
8......
9......3,7
10.....create function ConcatFld (@.RowId int, @.RowVal char(1))
returns varchar(100) AS
begin
declare @.Ret varchar(100)
set @.Ret=''
select @.Ret= @.Ret + cast(ColA as varchar)+',' from Tbl1 where ColB=@.RowVal and ColA<>@.RowId
if len(@.Ret) > 0
set @.Ret = left(@.Ret,len(@.Ret)-1)
return @.Ret
end
--------

select ColA, dbo.ConcatFld(ColA,ColB) from Tbl1|||Thanks Upalsen, this is exactly what I need!!!

Concatenate int & var char - SQL

Hi,

I am trying to write some simple SQL to join two fields within a table, the primary key is an int and the other field is a varchar.

But i am receiving the error:

'Conversion failed when converting the varchar value ',' to data type int.

The SQL I am trying to use is:

select game_no + ',' + team_name as match
from result

Thanks

As the error message says you can concatenate similar datatype values and use CONVERT or CAST functions otherwise to make them simialr.

selectConvert(Varchar,game_no)+','+ team_nameas match

from result

|||

Yep. Why the parser is so stupid that it assumes the presence of one number means all other exprssions must evaluate to a number, rather than the other way around, is a mystery to me.

Concatenate field based on unique id. (Follow up)

thanks for your earlier reply.

If i want to order the IDs by a Timestamp column in descending order how do i do it. I couldn't do it in Inner query.
right now it gives in random order. Is there any other way to get it?

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, createTS, count(*) as seq
from your_table as t1 join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment, createTS
order by createTS desc -> gives error
) as t3
group by t3.id;

Got it working. I used 'top 100 percent" and used the ORDER BY in inner query.
Thanks.|||Note that using TOP 100 PERCENT is still not guaranteed to work. It depends on the query plan and even more so in SQL Server 2005. The use of ORDER BY clause is specific to a scope only and in your example to the derived table. Generally, you should not rely on the order in which the rows are processed in a SELECT statement. You should basically consider a SELECT statement source as an unordered set of rows. In your example, you can achieve the results by changing the condition:

t2.Comment <= t1.Comment

to

t2.CreateTs <= t1.CreateTs

This assumes that time stamp value is unique per id and this would guarantee that the sequence number is based on sorting the values in ascending order. You can incorporate additional conditions to handle matching time stamps and different comments. As I said before, doing these type of operations in SQL is not the right approach. These can be done very easily on the client side and with less work / assumptions.

ConCat data

I need to create a csv file where it's just one giant file.
There is only one field I pull from a table called USerID(Char8). Then for
every record in the table create a file like this.
Receipents-c/n=XXXXX%Receipents-c/n=XXXXXReceipents-c/n=XXXXXReceipents-c/n=
XXXXX This will then Import into Exchange for a Distrubution List. Any IdeasSee if this link gives you some ideas
http://www.rac4sql.net/xp_execresultset.asp
Anith|||Would that not put each order on a separate line.
I need it all strung together ..as one big file...
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23QJSioLBFHA.936@.TK2MSFTNGP12.phx.gbl...
> See if this link gives you some ideas
> http://www.rac4sql.net/xp_execresultset.asp
> --
> Anith
>|||Anith has pointed you to a trick to create a file for each record/row for
your table.
Look like you want to concatenate the rows into a single string? If so, it's
probably best to do it from the client side (i.e. vb/script/etc).
Though, I am bit about your comment regarding CSV in your first
post. Perhaps, you want to clarify so we can help.
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:L92dnbdsqJ2N6WTcRVn-gg@.kconline.com...
> Would that not put each order on a separate line.
> I need it all strung together ..as one big file...
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23QJSioLBFHA.936@.TK2MSFTNGP12.phx.gbl...
>|||sorry .. I just meant to have a csv extension to the filename.
do you have an example vbscript to concat these records from the sql table
?
thanks again.
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23C0Ko0MBFHA.3492@.TK2MSFTNGP12.phx.gbl...
> Anith has pointed you to a trick to create a file for each record/row for
> your table.
> Look like you want to concatenate the rows into a single string? If so,
> it's probably best to do it from the client side (i.e. vb/script/etc).
> Though, I am bit about your comment regarding CSV in your first
> post. Perhaps, you want to clarify so we can help.
> --
> -oj
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:L92dnbdsqJ2N6WTcRVn-gg@.kconline.com...
>|||Here is a vbscript.
Main()
Sub Main()
Dim sqlcnt,rs,s
s="Begin"
Set cntsql = CreateObject("ADODB.Connection")
With cntsql
.provider = "SQLOLEDB"
.connectionstring = "Data Source=.\dev;integrated security=SSPI"
.Open
Set rs = .Execute("select OrderID from Northwind..Orders")
Do Until rs.EOF
s = s & rs.Fields("OrderID") & ","
rs.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set cntsql = Nothing
s = s & "End"
Call WriteToFile(s)
End Sub
Function WriteToFile(s)
Dim fso, tf
Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.CreateTextFile("c:\test.csv", True)
tf.Write(s)
tf.Close()
Set fso= Nothing
Set tf= Nothing
End Function
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:ZLydnddhH-JSVWTcRVn-tw@.kconline.com...
> sorry .. I just meant to have a csv extension to the filename.
> do you have an example vbscript to concat these records from the sql table
> ?
> thanks again.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23C0Ko0MBFHA.3492@.TK2MSFTNGP12.phx.gbl...
>|||Thanks again.
"oj" <nospam_ojngo@.home.com> wrote in message
news:uyuNXkQBFHA.3368@.TK2MSFTNGP10.phx.gbl...
> Here is a vbscript.
> Main()
> Sub Main()
> Dim sqlcnt,rs,s
> s="Begin"
> Set cntsql = CreateObject("ADODB.Connection")
> With cntsql
> .provider = "SQLOLEDB"
> .connectionstring = "Data Source=.\dev;integrated security=SSPI"
> .Open
> Set rs = .Execute("select OrderID from Northwind..Orders")
> Do Until rs.EOF
> s = s & rs.Fields("OrderID") & ","
> rs.MoveNext
> Loop
> .Close
> End With
> Set rs = Nothing
> Set cntsql = Nothing
> s = s & "End"
> Call WriteToFile(s)
> End Sub
> Function WriteToFile(s)
> Dim fso, tf
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set tf = fso.CreateTextFile("c:\test.csv", True)
> tf.Write(s)
> tf.Close()
> Set fso= Nothing
> Set tf= Nothing
> End Function
>
> --
> -oj
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:ZLydnddhH-JSVWTcRVn-tw@.kconline.com...
>|||oj The script worked great but...
The problem I'm having it puts an extra %Recipients/cn= at the end of the
file. The Import process that is using this output fails on this bogus
record since it doesn't have an ID attached. How can I remove this last
record from the file if it doesn't have a valid record.
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:msKdnbiYuoaxv2fcRVn-vw@.kconline.com...
> Thanks again.
>
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uyuNXkQBFHA.3368@.TK2MSFTNGP10.phx.gbl...
>|||You would need to check the returned value before concatenating it in your
vbscript.
e.g.
if rs("your_keycol")="abc" then
'it is good and concatenate
else
'it is bad and ignore
endif
Take a look at this site for help on vbscripting
http://msdn.microsoft.com/library/e...me=true

-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:g5WdnagRsbodGpzfRVn-2A@.kconline.com...
> oj The script worked great but...
> The problem I'm having it puts an extra %Recipients/cn= at the end of the
> file. The Import process that is using this output fails on this bogus
> record since it doesn't have an ID attached. How can I remove this last
> record from the file if it doesn't have a valid record.
>
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:msKdnbiYuoaxv2fcRVn-vw@.kconline.com...
>

Concantenating Dates

I am transferring data between DB's. One has DATE and TIME as two individual fields. I need to concantenate them into a single DATETIME field. I am sure the solution is simple but I can't figure it out.
Any advice?
Thanks!If you select from your time column, you should see that it's date is set to January 1st, 1900, like this:
1900-01-01 14:59:27.293

Your date column should show a date as of midnight like this:
2003-07-14 00:00:00.000

If this is the case, you can just add these value together to concatenate them:
select @.Yourdate + @.Yourtime

If this is not the case, you will need to concatenate them as formatted strings and then cast or convert the result to a datetime value.

blindman

Computed field with a maximum

Hi all,
I have a table with some fields, and I'm builiding a query upon it
which should have a computed field based upon the values in each row.
For example:
CREATE TABLE mytable(
-- ...
price decimal NOT NULL,
number decimal NOT NULL
);
The computed field should be the product of price and number fields,
multiplied by a coefficient, but should not be greater than a maximum
value. In MySQL, there is a function named IF(), which takes a
condition as the first parameter, and returns the second parameter if
the condition is true, or the third parameter if not. For example, I'd
write something like this in MySQL:
SELECT ..., price, number,
IF(price*number*0.004>10000,10000,price*number*0.004) FROM mytable;
How can I express something like this in SQL Server? I've browsed
through the functions supported by SQL Server, but I have not seen
anything which looks similar to the IF() function in MySQL.
Thanks in advance,The ANSI compliant functionality (which is implemented in SQL Server) is the
CASE expression.
Something like:
SELECT ..., price, number,
CASE WHEN price*number*0.004 > 10000 THEN 10000 ELSE price*number*0.004 END
FROM mytable;
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<ehsan.akhgari@.gmail.com> wrote in message
news:1130663844.123807.231710@.g44g2000cwa.googlegroups.com...
> Hi all,
> I have a table with some fields, and I'm builiding a query upon it
> which should have a computed field based upon the values in each row.
> For example:
> CREATE TABLE mytable(
> -- ...
> price decimal NOT NULL,
> number decimal NOT NULL
> );
> The computed field should be the product of price and number fields,
> multiplied by a coefficient, but should not be greater than a maximum
> value. In MySQL, there is a function named IF(), which takes a
> condition as the first parameter, and returns the second parameter if
> the condition is true, or the third parameter if not. For example, I'd
> write something like this in MySQL:
> SELECT ..., price, number,
> IF(price*number*0.004>10000,10000,price*number*0.004) FROM mytable;
> How can I express something like this in SQL Server? I've browsed
> through the functions supported by SQL Server, but I have not seen
> anything which looks similar to the IF() function in MySQL.
> Thanks in advance,
>|||SELECT answer = case when price * number * 0.004 > 10000 then 10000 else
price * number * 0.004 end
from mytable
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<ehsan.akhgari@.gmail.com> wrote in message
news:1130663844.123807.231710@.g44g2000cwa.googlegroups.com...
> Hi all,
> I have a table with some fields, and I'm builiding a query upon it
> which should have a computed field based upon the values in each row.
> For example:
> CREATE TABLE mytable(
> -- ...
> price decimal NOT NULL,
> number decimal NOT NULL
> );
> The computed field should be the product of price and number fields,
> multiplied by a coefficient, but should not be greater than a maximum
> value. In MySQL, there is a function named IF(), which takes a
> condition as the first parameter, and returns the second parameter if
> the condition is true, or the third parameter if not. For example, I'd
> write something like this in MySQL:
> SELECT ..., price, number,
> IF(price*number*0.004>10000,10000,price*number*0.004) FROM mytable;
> How can I express something like this in SQL Server? I've browsed
> through the functions supported by SQL Server, but I have not seen
> anything which looks similar to the IF() function in MySQL.
> Thanks in advance,
>|||A bit off topic, yet it might prove to be helpful...
Keep in mind that when declaring the decimal datatype the default scale (the
number of decimal digits) is 0.
ML

computed field syntax

Hello:
I am trying to set the value of a computed field in a stored procedure to
the value returned by another stored procedure but can't seem to find the
proper syntax:
CREATE PROCEDURE PlanListGet // syntax invalid
AS
SELECT
Plans.PlanID,
Plans.[Name],
Plans.[Description],
IsPlanEstablished = EXEC PlanIsEstablished PlanID
FROM Plans
In the above code, IsPlanEstablished is the computed field,
PlanIsEstablished is a stored procedure that returns an integer value, and
PlanID is a parameter for the PlanIsEstablished stored procedure.
Any suggestions?
Thanks!
ChrisYOu cant do that in a select but you can retrieve the Value to store it in
a temptable an retrieve this from that.
alte Procedure Testint
(
@.Valuetopass int
)
AS
SELECT @.Valuetopass*5
CREATE TABLE #TempTable
(
ValueToReturn INT
)
INSERT INTO #TempTable
EXEC Testint 5
SELECT * from #TempTable
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ChrisB" <pleasereplytogroup@.thanks.com> schrieb im Newsbeitrag
news:%237Cut3xWFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello:
> I am trying to set the value of a computed field in a stored procedure to
> the value returned by another stored procedure but can't seem to find the
> proper syntax:
> CREATE PROCEDURE PlanListGet // syntax invalid
> AS
> SELECT
> Plans.PlanID,
> Plans.[Name],
> Plans.[Description],
> IsPlanEstablished = EXEC PlanIsEstablished PlanID
> FROM Plans
> In the above code, IsPlanEstablished is the computed field,
> PlanIsEstablished is a stored procedure that returns an integer value, and
> PlanID is a parameter for the PlanIsEstablished stored procedure.
> Any suggestions?
> Thanks!
> Chris
>
>|||You can't use a stored procedure as an expression for a computed column. Per
haps you can convert the
proc into a user defined scalar function?
CREATE FUNCTION f() RETURNS INT AS BEGIN RETURN 1 END
GO
CREATE TABLE t(c1 int, c2 AS dbo.f())
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:%237Cut3xWFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello:
> I am trying to set the value of a computed field in a stored procedure to
the value returned by
> another stored procedure but can't seem to find the proper syntax:
> CREATE PROCEDURE PlanListGet // syntax invalid
> AS
> SELECT
> Plans.PlanID,
> Plans.[Name],
> Plans.[Description],
> IsPlanEstablished = EXEC PlanIsEstablished PlanID
> FROM Plans
> In the above code, IsPlanEstablished is the computed field, PlanIsEstablis
hed is a stored
> procedure that returns an integer value, and PlanID is a parameter for the
PlanIsEstablished
> stored procedure.
> Any suggestions?
> Thanks!
> Chris
>
>|||Looks like I'll have to take a different approach.
Thanks for the input!
Chris
"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:%237Cut3xWFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello:
> I am trying to set the value of a computed field in a stored procedure to
> the value returned by another stored procedure but can't seem to find the
> proper syntax:
> CREATE PROCEDURE PlanListGet // syntax invalid
> AS
> SELECT
> Plans.PlanID,
> Plans.[Name],
> Plans.[Description],
> IsPlanEstablished = EXEC PlanIsEstablished PlanID
> FROM Plans
> In the above code, IsPlanEstablished is the computed field,
> PlanIsEstablished is a stored procedure that returns an integer value, and
> PlanID is a parameter for the PlanIsEstablished stored procedure.
> Any suggestions?
> Thanks!
> Chris
>
>

Computed field references

I am currently developing a stored procedure that includes a number of computed fields. Is it possible to reference a computed value, (eg. FLdA), or do I need to CREATE a temp file and then reference the FldA and FldB values. I have simplified my code, it is much more extensive in that there are numerous WHEN clauses attached to each FldA and FldB computation.

SELECT FldA = CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END),
FldB = CASE ....
NewValue = CASE
WHEN ... THEN FldA * CurValue
WHEN ... THEN FldB * CurValue
etc.I'm not sure I understand the question...Do you want to reference the value again inside the sproc?

Then Yes...use a local table variable...

If it's being part of a result set being passed back, then you're already refrencing it...

I'm confused...|||I want to reference the value within the sproc and pass only those records where the OldValue is not equal to the NewValue. In the case I mentioned, I am trying to reference FldA and FldB to compute the NewValue from within the same SELECT stmt, but SQL does not let me reference the FldA and FldB computed values. Is that as clear as mud?|||Reference them, where? In the same query? Or later on in the sproc.

If it's later on in the sproc

SELECT <whatever> INTO #TEMP FROM <whatever>

Then just query the local temp table...

Is that what you mean?|||I'm trying to reference them in the same query.

The INSERT .. INTO stmt seems cumbersome as it appears I would have to define each field as part of the CREATE TABLE stmt. Can't see why it doesn't just pickup the data types from the TABLE.|||Well it's not data type is it...it's column names

Well do this...Keep your computed stuff isolated...and join to a derived table

SELECT * FROM (SELECT <your derived columns> FROM table join table ect) AS A
LEFT JOIN B ON a.key = b.key
WHERE <now you can reference the derived column name> = 'bananas'

Whatever...

I fyou make the derivation this derived table you'll be able to reference the column names you made up...|||Why so complicated?

select * from (
SELECT FldA = CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END),
FldB = CASE ....
NewValue = CASE
WHEN ... THEN CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END * CurValue
WHEN ... THEN CASE .... * CurValue
) x
where OldValue != NewValue

In other words, instead of trying to reference FldA, use its CASE...END when calculating NewValue. Same with FldB.|||I had mentioned earlier that the code was simplified. The CASE logic is fairly complex, could be up to 20 lines of code. That would mean that I would have to repeat the code everytime the field ('FldA') was referenced. I may just leave the logic in VBA code as it seems a lot easier to manipulate fields in code. My goal was to restrict the query ouput lines so the Access code would run quicker.|||Thanks Brett ... I'll give it a go.|||Here's a model

USE Northwind
GO
SELECT SUM(OutOfBusinessDays) AS VacationDays
FROM (
SELECT ShipLate-ShipDelay AS OutOfBusinessDays
FROM (
SELECT DATEDIFF(dd,OrderDate,ShippedDate) As ShipDelay
, DATEDIFF(dd,OrderDate,RequiredDate) As ShipLate
FROM Orders
) AS XXX
) AS DerivedTableName

computed columns or UDFs

Hi,

What is the difference between a computed column and a UDF?
Is a computed column the same as the "Formula" field under Design Table in Enterprise Manager?
Also, what is the proper syntax for the Formula field? Can I use regular SQL on it or is there more to it?

thanks,
Frankjust look up books online they have a better xplanation than anyone here can give you in 1-2 lines.

hth

Computed Columns

I have a quick question for someone that has worked with
computed columns. Can a computed column within a table
reference a field from another table in it's formula. If
so, how? Also, if using a view that joins multiple
tables, how can you do an insert with that view, or can
you? Have tried to look this up, but can't find much.
Thanks,
Van JonesHi Van,
1) You acces other tables on SQL Server 2000 if you use a user defined
function in the formula of your computed column.
2) You can insert in a view that joins multiple table if you create an
INSTEAD OF trigger on that view. Once again this is SQL Server 2000 only,
this won't work on earlier versions.
--
Jacco Schalkwijk
SQL Server MVP
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:09b601c3a7a3$c4dcfa30$a401280a@.phx.gbl...
> I have a quick question for someone that has worked with
> computed columns. Can a computed column within a table
> reference a field from another table in it's formula. If
> so, how? Also, if using a view that joins multiple
> tables, how can you do an insert with that view, or can
> you? Have tried to look this up, but can't find much.
> Thanks,
> Van Jones|||Thanks for your help.
Van
>--Original Message--
>Hi Van,
>1) You acces other tables on SQL Server 2000 if you use a
user defined
>function in the formula of your computed column.
>2) You can insert in a view that joins multiple table if
you create an
>INSTEAD OF trigger on that view. Once again this is SQL
Server 2000 only,
>this won't work on earlier versions.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote
in message
>news:09b601c3a7a3$c4dcfa30$a401280a@.phx.gbl...
>> I have a quick question for someone that has worked with
>> computed columns. Can a computed column within a table
>> reference a field from another table in it's formula.
If
>> so, how? Also, if using a view that joins multiple
>> tables, how can you do an insert with that view, or can
>> you? Have tried to look this up, but can't find much.
>> Thanks,
>> Van Jones
>
>.
>|||Van,
Although there are ways to achieve what you ask, you should wonder
whether to use this. There is nothing you can do with a computed column
that you can't do with a view. However, creating a computed column
complicates the table structure, especially if it is based on a UDF.
I would avoid using the (SQL-Server proprietary) computed columns, and
use them only if I *really* needed them.
My 5 cents,
Gert-Jan
Van Jones wrote:
> I have a quick question for someone that has worked with
> computed columns. Can a computed column within a table
> reference a field from another table in it's formula. If
> so, how? Also, if using a view that joins multiple
> tables, how can you do an insert with that view, or can
> you? Have tried to look this up, but can't find much.
> Thanks,
> Van Jones

Computed column question

I have a SQL table that maintains a field on the status of a report being completed.

I have in the record the date the report is due (DateDue)

I also have a field called DaysLate which I have set to be a calculated field with formula:

DATEDIFF(dd, DateDue, GETDATE())

Thsi works but when the report is *not* late I'd like this to be null is there I way I can do this conditional calculation in a calculated field?

Regards

Cvive

CASE WHEN {Your formula}<0 THEN NULL ELSE {Your formula} END

|||

Many thanks for that - that did the job perfectly.

Clive

Complicated SQL Select help needed

Hi,

My users table contains a field called researchInterestId which looks like this: 1, 5, 10

This is because users where allows to select multiple options when choosing their research interests.

I have another table which contains the names of those research interests, which looks like this:

researchInterestId researchInterestName

1 Biology

2 Cancer

My question is, when selecting my list of users, i wish to also display the names of their research interests. I know how to inner join but im not sure in this case as there are multiple values (1, 5, 10)

Hope that makes sense and that someone can point me in the right direction or let me know what this type of query is called?

Thanks

Sam

You can create a function as shown here:http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx in the comments and use it to join with the researchinterests table to get the description.

|||

It worked perfectly, thanks so much!

Complex sum

I have a report created using SQL Server Reporting tools.

The report contains a field 'FIELD A' which displays the difference of hours in that record.
At the end of the report, I need to sum all FIELD A and display in FIELD B.

How do I do it?One way to do this, if all the 'FIELD A' textboxes exist on one page, is to add the 'Field B' textbox to the Page Footer, and use the SUM aggregate over the textbox. For example,

=(Sum(ReportItems!FieldATextbox.Value))

This will only work if the expression is in the Page Header or Footer, and if all instances of the 'Field A' textbox is on the same page.

If not all instances will exist on one page, then you could use a simple custom function that is called from the 'Field A' textbox. This custom function adds the value passed to a member field, and then returns the value passed. In the 'Field B' textbox call a separate custom function that returns the value stored in the member field.

Ian

Complex substring query and value conversion help...

Ok, I have to convert the dollar values of some of my fields in a
query, if the field to the left (in the left column) has a word that
part of the word has "debit" in it. Like this:
if(t5.description = "TransferDebit")
{
DECLARE @.Num1 int
SET @.Num1 = t6.amount
SELECT -@.Num1
}
Thanks,
TrintIt is not clear from your post what you are exactly trying to do. What are
t5 and t6 in your sample code? Are you trying to extract a portion of the
string value in some column?
Perhaps you can use SUBSTRING function to extract a part of a string. To
find if "debit" is a part of the value in description column, you can use
CHARINDEX or PATINDEX. Details of all these functions, with examples, can be
found in SQL Server Books Online.
Anith|||Please provide actual DDL, sample data, and expected results
[http://www.aspfaq.com/etiquette.asp?id=5006]. how are T5 and T6 related? we
need to see the structure and data you are actually working with.
Without better specification, I am going to work from the premise that all
this information is actually in a single table. Do you want to actually
change the values as they are stored, or do you just want to display them in
a negative format?
If the former:
UPDATE T5 SET amount = -amount WHERE description LIKE '%Debit%'
If the latter:
SELECT
CASE WHEN description LIKE '%Debit%' THEN -amount ELSE amount END AS
Amount,
<other columns to display...>
FROM T5
"trint" <trinity.smith@.gmail.com> wrote in message
news:1122047085.356296.239470@.o13g2000cwo.googlegroups.com...
> Ok, I have to convert the dollar values of some of my fields in a
> query, if the field to the left (in the left column) has a word that
> part of the word has "debit" in it. Like this:
> if(t5.description = "TransferDebit")
> {
> DECLARE @.Num1 int
> SET @.Num1 = t6.amount
> SELECT -@.Num1
> }
> Thanks,
> Trint
>|||Jeremy,
the "lookup" for this description is in a table called "amount_type".
It somehow needs to be in this code here:
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount END) AS Purchase
thanks,
Trint|||Jeremy,
the "lookup" for this description is in a table called "amount_type".
It somehow needs to be in this code here:
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount END) AS Purchase
thanks,
Trint|||Jeremy,
Actually, here is the code:
SELECT t1.MemberId, t1.PeriodID,
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount) END) AS
Purchase,
SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
END) AS Matrix,
SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount END) AS
QualiFly,
SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
END) AS Dist,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount END) AS SM,
SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
END) AS BreakAway,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
END) AS Transfer,
SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount END) AS Spent
FROM tblTravelDetail t1 INNER JOIN
tblTravelDetailAmount t2 ON t1.TravelDetailId =
t2.TravelDetailId INNER JOIN
tblTravelDetail t3 ON t2.TravelDetailId =
t3.TravelDetailId INNER JOIN
tblTravelDetailMember t4 ON t3.TravelDetailId =
t4.TravelDetailId INNER JOIN
tblTravelEvent t5 ON t1.TravelEventId =
t5.TravelEventId INNER JOIN
amount_type t6 ON t2.amountTypeId =
t6.amount_type_id
WHERE (t4.TravelDetailMemberTypeId = 1) AND (t1.MemberId = 12391)
AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID
thanks,
Trint

complex stored procedure on history table

Hi All,

I have a table that hold status history records for cases. In this table is a status field with values, opened, assigned, or complete. Each case can be assigned a number of times before it is complete, and can be reassigned. I have the need to run a query that will get each case that is still assigned, and not yet complete. I wrote a stored procedure that contains a cursor containing each case, and get the last status history record for each case and puts it into a temp table to return to the user, but is hurting performance as there are .5 million records here. Does anyone know of a better way of doing this?

Thanks in advance : )

Found an answer elsewhere using correlated sub queries. thanks

complex sql server 2005 query

Hi,
A sql server table is populated with records every 2 minutes. See below sample table
In the table, the Import_Date is a datetime field.

create table tblData
(
ID int identity(1, 1),
SourceID int,
SourceCode varchar(255)
Security varchar(255),
Bprice decimal(12, 8),
Aprice decimal(12, 8),
ImportDate datetime
)

Here is a populated table.
I have left gaps for better visual checks for you.

ID SourceID SourceCode Security Bprice BpriceSize Aprice ApriceSize ImportDate

1 1 sourceA SecA 100.2 2 99.12 1 2007-11-07 16:24:31.297
2 2 sourceW SecH 95.7 89.43 2007-11-07 16:24:31.297
3 3 SourceX SecS 50.56 1 76.44 4 2007-11-07 16:24:31.297
4 4 SourceQ SecZ 87.98 2007-11-07 16:24:31.297
5 5 SourceJ SecH 100.2 99.12 2 2007-11-07 16:24:31.297
6 6 SourceK SecU 2007-11-07 16:24:31.297
7 7 SourceT SecA 50.56 3 87.11 2007-11-07 16:24:31.297

8 1 sourceA SecA 100.2 6 99.12 2 2007-11-07 16:26:15.123
9 2 sourceW SecH 99.54 4 89.43 2007-11-07 16:26:15.123
10 3 SourceX SecS 50.56 2 19.33 2007-11-07 16:26:15.123
11 4 SourceQ SecZ 16.98 87.98 2007-11-07 16:26:15.123
12 5 SourceJ SecH 100.2 1 99.12 2 2007-11-07 16:26:15.123
13 6 SourceK SecU 2007-11-07 16:26:15.123
14 7 SourceT SecA 50.56 2 87.11 1 2007-11-07 16:26:15.123

15 1 sourceA SecA 100.2 1 87.11 1 2007-11-07 16:26:15.123
16 2 sourceW SecH 99.66 89.43 2 2007-11-07 16:26:15.123
17 3 SourceX SecS 50.56 2 19.33 2007-11-07 16:26:15.123
18 4 SourceQ SecZ 16.98 3 87.98 3 2007-11-07 16:26:15.123
19 5 SourceJ SecH 100.2 3 99.12 3 2007-11-07 16:26:15.123
20 6 SourceK SecU 2007-11-07 16:26:15.123
21 7 SourceT SecA 101.32 5 87.11 3 2007-11-07 16:26:15.123
...

I am trying to build a sql query to show which source is offering the max(Bprice) and who is offering the min(Aprice).
In addition if more than one sources are offering the same prices then they should be shown as shown below in the first record i.e. (SourceA, SourceT) --> 3 + 1 = 4
This is what I would like to see:

Security Max_Bprice Bprice_Size Bprice_SourceCode Min_Aprice Aprice_Size Aprice_SourceCode

SecA 101.32 5 SourceT 87.11 4 SourceA, SourceT
SecH 100.2 3 SourceJ 89.43 2 SourceW
SecS 50.56 2 SourceX 19.33 SourceX
SecZ 16.98 3 SourceQ 87.98 3 SourceQ

What is the sql query to do this please?

This is what I have started with but it is not correct...

select
Security,
max(Bprice) as 'Max_Bprice',
SourceCode as 'Bprice_SourceCode',
min(Aprice) as 'Min_Aprice',
SourceCode as 'Aprice_SourceCode'
from
tblData
group by
Security,
SourceCodeHi

You are almost there but not quite. Could you provide your sample data as point 3 here please:
http://www.dbforums.com/showthread.php?t=1196943

Also, your DDL does not match the data you have supplied.

Not really related, but there looks to be a third normal form issue here.

Cheers|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92298|||Here it is.
Thanks

DECLARE @.Sample TABLE (ID INT, SourceID INT, SourceCode VARCHAR(20), Security VARCHAR(20), Bprice MONEY, BpriceSize INT, Aprice MONEY, ApriceSize INT, ImportDate DATETIME)

INSERT @.Sample
SELECT 1, 1, 'sourceA', 'SecA', 100.2 , 2, 99.12, 1, '2007-11-07 16:24:31.297' UNION ALL
SELECT 2, 2, 'sourceW', 'SecH', 95.7 , NULL, 89.43, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 3, 3, 'SourceX', 'SecS', 50.56, 1, 76.44, 4, '2007-11-07 16:24:31.297' UNION ALL
SELECT 4, 4, 'SourceQ', 'SecZ', 87.98, NULL, NULL, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 5, 5, 'SourceJ', 'SecH', 100.2 , NULL, 99.12, 2, '2007-11-07 16:24:31.297' UNION ALL
SELECT 6, 6, 'SourceK', 'SecU', NULL, NULL, NULL, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 7, 7, 'SourceT', 'SecA', 50.56, 3, 87.11, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 8, 1, 'sourceA', 'SecA', 100.2 , 6, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 9, 2, 'sourceW', 'SecH', 99.54, 4, 89.43, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 10, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 11, 4, 'SourceQ', 'SecZ', 16.98, NULL, 87.98, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 12, 5, 'SourceJ', 'SecH', 100.2 , 1, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 13, 6, 'SourceK', 'SecU', NULL, NULL, NULL, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 14, 7, 'SourceT', 'SecA', 50.56, 2, 87.11, 1, '2007-11-07 16:26:15.123' UNION ALL
SELECT 15, 1, 'sourceA', 'SecA', 100.2 , 1, 87.11, 1, '2007-11-07 16:26:15.123' UNION ALL
SELECT 16, 2, 'sourceW', 'SecH', 99.66, NULL, 89.43, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 17, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 18, 4, 'SourceQ', 'SecZ', 16.98, 3, 87.98, 3, '2007-11-07 16:26:15.123' UNION ALL
SELECT 19, 5, 'SourceJ', 'SecH', 100.2 , 3, 99.12, 3, '2007-11-07 16:26:15.123' UNION ALL
SELECT 20, 6, 'SourceK', 'SecU', NULL , NULL, NULL, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 21, 7, 'SourceT', 'SecA', 101.32, 5, 87.11, 3, '2007-11-07 16:26:15.123'|||I'll leave it to Peso. He'll get it soon enough I would imagine.|||Thanks anyway|||I suggest that you break it up

Do one, then the other, then combine them

I think you can do a union or a join of 2 derived table.

Since each derived table is going to be a single row, you wont have to worry about a cartesian product

Complex SQL Query - Joins, Max, Union

How to find maximum value from two tables have the same field name?

For example:
Table -1 has field calcuated_price and its max value is 3500 and then Table -2 has same field name calcuated_price has max value is 3000.

Nishith

SELECTMAX(P.iPlanid),MAX(AP.iPlanid)

FROM PLANS P INNERJOIN ANOTHERPLANS AP

ON P.iPlanid = AP.iPlanid

|||Hello Steve,
This query is fine, but it will return 2 values, I need just one max. value from 2 tables.|||

use northwind
select * into #product1 from products where productid<50
select * into #product2 from products where productid >=50

select case
when a.price>b.price then a.price
when a.price<b.price then b.price
end as maxprice

from (
select max(unitprice)price from #product1) as a
cross join
(
select max(unitprice)as price from #product2)
as b

|||

SELECTCASE

WHENMAX(P.iPlanid)>MAX(AP.iPlanid)THENMAX(P.iPlanid)

ELSE

MAX(AP.iPlanid)

ENDAS maxvalue

FROM

PLANS P

INNERJOIN ANOTHERPLANS AP

ON P.iPlanid = AP.iPlanid

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

Well, there are three things to discuss here. First off this statement:

Table -1 has field calcuated_price ... then Table -2 has same field name calcuated_price

This just shouts out "design issue" Of course it is totally out of context, so these may be quite different things you have modeled and you just want to compare their prices. The point I am trying to make is: if the tables have the same things in them, or even common columns that have the same meaning, then you ought to consider making one table from the common values.

Two: calulated_price sounds like you are storing an aggregate. This is generally a bad idea. As in all things it is very dependent on how the data is used, but it is usually so much more work to keep aggregates in proper sync that it is just best to calculate them as needed

Three: I live in a glass house myself, so even if your answer is: "I know, but this is what I have and can't change it" which is the case for so many database developers I come in contact with, here is what I would do:

Union the two sets first in a CTE or derived table, then treat the set as a single table. With this you can then do aggregates on theml, and groups as needed:

select groupColumn, max(calculate_price)
from (select columns, calculated_price from [table -1]
union all --I am guessing if there is overlap in column values you will want them
select columns, calculated_price from [table -2]) as tableThatShouldHaveBeen

Complex SQL Query - Joins, Max, Union

How to find maximum value from two tables have the same field name?

For example:
Table -1 has field calcuated_price and its max value is 3500 and then Table -2 has

same field name calcuated_price has max value is 3000.

Nishith

SELECT MAX(P.iPlanid),MAX(AP.iPlanid)

FROM PLANS P INNER JOIN ANOTHERPLANS AP

ON P.iPlanid = AP.iPlanid

|||Hello Steve,
This query is fine, but it will return 2 values, I need just one max. value from 2 tables.|||

use northwind
select * into #product1 from products where productid<50
select * into #product2 from products where productid >=50

select case
when a.price>b.price then a.price
when a.price<b.price then b.price
end as maxprice

from (
select max(unitprice)price from #product1) as a
cross join
(
select max(unitprice)as price from #product2)
as b

|||

SELECT CASE

WHEN MAX(P.iPlanid) > MAX(AP.iPlanid) THEN MAX(P.iPlanid)

ELSE

MAX(AP.iPlanid)

END AS maxvalue

FROM

PLANS P

INNER JOIN ANOTHERPLANS AP

ON P.iPlanid = AP.iPlanid

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

Well, there are three things to discuss here. First off this statement:

Table -1 has field calcuated_price ... then Table -2 has same field name calcuated_price

This just shouts out "design issue" Of course it is totally out of context, so these may be quite different things you have modeled and you just want to compare their prices. The point I am trying to make is: if the tables have the same things in them, or even common columns that have the same meaning, then you ought to consider making one table from the common values.

Two: calulated_price sounds like you are storing an aggregate. This is generally a bad idea. As in all things it is very dependent on how the data is used, but it is usually so much more work to keep aggregates in proper sync that it is just best to calculate them as needed

Three: I live in a glass house myself, so even if your answer is: "I know, but this is what I have and can't change it" which is the case for so many database developers I come in contact with, here is what I would do:

Union the two sets first in a CTE or derived table, then treat the set as a single table. With this you can then do aggregates on theml, and groups as needed:

select groupColumn, max(calculate_price)
from (select columns, calculated_price from [table -1]
union all --I am guessing if there is overlap in column values you will want them
select columns, calculated_price from [table -2]) as tableThatShouldHaveBeen