INSERT With Condition

Aug 15, 2007

I need to understand how to INSERT a record from one table to another only when a condition is meet between the two tables. Example...NewTable has new employees on it that are not on the current EmployeeTable. So, when NewTable.EmployeeNo does not match a EmployeeTable.EmployeeNo then I want to INSERT.

Can someone please help me understand what code I need to make this happen? Thanks.

View 2 Replies


ADVERTISEMENT

Insert With Condition

Apr 28, 2006

Hi,
what i am trying to do is, reading from an array which has the column name and value and then insert that value to the column which is fetched from that array.And on the other hand i should use select to get the data's which have the same ID.
more detail: what i have >> (VARID,columnName,Value)
what i want>> insert to table (columnName) values (Value) while ID =VARID

any idea how i should do that?
tnx

View 10 Replies View Related

Condition Before Insert Records ?

Feb 7, 2008



Hi guys,

Help me to write my query,

I want to write a insert statement but before insert statement run i need to check a condition like if the same record is already existing or not ? how do i do this using transact -SQL

I mean i have a table call Employee like this definition

ColumnName Datatype
EmployeeID INT
Name Varchar(255)


Records like this

EmployeeID Name
1 John E Mathew
2 Ethel Elizabeth


Ok Before i insert a new record , i need to check if a emplyee name call ="John E Mathew" already in the table or not if Employee Name call ='John E Mathew' not exisits only it should execute my insert statement .

how do i write my SQL ?

regards
suis





View 9 Replies View Related

T-SQL (SS2K8) :: Update / Insert Trigger With Condition

May 15, 2014

I am currently in the process of writing an INSERT/UPDATE trigger in SQL 2012, the scenario is, I have a table called Quote, what I want to happen is when a record gets added to the table with a field called Approved, populated with a value or a record gets Updated and the Approved field gets populated I want the trigger to execute a stored procedure, passing through a value from a field called Account within the Quote table as a variable. I know I can achieve the action using one trigger "AFTER INSERT, UPDATE" but I am struggling on forming the rest of the trigger.

View 1 Replies View Related

SQL 2012 :: How To Insert Multiple Rows With Condition

May 19, 2015

Create table #table (id int identity , from_country varchar(20) ,
to_country varchar(20),noofdays int, datetravel datetime )
insert into #table(from_country,to_country,noofdays,datetravel)
values
('Malaysia','India',2,getdate()-99),
('India','Singapore',4,getdate()-88),
('Singapore','China',5,getdate()-77),
('China','Japan',6,getdate()-66),
('Japan','USA',7,getdate()-55)
select * from #table

I want to insert data to another table based on "noofdays" columns. If "noofdays" is 4 then 4 rows should inserted to new table with 1 day increment in "datetravel" column.

Ex :
#table
1MalaysiaIndia22015-02-09 02:04:09.247
2IndiaSingapore42015-02-20 02:04:09.247

[code]...

In #table , 1st row noofdays is 2 , so in new table #table_new first 2 rows should inserted with 1 day increment in "datetravel" column.

View 2 Replies View Related

T-SQL (SS2K8) :: Insert Columns Based On Condition

Aug 15, 2015

I have a requirement to Insert Column 1 and Column 2 based on below condition only. Looking for a Store procedure or query

Condition : Allow Insert when column 1 and Column 2 have same values on 2nd row insert. But should not allow insert when Column 2 value is different.

ALLOW INSERT:

Column1 Column2
A0007 12-Aug
A0007 12-Aug
A0007 12-Aug

DONOT ALLOW INSERT: (COLUMN1 ID should not allow different dates)

Column1 Column2
A0007 23-Mar
A0007 02-Feb

FINAL OUTPUT Should be

Column1Column2
A000712-Aug
A000712-Aug
A000712-Aug
B000220-Jun
B000220-Jun
C000330-Sep

Discard Insert when Column1 ID's comes with Different dates.

View 4 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

Transact SQL :: Insert Missing Record Based On A Condition

Sep 29, 2015

How do I get the below scenario:

EmpID DepID
12 2
17 3
17 2
13 3

Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.

Desired Output:

EmpID DepID
12 2
17 3
17 2
13 3
12 3
13 2

View 5 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

Can I Print The Results Of A Condition Based On The Condition?

Feb 9, 2006

For example..

select * from mytable where MyNum = 7

If this brings back more than 1 row, I want to display a message that says,

Print 'There is more than one row returned'

Else (If only 1 row returned), I don't want to print anything.

Can I do this? Thx!

View 1 Replies View Related

COndition Spli - Error Date Condition

Apr 19, 2007

Dear friends,

I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...



I need to filter rows in a dataflow...

I created a condition spli to that... maybe there is a better solution...

And the condition is: Datex != NULL(DT_DATE)

(Some DATE != NULL)





[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.



What is wrong??

Regards,

Pedro

View 4 Replies View Related

Reporting Services :: SSRS IIF One Condition Or IIF Another Condition

Jun 22, 2015

I  am trying to write an visibility function to have message shown based on two different IIF  conditions:

If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS",    otherwize return " NAME CREATED")If behavior is to  Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST",    otherwize return "NAME SUCCESSFULLY DELETED")
I tried the following which doesn't work:
=IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAME  CREATED"),
IIF((UCase(First(Fields!Message.Value,  "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")

View 6 Replies View Related

DB Engine :: Can't Use The MERGE Statement / How To Design WHERE Condition For Insert Statement

Nov 5, 2015

I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.

View 2 Replies View Related

Transact SQL :: How To Add Condition In Where Clause According To Another Condition

Oct 17, 2015

I write a query to get some data as the following. but i need when a user check specified condition a query parameter change to specified condition :

create proc proc_ReservationDetails
(
@status nvarchar(50) = null
)
as
begin
select reservationId, reservationStatus, reservationDesc

[Code] .....

View 3 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

Where Condition

May 5, 2007

 da = New Data.SqlClient.SqlDataAdapter("SELECT [Products].[Names], Count([ProductList].[Products]) AS [Total]  FROM [Products]     LEFT JOIN [ProductList]     ON [ProductList].[Names] = [Products].[Names]       GROUP BY [Products].[Names] ", strConnection)
can we use a where condition in the statement.If so how can we use it.

View 10 Replies View Related

Condition (if / Else) In SQL?

Aug 4, 2007

Hi all,I'm building a DataSet on Visual Studio and don't know how to do a condition (if/else) with SQL... I have a search form, with a DropDownList and have 2 options in it: Search by Title or Search by Author. If the "Title" is selected, then the value is "title and if "Author" is selected, then the value is "author".Here is what I have right now for the DataSet, as seperated queries but I think I can combine them to be one single query 1.This will returns the songs that matches the title:SELECT     LYRICS_PK, LYRICS_TITLE, LYRICS_TITLE2, LYRICS_WRITER, LYRICS_WRITER2, LYRICS_COWRITER, LYRICS_DATE_ADDED, UserId_FK,                       LYRICS_APPROVED, LYRICS_TYPE, LYRICS_VIEWS, LYRICS_ADDED_BYFROM         t_lyricsWHERE ((@LYRICS_TITLE IS NULL) OR (LYRICS_TITLE LIKE '%' + @LYRICS_TITLE + '%') OR (LYRICS_TITLE2 LIKE '%' + @LYRICS_TITLE + '%')) AND (@LYRICS_TYPE = 'title') 2. This returns the songs that matches the author: SELECT     LYRICS_PK, LYRICS_TITLE, LYRICS_TITLE2, LYRICS_WRITER,
LYRICS_WRITER2, LYRICS_COWRITER, LYRICS_DATE_ADDED, UserId_FK,
                      LYRICS_APPROVED, LYRICS_TYPE, LYRICS_VIEWS, LYRICS_ADDED_BY
FROM         t_lyrics

WHERE ((@LYRICS_AUTHOR IS NULL) OR (LYRICS_AUTHOR LIKE '%' +
@LYRICS_AUTHOR + '%') OR (LYRICS_AUTHOR2 LIKE '%' + @LYRICS_AUTHOR + '%'))
AND (@LYRICS_TYPE = 'author') This is very inefficient because I have 2 queries, and I need to build 2 ObjectDataSources as well as 2 different GridViews to display the results. I think we can do something likeSELECT .... ... FROM t_lyricsif (@LYRICS_TYPE = 'title')     DO THE WHERE CLAUSE THAT RETURNS MATCHES WITH TITLEelse if (@LYRICS_TYPE = 'author')    DO THE WHERE CLAUSE THAT RETURNS MATCHES WITH AUTHOR But I don't know how to write that in T-SQL.Any help would be greatly appreciated,Thank you very much,Kenny. 

View 4 Replies View Related

Where Condition

Sep 13, 2004

hi

i want to give .text filed of a TextBox to where condition but the result is not correct.

The SQL is like following:

selStr = "SELECT COUNT(*) FROM Users WHERE (Name = usernameTextBox.Text) and (Password = passwdTextBox.Text)"

View 2 Replies View Related

Using Max With Where Condition?

Jun 15, 2012

i have a table like this

ID DATE time status

01 11/11/2011 10.23 Entry
02 11/11/2011 11 .47 Exit
03 11/11/2011 12.54 Entry
04 11/11/2011 2.54 Exit

i want to select the minimum time from entry and maximum time in exit.

the status should be checked when taking the max and min values .

how to do this.

View 1 Replies View Related

CONDITION

Apr 25, 2007

Hi,I want to write a SQL stmt.(condition) that checks the following @ActionPLanID > 0 and ActionPlanID exists in table A simultaneously?

I was thinking of using IF NOT exists but don't know how to write it. Am I going in the right direction?

Thanks,
VBJP

View 2 Replies View Related

And / Or Condition

Sep 28, 2007

hi,

i'm working on a query and have discovered something fairly simple regarding "and" / "or" condition.

if I use e.g.

id_product in ('1111','2222')

as a result i should get all products that match id = 1111 and id = 2222.

But if I do it like

id_product in ('1111')
and id_product in ('2222')

as a result i get 0 rows returned, where as i want to find invoices that have both products, and not those which have either product 1111 or 2222 or even both :)

thank you for any suggestions!

View 9 Replies View Related

No/any Condition

Nov 5, 2007

I have a query something like

select a, b, c from mytable where a=@prmA

If prmA is something like generic all, any, I want this query return value without condition. Is there a way to do this with sql or I should write stored procedure that checks @prmA and all other condition parameters and generate new SQL statement?


Regards,
Hakan

View 7 Replies View Related

Only NOT Condition In CONTAINS

Mar 11, 2008



Hi All,

I have stored Candidates Resume in binary(Image) format in database.
I have a search feature to search resume.
For Example:

with all these words: ASP.Net, SQL Server, 2 Years
without these words: Java

Here I get Candidate which CONTAINS("ASP.Net" AND "SQL Server" AND "2 Years" AND NOT "Java")

Now I want to search candidates only
without these words: Java

it means the condition is CONTAINS(NOT Java)

is this possible?
if yes, how?

Thanks in advance.



View 1 Replies View Related

WHERE Condition

Dec 20, 2007

Hi,

In following query, I use three conditions in WHERE caluse. When I use only CreditUnion.Id=@CreditUnionID
Then I get the right value set. But when I join other two conditions , i get all the values instead of relevant data for that parameter.

Can anyone say why it happenes?




Code Block
SELECT
Member.LastName + ' ' + Member.FirstName AS MemberName, CASE WHEN CuStatus = 'Existing' THEN 'Existing' ELSE 'New' END AS MemberType, EnumCUMembershipStatus.UIText AS Status, CDOrder.DecidedOnCU, SysUserLogon.LastName + ' ' + SysUserLogon.FirstName AS CUDecisionOfficer, 'CD' AS ProductType, CreditUnion.Name

FROM
Member INNER JOIN
CDOrder ON Member.LastCDOrderFK = CDOrder.Id AND Member.Id = CDOrder.MemberFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id INNER JOIN
EnumCUMembershipStatus ON Member.CuStatus = EnumCUMembershipStatus.Name INNER JOIN
SysUserLogon ON CreditUnion.Id = SysUserLogon.CreditUnionFK

WHERE (CreditUnion.Id = @CreditUnionID) AND (Member.CuStatus = 'Approved') OR (Member.CuStatus = 'Declined')

UNION

SELECT
Member_1.LastName + ' ' + Member_1.FirstName AS MemberName,
CASE WHEN CuStatus = 'Existing' THEN 'Existing' ELSE 'New' END AS MemberType, EnumCUMembershipStatus_1.UIText AS Status, LoanApplication.DecidedOnCU, SysUserLogon_1.LastName + ' ' + SysUserLogon_1.FirstName AS CUDecisionOfficer, 'Loan' AS ProductType,
CreditUnion_1.Name

FROM
Member AS Member_1 INNER JOIN
LoanApplication ON Member_1.LastLoanApplicationFK = LoanApplication.Id AND Member_1.Id = LoanApplication.MemberFK LEFT OUTER JOIN CreditUnion AS CreditUnion_1 ON Member_1.CreditUnionFK = CreditUnion_1.Id LEFT OUTER JOIN EnumCUMembershipStatus AS EnumCUMembershipStatus_1 ON Member_1.CuStatus = EnumCUMembershipStatus_1.Name LEFT OUTER JOIN SysUserLogon AS SysUserLogon_1 ON LoanApplication.SysUserLogonFK = SysUserLogon_1.Id AND LoanApplication.SysUserLogonCUFK = SysUserLogon_1.Id AND CreditUnion_1.Id = SysUserLogon_1.CreditUnionFK

WHERE (CreditUnion_1.Id = @CreditUnionID) AND
Member_1.CuStatus = 'Approved' OR Member_1.CuStatus = 'Declined'


View 4 Replies View Related

IN CONDITION QUESTION

Aug 11, 2006

I am an ASP.NET Developer I am using two SQL Server databases, 2005 and sql express.I am using a select statement on an IN CONDITION
One table, Table name = SOP10100 resides in a SQL Server 2005 DatabaseThe other table,  Table name = ORDER resides on a SQL server express Database
I am writing the following sql statement
SELECT ORDERNO, CARRIER FROM SOP10100WHERE ORDERNO IN ('ORD000234','ORD000384',....)
My question is how many values can I fit on this IN conditionI mean the maximum number of values (upperlimit)
Is there a better way to do this Using ASP.NET ?

View 3 Replies View Related

Search Condition

Feb 18, 2008

Hi This is madhavi
am working with a project with ASP.NET Using VB.NET..
i have requirement that i have to provide the result based on search condition....
First : For Serach i have to search based on given CITY and CATEGORY....
 
      For this i have written a StoredProcedure like:
******************************************************************************************************************
         Create PROCEDURE YellowPages_Search(@city nvarchar(50),@SearchWord nvarchar(200),@Name varchar(50) OUTPUT,@CompanyName varchar(50) OUTPUT,@Address varchar(1000) OUTPUT,@PhoneNo varchar(50) OUTPUT,@MobileNo varchar(50) OUTPUT,@Fax varchar(50) OUTPUT,@Email varchar(50) OUTPUT,@WebSite varchar(50) OUTPUT)AS
declare @sql nvarchar(1000)set @sql='select * from YellowPages_Userdetails where city='''+@city + '''and (category like ''%' + @SearchWord + '%'' or subcategory  like ''%' + @SearchWord + '%'') '
exec(@sql)
GO
*************************************************************************************************************************************************************************
Now  i want to extend this search condition for LOCATION and SUBCATEGORY
 means my search condition should include CITY , LOCATION , CATEGORY and SUBCATEGORY
(here the location and subcategory may be given or may not be given)
so please help me out
Thanks in Advance,
Madhavi
 

View 3 Replies View Related

Exclude Condition

Jul 26, 2005

i have two tables: "Person" and "Year". "Person" can have many "Year"
(one to many relation). i want a query which returns all the records
from "Person" where "Year" is 2005 but exclude if there is any "Year"
with 2004. how can i write that query? any help will be appreciated.
i did try
<code>
SELECT * FROM Person JOIN Year ON Person.Id = Year.PersonID WHERE Year.Year = 2005 AND Year.Year <> 2004
</code>
but it doesn't seem to work. i want this query to return records from
Person where there is no any year with 2004 but only 2005. If a person
has both 2004 and 2005 exclude that person.

View 1 Replies View Related

Copy On Condition

Sep 29, 2005

i have two tables A and B with the same fields,
If the id field of table B equals id field in Table A i need to update th edata for that id row.If the id field doesn;t match then i need to insert a new record in tale A for that id
that is i need to perform insertion or updation into table A depending on table B dataCan anyone give me some idea how to start?

View 3 Replies View Related

Using Where Condition With Dates

Jul 2, 2001

I'm trying to make sql that check date in database

Select ... From ....
Where ((id=1) and (port=2) and (logdate=#12/31/2001#));

I'm getting 0 records even if its exist.
I know the problem is in the "logdate=#12/31/2001#"
What is the problem and why the sql ignores it ?

View 4 Replies View Related

Using Alias In Where Condition

Mar 1, 2001

Can we do

Select BookNo as Catalog from Books
where Catalog = 12356

I need to find the way to use alias in "where" for very complex query
Is anyone has way around it ?
Thank you

View 1 Replies View Related

MDX - Exclude Condition

Dec 15, 2005

I have to built a query to get the % for all the Region (Americas, Asia and Europe) from a cube.

But in these regions some countries are excluded and treated seperate.

Like Asia does not include India and Japan.

How do I get the ASIA query using an EXCLUDE condition.

Please help.

View 1 Replies View Related

Stuck With OR Condition

Aug 25, 2006

Hi,

I have a sql problem I'm trying to solve. I'm selecting from a table based on a foreign key, and the select returns 2 rows. The table has a column called type, and each row for the foreign key has a different type value. Hopefully the example below can help to explain:

Case 1:

PK | FK | Type | Text
--------------------------
1 | 226 | 0 | some text goes here
2 | 226 | 1 | NULL

Case 2:

PK | FK | Type | Text
--------------------------
3 | 334 | 0 | some text goes here
4 | 334 | 1 | actual text I want to select is in this cell

I'm trying to create a select statement to grab the text for the foreign key I'm looking up. In case 2, I want the text where type=1 but in case 1 I want the text where type=0.

I had started writing it as

select text from table where fk=334 and ( (type=4 and text is not null) or type=0 )

but this returns both rows. What I what is something that I think is more akin to

case a || case b

expression in programming - if case a evaluates as true, use that, otherwise evaluate case b and use if true, otherwise return false.

I hope you can understand what I'm trying to get and any suggestions would be much appreciated.

Thanks in advance,
Peter

View 2 Replies View Related







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