One Table, Two Condition, Display Result As One Table

Nov 29, 2004

I got one table with 3 columns = Column1, Column2, Column3





Sample Table





Column1 | Column2 | Column3


------------------------------------


A | 12 | 0


A | 13 | 2


B | 12 | 5


C | 5 | 0





Select Column1, Column2, Column3 as New1


Where Column1 = A AND Column2 = 12 AND Column3 = 0





Select Column1, Column2, Column3 as New2


Where Column1 = A AND Column2 = 12 AND Column3 >0





The only difference is one condition Column3 = 0 and another one Column3 > 0. This two condition is not an "AND" condition... but just two separate information need to be display in one table.


So how do i display the result in one table where the new Output will be in this manner





Column1 | Column2 | New1 | New2|





Thanks

View 3 Replies


ADVERTISEMENT

How Can I Send Row Data To A SQL Database Table Via VB 2008, And Permanently Save And Display The Result?

Mar 11, 2008

This is related to:
How can I make some graphics drawings stick while others disappear?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2905460&SiteID=1


Except that now I am trying to connect and update to an Microsoft SQL Server Database File (SqlClient) via VB 2008 Express; specifically a table called €œHexMap€? that contains some columns that I am ready to insert some row data into. Here is what my program should do:

As I hover over a hexagon map of the US a red flickering hexagon follows the location of my mouse cursor. If I click on a given hexagon, the program draws a permanent blue hexagon, and sends a new set of row data into my database. Such information as the name of the state, row, column, center x, and center y, etc. Here is a quick snapshot of this program in action:

http://farm4.static.flickr.com/3128/2325675990_4155edbdee_o.jpg
-sorry, I didn't capture the mouse cursor inside the red hexagon

I think I am missing something since I appear to be able to connect successfully to the database table. Unfortunately, I never see the changes in the database, when I try to Show Table Data (via Database Explorer). I am hoping someone will review my code snippet (below) and tell me what I am missing. What happens when I run this code is that it acts like it works just fine, except that I have no indication that any changes were actually affected.




Code Snippet
'======================================================================================
Dim CN As New SqlClient.SqlConnection()
Dim da As New SqlClient.SqlDataAdapter

'Consider using Me._adapter that is used already

CN.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Mapboard.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
CN.Open()

'Use the following code to verify that a connection to the database has achieved
If CN.State = ConnectionState.Open Then


MsgBox("Workstation " & CN.WorkstationId & "connected to database " & CN.Database & "on the " & CN.DataSource & " server")
End If

Try

Catch ex As Exception MsgBox("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR" & vbCrLf & ex.Message)
End Try

'use the Connection object to execute statements
'against the database and then close the connection
da = New SqlClient.SqlDataAdapter("select * from HexMap order by Territory", CN)

If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================

Dim rows As Integer

rows = 0

Dim CMD As New SqlCommand("INSERT HexMap (Hexagon, HexRow, HexCol, HexX, HexY, Territory) VALUES(HexCounter, CaptureRow,CaptureCol,Hx,Hy,Territory_ComboBox1.Text)", CN)

CN.Open()

rows = CMD.ExecuteNonQuery

If rows = 1 Then
MsgBox("Table HexMap updated successfully")
Else
MsgBox("Failed to update the HexMap table")
End If


If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================



Thanks for reviewing my code.

Technozoide

View 1 Replies View Related

How To Search Multiple Table Which Table Name Is Store In Another Table And Join The Result Together?

Dec 1, 2006

I have one control table to store all related table name
 Table ID                   TableName
     1                           TableA
     2                           TableB
 
In Table A:
RecordID                Value
     1                         1
     2                         2
     3                         3
 
In Table B:
RecordID             Value
    1                         1
    2                         2
    3                         3
 How can I get the result by select the Table list first and then combine the data in table A and table B?
 
Thank you!

View 1 Replies View Related

An Easy Way To Reference The Nth Row From A Table Meeting A Condition X, And The Mth Row From The Same Table Meeting Condition Y

May 10, 2006

Hi

I am developing a scientific application (demographic forecasting) and have a situation where I need to update a variety of rows, say the ith, jth and kth row that meets a particular condition, say, x.

I also need to adjust rows, say mth and nth that meet condition , say y.

My current solution is laborious and has to be coded for each condition and has been set up below (If you select this entire piece of code it will create 2 databases, each with a table initialised to change the 2nd,4th,8th and 16th rows, with the first database ignoring the condition and with the second applying the change only to rows with 'type1=1' as the condition.)

This is an adequate solution, but if I want to change the second row meeting a second condition, say 'type1=2', I would need to have another WITH...SELECT...INNER JOIN...UPDATE and I'm sure this would be inefficient.

Would there possibly be a way to introduce a rank by type into the table, something like this added column which increments for each type:







ID
Int1
Type1
Ideal Rank by Type

1
1
1
1

2
1
1
2

3
2
1
3

4
3
1
4

5
5
1
5

6
8
2
1

7
13
1
6

8
21
1
7

9
34
1
8

10
55
2
2

11
89
1
9

12
144
1
10

13
233
1
11

14
377
1
12

15
610
1
13

16
987
2
3

17
1597
1
14

18
2584
1
15

19
4181
1
16

20
6765
1
17

The solution would then be a simple update based on an innerjoin reflecting the condition and rank by type...

I hope this posting is clear, albeit long.

Thanks in advance

Greg

PS The code:

USE

master

GO

CREATE DATABASE CertainRowsToChange

GO

USE CertainRowsToChange

GO

CREATE TABLE InitialisedValues

(

InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE PROCEDURE Initialise

AS

BEGIN

INSERT INTO InitialisedValues (Int1 )

SELECT 2

INSERT INTO InitialisedValues (Int1 )

SELECT 4

INSERT INTO InitialisedValues (Int1 )

SELECT 8

INSERT INTO InitialisedValues (Int1 )

SELECT 16

END

GO

EXEC Initialise

/*=======================================================*/

CREATE TABLE AllRows

(

AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE TABLE RowsToChange

(

RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE PROCEDURE InitialiseRowsToChange

AS

BEGIN

INSERT INTO RowsToChange (Int1 )

SELECT 2

INSERT INTO RowsToChange (Int1 )

SELECT 4

INSERT INTO RowsToChange (Int1 )

SELECT 8

INSERT INTO RowsToChange (Int1 )

SELECT 16

END

GO

EXEC InitialiseRowsToChange

GO

CREATE PROCEDURE PopulateAllRows

AS

BEGIN

INSERT INTO AllRows (Int1 )

SELECT 1

INSERT INTO AllRows (Int1 )

SELECT 1

INSERT INTO AllRows (Int1 )

SELECT 2

INSERT INTO AllRows (Int1 )

SELECT 3

INSERT INTO AllRows (Int1 )

SELECT 5

INSERT INTO AllRows (Int1 )

SELECT 8

INSERT INTO AllRows (Int1 )

SELECT 13

INSERT INTO AllRows (Int1 )

SELECT 21

INSERT INTO AllRows (Int1 )

SELECT 34

INSERT INTO AllRows (Int1 )

SELECT 55

INSERT INTO AllRows (Int1 )

SELECT 89

INSERT INTO AllRows (Int1 )

SELECT 144

INSERT INTO AllRows (Int1 )

SELECT 233

INSERT INTO AllRows (Int1 )

SELECT 377

INSERT INTO AllRows (Int1 )

SELECT 610

INSERT INTO AllRows (Int1 )

SELECT 987

INSERT INTO AllRows (Int1 )

SELECT 1597

INSERT INTO AllRows (Int1 )

SELECT 2584

INSERT INTO AllRows (Int1 )

SELECT 4181

INSERT INTO AllRows (Int1 )

SELECT 6765

END

GO

EXEC PopulateAllRows

GO

SELECT * FROM AllRows

GO

WITH Temp(OrigID)

AS

(

SELECT OrigID FROM

(SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows) AS FromTable

INNER JOIN

RowsToChange AS ToTable

ON FromTable.RowScore = ToTable.Int1

)

UPDATE AllRows

SET Int1=1000

FROM

Temp as InTable

JOIN Allrows as OutTable

ON Intable.OrigID = OutTable.AllRowsID

GO

SELECT * FROM AllRows

GO

USE

master

GO

CREATE DATABASE ComplexCertainRowsToChange

GO

USE ComplexCertainRowsToChange

GO

CREATE TABLE InitialisedValues

(

InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE PROCEDURE Initialise

AS

BEGIN

INSERT INTO InitialisedValues (Int1 )

SELECT 2

INSERT INTO InitialisedValues (Int1 )

SELECT 4

INSERT INTO InitialisedValues (Int1 )

SELECT 8

INSERT INTO InitialisedValues (Int1 )

SELECT 16

END

GO

EXEC Initialise

/*=======================================================*/

CREATE TABLE AllRows

(

AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL,

Type1 int NOT NULL

)

GO

CREATE TABLE RowsToChange

(

RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL,

Type1 int NOT NULL

)

GO

CREATE PROCEDURE InitialiseRowsToChange

AS

BEGIN

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 2, 1

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 4, 1

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 8, 1

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 16, 1

END

GO

EXEC InitialiseRowsToChange

GO

CREATE PROCEDURE PopulateAllRows

AS

BEGIN

INSERT INTO AllRows (Int1, Type1 )

SELECT 1, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 1, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 2, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 3, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 5, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 8, 2

INSERT INTO AllRows (Int1, Type1 )

SELECT 13, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 21, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 34, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 55, 2

INSERT INTO AllRows (Int1, Type1 )

SELECT 89, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 144, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 233, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 377, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 610, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 987, 2

INSERT INTO AllRows (Int1, Type1 )

SELECT 1597, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 2584, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 4181, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 6765, 1

END

GO

EXEC PopulateAllRows

GO

SELECT * FROM AllRows

GO

WITH Temp(OrigID)

AS

(

SELECT OrigID FROM

(SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows WHERE Type1=1) AS FromTable

INNER JOIN

RowsToChange AS ToTable

ON FromTable.RowScore = ToTable.Int1

)

UPDATE AllRows

SET Int1=1000

FROM

Temp as InTable

JOIN Allrows as OutTable

ON Intable.OrigID = OutTable.AllRowsID

GO

SELECT * FROM AllRows

GO

View 3 Replies View Related

Transact SQL :: How To Get First Table All Rows In Left Join If Condition Is Applied On Second Table

Aug 15, 2015

I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-

Faculty_Subject.Class_Id=@Class_Id

Then i don't get all subjects from subject table, how this can be achieved.

Sql Code:-
GO
ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details]
@Class_Id int
AS BEGIN

[code] ....

View 9 Replies View Related

Query To Only Display Information From One Table Where The Foreign Key Doesnt Exist In The Other Table.

Nov 28, 2006

I want to make a query, stored procedure, or whatever which will only display the primary key where there does no exist a foreign key in linked table.For example. If I had two tables with a one to many relationship.A [Computer] has one or more [Hard Drives]. I want to select only those computers which do not have a Hard Drive(s) associated with them. That is, show all computers where the Computer_ID field in the [Hard Drives] table does not exist. This seems simple but I'm drawing a blank here. 

View 1 Replies View Related

Retrieving Data From SQL Server Table To Display On Button On Datagrid Table.

Oct 10, 2007

I have nine type of buttons,
EnrollAmtBTM
PlacAmtBTM and so on, I also have a SQL setver view V_Payment_Amount_List from here i need to display the data on the button
this is the select value to display when i choose the agency list and the amount corresponding to that agency_ID is displayed here the agency_ID is fetched from the SQL CONDITION
 THIS IS WHERE I GET FETCH AGENCY DATA WHEN SELECTED i.e SQL CONDITIONprotected void CollectAgencyInformation()
{
WebLibraryClass ConnectionFinanceDB;ConnectionFinanceDB = new WebLibraryClass();
string SQLCONDITION = "";string RUN_SQLCONDITION = "";
SessionValues ValueSelected = null;int CollectionCount = 0;if (Session[Session_UserSPersonalData] == null)
{ValueSelected = new SessionValues();
Session.Add(Session_UserSPersonalData, ValueSelected);
}
else
{
ValueSelected = (SessionValues)(Session[Session_UserSPersonalData]);
}ProcPaymBTM.Visible = false;PaymenLstBTN.Visible = false;
Dataviewlisting.ActiveViewIndex = 0;TreeNode SelectedNode = new TreeNode();
SelectedNode = AgencyTree.SelectedNode;
SelectedAgency = SelectedNode.Value.ToString();
Agencytxt.Text = SelectedAgency;
Agencytxt2.Text = SelectedAgency;
Agencytxt3.Text = SelectedAgency;DbDataReader CollectingDataSelected = null;
try
{CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT DISTINCT AGENCY_ID FROM dbo.AIMS_AGENCY where Program = '" + SelectedAgency + "'");
}
catch
{
}DataTable TableSet = new DataTable();
TableSet.Load(CollectingDataSelected, LoadOption.OverwriteChanges);int IndexingValues = 0;foreach (DataRow DataCollectedRow in TableSet.Rows)
{if (IndexingValues == 0)
{SQLCONDITION = "where (Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'";
}
else
{SQLCONDITION = SQLCONDITION + " OR Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'";
}
IndexingValues += 1;
}SQLCONDITION = SQLCONDITION + ")";
ConnectionFinanceDB.DisconnectToDatabase();if (Dataviewlisting.ActiveViewIndex == 0)
{
Dataviewlisting.ActiveViewIndex += 1;
}
else
{
Dataviewlisting.ActiveViewIndex = 0;
}
SelectedAgency = SQLCONDITION;
ValueSelected.CONDITION = SelectedAgency;
 
 
???? this is where i use to get count where in other buttons and are displayed.... but i changed the query to display only the Payment_Amount_Budgeted respective to the agency selected. from the viewRUN_SQLCONDITION = "SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;
try
{
CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);
EnrollAmtBTM.Text = CollectionCount.ToString();
}
catch
{
}////this is my CollectedFinaceDataCount-- where fuction counts the records in the above select statement if i use for eg.
"SELECT Count(Placement_Retention_ID) FROM dbo.V_Retention_6_Month_Finance_Payment_List"
here is the functionpublic int CollectedFinaceDataCount(String SQLStatement)
{int DataCollection;
DataCollection = 0;
try
{
SQLCommandExe = FinanceConnection.CreateCommand();
SQLCommandExe.CommandType = CommandType.Text;
SQLCommandExe.CommandText = SQLStatement;
ConnectToDatabase();DataCollection = (int) SQLCommandExe.ExecuteScalar();
DisconnectToDatabase();
}catch (Exception ex)
{Console.WriteLine("Exception Occurred :{0},{1}",
ex.Message, ex.StackTrace.ToString());
}
 return DataCollection;
}
 
 
So here mu requirement request is to display only the value fronm the view i have against the agency selected
Please help ASAP
Thanks
Santosh

View 8 Replies View Related

UPDATE From A Table To Another Table On A Condition

May 7, 2008

I have four tables: Contact, Contact_Detail, Contact_Information and Contact_Main. I am trying to update a table called Contact_Detail with information from the other three tables.

tbl=Contact
pk=ContactId
Contact_Name

tbl=Contact_Information
pk=Information_ID
Information

tbl=Contact_Detail
detail_ID
Contact_ID
Information_ID
detail


tbl=Contact_Main (no pk, table is temporary)
Contact_Name
Airline
Focal
Title
MailAddress1
MailAddress2
ShippingAddress1
ShippingAddress2
Country
Email
Phone
Fax
Notes

Here is my pseudocode for transact SQL. I have completed QUERY1 and QUERY2 but not sure how to implement UPDATE 1. Any assistance is appreciated.

Select Contact_Main.Contact_Person = Contact.Contact_Name (QUERY 1)
Select Contact_Main.Focal = True (QUERY 2 from QUERY 1)
Begin Create a Contact_Detail record (UPDATE 1 from QUERY 2)
Contact.ContactID -> Contact_Detail.ConctactID
For X = 1 to 12 Update Contact_Detail record with
Increment PK -> Detail_ID
X -> Information_ID
Begin Case
Airline=1
Focal=2
Title=3
MailAddress1=4
MailAddress2=5
ShippingAddress1=6
ShippingAddress2=7
Country=8
Email=9
Phone=10
Fax=11
Notes=12
End Case
Next X
End Begin

View 1 Replies View Related

Using Result Of A Select Statement From One Table To Add Data To A Different Table.

Mar 17, 2008

I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table? 

View 3 Replies View Related

Table-Valued Function Result Vs. Calculation Table

Jun 6, 2006

 

I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.

Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:

 DataUserID, StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...

Or using a table-valued function to return a temp table as the result.

I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call. 

View 3 Replies View Related

Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?

Dec 11, 2007

Hi all,

I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:

USE AdventureWorks;

GO

IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

GO

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE

(

-- Columns returned by the function

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

)

AS

-- Returns the first name, last name, job title, and contact type for the specified contact.

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

ELSE NULL

END;

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'

END;

-- Return the information to the caller

IF @ContactID IS NOT NULL

BEGIN

INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

END;

RETURN;

END;

GO

----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.

Thanks in advance,
Scott Chang

View 1 Replies View Related

T-SQL (SS2K8) :: Create Union View To Display Current Values From Table A And All Historical Values From Table B

May 6, 2014

I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.

Q. Can this be done with one joined or conditional select statement?

DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid

View 9 Replies View Related

Select Condition From Log Table

Jun 1, 2006

Hi guys

Im not even sure how to word this right, but how do I compare one element(unsure word number 1) namely Filename, from a list of results, and use that value as a variable for another condition in the same select... I think?!

So if I get a list of 50 filenames (from a log table), some might be double.
If the file was successfully processed, it will log that filename and the status as OK and if not then BAD.
The file will be modified and then reprocessed until OK. Each time the file was processed, it will log the filename and status.
I then run a statement that shows all the files that were BAD (from the log table) and it will bring up then filename and BAD status.
What I want to do is check the status' and if the file is BAD, check if there is another logged entry with that filename and status is OK, if found then not produce output as Im only looking for the files that status' are BAD with no OK status for any of the logged entries for that filename.

Is that understandable? If not then Ill try reword it.

The help is much appreciated!
Justin

View 2 Replies View Related

How To Filter A Table With An OR Condition

Jun 7, 2007

I'd like to set the Filters in the Filters tab of the Table Properties dialog to say:



=Fields!WT_TO.Value > 0 OR

=Fields!WT_TO_PREV.Value > 0



but teh And/Or column is permanently disabled, and its sticking in a default value of AND



what's up with that?

View 6 Replies View Related

SQL 2012 :: Split Data From Two Columns In One Table Into Multiple Columns Of Result Table

Jul 22, 2015

So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:

**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3

**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3

In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID

I am trying to create a table such as shown below:

Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich

This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.

For e.g

Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One

[Code] .....

The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.

View 4 Replies View Related

Returns TABLE Based On A Condition

Jul 20, 2005

HI all,In SQL Server, i have a function which will return a table. likecreate function fn_test (@t int) returns table asreturn (select * from table)now i want the function to retun the table based on some condition like belowcreate function fn_test(@t int) returns table asif @t = 1 return (select * from table1)else return (select * from table2)It is not working for me. Please give me your suggesstions. It's very urgent.Thank you in advance....

View 1 Replies View Related

Update All The Records Of A Table On A Condition

Sep 20, 2007

Hi
I have a two tables as follows

Table Category
{

ID PK,
LastUpdate DateTime
}

Table Master
{

ID PK
Catrgory DateTime
}

I wanted to update Catrgory coulmn of all records in the Master table with the Value of LastUpdate of the CategoryTable the where the ID of the both the table are same

Can any one please let me know the query

~Mohan

View 8 Replies View Related

How Do I Use OR Condition In Table Filter Expression

Feb 11, 2008



Hai guys,

In my report I want to use OR condition instead of AND in Table Filter Expression. By default this option is disabled in VS2005 Pro. How do I enable this? or is there any other way to do this? Thanks in advance.

View 7 Replies View Related

Update Table With Multipe Condition

Jan 22, 2008



hi, I want to ask how I want update my table using a multiple condition.My table format is like this:






IC_NO
F05
F07
F08
F09
Ind


123


452
C


654
852


P




125

A





526
C



What I want to do is:

IF Ind = C, F05 <> NULL then IC_NO = F05
IF IND = C, F05 = Null and F09 <> Null then IC_NO = F09
IF IND = P, F05 <> Null and F07 <> Null then IC_NO = F07
IF Ind = A, F05 = Null and F08 <> Null then IC_NO = F08

your helpful highly appreciated..thanks

View 10 Replies View Related

Replace Characters On Condition In Table And Cells

Apr 14, 2008

need help

replace characters on condition in table and cells

but only if

if i put number or 1 , 2 , 3 , 4 above the cell of the eployee for example( employee id=222 name =bbbb day1)

i replace characters with '0' and '#'

and it must work dynamically AND replace ONLY THIS characters


table before the replace
id fname val day1 day11 day111 day2 day22 day222
------------------------------------------------------
111 aaaa 2 1 3
111 aaaa 1 A C
222 bbbb 2
222 bbbb 1
333 cccc 2
333 cccc 1
444 dddd 2
444 dddd 1
555 eeee 2 2
555 eeee 1 B

table after the replace
id fname val day1 day11 day111 day2 day22 day222
------------------------------------------------------
111 aaaa 2 0 0
111 aaaa 1 # #
222 bbbb 2
222 bbbb 1
333 cccc 2
333 cccc 1
444 dddd 2
444 dddd 1
555 eeee 2 0
555 eeee 1 #

tnx FOR THE HELP

View 5 Replies View Related

Insert Columns Into Table Based On Condition?

Jan 30, 2015

My requirement is below.enhancing the T- sql query as I was told not to use SSIS.

Insert data from Table A to Table B with conditions:

1. Truncate gender to one character if necessary.

2. Convert character fields to uppercase as necessary.

3. For systems that supply both residential and mailing addresses, use the residential address if available (both street_address and zip fields have value), mailing address otherwise.

In SSIS I took conditional split with 'ISNULL(res_street_address) == TRUE || ISNULL(res_zip) == TRUE '

default outputname :Consider Res Address; Outputname:Consider mail address.

and mapped as:

(Table A) mail_street_address---street address(Table B)

(Table A) mail_city----------------City(Table B)

(Table A) mail_Zip----------------Zip(Table B)

(Table A) mail_state-------------state(Table B)

(Table A) res_street_address--street address(Table B)

(Table A) res_city---------------City(Table B)

(Table A) res_Zip----------------Zip(Table B)

(Table A) res_state--------------state(Table B)

I want to do the same with T-sql code too:

I came up with below T-SQl but unable to pick(street,city,state,zip columns as I have take combination of street and zip from Table A not individual columns as I wrote in below query) based on above condition(3):

Insert into TABLE B
SELECT
Stats_ID
,UPPER(first_name) first_name
,UPPER(middle_name )middle_name
,UPPER(last_name) last_name
,UPPER(name_suffix) name_suffix

[code]....

View 2 Replies View Related

Insert Into Temp Table Based On If Condition

Apr 12, 2006

hello all,this might be simple:I populate a temp table based on a condition from another table:select @condition = condition from table1 where id=1 [this will giveme either 0 or 1]in my stored procedure I want to do this:if @condition = 0beginselect * into #tmp_tablefrom products pinner joinsales s on p.p_data = s.p_dataendelsebeginselect * into #tmp_tablefrom products pleft joinsales s on p.p_data = s.p_dataendTha above query would not work since SQL thinks I am trying to use thesame temp table twice.As you can see the major thing that gets effected with the condictionbeing 0/1 is the join (inner or outer). The actual SQL is much biggerwith other joins but the only thing changing in the 2 sql's is the joinbetween products and sales tables.any ideas gurus on how to use different sql's into temp table based onthe condition?thanksadi

View 5 Replies View Related

I Need To Find The Rows That Exist In One Table But Not In The Other With Condition

Jun 20, 2007

I need to find the rows that exist in one table but not in the otherwith this condition:(prod_name exist in table1 and not in table2.prod_name ) AND(prod_name exist in table1 and not in table2.'S'+prod_name )explanation:i want to know if the product not exit and if the combination of thecharachter "S" with the product Name also not exist at the othertableB.Ryuvi

View 2 Replies View Related

Replace Characters On Condition In Table And Cell

Apr 11, 2008

need help
condition 1
replace characters on condition in table and cells
but only if
how to do it - if i put * (asterisk) like in employee 111 in day1 - the the upper characters the (A S B)
i replace characters with '-'
and it must work dynamically
condition 2
replace characters on condition in table and cells
but only if
if i put number or 1 , 2 , 3 , 4 above any cell for example( employee id=222 name =bbbb day1)
i replace characters with '0' and '#'
and it must work dynamically
table before the replace




id
fname

val

day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
day5
day55
day555

111
aaaa

2
A
S
B
e
t
y
R
Y
M



j
o
p

111
aaaa

1
*


*


*





*



222
bbbb

2
1



1











222
bbbb

1
A
-
-
-
B
-










333
cccc

2
















333
cccc

1
















444
dddd

2








3





4

444
dddd

1






-
-
C





C

555
EEE

2
A
G
C













555
EEE

1
*



































table after the replace




id
fname

val

day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
day5
day55
day555

111
aaaa

2
-
-
-
-
-
-
-
-
-



-
-
-

111
aaaa

1
null


null


null





null



222
bbbb

2
0



0











222
bbbb

1
#
-
-
-
#
-










333
cccc

2
















333
cccc

1
















444
dddd

2








0





0

444
dddd

1






-
-
#





#

555
EEE

2
-
-
-













555
EEE

1
null































tnx for the help

View 11 Replies View Related

How To Make Table Row Invisible Based On Certain Condition

Apr 16, 2008



HI

I have the following scenario in my report.


-The data is displayed in a table
-The table groups by one field
-Each table row calls a subreport
-There is about 6 paramaters in the report
-The last paramater of the list of paramters is a multivalue paramater and based on what is selected in the list the corresponding subreport must be shown.
-So i use a custom vbscript funtion to determine if a specific value was selected or not.
This functionality is working fine.

My problem is if the user does not select all the values in the multi select then i want to make the row invisble
and remove the whitespace so that there is not a gap between the other subreports which is shown.

I can make the subreport invisible inside the row but there is still the white space which does not display very nicly.

How can i make the row invisible if the vbscript function that is called returns a false value?

Here is the funtion I call -> Code.InArray("ValueToSearchFor", Parameters!MultiValueDropDown.Value)

The Function returns a true or false.

Thanks.




View 3 Replies View Related

Return Difference Of Two Stamps In Table Based On Some Condition

Aug 10, 2015

I have table that contains below data

CreatedDate                ID             Message
 2015-05-29 7:00:00      AOOze            abc
 2015-05-29 7:05:00      AOOze            start
 2015-05-29 7:10:00      AOOze            pqy
 2015-05-29 7:15:00      AOOze            stop
 2015-05-29 7:20:00      AOOze            lmn   

 and so on following the series for every set of same ID with 5 entries for each ID

I need to Find Maximum interval time for each ID and for condition in given message (between message like Start and Stop) I used below query and it works fine

select Id, max(CreatedDate) AS 'MaxDate',min(CreatedDate) AS 'MinDate',
DATEDIFF(second,min(CreatedDate),max(CreatedDate)) AS 'MaxResponseTimeinSeconds' from Table where Id in (
SELECT distinct Id
from Table
where Message like  'stop')
group by Id

Above query displays max response time for ID A00ze as 20 minutes, but stop message has occured at 7.15. I would need to modify the query to return max response time  as 15 min(from 7.00 to 7.15).

Difference of starttime(where A00ze id started) and stoptime(where stop string is found in message).

View 7 Replies View Related

Adding A Column Name To A Table In Each Of The Databases Based On A Condition

Oct 3, 2007

i have the folowing databases DB1,DB2,DB3,D4,DB5........

i have to loop through each of the databases and find out if the database has a table with the name 'Documents'( like 'tbdocuments' or 'tbemplyeedocuments' and so on......)

If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in that database and there can be more than 1 'Documents' table in a database.


can someone show me the script to do it?


Thanks.

View 3 Replies View Related

Trying To Create A Proc That Will Insert Values Based On A Condition That Is Another Table

Feb 16, 2005

Can someone give me a clue on this. I'm trying to insert values based off of values in another table.

I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year).

Two questions

1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great.

2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated.


Thanks in advance, bellow is the code that I have so far:

RB



Select Results.custID, Results.PledgeLastYr
From Results, PledgeInLastYear
Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear
Insert Into Results(PledgeLastYr)
Values ('Y')

View 1 Replies View Related

SQL Server 2012 :: How To Exclude Row From Result Set Based On Condition

Sep 15, 2015

Lets say I have a set of columns as follows:

Table.Name - Table.ID - Table.Code

I want to write a query that will cycle through the results and if it comes across another record that has a matching Table.ID I want to exclude that row from the result set.

I am not all too familiar with how to use either a Case or If..Else Statement within a Sql statement that would accomplish this.

View 7 Replies View Related

T-SQL (SS2K8) :: Delete All Rows Satisfying Certain Condition From Table A And Related Records From B And C

Apr 14, 2015

I have around 3 tables having around 20 to 30gb of data. My table A related to table B by a FK and same way table B related to table C by FK. I would like to delete all rows satisfying certain condition from table A and all corresponding related records from table B and C. I have created a query to delete the grandchild first, followed by child table and finally parent. I have used inner join in my delete query. As you all know, inner join delete operations, are going to be extremely resource Intensive especially on bigger tables.

What is the best approach to delete all these rows? There are many constraints, triggers on these tables. Also, there might be some FK relations to other tables as well.

View 3 Replies View Related

Display DB Table In ASP

Aug 21, 2007

hello forum friends,

i need to display the database table in ASP
page.how it is possible,can anyone explain me
with code.

Regards
Prathap

View 1 Replies View Related

Update Statement Performing Table Lock Even Though Where Condition On Clustered Primary Index?

Jul 20, 2005

Hi All,I have a database that is serving a web site with reasonably hightraffiic.We're getting errors at certain points where processes are beinglocked. In particular, one of our people has suggested that an updatestatement contained within a stored procedure that uses a wherecondition that only touches on a column that has a clustered primaryindex on it will still cause a table lock.So, for example:UPDATE ORDERS SETprod = @product,val = @valWHERE ordid = @ordidIn this case ordid has a clustered primary index on it.Can anyone tell me if this would be the case, and if there's a way ofensuring that we are only doing a row lock on the record specified inthe where condition?Many, many thanks in advance!Much warmth,Murray

View 1 Replies View Related

Parent/Child Rows In Report, Nested Table, Textbox Value In Filter Condition

Mar 26, 2008

Hi All,

I am working on SQL server 2005 Reports.
I have one report, one dataset is assigned to it, and one table which displays it.
Now I come accros requirement that, the column value in the filter condition for the table is present in one textbox.

I can not use textbox i.e. reportItems in filter condition. Can someone suggest me how to use textbox value in filters?


I want to display parent/child records on report. I am not getting the proper solution.

The data is like this:

Sequence ItemCode IsParent

1 XYZ 0 'do not have child record

2 PQR 1 'have child records with sequence no 3

3 ASD 0

3 AFDGE 0

3 VDC 0

4 ASR 1 'have child records with sequence no 5
5 ASR 0

If IsParent = 1, that record has child records with sequence = parent sequenece + 1



I think u can understand the data I need to bind, and it is like:

XYZ

+ PQR

ASD

AFDGE

VDC

ASR

On + click we can do show/hide of child records.

I m not getting how to achive this in SQL server report. Can u give some hint?

Thanks in advance
Pravin

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved