Need Help Coalescing

Hi,
Below is the code I am struggling with. I want to return only those
"item_id(s)" that pertain to each member. The current results are
giving me every "item_id" for every member.
What am I doing wrong.
Thanks!!!
declare @.Indicatorgroup varchar(8000)
select
@.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
varchar)
from #temp_BLAH
group by Member, item_id
select +@.Indicatorgroup, Member
from #temp_BLAH
group by Member
So the results now are like this:
A,B,C,D,E....Z Smith, Ben
A,B,C,D,E....Z Jones, Dave
But Ben only has A, C, and D.
So I'd like the results to be like:
A,C,D Smith,Ben
A,J,Q Jones,DaveHi
The safe way to do this is to use a cursor if using SQL 2000 see
http://tinyurl.com/yat5xr
John
"Dubs" wrote:

> Hi,
> Below is the code I am struggling with. I want to return only those
> "item_id(s)" that pertain to each member. The current results are
> giving me every "item_id" for every member.
> What am I doing wrong.
> Thanks!!!
> declare @.Indicatorgroup varchar(8000)
> select
> @.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
> varchar)
> from #temp_BLAH
> group by Member, item_id
> select +@.Indicatorgroup, Member
> from #temp_BLAH
> group by Member
> So the results now are like this:
> A,B,C,D,E....Z Smith, Ben
> A,B,C,D,E....Z Jones, Dave
> But Ben only has A, C, and D.
> So I'd like the results to be like:
> A,C,D Smith,Ben
> A,J,Q Jones,Dave
>sql

Need Help Coalescing

Hi,
Below is the code I am struggling with. I want to return only those
"item_id(s)" that pertain to each member. The current results are
giving me every "item_id" for every member.
What am I doing wrong.
Thanks!!!
declare @.Indicatorgroup varchar(8000)
select
@.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
varchar)
from #temp_BLAH
group by Member, item_id
select +@.Indicatorgroup, Member
from #temp_BLAH
group by Member
So the results now are like this:
A,B,C,D,E....Z Smith, Ben
A,B,C,D,E....Z Jones, Dave
But Ben only has A, C, and D.
So I'd like the results to be like:
A,C,D Smith,Ben
A,J,Q Jones,Dave
Hi
The safe way to do this is to use a cursor if using SQL 2000 see
http://tinyurl.com/yat5xr
John
"Dubs" wrote:

> Hi,
> Below is the code I am struggling with. I want to return only those
> "item_id(s)" that pertain to each member. The current results are
> giving me every "item_id" for every member.
> What am I doing wrong.
> Thanks!!!
> declare @.Indicatorgroup varchar(8000)
> select
> @.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
> varchar)
> from #temp_BLAH
> group by Member, item_id
> select +@.Indicatorgroup, Member
> from #temp_BLAH
> group by Member
> So the results now are like this:
> A,B,C,D,E....Z Smith, Ben
> A,B,C,D,E....Z Jones, Dave
> But Ben only has A, C, and D.
> So I'd like the results to be like:
> A,C,D Smith,Ben
> A,J,Q Jones,Dave
>

Need Help Coalescing

Hi,
Below is the code I am struggling with. I want to return only those
"item_id(s)" that pertain to each member. The current results are
giving me every "item_id" for every member.
What am I doing wrong.
Thanks!!!
declare @.Indicatorgroup varchar(8000)
select
@.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
varchar)
from #temp_BLAH
group by Member, item_id
select +@.Indicatorgroup, Member
from #temp_BLAH
group by Member
So the results now are like this:
A,B,C,D,E....Z Smith, Ben
A,B,C,D,E....Z Jones, Dave
But Ben only has A, C, and D.
So I'd like the results to be like:
A,C,D Smith,Ben
A,J,Q Jones,DaveHi
The safe way to do this is to use a cursor if using SQL 2000 see
http://tinyurl.com/yat5xr
John
"Dubs" wrote:
> Hi,
> Below is the code I am struggling with. I want to return only those
> "item_id(s)" that pertain to each member. The current results are
> giving me every "item_id" for every member.
> What am I doing wrong.
> Thanks!!!
> declare @.Indicatorgroup varchar(8000)
> select
> @.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
> varchar)
> from #temp_BLAH
> group by Member, item_id
> select +@.Indicatorgroup, Member
> from #temp_BLAH
> group by Member
> So the results now are like this:
> A,B,C,D,E....Z Smith, Ben
> A,B,C,D,E....Z Jones, Dave
> But Ben only has A, C, and D.
> So I'd like the results to be like:
> A,C,D Smith,Ben
> A,J,Q Jones,Dave
>

Need call a DB function in the middle of the dataflow process

All,

I have to use a field that is calculated in a data flow process and call a database function (return a value) to do anther calculation; then return a value back to the data flow.I tried OLD DB Command but I cannot configure to return a value back to the same data flow.

If there any transformations that can call a DB function and get a value from the function in the middle of the data flow process?Need more detailed instruction.

The data flow is Like:

SourceDB à New_filed 1 = field1 + filed2 à New_filed 2= DB_function (New_filed 1) à Destination DB

Thanks in Advance

Jessie

HI, to return a value from a DBfunction, you need to add a derived column into the pipeline and map the OLEDB command return value to this newly added derived column. In the OLEDB command text you insert the following command:

EXEC ? = DB_Function(?)

Then on the mapping tab, you map the return value (first ?) to the derived column and the parameter (second ?) to your New_Field 1 parameter. This way, the new derived column gets the parameter from the DB_function.

HTH,
Ccote

|||

Hi, Ccote,

Thanks so much for the reply. I have followed your instruction but still getting errors. Here is the detail.

before the OLE command, I added a derived column, make a new column(NEW_Col) there with the same data type as the DB_function return value (int), set the default value to 0.

In the OLE command,I put exec ?=[dbo].[F_DBfunction] (?,?,?) in the SQLcommand field, mapping the 3 input column with the parameters, map the first ? with the NEW_Col from the derived column.

The OLE command does not let me to add any new column to the OLE command output.

I got error when I click on REFRESH, “Invalid parameter number ‘

Where I’m missing here?

Thanks

Jessie

|||

I got it, after change exec ?= [dbo].[F_DBfunction] (?,?,?) to exec ?= [dbo].[F_DBfunction] ?,?,?

Thanks

Need basic SQL query help

Trying to select unique records from a database. A combination of 2 variables make the record unique. Need to export all rows of the return. I have below rudimentary sql skills (select/where) and cannot figure this one out. The database has ~ 180k rows, of which there are only ~ 50k unique records.

The data is on a single table, 15 seperate columns. It is a table containing network router data. The existing table set up shows the historical progression of changes/updates to these routers. I am trying to determine the latest records of the routers in question. Since some of the IP addresses have been used multiple times (moved from site A to site B for example), there are multiple dupe records that I do not want.
The two fields I need to use to identify unique records are:
serial_IP and site_id
In addition to that, I would also like to know how to format "max date" query, so that I get the latest information. I can do that manually if I must via a sort when I export the data, but it would be nice to know how to do that in the future.

thanks

noclueAs those two columns make a unique key on the table, there is possibility of use of a DISTINCT keyword, such as

SELECT DISTINCT serial_IP, site_id FROM your_table;

However, regarding your next request (selecting records with the latest date), there's need to use a subquery:SELECT DISTINCT t.serial_IP, t.site_id
FROM your_table t
WHERE t.date_column = (SELECT max(t1.date_column)
FROM your_table t1
WHERE t1.serial_IP = t.serial_IP
AND t1.site_id = t.site_id
);The DISTINCT keyword might, or might not be needed in this case: if table has only one record per "date_column", you won't need that. If there are multiple records per every "date_column" value, you'll still need it to eliminate multiple records.

Need alittle help with sql statemtn stored procedure

Here is what I am trying to do, If i type to query the stored procedure with two parameters and the two parameters are null, return all rows, else do the query based on the two parameters. Thanks in advance.

Create PROCEDURE [dbo].[Admin_RetrieveCustomerOrders] @.Yearnumint,@.monthnumintASBEGINSET NOCOUNT ON;SELECT o.intOrderId, o.vcCustomerID, o.decTotal, o.dtOrdered,u.vcFirstName, u.vcLastName,(SELECTCOUNT(d.intOrderID)FROM tblOrderDetails dWHERE d.intOrderID = o.intOrderID)AS TotalProductsFROM tblOrders oINNERJOIN tblUserInformation uON o.vcCustomerID = u.vcCustomerIDWHERE YEAR(o.dtOrdered)=@.yearnumANDMONTH(o.dtOrdered)=@.monthnumORDER BY o.dtOrderedDESC END

Thanks Again

Joshua

I think this may work

Essentially you need to modify you WHERE clause to also allow null for the parameters

WHERE (YEAR(o.dtOrdered)=@.yearnumOR @.yearnumisNULL)AND (MONTH(o.dtOrdered)=@.monthnumOR @.monthnumisNULL)

Give the above ago and let us know if it works.