T-SQL (SS2K8) :: Query Column With Multi Values Using IN?

Aug 26, 2014

I am working with a table that has a column which stores multiple data/values that are comma separated.

I need to be able to query that table and get those rows where the values in that column match a pre-defined search list.

I was thinking of somehow trying to take the search list and convert it to a table(temp or a cte) and then JOIN to the table.

however, since the column may contain multiple values, i would need to parse/separate that first. I am not sure how to parse and then join to a list (if that is even the best way to solve this) to only get the rows where the search column contains one or more of the items we're looking for.

Below is some sample data:

Declare @BaseTable table (PKCol int, Column2Search varchar(2000))
Insert into @BaseTable (PKCol, Column2Search)
Select 1001, 'apple,orange,grapefruit'
UNION ALL
Select 1002, 'grapefruit,coconut'
UNION ALL

[Code] ....

View 8 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Query To Display Different Values From Same Column In Same Table?

Sep 18, 2015

I have a table with a column AttributeNumber and a column AttributeValue. The data is like this:

OrderNo. AttributeNumber AttributeValue
1.-Order_1 2001 A
2.-Order_1 2002 B
3.-Order_1 2003 C
4.-Order_2 2001 A
5.-Order_2 2002 B
6.-Order_2 2003 C

So the logic is as follows:

I need to display in my query the values are coming from Order_1, means AttributreValues coming from AttibuteNumbers: 2001,2002,2003...and Order_2 the same thing.

Not sure how to create my Select here since the values are in the same table

View 2 Replies View Related

T-SQL (SS2K8) :: How To Show Multiple Values In Column Of Join Query

Nov 6, 2014

in my table i ve the column of item code which contains '1000' ,'2000' ,'3000' series i jus wanna display the output of item codes '1000','2000'series and some of ('3000019','3000020','3000077','3000078').

i tried in my join query

these code left(itemcode,4) in ('1000','2000') or itemcode in ('3000019','3000020','3000077','3000078')

its taking so much of time how t o solve ?

View 1 Replies View Related

T-SQL (SS2K8) :: Appropriate Query For Multi Parameters?

Mar 13, 2014

I am using the following code in my query to fetch data for my ssrs report which have a parameter @auditCode, where multiple auditCodes can be inputted to generate the report.

Is there any other way I can achieve the same functionality avoiding the part charindex(LU.auditCode,@auditCode)<>0 , as it will return wrong results.

For instance, it will return, the results for the audit code ‘INPS45’ and ‘INPS450000’ when audit code ‘INPS45’ is inputted.

SELECT distinct Ac.activityCode,
Ac.ActivityName + isnull(Ac.description,'') AS ActivityName,
Ac.activityStartDate, Ac.activityEndDate,
LU.auditCode,
LU.AuditName,
St.studyCode AS StudyCode,
St.StudyName AS StudyName

[Code] ....

View 3 Replies View Related

Sum Up Multi Values Same Column

Feb 8, 2008

Hi All,

I have the following Ex:

Table A
Col A <- Col that I need to check against
Col B
Col C <- Sum this col


So if Col A has the following values:
Col A Col C
2 10.00
4 15.00
2 25.00
4 15.00
3 10.00
3 5.00
7 4.00
9 20.00

I need to bring back the sums of 2, 4 and 3 in one resultset. How would I do this?

Thanks,

JJ

View 10 Replies View Related

T-SQL (SS2K8) :: Elegant Query Needed To Combine Multi Rows Into One

Apr 8, 2014

Table:

CREATE TABLE [dbo].[KPI](
[SVP] [varchar](20) NULL,
[Wk1] [int] NULL,
[Wk2] [int] NULL,
[Wk3] [int] NULL,
[Wk4] [int] NULL,
[Wk5] [int] NULL,
[Y] [int] NULL,
[Q] [int] NULL,
[Wk] [int] NULL
)

To generate sample data:

insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 1,0,0,0,0,2014,1,1)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,2,0,0,0,2014,1,2)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,3,0,0,2014,1,3)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,4,0,2014,1,4)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,0,0,2014,1,5)

[Code] ....

Current result:
SVPWk1Wk2Wk3Wk4Wk5YQWk
SVP10000201411
SVP02000201412
SVP00300201413
SVP00040201414

[Code] ....

Expected result:

SVPWk1Wk2Wk3Wk4Wk5YQ
SVP1234020141
SVP30 1 2 6 9 20142
SVP103226820143
SVP17233141120144

I surely can loop each row and insert the needed value into the result, I want to know if there is a better way to generate the result.

View 2 Replies View Related

T-SQL (SS2K8) :: Assign Next Available Column Values?

Sep 29, 2014

find below object and data:

create table #StuDetails(City varchar(25),StuStatus varchar(25), currentValue int,Week1 int,week2 int,week3 int,week4 int)
insert into #StuDetails values('A','new',13,10,0,0,12)
insert into #StuDetails values('B','Old',10,10,41,0,12)
insert into #StuDetails values('C','Fail',10,9,0,0,5)
select * from #StuDetails

Output of above is display as:

CityStuStatuscurrentValueWeek1week2week3week4
Anew 13 10 0 0 12
BOld 10 10 41 0 12
CFail 10 9 0 0 5

Now for columns Week1 to week3 if value is 0 then i want to display by searching next week value, if it is also 0 then go for next week and if value found there then display instead of zero. so my output would be as below instead of above.

CityStuStatuscurrentValueWeek1week2week3week4
Anew 13 10 12 12 12
BOld 10 10 41 12 12
CFail 10 9 5 5 5

View 2 Replies View Related

T-SQL (SS2K8) :: Get Column Names Where Values Are Not Matching

May 20, 2014

We have 2 tables (table a and b)

select a.* from table a inner join table b
on a.col1<> b.col2

I would like to have column names where the values are not matching.

View 4 Replies View Related

T-SQL (SS2K8) :: Concatenate Specific Values From A Column

May 20, 2014

Give this data set;

declare @BO table (Col1 varchar(50), Col2 varchar(50))
insert into @BO values
('01','009920140516102116'),
('071710811019600001000005',''),
('070534524264000001000005',''),
('001806505517000001000005',''),

[Code] ....

select * from @BO

The 2 digit number that appears on line 1, 7 and 13 (only in this example) i need to have added to the begin of each value below it until the next 2 digit number is encountered. The desired result set would look like:

declare @BOD table (Col1 varchar(50), Col2 varchar(50), col3 varchar(50))
insert into @BOD values
('01','009920140516102116',''),
('071710811019600001000005','','01071710811019600001000005'),
('070534524264000001000005','','01070534524264000001000005'),

[Code] ....

View 7 Replies View Related

T-SQL (SS2K8) :: Comparing Column Values In Same Table

Jun 16, 2014

How to resolve the below task

create table #temp ( idx int identity(1,1), col1 int, col2 int )

Here i want a flag success or fail on basis of below conditions.

I need to take all the col1 values and then i need to compare to each other.

if any difference found, i need to check difference more than 30, then it should raise the flag as "Failure".

if all the col1 values are ok , then we need to check Col2 values same as above.

--case 1

insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16582,18522

--select * from #temp

--truncate table #temp

Because of difference in col1 values . the value of flag should be fail.

--case 2

insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16522,17522

Here also the col1 is ok but col2 values have difference so it should be Fail.

Otherwise it should be success.

View 6 Replies View Related

T-SQL (SS2K8) :: Dividing Two Values Of Same Column / Grouping

Aug 4, 2014

I'm trying to divide two values from separate rows. Each row is a separate UNION statement.

2014-08-03 00:00:00.000NKBB (N) - Total Offers 1218 UNION (A)
2014-08-03 00:00:00.000NKBB (N) - With Lead 301 UNION (B)
2014-08-03 00:00:00.000NKBB (N) - Without Leads 917 UNION (C)

In the below example, I would like to divide KBB (N) - With Lead (UNION (B)/KBB (N) - Total Offers UNION (A)

What would be the best way to accomplish this?

View 1 Replies View Related

T-SQL (SS2K8) :: Split The Column And Parse Values

Aug 18, 2014

I have a column with the following information in it. I need to split the column and parse the values. I have Parsed one single values from but, not multiple times.

<TV<MR1#4.0#true#2.0#USD>VT>,<TV<MR2#3.0#true#1.5#USD>VT>,<TV<MR3#0.0#true#0.0#USD>VT>,<TV<MR4#0.375#true#0.19#USD>VT>

MR1 -Model Code,
4.0 - Percentage,
true - isAbs,
2.0 - AppliedValue --> This is Actual amount applied as tax.
USD - Currency

For the example provide , the totalTaxValue would be 2.0 + 1.5+0.0+0.19 = 3.69

View 5 Replies View Related

T-SQL (SS2K8) :: List When Both Column Values Are Null

Feb 24, 2015

I have the following tables:

tbl_Person (personID, first_name, lastname)
tbl_student (studentID)
tbl_stupar (personID, StudentID, relationship)
tbl_person_Phone (personID, phone)

I need to list all students who have both parents phone number is null. The parent relationship values 1 and 2 indicates the person is either Mom (value 2) or dad (value 1) of the student. Note: I am using student parent as an example to write my query.

View 4 Replies View Related

T-SQL (SS2K8) :: Remove Non-numeric Values From A Nvarchar Column

Dec 9, 2014

We have an address table with a house_num field which is a nvarchar.

Most of house numbers are numbers like 1234, 0989

But some of them have a letter behind it like 678 B, 8909 F, even some like this 123/B

We would like to remove any non-numeric letter for this column.

Is there a way to do it?

View 4 Replies View Related

Can A Calc'd Query Column Be Compared Against A Multi Value Variable Without A Nested Query?

Nov 15, 2007

do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like

SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)

View 1 Replies View Related

T-SQL (SS2K8) :: SUM OVER Coming Up With Different Values Than CTE Query

Mar 6, 2015

I am currently reading through Itzik Ben-Gan's "Microsoft SQL Server 2012 High-Performance T-SQL using Windows Functions." In attempt to test the SUM OVER() function in SQL 2008 because that's what I've got. I do not currently have sample data (trying to generate it has become a major PITA), but I have some pseudocode.

My current code (actual production code) pulls a bunch of ITD (inception to date) contracts then calculates a certain dollar amount based on monthly changes. Not all contracts have values during a given month, so here's what I cobbled together a few months ago. (Per our finance team, these numbers ARE accurate).

WITH MonthlyVals AS
(SELECT ContractID, SUM(Col1 - (Col2 + Col3 + Col4 + Col5)) AS MyTotal
FROM MyTable
WHERE MyDate >= @ThisMonthStartDate AND MyDate <= @ThisMonthEndDate
AND StatementType IN (8,4,2)

[code]....

To test the totals, I also added a COMPUTE SUM(MyTotal) to the end of each query. (Yes, I know COMPUTE is deprecated. Just wanted a quick check.). The difference between the two bits of code was over 68k, with the SUM OVER() code coming up with a total higher than the CTE code. I know CTE code is correct for a fact. It went through extensive testing before getting put in Production. Is it the way I joined the table for the SUM OVER()? Or is it the use of PARITION BY?

View 5 Replies View Related

T-SQL (SS2K8) :: Creating Range Between Values In Query?

Apr 26, 2015

On a new project i need to create possible ranges between a specific interval.

suppose i have this main product:

main product : LY E67F

Also, in first level i have a table classify by Group depending on Intensity.

table group
Group intensity
AA 1120
AB 1400
BA 1800
BB 2240
CA 2800

I need to create these diferent options:

1 option : AAAB or AABA or AABB or AACA
2 option : ABBA or ABBB or ABCA
3 option : BABB or BACA or BBCA
or beginning from the end
1.option CABB or CABA...
or beginning from the middle
1.option BBBA or BBAB
and so on.

I fact, i need to find all available options possibles to build article code, like a matrix.

View 7 Replies View Related

T-SQL (SS2K8) :: Include Row Values As Columns In Select Query

Apr 28, 2015

How to include row values as columns in my select query. I have a table that stores comments for different sections in a web application. In the table below, I would like display each comment as a new column. I only want one row for each record_ID.

Existing table layout

table name - tblcomments
Record_ID Comment_Section_ID Comment
1 5 Test 5 comment
1 7 Test 7 comment
2 5 New comment
2 7 Old comment
3 5 Stop
3 7 Go

Desired table layout
table name - #tempComment
Record_ID Comment_Section_5 Comment_Section_7
1 Test 5 comment Test 7 comment
2 New comment old comment
3 Stop Go

Once I figure out how to get the data in the layout above, I will need to join the table with my record table.

table name - tblRecord
Record_ID Record_Type_ID Record_Status
1 23 Closed
2 56 Open
3 67 Open
4 09 Closed
5 43 In progress

I would like to be able to join the tables in the query below for the final output.

Select r.Record_ID, r.Record_Type_ID, r.Record_Status,
c.Comment_Section_5, c.Comment_Section_7
from tblRecord r
left outer join #tempComment c
on r.record_ID = c.record_ID

How I can get the data in the desired #tempComment table layout mentioned above?

View 2 Replies View Related

T-SQL (SS2K8) :: Query To Group By Only One Column

Jan 21, 2015

How can we write the query using groupby. I need to have group by only one column. Is it possible through subquery?

select col1, col2, col3
from testtable
group by col1.

View 4 Replies View Related

How Do I Enter In A Default Values For A Report Parameter That Accepts Multi Values

Apr 11, 2008



I have my stored procedure set to
Territory_code IN (@Territory)

, now , how do i enter in more then one value. When i select the multi value check box, it gives me more spaces. But then doesnt recognize the values when i put in more then one. am i doing something wrong?

The field is a Varchar 20

View 1 Replies View Related

Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List

Sep 7, 2015

We have SharePoint list which has, say, two columns. Column A and Column B.

Column A can have three values - red, blue & green.

Column B can have four values - pen, marker, pencil & highlighter.

A typical view of list can be:

Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil

We are looking to create a report from SharePoint List using SSRS which has following view:

                    red     blue   green
    pen            2       0      1
    marker       0       1      0
    pencil          1       3      0
    highlighter  1       1      1 

We tried Sum but not able to display in single row.

View 2 Replies View Related

T-SQL (SS2K8) :: Count Number Of Values That Exist In A Row Based On Values From Array Of Numbers

Apr 16, 2014

How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:

Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10

------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)

Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10

Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5

View 2 Replies View Related

Integration Services :: SSIS Package - Replacing Null Values In One Column With Values From Another Column

Sep 3, 2015

I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.

What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column. 

I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far. 

View 3 Replies View Related

T-SQL (SS2K8) :: Dynamically Change Values In Script Based On 3 Values

Feb 27, 2014

I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the

1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:

where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)

2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.

3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)

If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.

Again, the series are usually discontinuous and specific to each specialty.

Here's the portion of the script (it's at the end) that I'm talking about:

--change values in square brackets below for each specialty as needed and change the denom number in the very last query.

if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T

[Code] ....

If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.

View 6 Replies View Related

Add Symbol To Column Values And Convert Column Values To Western Number System

Feb 12, 2014

I want to add $ symbol to column values and convert the column values to western number system

Column values
Dollar
4255
25454
467834

Expected Output:
$ 4,255
$ 25,454
$ 467,834

My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application

COST is the int datatype and needs to be changed.

View 2 Replies View Related

T-SQL (SS2K8) :: Aggregate Monthly Values From Interval Values?

May 14, 2014

I want to aggregate to monthly values for the reading. I want to display Reading value for Oct 2010, November 2010 likewise My question is simple and I have tried to follow the etiquette.

Currently it is displaying.....

MeterIDReadingdateReading
3969 22/10/2013 0:150
3969 22/10/2013 0:300
3969 22/10/2013 0:450
3969 22/10/2013 1:000
3969 22/10/2013 1:150
3969 22/10/2013 1:300
3969 22/10/2013 1:450
3969 22/10/2013 2:001
3969 22/10/2013 2:150
MeterId int
ReadingDate datetime
Reading real

-===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable
(
meterID INT PRIMARY KEY,
Readingdate DATETIME,
reading real
)

--===== Setup any special required conditions especially where dates are concerned

SET DATEFORMAT DMY
SELECT '4','Oct 17 2013 12:00AM','5.1709' UNION ALL
SELECT '4','Oct 17 2013 12:15AM','5.5319' UNION ALL
SELECT '4','Nov 17 2013 12:00AM','5.5793' UNION ALL
SELECT '4','Nov 17 2013 14:00AM','5.2471' UNION ALL
SELECT '5','Nov 17 2013 12:00AM','5.1177' UNION ALL
SELECT '5','Nov 17 2013 14:00AM','5.5510' UNION ALL
SELECT '5','Dec 17 2013 15:00AM','5.5128', UNION ALL
SELECT '5','Dec 17 2013 16:00AM','5.5758' UNION ALL

Output should display as

MeterId Period Reading

4 Oct 13 10.20
4 Nov 13 10.40
5 Oct 13 10.20
5 Nov 13 10.40
4 Dec 13 11.15

View 4 Replies View Related

Query To Parse Out Values From One Column Into Different Columns

Feb 2, 2006

I have a table where different types of values are stored in one field, but I need to seperate them into different fields based on a value in another field.

For (hypothetical) example:

There is an existing table with following info in three columns:
userid record recordtag
1 joe 1
1 j 2
1 jr 3
2 bob 1
2 a 2
2 sr 3
where recordtag indicates (1 for first name, 2 for middle initial, 3 for suffix)

I need to query these records for a report so it the output is:

userID firstname middleinitial suffix
1 joe j jr
2 bob a sr


What's the most efficient approach to create a query that will give me desired results? I have managed to create a very complex query that derives tables for each column I want to create and queries off of that derived table for the 'record' value based on the 'recordtag' values for a given 'userid'. The query is extremely slow, so I know there's some better way out there to get the results I want. Any help would be greatly appreciated. Thanks.

View 3 Replies View Related

T-SQL (SS2K8) :: Update Table With Multi-line Comments?

Apr 14, 2014

How to update column with multiline comments.

eg. I need to update Notification column in my table as below.

Exit Formalities Notification sent to:

Employee: LastName, FirstName
EmployeeEmail: EmployeeEmailAddress

View 4 Replies View Related

T-SQL (SS2K8) :: Unpivot Syntax And Multi-part Identifiers

Jan 29, 2015

This query is the first time I am using the Unpivot syntax and I am coming across a problem. When I try to unpivot my data, I get the following statement:

"Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Table3.DocketId" could not be bound."

What is the cause of this issue?

Select
Table3.DocketId,
UP.AssignmentType,
Up.AssignedStaff
From
(
Select distinct
Table2.DocketId,

[Code] ....

View 3 Replies View Related

Query To Get Values From Datetime Column Into Comma Separated Text

Dec 20, 2005

Hi All
I am working on a query to get all the datetime values in a column in a table into a comma separated text.
eg.
     ColumnDate                                                  --------------------------- 2005-11-09 00:00:00.0002005-11-13 00:00:00.0002005-11-14 00:00:00.0002005-11-16 00:00:00.000
I wanted to get something like
2005-11-09, 2005-11-13, 2005-11-14, 2005-11-16 
Have just started SQL and hence am getting confused in what I think should be a relatively simple query. Any help will be much appreciated. Thanks

View 1 Replies View Related

HELP With SQL Query: Select Multiple Values From One Column Based On &&<= Condition.

Aug 7, 2007

Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation:

I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner:

(AISC_Shapes_Table)

AISC_MANUAL_LABEL W
W44x300 300
W42x200 200
(and so on)
WT22x150 150
WT21x100 100

(and so on)
MT12.5x12.4 12.4
MT12x10 10
(etc.)

I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is:




Code SnippetSELECT AISC_MANUAL_LABEL, W
FROM AISC_Shape_Table
WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%'))



It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance.

Regards,

Steve G.



View 4 Replies View Related

Parameterized Query That Counts The Number Of Null Values In An Int32 Column

Jul 18, 2006

Using c# in the compact framework, is there a way to do a parameterized query for counting the number of records where a specified column is null. I tried all of these, but none of them work:

cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null));
cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";

cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value));

cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";

cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null));

cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value));


cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));
cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));

cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;

cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));

cmd.Parameters["@IntColumn"].Value = DBNull.Value;

cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";



cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));


cmd.Parameters["@IntColumn"].Value = DBNull.Value;


cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));


cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));



cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;

cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn";

md.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));



cmd.Parameters["@IntColumn"].Value = DBNull.Value;

cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn";



cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));




cmd.Parameters["@IntColumn"].Value = DBNull.Value;


cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn";

Whenever I use a "is not" in the query I get an exception, otherwise it returns a count of 0 which is not accurate. Any help will be appreciated.

View 5 Replies View Related

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







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