Combining two Tables into one TempTable, with a condition.

Hi,

I'm very new to Sql, and need to combine information from two tables into one temp table. All tables have the same structure.

The problem is I want to set a condition that if Field 1 is already in the TempTable, dont include that field from the second table. Along the lines of

Table1 Description = Blue, if Table 2 Description = Blue, dont include this row.

I have been trying to use INSERT..SELECT and UNION. , the only problem I have is that I cannot come up with a working conditional statement to prevent both complete sets of data being written to the TempTable.

I'm using Sql Server 2005 deveoper Edition and VS2005Pro.

Any ideas on how to get around this would be appreciated.

Tailor

Hi Tailor,

I'm not 100% clear on what your problem is. Is it that you wanted to union two tables and insert this result into a temp table only if data for a particular column (pk I presume) doesn't already exist in the temp table, or is it that you want to union the result of two tables but only select (and thus insert) distinct rows?

Can you provide a bit more info?

Cheers

Rob

|||

Hi Rob,

Thanks for your reply, I'll try to make this a little clearer with some code.

INSERT INTO tempTable (Description,Id,Reference)

SELECT Description,Id,Reference

From Table1

Where (Id = 'STR001')

UNION

SELECT Description,Id,Reference

FROM Table2

WHERE (Id = 'STR001') AND Description != Table1.Description.

Without the AND statement, all data from both tables is inserted in tempTable, however with the And statement, I get the error message.

'The multi-part identifier Table1.Description could not be bound'

If the description from Table2, matches the Description in Table1, I dont want to include the row in the TempTable.

Sorry if i'm not too clear. At 64, trying to learn VS2005, Sql2005, and intergrating excel in my code, and write a reasonable sized application, all in six months is not something I can recommend. There is just so much to learn, and I dont have a good grasp of a lot of the basics.

If you can suggest a workable solution to my problem, it would be much appreciated.

John

|||

INSERT INTO tempTable (Description,Id,Reference)

SELECT Description,Id,Reference

From Table1

Where (Id = 'STR001')

INSERT INTO tempTable (Description,Id,Reference)

SELECT Description,Id,Reference

FROM Table2 t left outer join temptable tt on t.description = tt.description

WHERE (t.Id = 'STR001') AND tt.description is null

Regards

|||

This is a bit of a messy solution

either

1. Insert the data from both tables with a UNION, but place the UNION statement into a subquery and alias the columns to be inserted.

2. Insert data from table1. Then do the same insert from table2 where NOT EXISTS in the temp table (or table1, either will work). This is a common task you will need to learn to do in SQL Server

|||

Hi JMattias and SHughes,

Thank you both, very much, for your replies. It not only solved the problem, I learnt a lot more on the way through.

Your assistance is greatly appreciated.

John

sqlsql

combining select staements

Folks

I have three select statements. I want to display q_text based on
the respective where condition. How do i combine these three and write
as one select statement.

select q_text Questions from question
where new_account_flag = '1'

select q_text Questions from question
where disc_account_flag = '1'

select q_text Questions from question
where disc_account_flag = '0'

Remember that all the queries returns more than 1 value.

I tried to use
select (query1),
(query2),
(query3)
but because it is returning more than one value, there is error.

Can any suggest me any other syntax??

Thanksselect [q_text Questions] from question
where new_account_flag = '1'
UNION
select [q_text Questions] from question
where disc_account_flag = '1'
UNION
select [q_text Questions] from question
where disc_account_flag = '0'|||I think it should be UNION ALL, since either condition may produce duplicate results. UNION will eliminate duplicates.sqlsql

Column Header Color

Is there a way in SSRS to conditionally set the color of a column?

I want to do something like:

=IFF(some condition, Color(Fields!Date, "Green"), Color(Fields!Date, "Red"))

Yes you go into the Color property of the text box in the Detail row of the table and set the color to an <expression..> then in the dialog that comes up enter somthing like this...

=iif(Fields!Name.Value = "Smith", "Green", "Red")

if the name = smith make the color green other wise make it red

the key point being that you go to the property and set the property equal to an expression.

if you need more value/color options you can put in a little code in the report that you can reference from the expression.

1. right click in the little box at the top left of the report (intersection of the vertical and horizontal rulers) in the designer and choose properties.

2. select the code tab

3. type in some VB code

ex.

Public Function GetColor(ByVal sname as String) as String
IF sname = "Smith" Then
Return "Blue"
End IF
IF sname = "Jones" Then
Return "Black"
End IF
IF sname = "Woods" Then
Return "Red"
End IF
End Function

4. set the color or background property to =code.GetColor(Fields!Name.Value)

================= off topic FYI

here is another example of a little code for the report that can be used in an expression

this is used in a calulated field in a dataset =Code.GetBox(Fields!Percentile.Value,Fields!Importance.Value)

Public Function GetBox(ByVal PercentImp As Integer, ByVal Important As Integer) As Integer

Select Case Important
Case 1
Select Case PercentImp
Case 0 To 39
GetBox = 1
Case 40 To 75
GetBox = 2
Case Is > 75
GetBox = 3
End Select

Case 2
Select Case PercentImp
Case 0 To 39
GetBox = 4
Case 40 To 75
GetBox = 5
Case Is > 75
GetBox = 6
End Select
Case 3
Select Case PercentImp
Case 0 To 39
GetBox = 7
Case 40 To 75
GetBox = 8
Case Is > 75
GetBox = 9
End Select

End Select

End Function

|||You've made my day. Totally sweet. Thank you!!!

Column Header Color

Is there a way in SSRS to conditionally set the color of a column?

I want to do something like:

=IFF(some condition, Color(Fields!Date, "Green"), Color(Fields!Date, "Red"))

Yes you go into the Color property of the text box in the Detail row of the table and set the color to an <expression..> then in the dialog that comes up enter somthing like this...

=iif(Fields!Name.Value = "Smith", "Green", "Red")

if the name = smith make the color green other wise make it red

the key point being that you go to the property and set the property equal to an expression.

if you need more value/color options you can put in a little code in the report that you can reference from the expression.

1. right click in the little box at the top left of the report (intersection of the vertical and horizontal rulers) in the designer and choose properties.

2. select the code tab

3. type in some VB code

ex.

Public Function GetColor(ByVal sname as String) as String
IF sname = "Smith" Then
Return "Blue"
End IF
IF sname = "Jones" Then
Return "Black"
End IF
IF sname = "Woods" Then
Return "Red"
End IF
End Function

4. set the color or background property to =code.GetColor(Fields!Name.Value)

================= off topic FYI

here is another example of a little code for the report that can be used in an expression

this is used in a calulated field in a dataset =Code.GetBox(Fields!Percentile.Value,Fields!Importance.Value)

Public Function GetBox(ByVal PercentImp As Integer, ByVal Important As Integer) As Integer

Select Case Important
Case 1
Select Case PercentImp
Case 0 To 39
GetBox = 1
Case 40 To 75
GetBox = 2
Case Is > 75
GetBox = 3
End Select

Case 2
Select Case PercentImp
Case 0 To 39
GetBox = 4
Case 40 To 75
GetBox = 5
Case Is > 75
GetBox = 6
End Select
Case 3
Select Case PercentImp
Case 0 To 39
GetBox = 7
Case 40 To 75
GetBox = 8
Case Is > 75
GetBox = 9
End Select

End Select

End Function

|||You've made my day. Totally sweet. Thank you!!!

Colors hadling with ReportViewer

Hello,
My doubt is how to change in a LocalReport environment the text color of a
textbox of a report depending on certain condition at runtime.
I will thank any help.
Sincerely,
Arturo Carrión
at Tiempo HardHello Arturo,
My understanding of this issue is: You have a reportviewer control using
the Local Report. And you want to control the color of a textbox in the
runtime. If I misunderstood your concern, please feel free to let me know.
Based on my research, you could only control the color in the report. You
could use the Expression to control the color.
Expressions Commonly Used in Reporting Services
http://msdn2.microsoft.com/en-us/ms157328.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Wei,
Until this moment, I have not been able to enter the link that you gave me
because it gives an error of "Server is too busy".
I will try it later, then I tell you.
Sincerely,
Arturo Carrión
at Tiempo Hard
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:bmTJvs%23THHA.1860@.TK2MSFTNGHUB02.phx.gbl...
> Hello Arturo,
> My understanding of this issue is: You have a reportviewer control using
> the Local Report. And you want to control the color of a textbox in the
> runtime. If I misunderstood your concern, please feel free to let me know.
> Based on my research, you could only control the color in the report. You
> could use the Expression to control the color.
> Expressions Commonly Used in Reporting Services
> http://msdn2.microsoft.com/en-us/ms157328.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Jackpot!
I realized that most properties of a textbox accept an expression instead a
value.
Thanks, Wei
<tiempotecnologia@.newsgroup.nospam> escribió en el mensaje
news:eaynrmEUHHA.1636@.TK2MSFTNGP02.phx.gbl...
> Thanks Wei,
> Until this moment, I have not been able to enter the link that you gave me
> because it gives an error of "Server is too busy".
> I will try it later, then I tell you.
> Sincerely,
> Arturo Carrión
> at Tiempo Hard
> "Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
> news:bmTJvs%23THHA.1860@.TK2MSFTNGHUB02.phx.gbl...
>> Hello Arturo,
>> My understanding of this issue is: You have a reportviewer control using
>> the Local Report. And you want to control the color of a textbox in the
>> runtime. If I misunderstood your concern, please feel free to let me
>> know.
>> Based on my research, you could only control the color in the report. You
>> could use the Expression to control the color.
>> Expressions Commonly Used in Reporting Services
>> http://msdn2.microsoft.com/en-us/ms157328.aspx
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>|||Hello Arturo,
Glad to hear the information is helpful.
If you have any question, please feel free to let me know.
Have a nice day!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Color Expressions

Hey everyone,

I know that you can make an expression that will make it one color if a certain condition is met and a different one if it is not but is there anyway to make it so that if a number is less than another it's one color, if it's greater it's a different color and if they're equal it's a third color? Thanks for the help.

-Keith

Yes. You want to use the switch command in your expression. You can find this and other examples in the following article:

http://technet.microsoft.com/en-us/library/ms157328.aspx

Simone

collecting values into a variable in a loop

Dear All,

Im trying to collect values from a query into a single variable within a loop, like so:

WHILE condition is true
BEGIN

SET @.intLoop = @.intLoop + 1

@.myString = @.myString + , + (SELECT companyName FROM @.tblTheseComp WHERE id = @.intLoop

END

For some reason though the @.myString does not collect up the values, but will equal NULL at the end of the loop.

If however I simple do

WHILE condition is true
BEGIN

SET @.intLoop = @.intLoop + 1

@.myString = (SELECT companyName FROM @.tblTheseComp WHERE id = @.intLoop
END

Then I get the last value from the query as expected.

Can anyone explain why this might be?

Thanks in advance!Hi

Your variable is null initially -
NULL +'Something' = NULL

HTH|||Also - you don't need a loop:

DECLARE @.myString AS VarChar(1000)

SELECT @.myString = ''

SELECT @.myString = @.myString + ', ' + companyName
FROM @.tblTheseComp

SELECT @.myString = SUBSTRING(@.myString, 3, LEN(@.myString)-2)

SELECT @.myString|||This code will handle if any of the companyName is null.

DECLARE @.myString AS VarChar(1000)
SELECT @.myString = ''

SELECT @.myString = @.myString + coalesce(companyName+',','')
FROM @.tblTheseComp

SELECT @.myString = SUBSTRING(@.myString, 1, LEN(@.myString)-1)

SELECT @.myString|||Nice one thanks a lot! A final obsticle and I'm there!

As I need to send a list of action items to my users, I need to add a carriage return at the end of each line, any idea how I would do that, the mail is sent as a text file so I assume I'll need the CTRL + M combination or in ASCII:

Char: CR, Oct:15, Dec:13, Hex:d

Any ideas anyone?|||Hmm wasnt that tricky I needed CHAR(13) + CHAR(10) cool, thanks for all the help again!|||add char(13) with ur string.
eg:

PRINT 'First line.' + CHAR(13) + CHAR(10) + 'Second line.'|||Thanks yes I noticed I had to include a string to add CHAR(13) + CHAR(10) to. Cheers!