Exclude Records From A Table Where ID Column Is Same But Mail Code Column Is Multi-valued

Nov 12, 2012

I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)

ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC

So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.

View 3 Replies


SQL Server 2012 :: Subtract / Exclude Value Items From A Column And Add It To Another Column In Same Table

May 26, 2014

I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.

View 1 Replies View Related

How To Exclude Records That Have A Common Column

Dec 26, 2007

I am creating a query which will show patients that are enrolled in more than one program, but I need to exclude those patients that enrolled more than once in the same program. Here's part of the code:

SELECT member_id, service_id
FROM pat_prg_info ppi

This query produces results like the following:

member_id service_id
1001 1
1001 2
1003 9
1003 9
1004 2
1004 9

I would like to exclude 1003, since this member_id is enrolled twice in service_id 9. How can I accomplish this?

View 1 Replies View Related

Code To Exclude Records In A Crosstab

Jun 20, 2014

Using MS ACCESS I have:


which works fine. I want to look at the query SAP_CALD and disclude any record in the field 'department' that has a record of any {DPSG, System, Dealer, DealerIT, Inbound}..I can do this by piggybacking a another query that removes these but wondered if it would be easier to exclude them in the crosstab code.

View 1 Replies View Related

Transact SQL :: Passing Column To Table Valued Function And Getting Could Not Be Bound

Nov 10, 2015

I'm running 2014 enterprise and getting an error on this form of a query...it says the multi part identifier "mns.col3" could not be bound.  I'm aware that a cross apply would be more appropriate but i'm just prototyping and probably going to move to a set based approach anyway.The udf returns a table.

select mns.col1,
from table1 mns
left join dbo.udf_udf1(@firstofmonth,@lastofmonth, mns.col3) x
on 1=1

View 3 Replies View Related

Transact SQL :: Table Valued Function Returns Nothing When Single Column Selected

Apr 21, 2015

We run std 2008 r2.  I haven't looked at my friend's function closely yet bur he showed me that when he selects from the function with one column and the same where clause he uses on same func with select *, he gets no data under the column he requested. 

But when he selects * he gets a single row.

I took a peek and see a bunch of left joins followed by a bunch of outer applies in his func. I suppose (thinking out load) if anything random like the order of rows returned or sql decisions on how query runs can affect his function, that might explain it.  

View 8 Replies View Related

Integration Services :: SSIS - Split Multi Value Column Into Multiple Records?

Oct 12, 2014

I have one scenario

Col1. Col2
1. A,b,c,df,ghf
2. C,b
3. B

Output should be

Col1. Col2
1. A
1. B
1. C
1. Df
1. Ghf
2. C
2. B
3. B

View 9 Replies View Related

Multi-statement Table-valued Functions

May 25, 2004


I am trying to do the following:

1. Create a Multi-statement Table-valued Functions, say mstvF1, with 1 parameter.
2. Then, use it like this: "Select * from table T, mstvF1( T.id )"

It gives me Line 100: Incorrect syntax near 'T', referring to the T of T.id.

If I do
Select * from table T, mstvF1( 5 ), then it works.

Is there any way to do a select from a table T combined with an MSTV function and passing in as a parameter a field from T?

Thanks for any help.

View 3 Replies View Related

Multi-statement Table-Valued Function

Oct 18, 2007

I'm creating a Multi-statement Table-Valued Function...

Is it possible to insert variables into the table? In other words, is it possible
to have something like

@value1 varchar(10)
@value2 varchar(10)

<do some work on value1 and value2>
INSERT @returningTable
@value1, @value2

instead of

<do some work on value1 and value2>
INSERT @returningTable
SELECT col1, col2 from T_SOURCE

Here's why I want to insert variables...My function needs to return a table which contains a 'partial' incremental key.
I'll go with an example to explain what i have to do

col1 col2
Mike 10
Mike 20
Ben 50
John 15
John 25
John 35

The table that my function needs to create should look like this
col1 col2 col3
Mike 10 1
Mike 20 2
Ben 50 1
John 15 1
John 25 2
John 35 3

I thought of creating a cursor and then looping through it generate col3 and save values of other individual columns in variables. But don't know how to use those variables when inserting records into function table.

Any other ideas? I'm caoming from Oracle world, I might be having some strange ideas on how to solve this problem. Any help is appreciated.

Thank you.

View 7 Replies View Related

Create Multi Column View From Single Column Data

Jan 9, 2008

I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.

product_code month value
350 1 10
350 2 20
350 3 30

product_code profit_center
350 4520


product_code profit_center mon1 mon2 mon3
350 4520 10 20 30

My current query gives the following result

product_code profit_center mon1 mon2 mon3
350 4520 10 0 0
350 4520 0 20 0
350 4520 0 0 30

Any direction toward a solution would be appreciated. Am using SS2005.

View 5 Replies View Related

Multi Statement Table-valued UDF Without Declaring Columns?

May 6, 2007

I have created a multi-statement
table valued function

fn_x(@x int)returns
@tbl table
          position int identity primary key,          i
@tbl values
(@x)          insert
@tbl values
(@x)          insert
@tbl values
(@x)          insert
@tbl values
(@x)          returnend

Is it possible skipping the
definition of the table columns (the light blue
part)?I need to return a different
structure based on a parameter.Dropping those lines throws an
error "incorrect syntax near 'as'" 
The other solution is declaring
each udf separately as one statement udf.Thanks 

View 2 Replies View Related

Inline Table-valued Function With Multi-value Parameter

Jul 18, 2007

Hello everybody,

I need to create a function which takes a multi-value parameter. When I select more than one item, I get the error that I have too many arguments. Does anybody have a solution?

Or can I create a view and then do a "SELECT * FROM viewName WHERE columnName IN (@param)"?

Thanks in advance for your answers.

View 7 Replies View Related

Checking Column Name From Multi Database's Table

Aug 18, 2006

i have over 200 tables with all same column and data type locate all over the server(20)different database. so i have table call Tname to stored all the link like (databasename.dbo.tablename) so my job will call the Tname table to use cursor to insert records. but the problem is there's one or more table's column name K datatype has been modify by someone else. so is it cause the job fail.
if there a way or SQL statment that i can use Table Tname to see which one is missing column name K? and is there a way i can print out all the datatype and len for all the table column k? please help thanks

View 2 Replies View Related

Multi-column Index Vs Single Column Indexes

Aug 14, 2007

Hi,Would like to know the performance differenece between Multi-columnIndex vs Single Column Indexes. Let's say I have a table with col1,col2, col3 along with a primary key column and non-indexed columns.In queries, I will use col1, col2, and col3 together and some timesjust one or two of these three columns. My questions is, should Icreate one index contains col1, col2, and col3, or create 3 seperatedcolumns. I.e. each column has its own index. Any performancedifference?Thanks a lot.

View 1 Replies View Related

Multi Column Grouping Vs Page/Column Break

Mar 18, 2007

In my report i would have 2 groups.

The first group should cause a real page break, the secound group should cause a column break.

Any idea on how to realize this, i've been playing with quite some settings but .....

So, any help ...

View 1 Replies View Related

Ambiguous Column Names In Multi-Table Join

Jun 21, 2006

Hi all,A (possibly dumb) question, but I've had no luck finding a definitiveanswer to it. Suppose I have two tables, Employees and Employers, whichboth have a column named "Id":Employees-Id-FirstName-LastName-SSNetc.Employers-Id-Name-Addressetc.and now I perform the following join:SELECT Employees.*, Employers.*FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)The result-set will contain two "Id" columns, so SQL Server willdisambiguate them; one column will still be called "Id", while theother will be called "Id1." My question is, how are you supposed toknow which "Id" column belongs to which table? My intuition tells me,and limited testing seems to indicate, that it depends on the order inwhich the table names show up in the query, so that in the aboveexample, "Id" would refer to Employees.Id, while "Id1" would refer toEmployers.Id. Is this order guaranteed?Also, why does SQL Server use such a IMO brain-damaged technique tohandle column name conflicts? In MS Access, it's much morestraightforward; after executing the above query, you can use"Employees.Id" and "Employers.Id" (and more generally,"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"column you want, instead of "Id" and "Id1" -- the"just-tack-on-a-number" strategy is slightly annoying when dealing withcomplex queries.--Mike S

View 6 Replies View Related

Exclude Row If Calculated Column Is Zero?

Oct 3, 2013

I am attempting to create an SQL statement that will query a file and give me amount totals by company number/customer number. The totals have to be combined into 4 groups (1/2/3/4) for each amount total in company number/customer number combination. In effect it will look something like this:

00001 | 11111 | 1 | $55
00001 | 11111 | 2 | $12
00001 | 11111 | 3 | $19
00001 | 11111 | 4 | $ 0
00001 | 22222 | 1 | $99
00001 | 22222 | 2 | $53
...and so on.

I HAVE THIS PART WORKING ALREADY. The problem is that I am trying to exclude the rows that have 0 (zero) in the amount column from showing up in the output. The amount is a calculated field of all the invoice for that company number/customer number combination for that sort (eg: Company 00001/Customer 11111/Sort 1 has $55 associated to it). I cannot use the calculated field in my where clause.

I will include a simplified version of my select statement so you can see how I got as far as I have and where to go so I pretty much say "WHERE SUM(SubTBL.Amount) <> 0".

SUM(SubTBL.Amount) As TotAmt,
WHEN (days (currdate) - days (MainTBL.DateFLD)) <= 30 THEN '1'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 31 AND 60 THEN '2'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 61 AND 90 THEN '3'
WHEN (days (currdate) - days (MainTBL.DateFLD))> 90 THEN '4'


View 1 Replies View Related

Exclude Column For Replication In SQL Server 2000

Mar 3, 2008

Hi. I'm creating a new publication for replication. There are a few columns on our main database I do not want published as they contain some confidential information. When I go to create a new publication, I go through the wizard and I select the option to filter rows vertically. I uncheck the columns I don't want published and the wizard completes without any errors.

When I go to start the Snapshot agent to create the database on new server, it gets error saying it cannot find certain columns--the columns I do not want published to begin with.

Am i doing something wrong? I appreciate any help you may provide!

View 7 Replies View Related

Transact SQL :: Need To Exclude Last Column Comma Using Script

Jun 4, 2015

I want to populate data from Production to UAT(except Identity column).For that I created query,its generating script what i have required.But in last column getting with comma.I should eliminate the last comma.

script to my requirement:

declare @TABLE_SCHEMA varchar(10)
set @TABLE_SCHEMA='dbo'
declare @TABLE_NAME varchar(100)
set @TABLE_NAME='Demo_Table'

[Code] ....

View 12 Replies View Related

SQL2K SP4 Gives Error 1706 Creating Multi-statement Table-valued Function Names Beginning With Sys?

Nov 2, 2006

Hi all,

I've created a number of tables, views, sproc, and functions whose names begin with "sys_", but when I tried to create a multi-statement table-valued function with this type of name, I got:

Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 9
System table 'sys_test' was not created, because ad hoc updates to system catalogs are not enabled.

I had a quick look in this forum for 1706 (and on Google) but couldn't find anything. Does anyone know for certain if this is a bug in SQL2K?

Thanks, Jos

Here's a test script:
T-SQL code to test creation of three types of function where the function name begins with "sys_".
Jos Potts, 02-Nov-2006


PRINT 'Scalar function with name "sys_" creates ok...'




PRINT 'In-line table-valued function with name "sys_" creates ok...'




PRINT 'Multi-statement table-valued function with name "sys_" generates error 1706...'

(c INT)







And heres the output from running the test script in Query Analyser on our server:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Scalar function with name "sys_" creates ok...

In-line table-valued function with name "sys_" creates ok...

Multi-statement table-valued function with name "sys_" generates error 1706...
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 11
System table 'sys_tmp' was not created, because ad hoc updates to system catalogs are not enabled.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the function 'sys_test', because it does not exist in the system catalog.

View 3 Replies View Related

SQL Server 2012 :: Exclude Rows Where Value In Column Not Found In Another Row

Jul 16, 2014

This is a followup to a previous question to a previous but in reverse of Find rows where value in column not found in another row

Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...

I would like to exclude any two rows where Type is equal to 'TypeA' and Key2 is Null that have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row.

So, given the data

**KEY1** **Key2** **Type**
1 NULL TypeA
2 5 TypeA
3 1 TypeB
4 NULL TypeA
5 NULL TypeB
6 26 TypeC
7 NULL TypeD
8 NULL TypeD

I would like to return all the rows except where Key=1 and Key=3 because those rows together meet the criteria of Type='TypeA'/Key2=NULL and does have a corresponding row with Type='TypeB'/Key1=Key2.

View 2 Replies View Related

SQL Server 2014 :: Exclude Duplicate And Fetch Max Of X Column

Sep 11, 2014

I want to fetch max of Field2 if duplicate records in Field1 and rest of the values of field1 , below is the sample format.

Field1 Field2 Field3 Field4
32 375 abc-xyz A
32 379 xyz-efg A
55 405 abc-xyz B
55 407 xyz-efg B
132 908 abc-xyz C
132 999 xyz-efg C
152 800 abc-xyz D
152 850 xyz-efg D
155 900 abc-xyz E
156 925 abc-xyz F
157 935 abc-xyz G

View 2 Replies View Related

Tables Joined On Multiple Columns, Exclude Records Found In Table A

Nov 7, 2006

I'm using SQL server 200

Table A has columns CompressedProduct, Tool, Operation

Table B in a differnt database has columns ID, Product, Tool Operation

I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.

But I want to be able to select any records that are in table A but not in Table B.

ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.

This code return all the records from A. I need to filter out the records found in Table B.


SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link,
'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts
JOIN tblProduct p ON ts.ProductID = p.ProductID
JOIN tblTool t ON ts.ToolID = t.ToolID
JOIN tblOperation o ON ts.OperationID = o.OperationID
WHERE ts.ToolID=66
') oq
LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation

View 1 Replies View Related

Transact SQL :: Delete Records From Table (Table1) Which Has A Foreign Key Column In Related Table (Table2)?

Jun 29, 2015

I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).

Table1 columns are: table1Id; Name.  Table2 columns include Table2.table1Id which is the foreign key to Table1.

What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?

View 11 Replies View Related

How To Update One Column To Be The Same For All Records In Database Table?

May 6, 2007

I have a set of records in database table and I want to update one column to be the same for all of them.
Can you suggest code solution?

View 1 Replies View Related

Selecting Records From One Column In A Table And Inserting..

Feb 16, 2006

Godwin writes "Hello,
Heres my question..
I have 2 tables.2 paticular columns exist in both the tables.
I want to be able to select those 2 columns on the 1st table and insert them on to the same 2 columns on the 2nd table.

Now,this 2nd table has another 3 columns that exist in another table.I would like to take those 3 column values from that 3rd table and insert it into the 2nd table by modifying those existing records in the 2nd table.In the 3rd table,there will be around 5 records...I want to copy the existing records 5 times in the 2nd table and insert the 3rd tables rows inside the 2nd table in that respective column for 5 rows.

I hope you understand what I mean...Im sorry for really confusing..
Please help me

View 1 Replies View Related

Counting Distinct Records Of Column 1 With A Certain Value In Column 2

Feb 6, 2008

Ok, so I need to count the Distinct records from column 1 in which there is not a true value in any of the records for that distinct column 1 number. Here is a short example of my records:dbo_dbWafer_Slicing



1/8/2008 9:54:00 AM    

1/8/2008 9:53:00 AM    

1/8/2008 9:53:00 AM    

1/8/2008 9:52:00 AM    

1/8/2008 9:50:00 AM    

1/4/2008 10:46:00 AM    

1/4/2008 10:46:00 AM    

1/4/2008 10:45:00 AM    

1/4/2008 10:45:00 AM    

1/4/2008 10:44:00 AM    

1/4/2008 10:44:00 AM    

1/4/2008 10:43:00 AM    

 So, how many Distinct SawRunNumbers had no passing wafers? In trying to do this I've come up with:"SELECT COUNT(DISTINCT SawRunNumber) AS BouleCount FROM dbWafer_Slicing WHERE (SawDate >= @MinDate) AND (SawDate <= @MaxDate) AND (Pass = 1) HAVING (COUNT(DISTINCT WaferID) > 0)" but that doenst work. It still counts records where pass = 0 for distinct SawRunNumbers even if one record within that SawRunNumber is passing. From the above sample data I should get a result of 1 not 2 or 3. Can I do this with this set of data? I'm using SQL Server 2005 EE.Thanks for your help. 

View 6 Replies View Related

SQL Server 2012 :: Return Random Records In A Table-valued Function?

Dec 19, 2013

My overarching goal is to generate sets of random Symptom records for each Enrollee in a drug study, so that for each cycle (period of time), the code will insert a random number of random records for each enrollee.

I'm trying to return a number of random records from a table, but inside a table-valued function... (which could be my problem).

CREATE FUNCTION dbo.ufn_GetTopSymptoms (
@enrollID INT
, @CTCVersion VARCHAR(20)
, @NumRecords INT

[Code] ....

But that ORDER BY NEWID() clause is illegal apparently, because here's the error it throws:

Msg 443, Level 16, State 1, Procedure ufn_GetTopSymptoms, Line 13
Invalid use of a side-effecting operator 'newid' within a function.

I was hoping I could return a set of enrollmentIDs and then use CROSS APPLY to generate a random set of records for each enrollmentID... is this not possible with APPLY? I was trying to avoid using a cursor...

The idea is basically to create all the Symptom records for all the patients in treatment cycle at once by using Enrollee OUTER APPLY dbo.ufn_GetTopSymtoms(dbo.Enrollment.EnrolleeID)

but that's clearly not working. Is there a way to do this without resorting to a cursor?

View 9 Replies View Related

How To Pull Few Records From A Table (Column) And Concatanate And Return As A Single Value.

Aug 6, 2007

Hi Gurus,
I have a table having sales records and there are more than one record per one customer. The sales table has a reference number like below.





I need to do a query and generate the following query.

CustomerID Ref
2 H_1123,H_2344,H_4322

Could someone help me on this.


View 3 Replies View Related

SQL Server 2008 :: Archive And Delete Old Records In Table With No Date Column

Feb 4, 2015

I have these two tables Log and CategoryLog, I need to archive records older than 13 months in these two tables to two separate tables and then delete the archived records from Log and CategoryLog tables. The problem is that only 'Log' table has a date column, the other table CategoryLog does not have any date column. But the two tables are connected by a column(LogID). How to archive the data and then delete the archive data from both tables.

View 9 Replies View Related

TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column

Sep 7, 2007

Hi guys,
If I have a temporary table called #CTE
With the columns
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?

I am trying to solve the question in the link below:

Thanks in advance,

I have tried the code below, but getting syntax error...

[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;

I have also tried:

[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;

View 18 Replies View Related

Updating A Single Column On A Table SQL Server 2005 (45 Records), The Session Hangs...

May 12, 2007

Hello, I am pretty new with SQL Server 2005.

I have installed SQL Server Express Edition. I have migrated a set of tables from Oracl10g (by using Microsoft's Migration Tool Kit).While I am trying the following simple update command, the session hangs and it never finishes !!!!!!!!!!!!


select pos_key from pos_station where staff_key = 1105


update pos_station set pos_key = 1 where staff_key = 1105


The table has a few constraints and a couple of indices in place.

Then I create another table (but no contraints or indices), just copy the data from the problematic one and the update WORKS (in msecs) :

update pos_station_new set pos_key = 1 where staff_key = 1105


Is there any way to tell if the table (any table in SQL Server) is corrupted or not ?

How can I tell if a session is waiting for something and what is that something ?

Thank you very much for your help.


View 7 Replies View Related

How Can I Create A New Table With Its Column Named From Another Table's One Column Value By Using A Select Sentence?

Sep 27, 2006

For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".

can you tell me how can I complete this with the SQL?

View 2 Replies View Related

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