Conditionally referring to fields

Hi,
I am using RS 2000. In a report, I have database field whose name keeps changing everytime based on some condition. Say, a stored proc returns a field Aug2005. The name of this field becomes Oct2005 on some other condition. How can I use this field in the layout (to drag n drop). By what name/alias could I refer to this field. I read that in RS 2005 there is an option like Fields.Items(index).Value to access the field conditionally but I tried it in RS 2000 to no avail. Please suggest a solution.
Thanks,
Biju.

When you use the Fields.Items syntax, what you are varying is the field name, not the underlying database query column name (called DataField in RDL). All columns returned by the query must be known and mapped in the RDL.

If you have a query that returns different columns, you need to add them both to the query and then conditionally switch between them.

Conditionally referring to fields

Hi,
I am using RS 2000. In a report, I have database field whose name keeps changing everytime based on some condition. Say, a stored proc returns a field Aug2005. The name of this field becomes Oct2005 on some other condition. How can I use this field in the layout (to drag n drop). By what name/alias could I refer to this field. I read that in RS 2005 there is an option like Fields.Items(index).Value to access the field conditionally but I tried it in RS 2000 to no avail. Please suggest a solution.
Thanks,
Biju.

When you use the Fields.Items syntax, what you are varying is the field name, not the underlying database query column name (called DataField in RDL). All columns returned by the query must be known and mapped in the RDL.

If you have a query that returns different columns, you need to add them both to the query and then conditionally switch between them.

sqlsql

Conditional Union!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible'
A basic example would be great!!
Cheers,
Adam.Adam Knight wrote:
> Hi all,
> I have a query that if it returns data i want to perform a union on
> it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
> A basic example would be great!!
> Cheers,
> Adam.
IF EXISTS (Select * FROM myTable WHERE myColumn = 'a')
Select NEWID(), <explicitly specify columns> FROM myTable WHERE
myColumn = 'a'
UNION ALL -- Use a UNION ALL in most cases
SELECT NEWID(), <explicitly specify columns> FROM myTable 2 WHERE
myColumn = 'b'
ORDER BY 1
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Answered in microsoft.public.sqlserver.programming.
Help others to help you. Please do not multi-post!
David Portas
SQL Server MVP
--

Conditional UNION!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible'
A basic example would be great!!
Cheers,
Adam."Adam Knight" <adam@.pertrain.com.au> wrote in message
news:OKNz$V9wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have a query that if it returns data i want to perform a union on it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
>
Would this work?
Select *
FROM myTable
WHERE myColumn = 'a'
UNION
SELECT *
FROM myTable
WHERE myColumn = 'b'
and exists(
Select *
FROM myTable
WHERE myColumn = 'a')
Regards,
John|||Adam
Is there any reason to use UNION instead of UNION ALL? Do you want to
eliminate duplications?
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:OKNz$V9wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have a query that if it returns data i want to perform a union on it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
> A basic example would be great!!
> Cheers,
> Adam.
>
>|||Yes!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edBylb%23wFHA.3000@.TK2MSFTNGP12.phx.gbl...
> Adam
> Is there any reason to use UNION instead of UNION ALL? Do you want to
> eliminate duplications?
>
> "Adam Knight" <adam@.pertrain.com.au> wrote in message
> news:OKNz$V9wFHA.3644@.TK2MSFTNGP11.phx.gbl...
>|||Try:
SELECT DISTINCT *
FROM MyTable
WHERE mycolumn IN ('A','B')
AND EXISTS
(SELECT *
FROM MyTable
WHERE mycolumn = 'A') ;
ORDER BY NEWID() fails under UNION or DISTINCT unless you also add NEWID()
to the SELECT list (in which case duplicates would not be eliminated).
Apparently your table doesn't have a key. I suggest you fix that problem
first but I don't see how this query helps you do that.
If the above doesn't help, please post DDL, sample data and required results
as suggested here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--

Conditional Union!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible?
A basic example would be great!!
Cheers,
Adam.
Adam Knight wrote:
> Hi all,
> I have a query that if it returns data i want to perform a union on
> it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible?
> A basic example would be great!!
> Cheers,
> Adam.
IF EXISTS (Select * FROM myTable WHERE myColumn = 'a')
Select NEWID(), <explicitly specify columns> FROM myTable WHERE
myColumn = 'a'
UNION ALL -- Use a UNION ALL in most cases
SELECT NEWID(), <explicitly specify columns> FROM myTable 2 WHERE
myColumn = 'b'
ORDER BY 1
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Answered in microsoft.public.sqlserver.programming.
Help others to help you. Please do not multi-post!
David Portas
SQL Server MVP

Conditional Union!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible'
A basic example would be great!!
Cheers,
Adam.Adam Knight wrote:
> Hi all,
> I have a query that if it returns data i want to perform a union on
> it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
> A basic example would be great!!
> Cheers,
> Adam.
IF EXISTS (Select * FROM myTable WHERE myColumn = 'a')
Select NEWID(), <explicitly specify columns> FROM myTable WHERE
myColumn = 'a'
UNION ALL -- Use a UNION ALL in most cases
SELECT NEWID(), <explicitly specify columns> FROM myTable 2 WHERE
myColumn = 'b'
ORDER BY 1
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Answered in microsoft.public.sqlserver.programming.
Help others to help you. Please do not multi-post!
--
David Portas
SQL Server MVP
--

Conditional Send Mail Task

Hi,

I want send email if certain conditions are met (by send mail task)... if compnay records does not exists in some table (returns null)... not task failure.... how do I achieve this without using Script task?

does any one have an idea about it,

regards

You need conditional workflow: http://www.sqlis.com/default.aspx?306

You can base your expression on a boolean variable that can be set from various places, including a script task.

-Jamie

|||

In contiuation i would like to ask another question i.e. I want to send the results of my query (complete result sets) from email task? How will I able to achieve the task.?

ur help will be appreciated

|||

Zadoras wrote:

In contiuation i would like to ask another question i.e. I want to send the results of my query (complete result sets) from email task? How will I able to achieve the task.?

ur help will be appreciated

Hmmm interesting one. probably the easiest way is to push that data into a flat file destination and then email that file as an attachment.

-Jamie

|||

that's what i was thinking... but i m searching for other way (may be the easiest way)

BTW... thanx for your support

if ne one come across to ne better idea than that please let me know

Conditional iif statement

Ok I have a sp that returns a number of records, each identfied as being part
of a group based on the Type field.
Type Name Amount
1 Test1 1.00
1 Test2 1.00
2 Test3 2.00
3 Test4 3.00
What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
works fine but when I use them middle records iff(Type.value = 2,
Price.value, 0) it displays 1.00 as the price for type 2, and it should
display 2.00 for type 2.
Any help is greatly apprciated. Also, due to the formatting specifics I am
using single textboxes instead of a table or list.
DigivixTry this...
=iif(Fields!Type.Value=1, Fields!Price.Value,0)
the same with Value =2
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> Ok I have a sp that returns a number of records, each identfied as being
> part
> of a group based on the Type field.
> Type Name Amount
> 1 Test1 1.00
> 1 Test2 1.00
> 2 Test3 2.00
> 3 Test4 3.00
> What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> works fine but when I use them middle records iff(Type.value = 2,
> Price.value, 0) it displays 1.00 as the price for type 2, and it should
> display 2.00 for type 2.
> Any help is greatly apprciated. Also, due to the formatting specifics I
> am
> using single textboxes instead of a table or list.
> Digivix|||Sorry for the typo's but that is exactly what i am using and it is giving me
the first record's price only.
"Wayne Snyder" wrote:
> Try this...
> =iif(Fields!Type.Value=1, Fields!Price.Value,0)
> the same with Value =2
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> > Ok I have a sp that returns a number of records, each identfied as being
> > part
> > of a group based on the Type field.
> >
> > Type Name Amount
> > 1 Test1 1.00
> > 1 Test2 1.00
> > 2 Test3 2.00
> > 3 Test4 3.00
> >
> > What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> > works fine but when I use them middle records iff(Type.value = 2,
> > Price.value, 0) it displays 1.00 as the price for type 2, and it should
> > display 2.00 for type 2.
> >
> > Any help is greatly apprciated. Also, due to the formatting specifics I
> > am
> > using single textboxes instead of a table or list.
> >
> > Digivix
>
>|||Can't you go back to a table and simulate the look & feel? I think if you
did it in the detail row it would pull the right values.
--
"Everyone knows something you don't know"
"DigitalVixen" wrote:
> Sorry for the typo's but that is exactly what i am using and it is giving me
> the first record's price only.
> "Wayne Snyder" wrote:
> > Try this...
> >
> > =iif(Fields!Type.Value=1, Fields!Price.Value,0)
> > the same with Value =2
> >
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> > news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> > > Ok I have a sp that returns a number of records, each identfied as being
> > > part
> > > of a group based on the Type field.
> > >
> > > Type Name Amount
> > > 1 Test1 1.00
> > > 1 Test2 1.00
> > > 2 Test3 2.00
> > > 3 Test4 3.00
> > >
> > > What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> > > works fine but when I use them middle records iff(Type.value = 2,
> > > Price.value, 0) it displays 1.00 as the price for type 2, and it should
> > > display 2.00 for type 2.
> > >
> > > Any help is greatly apprciated. Also, due to the formatting specifics I
> > > am
> > > using single textboxes instead of a table or list.
> > >
> > > Digivix
> >
> >
> >|||Hi David,
Thank you for the reply, however I don't understand what is meant by
"simulate the look & feel", can you please be a little more specific?
Thanks
"David Bienstock" wrote:
> Can't you go back to a table and simulate the look & feel? I think if you
> did it in the detail row it would pull the right values.
> --
> "Everyone knows something you don't know"
>
> "DigitalVixen" wrote:
> > Sorry for the typo's but that is exactly what i am using and it is giving me
> > the first record's price only.
> >
> > "Wayne Snyder" wrote:
> >
> > > Try this...
> > >
> > > =iif(Fields!Type.Value=1, Fields!Price.Value,0)
> > > the same with Value =2
> > >
> > >
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> > > news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> > > > Ok I have a sp that returns a number of records, each identfied as being
> > > > part
> > > > of a group based on the Type field.
> > > >
> > > > Type Name Amount
> > > > 1 Test1 1.00
> > > > 1 Test2 1.00
> > > > 2 Test3 2.00
> > > > 3 Test4 3.00
> > > >
> > > > What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> > > > works fine but when I use them middle records iff(Type.value = 2,
> > > > Price.value, 0) it displays 1.00 as the price for type 2, and it should
> > > > display 2.00 for type 2.
> > > >
> > > > Any help is greatly apprciated. Also, due to the formatting specifics I
> > > > am
> > > > using single textboxes instead of a table or list.
> > > >
> > > > Digivix
> > >
> > >
> > >

Conditional Formatting - text to text

Hello,
My query returns numbers for months. Can I use conditional formatting to
convert a 1 into Jan, 2 into Feb, ect?
I know that there is a way to do this within the query itself, but am
curious as to the conditional formatting way. Thanksabunch.You could use the MonthName function to achieve this effect in the report:
=MonthName(CInt(Fields!Month.Value))
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctMonthName.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Drew" <Drew@.discussions.microsoft.com> wrote in message
news:D4481668-F461-41CF-972B-162E88F716B8@.microsoft.com...
> Hello,
> My query returns numbers for months. Can I use conditional formatting to
> convert a 1 into Jan, 2 into Feb, ect?
> I know that there is a way to do this within the query itself, but am
> curious as to the conditional formatting way. Thanksabunch.

Conditional Expression

I have an expression that looks like
this;"=Fields!Quantity.Value-Fields!QuantityAvailable.Value-Fields!QTYONORD.Value"
The field returns fine, but I want negative values to be returned as 0 when
the value is <=0.
Any ideas on how to do this? Would I add a IIF to the expression? Any help
is appreciated.
Thanks!
RyanOn May 24, 1:19 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> I have an expression that looks like
> this;"=Fields!Quantity.Value-Fields!QuantityAvailable.Value-Fields!QTYONORD.Value"
> The field returns fine, but I want negative values to be returned as 0 when
> the value is <=0.
> Any ideas on how to do this? Would I add a IIF to the expression? Any help
> is appreciated.
> Thanks!
> Ryan
This should work.
=iif(Fields!Quantity.Value-Fields!QuantityAvailable.Value-Fields!
QTYONORD.Value <= 0, 0, Fields!Quantity.Value-Fields!
QuantityAvailable.Value-Fields!QTYONORD.Value)
Regards,
Enrique Martinez
Sr. Software Consultant

conditional dynamic SQL in stored procedure, not returning any result

Created a stored procedure which returns Selected table from database.

I pass variables,according to conditions

For some reason it is not returning any result for any condition

Stored Procedure

ALTER PROCEDUREdbo.StoredProcedure

(

@.conditionvarchar(20),

@.IDbigint,

@.date1as datetime,

@.date2as datetime

)

AS

/* SET NOCOUNT ON */

IF@.conditionLIKE'all'

SELECT CllientEventDetails.*

FROM CllientEventDetails

WHERE (ClientID = @.ID)

IF@.conditionLIKE'current_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventFrom <=ISNULL(@.date1, EventFrom))AND

(EventTill >=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'past_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventTill <=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'upcoming_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE(ClientID = @.ID)AND

(EventFrom >=ISNULL(@.date1, EventFrom))

IF@.conditionLIKE''

SELECT CllientEventDetails.*

FROM CllientEventDetails

RETURN

Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants

Hi,

Please check whether the @.condition parameter you have provided can hit in the IF statements. At the end, you don't need to use RETURN if you don't return anything.

I would not suggest you put the condition in your WHERE clause, because it will return an empty result set for the condition that does not meet. And multiple result sets will be returned for all the SELECT statements.

|||

Nitin Pawar:

Created a stored procedure which returns Selected table from database.

I pass variables,according to conditions

For some reason it is not returning any result for any condition

Stored Procedure

ALTER PROCEDUREdbo.StoredProcedure

(

@.conditionvarchar(20),

@.IDbigint,

@.date1as datetime,

@.date2as datetime

)

AS

/* SET NOCOUNT ON */

IF@.conditionLIKE'all'

SELECT CllientEventDetails.*

FROM CllientEventDetails

WHERE (ClientID = @.ID)

IF@.conditionLIKE'current_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventFrom <=ISNULL(@.date1, EventFrom))AND

(EventTill >=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'past_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventTill <=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'upcoming_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE(ClientID = @.ID)AND

(EventFrom >=ISNULL(@.date1, EventFrom))

IF@.conditionLIKE''

SELECT CllientEventDetails.*

FROM CllientEventDetails

RETURN

Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants

replaceLike by= and then try .. hope it will help

Concatenation of patient name question

I have a report built that returns patient information. My code to concatenate the patient name fields (last name, first name, middle name and surname) work fine unless the middle name or surname fields are null, then it returns the concatenated patient name field as null. The code is posted below. Is there an easy method to determine if the field is null and then apply the correct logic to concatenate the name with the elements that are not null?

ltrim(rtrim(srm.patients.patient_lname))

+ ', '

+ ltrim(rtrim(srm.patients.patient_fname))

+ ' '

+ ltrim(rtrim(srm.patients.patient_mname))

+ ' '

+ ltrim(rtrim(srm.patients.patient_sname))

as SRM_PatientName

Hello,

Yes, you can. This should work.

ltrim(rtrim(srm.patients.patient_lname))

+ ', '

+ ltrim(rtrim(srm.patients.patient_fname))

+ ' '

+ isnull(ltrim(rtrim(srm.patients.patient_mname)), '')

+ ' '

+ isnull(ltrim(rtrim(srm.patients.patient_sname)), '')

as SRM_PatientName

Hope this helps.

Jarret

|||

Use the following expression,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '')

as SRM_PatientName

|||

I created a lot of reports with concatenated columns at least 20 in different combinations and all I needed was the CONVERT function. But you need ISNULL or COALESCE so here are some examples and the link for SQL Server Concatenation documentation for more options. Hope this helps.

COALESCE(a,'') + COALESCE(b,'')

ISNULL(a, ”) + ISNULL(b, ”)

http://msdn2.microsoft.com/en-us/library/ms177561.aspx

|||

Thanks Jarrett and Manivannan. I have adapted your examples and they're working great. I appreciate your helpl

Concatenating rows into one field for summary

Hi,
I am looking for a way of putting multiple values from different rows into
one field, for example:
The dataset returns 3 rows, with two fields, first name and surname like
below:
FirstName Surname
Joe Bloggs
David Beckham
Sue Smith
I want to get all of those surnames, and put them say into the final totals
of the report so they look like Bloggs,Beckham,Smith.
Can anyone see a way of archiving this.
Cheers
LukeYou cant do it in the same query because you will be using the first name if
not then you can use this method. what you can do is to create a seperate
dataset with the following method and refer this dataset in your report in
final totals
select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
you
need to fillup. :-)
any problem let me know.
Amarnath
"lukethepunk" wrote:
> Hi,
> I am looking for a way of putting multiple values from different rows into
> one field, for example:
> The dataset returns 3 rows, with two fields, first name and surname like
> below:
> FirstName Surname
> Joe Bloggs
> David Beckham
> Sue Smith
> I want to get all of those surnames, and put them say into the final totals
> of the report so they look like Bloggs,Beckham,Smith.
> Can anyone see a way of archiving this.
> Cheers
> Luke
>|||Hi,
Thanks thats almost got me what i want (i'd never heard of the COALESCE
function!)
The only problem i've got now is there are duplicate values ending up in the
end string, eg: Bloggs, Bloggs, Smith, Beckham, Beckham
Is there anyway i can keep them out using the sql, or will i have to use
some custom code in the report to keep them uniquie?
Cheers
Luke
"Amarnath" wrote:
> You cant do it in the same query because you will be using the first name if
> not then you can use this method. what you can do is to create a seperate
> dataset with the following method and refer this dataset in your report in
> final totals
> select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
> you
> need to fillup. :-)
> any problem let me know.
> Amarnath
>
> "lukethepunk" wrote:
> > Hi,
> >
> > I am looking for a way of putting multiple values from different rows into
> > one field, for example:
> >
> > The dataset returns 3 rows, with two fields, first name and surname like
> > below:
> >
> > FirstName Surname
> > Joe Bloggs
> > David Beckham
> > Sue Smith
> >
> > I want to get all of those surnames, and put them say into the final totals
> > of the report so they look like Bloggs,Beckham,Smith.
> >
> > Can anyone see a way of archiving this.
> >
> > Cheers
> > Luke
> >|||Hi luke,
unfortunetly there is no such thing like distinct coalesce.. May be what you
can do is to take a distinct and then pass the result set to coalesce. Pl try.
Amarnath
"lukethepunk" wrote:
> Hi,
> Thanks thats almost got me what i want (i'd never heard of the COALESCE
> function!)
> The only problem i've got now is there are duplicate values ending up in the
> end string, eg: Bloggs, Bloggs, Smith, Beckham, Beckham
> Is there anyway i can keep them out using the sql, or will i have to use
> some custom code in the report to keep them uniquie?
> Cheers
> Luke
>
> "Amarnath" wrote:
> > You cant do it in the same query because you will be using the first name if
> > not then you can use this method. what you can do is to create a seperate
> > dataset with the following method and refer this dataset in your report in
> > final totals
> > select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
> > you
> > need to fillup. :-)
> >
> > any problem let me know.
> >
> > Amarnath
> >
> >
> >
> > "lukethepunk" wrote:
> >
> > > Hi,
> > >
> > > I am looking for a way of putting multiple values from different rows into
> > > one field, for example:
> > >
> > > The dataset returns 3 rows, with two fields, first name and surname like
> > > below:
> > >
> > > FirstName Surname
> > > Joe Bloggs
> > > David Beckham
> > > Sue Smith
> > >
> > > I want to get all of those surnames, and put them say into the final totals
> > > of the report so they look like Bloggs,Beckham,Smith.
> > >
> > > Can anyone see a way of archiving this.
> > >
> > > Cheers
> > > Luke
> > >|||Hi,
I have managed to get it working by passing the results to some custom code,
and looping through the string and sending back only one of each.
Thanks for your help
Luke
"Amarnath" wrote:
> Hi luke,
> unfortunetly there is no such thing like distinct coalesce.. May be what you
> can do is to take a distinct and then pass the result set to coalesce. Pl try.
> Amarnath
> "lukethepunk" wrote:
> > Hi,
> >
> > Thanks thats almost got me what i want (i'd never heard of the COALESCE
> > function!)
> >
> > The only problem i've got now is there are duplicate values ending up in the
> > end string, eg: Bloggs, Bloggs, Smith, Beckham, Beckham
> >
> > Is there anyway i can keep them out using the sql, or will i have to use
> > some custom code in the report to keep them uniquie?
> >
> > Cheers
> > Luke
> >
> >
> > "Amarnath" wrote:
> >
> > > You cant do it in the same query because you will be using the first name if
> > > not then you can use this method. what you can do is to create a seperate
> > > dataset with the following method and refer this dataset in your report in
> > > final totals
> > > select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
> > > you
> > > need to fillup. :-)
> > >
> > > any problem let me know.
> > >
> > > Amarnath
> > >
> > >
> > >
> > > "lukethepunk" wrote:
> > >
> > > > Hi,
> > > >
> > > > I am looking for a way of putting multiple values from different rows into
> > > > one field, for example:
> > > >
> > > > The dataset returns 3 rows, with two fields, first name and surname like
> > > > below:
> > > >
> > > > FirstName Surname
> > > > Joe Bloggs
> > > > David Beckham
> > > > Sue Smith
> > > >
> > > > I want to get all of those surnames, and put them say into the final totals
> > > > of the report so they look like Bloggs,Beckham,Smith.
> > > >
> > > > Can anyone see a way of archiving this.
> > > >
> > > > Cheers
> > > > Luke
> > > >

Concatenating multiple returns on one field only

I have a SQL query that returns several fields from several tables, eg. Title, Subtitle, Author, Binding and Imprint. When these are returned everything seems rosy until there are two authors linked to one title. When this happens Title, Subtitle, Binding and Imprint are repeated which is not required. Is there a way to concatenate the authors from multple records to return a single title with the concatenated authors, instead of repeating titles due to multiple authors?.

Example:
A query may currently return:
Title1 - Subtitle1 - Author 1a - etc
Title1 - Subtitle1 - Author 1b - etc
Title1 - Subtitle1 - Author 1c - etc
Title2 - Subtitle2 - Author 2a - etc
Title3 - Subtitle3 - Author 3a - etc

When I would like
Title1 - Subtitle1 - Author 1a, Author 1b, Author 1c - etc
Title2 - Subtitle2 - Author 2a - etc
Title3 - Subtitle3 - Author 3a - etc

My actual SQL code, if you are interested, is:

SELECT dbo.edition.ISBN, dbo.edition.title, dbo.party.first_name+' '+dbo.party.surname as name, dbo.edition.reviews, dbo.edition.long_blurb, dbo.series.series_id, dbo.series.series_number,
dbo.series.series_title, dbo.edition.sub_title, dbo.edition.about_author, dbo.edition.short_blurb,
dbo.series.editors_affiliations, dbo.title.contents, dbo.title.affiliations, dbo.series.series_editors
FROM dbo.edition INNER JOIN
dbo.series ON dbo.edition.series_id = dbo.series.series_id INNER JOIN
dbo.title ON dbo.edition.title_id = dbo.title.title_id INNER JOIN
dbo.agreement ON dbo.edition.edition_id = dbo.agreement.edition_id INNER JOIN
dbo.role ON dbo.agreement.role_id = dbo.role.role_id INNER JOIN
dbo.party ON dbo.role.party_id = dbo.party.party_idYou could use a simple variant on fGlue() (http://www.dbforums.com/showpost.php?p=3676834&postcount=5) to do this.

-PatP