Conditionals on derived columns

Hi,

Here's my current query, which throws an error that "AgeCalc" is an invalid column in the WHERE clause:

----------
SELECT
.
.
.,
AgeCalc =
CASE
WHEN dateadd(year, datediff (year, B.DOB, B.DateIn), B.DOB) > B.DateIn
THEN datediff (year, B.DOB, B.DateIn) - 1
ELSE datediff (year, B.DOB, B.DateIn)
END

FROM
ResidentData B

WHERE
(AgeCalc >= 18)
----------

How do I do conditionals on the "AgeCalc" derived column?

Thanks.How do I do conditionals on the "AgeCalc" derived column?

Thanks.

You have to write the expression over again:
WHERE
CASE
WHEN dateadd(year, datediff (year, B.DOB, B.DateIn), B.DOB) > B.DateIn
THEN datediff (year, B.DOB, B.DateIn) - 1
ELSE datediff (year, B.DOB, B.DateIn)
END >= 18

Alternatively, write a view that includes your derived column and then you can use your column name in an expression.

I don't recommend using CASE statements in WHERE clauses. It can result in sub-optimal query execution plans.

Regards,

hmscott|||Thanks for your help - I will test the solution and see what the performance is like.

The current situation does not allow me to consider creating views, so I'll have to stick to keeping the query similar to the way it already is.|||select *
from (
SELECT ...
, AgeCalc =
CASE WHEN dateadd(year
, datediff(year, B.DOB, B.DateIn)
, B.DOB) > B.DateIn
THEN datediff(year, B.DOB, B.DateIn) - 1
ELSE datediff(year, B.DOB, B.DateIn)
END
FROM ResidentData B
) as T
WHERE AgeCalc >= 18|||Thanks guys. Both solutions worked well. I will use the second one since it's about half a second faster.

Conditionally Expand a Table

I have a table that is centered on the page. I have some conditional
hiding on the columns in the table. Is there a way to keep my table
centered when columns conditionally hide? I tried embedding the table
inside another table, but that didn't work. I also tried embedding
the table in a rectangle, but that didn't work either. I thought
someone might have a clever trick for doing this.
BillyOn Mar 12, 5:35 pm, billyburd...@.gmail.com wrote:
> I have a table that is centered on the page. I have some conditional
> hiding on the columns in the table. Is there a way to keep my table
> centered when columns conditionally hide? I tried embedding the table
> inside another table, but that didn't work. I also tried embedding
> the table in a rectangle, but that didn't work either. I thought
> someone might have a clever trick for doing this.
> Billy
As far as I know, there is not a way to handle this. If you haven't
already, you could try putting the table in a list control and set
'fit table in same page if possible' -or- you could try controlling
the resultset in the stored procedure or query used to source the
report. Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On Mar 12, 5:35 pm, billyburd...@.gmail.com wrote:
> I have a table that is centered on the page. I have some conditional
> hiding on the columns in the table. Is there a way to keep my table
> centered when columns conditionally hide? I tried embedding the table
> inside another table, but that didn't work. I also tried embedding
> the table in a rectangle, but that didn't work either. I thought
> someone might have a clever trick for doing this.
> Billy
The only other things I can think of are:
- Verify that 'Fit table on one page if possible' is selected in the
table properties.
- Put the table in a list control and verify that 'Fit table on one
page if possible' is selected for the list control properties.
- Try to control the table flow from the stored procedure or query
that is sourcing the report.
Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On Mar 12, 5:35 pm, billyburd...@.gmail.com wrote:
> I have a table that is centered on the page. I have some conditional
> hiding on the columns in the table. Is there a way to keep my table
> centered when columns conditionally hide? I tried embedding the table
> inside another table, but that didn't work. I also tried embedding
> the table in a rectangle, but that didn't work either. I thought
> someone might have a clever trick for doing this.
> Billy
The only other things I can think of are:
- Verify that 'Fit table on one page if possible' is selected in the
table properties.
- Put the table in a list control and verify that 'Fit table on one
page if possible' is selected for the list control properties.
- Try to control the table flow from the stored procedure or query
that is sourcing the report.
Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On Mar 12, 5:35 pm, billyburd...@.gmail.com wrote:
> I have a table that is centered on the page. I have some conditional
> hiding on the columns in the table. Is there a way to keep my table
> centered when columns conditionally hide? I tried embedding the table
> inside another table, but that didn't work. I also tried embedding
> the table in a rectangle, but that didn't work either. I thought
> someone might have a clever trick for doing this.
> Billy
The only other things I can think of are:
- Verify that 'Fit table on one page if possible' is selected in the
table properties.
- Put the table in a list control and verify that 'Fit table on one
page if possible' is selected for the list control properties.
- Try to control the table flow from the stored procedure or query
that is sourcing the report.
Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer

conditional widths

I have a report with columns which the user may optionally turn on. when
these columns are not displayed (hidden=true), the report fits nicely in
legal sized paper, and when the columns are displayed, it is typically
because the report will be exported to Excel.
here's my quandary: if I assign those optional columns a particular width,
the report exceeds the paper size (even though the columns are hidden),
causing extra pages to print (blank). the solution is to make those columns
0 width (or as close as Reporting Services will allow)... however, when
exporting to Excel, the columns have to be manually expanded. grr...
the answer would be conditional width sizes i.e. if a report parameter
specifies display of the columns then use one width, otherwise make the width
0... but the width property doesn't seem to accept an expression.
has anyone out there faced this same problem? any ideas of how I can
address it?
thx - ekkisI am having a similar problem, I want conditional widths so that I can use
the same report for different years. I have information broken down by weeks
and want the parameter to be the year, based on that parameter I need the
month name above the week names to change in size. Haven't figures it out
yet...

Conditional table entries and sums

What I have is 3 columns. The first and second are data from a database, the
third is a conditional difference of the two. That is, column 3 is C1-C2 if
that is > 0, else it is 0.
So I have:
C1 C2 C3
1 2 0
2 1 1
I can get that to work fine, using Iif(). The problem is that I need a total
row at the bottom of the table. Columns 1 and 2 are easy to sum, but how can
I sum the conditional entries of column 3?
Thanks!You can add a calculated field to your dataset. Check out RS Books Online
for instructions. (In the report designer, right-click in the Fields pane.)
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"RPH" <RPH@.discussions.microsoft.com> wrote in message
news:FC250591-C2B6-4399-AAC4-4515217CEAFA@.microsoft.com...
> What I have is 3 columns. The first and second are data from a database,
> the
> third is a conditional difference of the two. That is, column 3 is C1-C2
> if
> that is > 0, else it is 0.
> So I have:
> C1 C2 C3
> 1 2 0
> 2 1 1
> I can get that to work fine, using Iif(). The problem is that I need a
> total
> row at the bottom of the table. Columns 1 and 2 are easy to sum, but how
> can
> I sum the conditional entries of column 3?
> Thanks!

Conditional Sum trouble

Can someone help me figure this out? I'm trying to total up 4 columns which
have been X'd to indicate inventory types: RAWX, NSFGX, BUYX and SFGX.
I've tried:
=iif( Fields!RAWX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
=iif( Fields!NSFGX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
=iif( Fields!BUYX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
=iif( Fields!SFGX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
The reoport runs without errors, but I get a Grand Total for RAWX and 0 for
the other three columns.
If I change the expressions to:
=Sum(iif( Fields!RAWX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
=Sum(iif( Fields!NSFGX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
=Sum(iif( Fields!BUYX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
=Sum(iif( Fields!SFGX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
I get #ERROR in all columns and a warning for each field:
"The value expression for the textbox â'RAWXâ' uses an aggregate function on
data of varying data types. Aggregate functions other than First, Last,
Previous, Count, and CountDistinct can only aggregate data of a single data
type.
BKIC_PROD_AVGC and BKIC_PROD_UOH are the same data type.
What is this message trying to tell me?Did you try this for the RAWX column?
=iif( Fields!RAWX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value) *
Sum(Fields!BKIC_PROD_UOH.Value), 0)
--
I would also try to display the conditional value for the other three
to verify that those fields equal your "X" value
For example (for the NSFGX column), just put
= (Fields!NSFGX.Value = "X")
and see if it returns 'True' or 'False'
that may lead you in the right direction.
--
Also, I'm not sure how your report is set up, but did you notice that
for each of your columns, you're referencing the same values (
Fields!BKIC_PROD_AVGC.Value and Fields!BKIC_PROD_UOH.Value )?

Conditional SQL Query?

Iin SQL Server 2000 I have two tables that I need to join table
A and table B. The result set is a little tricky though. Table A has a set
of columns that are duplicated in table B. The reason is if there is no
data in these columns in table A, then that means that the data "defaults"
to the same named columns in table B. There is a many-to-one relationship
from A to B. What I would like to do is build this join query such that I
would return X number of columns using alias column names. I would like the
query to be able to populate those alias columns with the column values from
table A if there is data in those columns, but if there is no data then
populate those alias columns with the column values from table B. So in
essence, I have something like this:
Table A
ID
B_ID
A_1
A_2
Table B
ID
B_1
B_2
I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
return these alias columns:
COL_1: This retuns data from A_1 if data exists in this column, otherwise
returns data from B_1.
COL_2: This retuns data from A_2 if data exists in this column, otherwise
returns data from B_2.
Any help would be much appreciated.
Thanks!Try:
select
isnull (A_1, B_1)
, isnull (A2, B_2)
from
A
left join
B on B.ID = A.ID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"epigram" <nospam@.spammy.com> wrote in message
news:1112011902. 9f7c78e9def104fa4579b49849e7cce5@.bubbane
ws...
Iin SQL Server 2000 I have two tables that I need to join table
A and table B. The result set is a little tricky though. Table A has a set
of columns that are duplicated in table B. The reason is if there is no
data in these columns in table A, then that means that the data "defaults"
to the same named columns in table B. There is a many-to-one relationship
from A to B. What I would like to do is build this join query such that I
would return X number of columns using alias column names. I would like the
query to be able to populate those alias columns with the column values from
table A if there is data in those columns, but if there is no data then
populate those alias columns with the column values from table B. So in
essence, I have something like this:
Table A
ID
B_ID
A_1
A_2
Table B
ID
B_1
B_2
I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
return these alias columns:
COL_1: This retuns data from A_1 if data exists in this column, otherwise
returns data from B_1.
COL_2: This retuns data from A_2 if data exists in this column, otherwise
returns data from B_2.
Any help would be much appreciated.
Thanks!|||Hello, epigram!
You wrote on Mon, 28 Mar 2005 07:28:22 -0500:
e> I'd like to build a query that joins these tables on (A.B_ID = B.ID),
e> and return these alias columns:
e> COL_1: This retuns data from A_1 if data exists in this column,
e> otherwise returns data from B_1.
e> COL_2: This retuns data from A_2 if data exists in this column,
e> otherwise returns data from B_2.
SELECT B.ID,
COALESCE(A_1, B_1) as COL_1,
COALESCE(A_2, B_2) as COL_2,
FROM A JOIN B ON A.B_ID = B.ID
e> Any help would be much appreciated.
e> Thanks!
With best regards, Alexander Sinitsin. E-mail: al_sin[dog]ukr.net|||Did you read your last post?
http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"epigram" wrote:

> Iin SQL Server 2000 I have two tables that I need to join table
> A and table B. The result set is a little tricky though. Table A has a s
et
> of columns that are duplicated in table B. The reason is if there is no
> data in these columns in table A, then that means that the data "defaults"
> to the same named columns in table B. There is a many-to-one relationship
> from A to B. What I would like to do is build this join query such that I
> would return X number of columns using alias column names. I would like t
he
> query to be able to populate those alias columns with the column values fr
om
> table A if there is data in those columns, but if there is no data then
> populate those alias columns with the column values from table B. So in
> essence, I have something like this:
> Table A
> ID
> B_ID
> A_1
> A_2
> Table B
> ID
> B_1
> B_2
> I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
> return these alias columns:
> COL_1: This retuns data from A_1 if data exists in this column, otherwise
> returns data from B_1.
> COL_2: This retuns data from A_2 if data exists in this column, otherwise
> returns data from B_2.
> Any help would be much appreciated.
> Thanks!
>
>|||I couldn't. For some reason, my newsreader program was telling me that the
responses to that original post were unavailabe.
Thanks.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:90602A4C-1EB2-4693-BC4C-79D35C8FA263@.microsoft.com...
> Did you read your last post?
> [url]http://support.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserv
er.programming&mid=91835d68-563b-4260-a3ae-83dfdd2e8b2e&sloc=en-us&sloc=en-us[/url
]
>
> AMB
>
> "epigram" wrote:
>

Conditional Split Transformation

Hi

Can any one please tell me how do I give multiple conditions in Conditional Split Transformation.

Exp:

I have few columns as

ReturnSUK

TimeSUK

EntitySUK

PeriodSUK

Now the condition should be :

! ISNULL (ReturnSUK) & ! ISNULL (TimeSUK) & ! ISNULL (EntitySUK) &! ISNULL (PeriodSUK)

Please provide me the proper condition for the above mentioned requirement.

Thank you

Use two & symbols:

!ISNULL(ReturnSUK) && !ISNULL(TimeSUK) && ....|||

Thank you Its Working

If i need to give the same condition for OR (^) rather then AND (&) so the condition would be

this :

ISNULL(ReturnSUK) ^ ISNULL(TimeSUK) ^ ISNULL(BankSUK) ^ ISNULL(EntitySUK) ^ ISNULL(PeriodSUK)

or ,can you please help me in this too.

Thank you

|||Or is written by using two || symbols:

TEST1 || TEST2 || TEST3 ....

Conditional Page Breaks

Hi -
I'm trying to generate invoice reports where there are two types of
line items (with a different number of columns). I am basically looking
to have two sets of tables as follows:
Case A - at least one of each item
Table Items 1
<PAGE BREAK>
Table Items 2
Case B - items 2 exist, but no items 1
Table Items 2
Case C - items 1 exist, but no items 2
Table Items 1
I've spent hours trying to figure out how to accomplish this - I have
played with putting Table 1 in an object with conditional visibility,
but that doesn't seem to suppress a page break. I've tried a "hidden
group" but no luck there either. I've also tried having Table items 1
in a subreport.
I'm open to suggestion if you don't think I am going about this the
right way - I would really appreciate any help!
Thanks
BNo conditional page breaks. I've been asking for them since the first
release a couple years ago. It is the main reason we have not migrated. If
you can't control the layout of the report it's pointless. I've tried many
workarounds but it seems like the developers went out of their way to not let
you programatically control them. Be it if you want to remove them as in
HTML reports or insert them manually.
Good luck on your quest. If you find something let us know.
"bigbrorpi@.gmail.com" wrote:
> Hi -
> I'm trying to generate invoice reports where there are two types of
> line items (with a different number of columns). I am basically looking
> to have two sets of tables as follows:
> Case A - at least one of each item
> Table Items 1
> <PAGE BREAK>
> Table Items 2
> Case B - items 2 exist, but no items 1
> Table Items 2
> Case C - items 1 exist, but no items 2
> Table Items 1
>
> I've spent hours trying to figure out how to accomplish this - I have
> played with putting Table 1 in an object with conditional visibility,
> but that doesn't seem to suppress a page break. I've tried a "hidden
> group" but no luck there either. I've also tried having Table items 1
> in a subreport.
> I'm open to suggestion if you don't think I am going about this the
> right way - I would really appreciate any help!
> Thanks
> B
>|||See if this can help :
You can insert a rectangle into a rectangle, and set a page break in the
inner rectangle and visibility in the outer and paste this empty nested
rectangles
in between subreports or tables . If Visible expression is True, the page
break will
work. if Visible expression is False , it will ignore a Page Break. So,
this nested construction will work as a conditional page break ( for RSS
2000) .

Conditional 'Order By' statement

I have a table that stores three columns of data, namely
1. Order_ID
2. Exam_Start_Date
3. Order_Received_Date
I want to order the records as follows:
If the Exam_Start_Date is within the next 10 days or past, then order by the
exam_start_date. Otherwise order by the order_Received_Date. Therefore the
result set should display records where the exam_start_date is within next 10
days first, then display all other records.
I have tried the following SQL but it doesnt appear to work (i.e. Order of
the records is not exam_start_date (if within next 10 days), otherwise
Order_Received_Date.
select order_id, order_received_date, exam_start_date
from orders
ORDER BY CASE WHEN Exam_Start_date < '20050118' THEN Exam_Start_Date ELSE
Order_Received_Date END
Any suggestions greatly appreciated!
Wes.
On Sat, 7 Jan 2006 18:51:02 -0800, Wez wrote:

>I have a table that stores three columns of data, namely
>1. Order_ID
>2. Exam_Start_Date
>3. Order_Received_Date
>I want to order the records as follows:
>If the Exam_Start_Date is within the next 10 days or past, then order by the
>exam_start_date. Otherwise order by the order_Received_Date. Therefore the
>result set should display records where the exam_start_date is within next 10
>days first, then display all other records.
>I have tried the following SQL but it doesnt appear to work (i.e. Order of
>the records is not exam_start_date (if within next 10 days), otherwise
>Order_Received_Date.
>select order_id, order_received_date, exam_start_date
>from orders
>ORDER BY CASE WHEN Exam_Start_date < '20050118' THEN Exam_Start_Date ELSE
>Order_Received_Date END
>Any suggestions greatly appreciated!
Hi Wes,
Try if this works better:
ORDER BY CASE WHEN Exam_Start_date < '20050118'
THEN Exam_Start_Date
ELSE '20050118'
END DESC,
Order_Received_Date DESC
Or, a more generic version:
ORDER BY CASE WHEN Exam_Start_date < DATEADD(day,-10, CURRENT_TIMESTAMP)
THEN Exam_Start_Date
ELSE DATEADD(day,-10, CURRENT_TIMESTAMP)
END DESC,
Order_Received_Date DESC
Hugo Kornelis, SQL Server MVP

conditional group summing problem

I am having a little difficulty with aggregate functions in a group. I have two columns, one for the current year and one for the previous year. the functions are as follows:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C",Fields!AMOUNT.Value,0)) // Current year sales

and

=Sum(iif(Fields!YEAR_DESIGNATION.Value="P",Fields!AMOUNT.Value,0)) // Previous year sales

The YEAR_DESIGNATION field is based on a sql server case statement that returns a "P" or a "C" depending on the year (invoice date). Anyway, the data looks perfectly normal, each row has a "P" or a "C" along with a value in the amount field. For some reason if the grouping contains both P" and "C" rows, I get #error where the data should be. If a row only contains all P's or all C's the totals work. I have done this before but for some reason I can't get this to work.

P.S. All fields have data (there are no nulls)

It sounds like the AMOUNT field value is not of type System.Int32, but either Int16 or Double or Decimal. Assuming you are conditionally aggregating double values, you have to ensure that the datatypes are always the same:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C", CDbl(Fields!AMOUNT.Value), 0.0))

-- Robert

|||That was it! Thanks again. Your always a very big help Robert.

conditional group summing problem

I am having a little difficulty with aggregate functions in a group. I have two columns, one for the current year and one for the previous year. the functions are as follows:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C",Fields!AMOUNT.Value,0)) // Current year sales

and

=Sum(iif(Fields!YEAR_DESIGNATION.Value="P",Fields!AMOUNT.Value,0)) // Previous year sales

The YEAR_DESIGNATION field is based on a sql server case statement that returns a "P" or a "C" depending on the year (invoice date). Anyway, the data looks perfectly normal, each row has a "P" or a "C" along with a value in the amount field. For some reason if the grouping contains both P" and "C" rows, I get #error where the data should be. If a row only contains all P's or all C's the totals work. I have done this before but for some reason I can't get this to work.

P.S. All fields have data (there are no nulls)

It sounds like the AMOUNT field value is not of type System.Int32, but either Int16 or Double or Decimal. Assuming you are conditionally aggregating double values, you have to ensure that the datatypes are always the same:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C", CDbl(Fields!AMOUNT.Value), 0.0))

-- Robert

|||That was it! Thanks again. Your always a very big help Robert.

Conditional Formatting

I have a matrix report that has rows grouped by year, by period, and
columns grouped by sales office.
I would like to conditionally format the b/g colour of the data
values so that if for example period 1 sales in 2007 were less than
period 1 sales in 2006, the b/g would be red.
I have done this in othjer reports but how can it be done in a
matrix table?
--
-- -- -- -
Posted with NewsLeecher v3.7 Final
Web @. http://www.newsleecher.com/?usenet
-- -- -- -- -On May 7, 5:54 am, Jason (j...@.junkiesplace.net) wrote:
> I have a matrix report that has rows grouped by year, by period, and
> columns grouped by sales office.
> I would like to conditionally format the b/g colour of the data
> values so that if for example period 1 sales in 2007 were less than
> period 1 sales in 2006, the b/g would be red.
> I have done this in othjer reports but how can it be done in a
> matrix table?
> --
> -- -- -- -
> Posted with NewsLeecher v3.7 Final
> Web @.http://www.newsleecher.com/?usenet
> -- -- -- -- -
The best way to manage this would be to set a flag column in the query/
stored procedure that is sourcing the report. Then if the sales are
lower for a later year, the LowerFlag column = 1 in the query. Then in
the report, on the Layout tab, select the cells that will be affected
by the background color change and in the Properties window next to
background color, enter in an expression something like:
=iif(Fields!LowerFlag.Value = 1, "Red", "White")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Conditional Count in Detail Row - Count results across columns

Hi,
I am trying to establish a way to count the number of True/False entries
across a number of columns. That is I have a dataset returning results of
organisations (rows) and whether they have opted for a particular criteria
(columns). I want to know how many of the criteria a particular organisation
has opted for and display that on the end of the detail row for each
organisation.
Org Name Criteria1 Criteria2 Criteria3 Total Selected
ABC Ltd Yes No Yes 2
LMO Ltd No No Yes 1
XYZ Ltd Yes Yes Yes 3
Totals 2 1 3
I have managed the Totals in the footer using a conditional count.
eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
Fields!cf_subindicator111name.Value, Nothing))
I am unsure of how to do the same thing at the end of the detail line (Total
Selected) for a conditional count of the discreet fields in the dataset. I
have 89 Criteria in all to count.
I have a feeling that this needs to be done at the SQL query level creating
a running total for each organisation returned where they have opted Yes for
a Criteria but I don't know where to start with this.
Hope this is clear.
Any ideas?
SimonHi,
You were right :). You should do it in your stored procedure. Maybe you
should use PIVOT operator ans aggregation function, like COUNT. Also, you
should try the RunningValue function in SSRS in worst case scenario.
Regards,
Janos
"Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
> Hi,
> I am trying to establish a way to count the number of True/False entries
> across a number of columns. That is I have a dataset returning results of
> organisations (rows) and whether they have opted for a particular criteria
> (columns). I want to know how many of the criteria a particular
> organisation
> has opted for and display that on the end of the detail row for each
> organisation.
> Org Name Criteria1 Criteria2 Criteria3 Total Selected
> ABC Ltd Yes No Yes 2
> LMO Ltd No No Yes 1
> XYZ Ltd Yes Yes Yes 3
> Totals 2 1 3
> I have managed the Totals in the footer using a conditional count.
> eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
> Fields!cf_subindicator111name.Value, Nothing))
> I am unsure of how to do the same thing at the end of the detail line
> (Total
> Selected) for a conditional count of the discreet fields in the dataset. I
> have 89 Criteria in all to count.
> I have a feeling that this needs to be done at the SQL query level
> creating
> a running total for each organisation returned where they have opted Yes
> for
> a Criteria but I don't know where to start with this.
> Hope this is clear.
> Any ideas?
> Simon|||Thanks Janos.
The issue here is I am unclear on the specifics of how to do this, where it
sits in the select statement and what the format would be. I am only just
managing to get the select, from and where statements working at the moment
:) New to Reporting Services and SQL and could do with some pointers. I will
look up the Pivot operator and see if that gets me anywhere. Any pointers
would be appreciated.
Simon
"BERKE Janos" wrote:
> Hi,
> You were right :). You should do it in your stored procedure. Maybe you
> should use PIVOT operator ans aggregation function, like COUNT. Also, you
> should try the RunningValue function in SSRS in worst case scenario.
> Regards,
> Janos
> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
> news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
> > Hi,
> >
> > I am trying to establish a way to count the number of True/False entries
> > across a number of columns. That is I have a dataset returning results of
> > organisations (rows) and whether they have opted for a particular criteria
> > (columns). I want to know how many of the criteria a particular
> > organisation
> > has opted for and display that on the end of the detail row for each
> > organisation.
> >
> > Org Name Criteria1 Criteria2 Criteria3 Total Selected
> >
> > ABC Ltd Yes No Yes 2
> > LMO Ltd No No Yes 1
> > XYZ Ltd Yes Yes Yes 3
> >
> > Totals 2 1 3
> >
> > I have managed the Totals in the footer using a conditional count.
> >
> > eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
> > Fields!cf_subindicator111name.Value, Nothing))
> >
> > I am unsure of how to do the same thing at the end of the detail line
> > (Total
> > Selected) for a conditional count of the discreet fields in the dataset. I
> > have 89 Criteria in all to count.
> >
> > I have a feeling that this needs to be done at the SQL query level
> > creating
> > a running total for each organisation returned where they have opted Yes
> > for
> > a Criteria but I don't know where to start with this.
> >
> > Hope this is clear.
> >
> > Any ideas?
> >
> > Simon
>|||Hi Simon,
Post here a similar table structure to me, and I'll write the code for you
;). I will add some comments to my code as well.
Regards,
Janos
"Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
news:0C17ED91-B21D-4805-9B65-A200BBA595B4@.microsoft.com...
> Thanks Janos.
> The issue here is I am unclear on the specifics of how to do this, where
> it
> sits in the select statement and what the format would be. I am only just
> managing to get the select, from and where statements working at the
> moment
> :) New to Reporting Services and SQL and could do with some pointers. I
> will
> look up the Pivot operator and see if that gets me anywhere. Any pointers
> would be appreciated.
> Simon
> "BERKE Janos" wrote:
>> Hi,
>> You were right :). You should do it in your stored procedure. Maybe you
>> should use PIVOT operator ans aggregation function, like COUNT. Also, you
>> should try the RunningValue function in SSRS in worst case scenario.
>> Regards,
>> Janos
>> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
>> news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
>> > Hi,
>> >
>> > I am trying to establish a way to count the number of True/False
>> > entries
>> > across a number of columns. That is I have a dataset returning results
>> > of
>> > organisations (rows) and whether they have opted for a particular
>> > criteria
>> > (columns). I want to know how many of the criteria a particular
>> > organisation
>> > has opted for and display that on the end of the detail row for each
>> > organisation.
>> >
>> > Org Name Criteria1 Criteria2 Criteria3 Total Selected
>> >
>> > ABC Ltd Yes No Yes 2
>> > LMO Ltd No No Yes 1
>> > XYZ Ltd Yes Yes Yes 3
>> >
>> > Totals 2 1 3
>> >
>> > I have managed the Totals in the footer using a conditional count.
>> >
>> > eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
>> > Fields!cf_subindicator111name.Value, Nothing))
>> >
>> > I am unsure of how to do the same thing at the end of the detail line
>> > (Total
>> > Selected) for a conditional count of the discreet fields in the
>> > dataset. I
>> > have 89 Criteria in all to count.
>> >
>> > I have a feeling that this needs to be done at the SQL query level
>> > creating
>> > a running total for each organisation returned where they have opted
>> > Yes
>> > for
>> > a Criteria but I don't know where to start with this.
>> >
>> > Hope this is clear.
>> >
>> > Any ideas?
>> >
>> > Simon|||Janos,
Hi, not sure what you mean but my current query is as follows.
SELECT CRMAF_FilteredIncident.customeridname,
CRMAF_FilteredIncident.incidentid,
CRMAF_FilteredCF_Assessment.cf_casenumberid,
CRMAF_FilteredCF_Assessment.cf_applicationsubtypename,
CRMAF_FilteredCF_Assessment.cf_applicationtypename,
CRMAF_FilteredCF_Assessment.cf_subindicator111name,
CRMAF_FilteredCF_Assessment.cf_subindicator112name,
CRMAF_FilteredCF_Assessment.cf_subindicator121name,
CRMAF_FilteredCF_Assessment.cf_subindicator122name,
CRMAF_FilteredCF_Assessment.cf_subindicator123name,
CRMAF_FilteredCF_Assessment.cf_subindicator131name,
CRMAF_FilteredCF_Assessment.cf_subindicator141name,
CRMAF_FilteredCF_Assessment.cf_subindicator211name,
CRMAF_FilteredCF_Assessment.cf_subindicator221name,
CRMAF_FilteredCF_Assessment.cf_subindicator231name,
CRMAF_FilteredCF_Assessment.cf_subindicator232name,
CRMAF_FilteredCF_Assessment.cf_subindicator233name,
CRMAF_FilteredCF_Assessment.cf_subindicator241name,
CRMAF_FilteredCF_Assessment.cf_subindicator242name,
CRMAF_FilteredCF_Assessment.cf_subindicator243name,
CRMAF_FilteredCF_Assessment.cf_subindicator244name,
CRMAF_FilteredCF_Assessment.cf_subindicator245name,
CRMAF_FilteredCF_Assessment.cf_subindicator251name,
CRMAF_FilteredCF_Assessment.cf_subindicator252name,
CRMAF_FilteredCF_Assessment.cf_subindicator311name,
CRMAF_FilteredCF_Assessment.cf_subindicator312name,
CRMAF_FilteredCF_Assessment.cf_subindicator313name,
CRMAF_FilteredCF_Assessment.cf_subindicator321name,
CRMAF_FilteredCF_Assessment.cf_subindicator322name,
CRMAF_FilteredCF_Assessment.cf_subindicator323name,
CRMAF_FilteredCF_Assessment.cf_subindicator331name,
CRMAF_FilteredCF_Assessment.cf_subindicator332name,
CRMAF_FilteredCF_Assessment.cf_subindicator341name,
CRMAF_FilteredCF_Assessment.cf_subindicator342name,
CRMAF_FilteredCF_Assessment.cf_assessmenttypename,
CRMAF_FilteredCF_Assessment.cf_subindicator411name,
CRMAF_FilteredCF_Assessment.cf_subindicator421name,
CRMAF_FilteredCF_Assessment.cf_subindicator422name,
CRMAF_FilteredCF_Assessment.cf_subindicator423name,
CRMAF_FilteredCF_Assessment.cf_subindicator431name,
CRMAF_FilteredCF_Assessment.cf_subindicator441name,
CRMAF_FilteredCF_Assessment.cf_subindicator511name,
CRMAF_FilteredCF_Assessment.cf_subindicator512name,
CRMAF_FilteredCF_Assessment.cf_subindicator513name,
CRMAF_FilteredCF_Assessment.cf_subindicator514name,
CRMAF_FilteredCF_Assessment.cf_subindicator521name,
CRMAF_FilteredCF_Assessment.cf_subindicator522name,
CRMAF_FilteredCF_Assessment.cf_subindicator523name,
CRMAF_FilteredCF_Assessment.cf_subindicator524name,
CRMAF_FilteredCF_Assessment.cf_subindicator525name,
CRMAF_FilteredCF_Assessment.cf_subindicator6110name,
CRMAF_FilteredCF_Assessment.cf_subindicator6111name,
CRMAF_FilteredCF_Assessment.cf_subindicator611name,
CRMAF_FilteredCF_Assessment.cf_subindicator612name,
CRMAF_FilteredCF_Assessment.cf_subindicator613name,
CRMAF_FilteredCF_Assessment.cf_subindicator614name,
CRMAF_FilteredCF_Assessment.cf_subindicator615name,
CRMAF_FilteredCF_Assessment.cf_subindicator616name,
CRMAF_FilteredCF_Assessment.cf_subindicator617name,
CRMAF_FilteredCF_Assessment.cf_subindicator618name,
CRMAF_FilteredCF_Assessment.cf_subindicator619name,
CRMAF_FilteredCF_Assessment.cf_subindicator621name,
CRMAF_FilteredCF_Assessment.cf_subindicator622name,
CRMAF_FilteredCF_Assessment.cf_subindicator623name,
CRMAF_FilteredCF_Assessment.cf_subindicator624name,
CRMAF_FilteredCF_Assessment.cf_subindicator625name,
CRMAF_FilteredCF_Assessment.cf_subindicator626name,
CRMAF_FilteredCF_Assessment.cf_subindicator631name,
CRMAF_FilteredCF_Assessment.cf_subindicator632name,
CRMAF_FilteredCF_Assessment.cf_subindicator633name,
CRMAF_FilteredCF_Assessment.cf_subindicator634name,
CRMAF_FilteredCF_Assessment.cf_subindicator641name,
CRMAF_FilteredCF_Assessment.cf_subindicator642name,
CRMAF_FilteredCF_Assessment.cf_subindicator643name,
CRMAF_FilteredCF_Assessment.cf_subindicator651name,
CRMAF_FilteredCF_Assessment.cf_subindicator652name,
CRMAF_FilteredCF_Assessment.cf_subindicator711name,
CRMAF_FilteredCF_Assessment.cf_subindicator712name,
CRMAF_FilteredCF_Assessment.cf_subindicator713name,
CRMAF_FilteredCF_Assessment.cf_subindicator714name,
CRMAF_FilteredCF_Assessment.cf_subindicator721name,
CRMAF_FilteredCF_Assessment.cf_subindicator731name,
CRMAF_FilteredCF_Assessment.cf_subindicator741name,
CRMAF_FilteredCF_Assessment.cf_subindicator742name,
CRMAF_FilteredCF_Assessment.cf_subindicator743name,
CRMAF_FilteredCF_Assessment.cf_subindicator811name,
CRMAF_FilteredCF_Assessment.cf_subindicator812name,
CRMAF_FilteredCF_Assessment.cf_subindicator821name,
CRMAF_FilteredCF_Assessment.cf_subindicator911name,
CRMAF_FilteredCF_Assessment.cf_subindicator921name,
CRMAF_FilteredCF_Assessment.cf_subindicator931name,
CRMAF_FilteredCF_Assessment.cf_subindicator941name,
CRMAF_FilteredCF_Assessment.cf_subindicator951name,
CRMAF_FilteredCF_Assessment.cf_subindicator961name,
CRMAF_FilteredIncident.cf_casesubtypename
FROM FilteredCF_Assessment AS CRMAF_FilteredCF_Assessment INNER JOIN
FilteredIncident AS CRMAF_FilteredIncident ON
CRMAF_FilteredCF_Assessment.cf_casenumberid =CRMAF_FilteredIncident.incidentid
WHERE (CRMAF_FilteredIncident.cf_casesubtypename = 'Assessment')
What I want to do is a conditional count where the subindicator fields are
equal to yes and be able to total those at the end of each row.
Thanks.
"BERKE Janos" wrote:
> Hi Simon,
> Post here a similar table structure to me, and I'll write the code for you
> ;). I will add some comments to my code as well.
> Regards,
> Janos
>
> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
> news:0C17ED91-B21D-4805-9B65-A200BBA595B4@.microsoft.com...
> > Thanks Janos.
> >
> > The issue here is I am unclear on the specifics of how to do this, where
> > it
> > sits in the select statement and what the format would be. I am only just
> > managing to get the select, from and where statements working at the
> > moment
> > :) New to Reporting Services and SQL and could do with some pointers. I
> > will
> > look up the Pivot operator and see if that gets me anywhere. Any pointers
> > would be appreciated.
> >
> > Simon
> >
> > "BERKE Janos" wrote:
> >
> >> Hi,
> >>
> >> You were right :). You should do it in your stored procedure. Maybe you
> >> should use PIVOT operator ans aggregation function, like COUNT. Also, you
> >> should try the RunningValue function in SSRS in worst case scenario.
> >>
> >> Regards,
> >>
> >> Janos
> >>
> >> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
> >> news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
> >> > Hi,
> >> >
> >> > I am trying to establish a way to count the number of True/False
> >> > entries
> >> > across a number of columns. That is I have a dataset returning results
> >> > of
> >> > organisations (rows) and whether they have opted for a particular
> >> > criteria
> >> > (columns). I want to know how many of the criteria a particular
> >> > organisation
> >> > has opted for and display that on the end of the detail row for each
> >> > organisation.
> >> >
> >> > Org Name Criteria1 Criteria2 Criteria3 Total Selected
> >> >
> >> > ABC Ltd Yes No Yes 2
> >> > LMO Ltd No No Yes 1
> >> > XYZ Ltd Yes Yes Yes 3
> >> >
> >> > Totals 2 1 3
> >> >
> >> > I have managed the Totals in the footer using a conditional count.
> >> >
> >> > eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
> >> > Fields!cf_subindicator111name.Value, Nothing))
> >> >
> >> > I am unsure of how to do the same thing at the end of the detail line
> >> > (Total
> >> > Selected) for a conditional count of the discreet fields in the
> >> > dataset. I
> >> > have 89 Criteria in all to count.
> >> >
> >> > I have a feeling that this needs to be done at the SQL query level
> >> > creating
> >> > a running total for each organisation returned where they have opted
> >> > Yes
> >> > for
> >> > a Criteria but I don't know where to start with this.
> >> >
> >> > Hope this is clear.
> >> >
> >> > Any ideas?
> >> >
> >> > Simon
> >>
>

Conditional Computed Columns

I have a table with three columns namely "A","B", anc "C".
Column C is a computed column which is derived from Column "B".Something like " IF B < 100 then C=B*0.5 ELSE C=B*0.25"
How do I make it possible in Sql Server?Is there a way of doing this?
Thanks!Use the case when statement. So for your example:

select a,b, case when b < 100 then b*.5 else b*.25 end as c
from ...|||To make ColC a computed column
drop table test
go
create table test
(
colA int,
colB int,
colC as case when ColB < 100 then ColB*0.5 else ColB*0.25 end
)

go
insert test (ColA,ColB) values (100,80)
insert test (ColA,ColB) values (100,100)
go
select *
from test

Output

colA colB colC
---- ---- -----
100 80 40.00
100 100 25.00|||It's working now|||humm.. This really new for me.

Would this create a Trigger??

Originally posted by achorozy
To make ColC a computed column
drop table test
go
create table test
(
colA int,
colB int,
colC as case when ColB < 100 then ColB*0.5 else ColB*0.25 end
)

go
insert test (ColA,ColB) values (100,80)
insert test (ColA,ColB) values (100,100)
go
select *
from test

Output

colA colB colC
---- ---- -----
100 80 40.00
100 100 25.00|||No it doesn't create a trigger, however it does store the computed information in syscomments

Conditional Column Mapping

Hey all! I have a bunch of questions, but let's start with this one:

Incoming from my flat file, I have two columns:

employee_id
dept_id

These indicate who did the work, and for which department (people can work for more than one department). In my destination table, I have the following two columns:

employee_id_sales
employee_id_wrhs

I want to map the employee id either to employee_id_sales or employee_id_wrhs, depending on the dept_id from the flat file.

How do I specify conditional column mapping?

I'm really new to SSIS, so I might be missing something obvious.

Thanks!

-- Jim
I'd use a derived column transformation...

New column name: employee_id_sales
Expression: dept_id == 1 ? employee_id : NULL(DT_WSTR,20)

New column name: employee_id_wrhs
Expression: dept_id == 2 ? employee_id : NULL(DT_WSTR,20)

The NULL() function should represent whatever data type you are really working with. I just used DT_WSTR as an example.

Then coming out of the derived column transformation, you have your two columns that you simply map to the similarly named column in the destination.|||Outstanding! Thanks!

Cheers!

-- jim

Concating text columns

I have a table with a text column
I want to select that column and add some text to the result
e.g.
select textcolumn + 'Added Text' From xTable

but this fails because one can not use + or concat on text columns
i get around this by using cast as varchar(1024)
e.g.
select cast(textcolumn as varchar(1024)) + 'Added Text' From xTable

but this would cut the text column after 1024 chars

is there a way around this and not limiting the length of the text columnI'm at home and not a work at the moment so I can't check for sure (or rather I'm not going to), but can't you omit the length of the varchar in the cast statement??|||select cast(textcolumn as varchar) + 'Added Text' From xTable
or
select convert(varchar, textcolumn) + 'Added Text' From xTable

works for my SQL2000, otherwise, you can just trim out the trailing spaces|||Just be aware that the max length of varchar is 8000.

Originally posted by shianmiin
select cast(textcolumn as varchar) + 'Added Text' From xTable
or
select convert(varchar, textcolumn) + 'Added Text' From xTable

works for my SQL2000, otherwise, you can just trim out the trailing spaces

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advance
perspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4
|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advanceperspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>|||On Apr 30, 9:13 am, "perspolis" <reza...@.hotmail.com> wrote:
> I want to do that for many levels as is not for 2 rows.
> "Ed Enstrom" <nos...@.invalid.net> wrote in message
> news:np8Zh.98$eH4.18@.newsfe12.lga...
>
>
>
>
>
>
>
> - Show quoted text -
If you are using SQL Server 2005 CTE with recursive query.
with temp as
(select convert(varchar(50),'') + convert(varchar(50),'')
name ,parentcode,code from table1 where code =4
union all
select convert(varchar(50),t1.name)+convert(varchar(50),t.name) as
name ,t1.parentcode,t1.code
from table1 t1 inner join temp t on t.parentcode = t1.code)
select name from temp where parentcode is null
Regards
Amish shah
http://shahamishm.tripod.com

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advanceperspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
>> this is my DDL:
>> CREATE TABLE [dbo].[Table1] (
>> [Code] [int] IDENTITY (1, 1) NOT NULL ,
>> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [ParentCode] [int] NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Table1] ADD
>> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>> (
>> [Code]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Table1] ADD
>> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
>> (
>> [ParentCode]
>> ) REFERENCES [dbo].[Table1] (
>> [Code]
>> )
>> I want to concat Column of Name:
>> Code Name ParentCode
>> 1 test NULL
>> 2 book NULL
>> 3 Cake 1
>> 4 Mouse 3
>> I want to concat column of Name for Code=4 and output will be: testCake
>> thanks in advance
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>|||On Apr 30, 9:13 am, "perspolis" <reza...@.hotmail.com> wrote:
> I want to do that for many levels as is not for 2 rows.
> "Ed Enstrom" <nos...@.invalid.net> wrote in message
> news:np8Zh.98$eH4.18@.newsfe12.lga...
>
> > perspolis wrote:
> >> this is my DDL:
> >> CREATE TABLE [dbo].[Table1] (
> >> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> >> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> >> [ParentCode] [int] NULL
> >> ) ON [PRIMARY]
> >> GO
> >> ALTER TABLE [dbo].[Table1] ADD
> >> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> >> (
> >> [Code]
> >> ) ON [PRIMARY]
> >> GO
> >> ALTER TABLE [dbo].[Table1] ADD
> >> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> >> (
> >> [ParentCode]
> >> ) REFERENCES [dbo].[Table1] (
> >> [Code]
> >> )
> >> I want to concat Column of Name:
> >> Code Name ParentCode
> >> 1 test NULL
> >> 2 book NULL
> >> 3 Cake 1
> >> 4 Mouse 3
> >> I want to concat column of Name for Code=4 and output will be: testCake
> >> thanks in advance
> > SELECT c.[Name] + b.[Name] AS ConcatName
> > FROM Table1 AS a
> > JOIN Table1 AS b ON a.ParentCode = b.Code
> > JOIN Table1 AS c ON b.ParentCode = c.Code
> > WHERE a.Code = 4- Hide quoted text -
> - Show quoted text -
If you are using SQL Server 2005 CTE with recursive query.
with temp as
(select convert(varchar(50),'') + convert(varchar(50),'')
name ,parentcode,code from table1 where code =4
union all
select convert(varchar(50),t1.name)+convert(varchar(50),t.name) as
name ,t1.parentcode,t1.code
from table1 t1 inner join temp t on t.parentcode = t1.code)
select name from temp where parentcode is null
Regards
Amish shah
http://shahamishm.tripod.com

Concatenation of two columns

Hi all,

I am trying to concatenate two columns First_Name and Last_Name to display as Name in a View. I used the following statement but the result only shows the First_Name.

Select First_Name + Last_Name as Name from Address;

How do i combine the two columns??

SQL 2000 running on Win 2000

Thanks in advance.This is only a guess, but:SELECT RTrim(first_name) + ',' + last_name
FROM Address-PatP|||Thank you for the prompt reply, it worked!