Cannot Check Column In Where Clause

Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave

View Replies !

---

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause
2 examples:
 
1) Rows ordered using textual id rather than numeric id


Code Snippet
select
 cast(v.id as nvarchar(2)) id
from
 (
  select 1 id
  union select 2 id
  union select 11 id
 ) v
order by
 v.id

 
 



Result set is ordered as: 1, 11, 2
I expect: 1,2,11

 
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
 
2) SQL server reject query below with next message
 
Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

 


Code Snippet
select
 cast(v.id as nvarchar(2)) id
from
 (
  select 1 id
  union select 2 id
  union select 11 id
 ) v
 cross join (
  select 1 id
  union select 2 id
  union select 11 id
 ) u
order by
 v.id
    ,u.id

 


Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
 
It reproducible on
 
Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

 
and
 

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
 
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
 
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
 
Could someone clarify - is it bug or expected behaviour?

View Replies !

---

Check For Column Before Adding A Column
How can I test to see if a column exists before adding a column to a sql mobile table?


thanks,

Luis

View Replies !

---

Check If Column Is Substring Of Another Column
I have two columns.
policyNumber contains a 12-13 varchar string
AllPolicyNumbersIncluded contains one or more 12-13 varchar strings (policy nums) seperated by commas

I want to check if policyNumber is contained in AllPolicyNumbersIncluded?

I have policyNumber LIKE AllPolicyNumbersIncluded which works when only one policy number is in AllPolicyNumbersIncluded and incidently works switched around AllPolicyNumbersIncluded LIKE policyNumber I assume because they are equal.

Can anyone tell me how to check if one column's value is a substring of another - without going through every possible substring of the second

View Replies !

---

Using Column Name In 'LIKE' Clause
Hi i want to join two tables basing on like condition of the column values of two tables.
My query is like this:

select
pc_assign_worklist.pxRefObjectInsName AS "pxRefObjectInsName",
pc_assign_worklist.pxUrgencyAssign AS "pxUrgencyAssign",
pc_assign_worklist.pyLabel AS "pyLabel",
pc_assign_worklist.pyAssignmentStatus AS "pyAssignmentStatus",
pc_assign_worklist.pxAssignedOperatorID AS "pxAssignedOperatorID" ,
pc_assign_worklist.pxCreateDateTime AS "pxCreateDateTime" ,
pc_assign_worklist.pxCreateOpName AS "pxCreateOpName",
pc_index_workparty.MemberIdentifier AS "MemberIdentifier",
pc_index_workparty.LastName AS "Last Name",
pc_index_workparty.FirstName AS "First Name",
pc_index_workparty.pxInsName AS "pxInsName"
from
dbo.pc_assign_worklist, dbo.pc_index_workparty
where
pxAssignedOperatorID ='dasxkx1'
AND pc_index_workparty.pzInsKey Like '%'+pc_assign_worklist.pxRefObjectInsName+'%' ORDER BY pxUrgencyAssign DESC

-----------

i want to compare the two columns of two tables using like or contains clause as column1 in table a has value like "hi i am" where as column2 in table2 has value "hi". I need help on how to accomplish this.

View Replies !

---

Selecting The Same Column Twice In The Same Where Clause
Hi :From a crystal report i get a list of employee firstnames as a stringinto my store procedure. Why is it comming this way ? hmmmmmm it's aquestion for me too.ex: "e1,e2,e3"here are my tablestblProjectsProjectId123tblEmployeeemployeeId FirstName1 e12 e23 e3tblProjectsToEmployeeProjectId employeeId1 11 21 32 12 23 13 23 34 14 3i need to find out the project ids all 3 of these employees worked on.so the out put i need isprojectId13How can i get it ????????????now i can use replace command to format it to a OR clause or ANDclauseSET @string= 'employeeId =' + '''' + REPLACE('e1,e2,e3',',',''' ORemployeeId = ''') + ''''some thing like this.OR clause will give me all 4 projects.in('e1','e2','e3') will give me all 4 projects.of cause AND command will not give me any.other method i tried was adding the employee table 3 times into thesame SQL string and doing some thing likeWHERE (empTable1.Firstname ='e1' AND (empTable1.Firstnamein('e2','e3'))AND (empTable2.Firstname ='e2' AND (empTable1.Firstname in('e1','e3'))AND ...and goes alone. this gives me some what i needed. but it's a verymessy way of doing it, because i get a comma seperated stringparameter i have to construct the sql string on the fly.any help or direction on this matter would greatly appreciated.thankseric

View Replies !

---

Using @column For Where Clause In Select
Hi,

I want to do something like

"select range from mytable where @ColumnToUse = 1" then
"select range from mytable where @ColumnToUse = 2" etc.

and @columnToUse lists a different column heading each time. I know I can put this into an "exec ('select range...')" type statement, but it's really helpful if I don't evaluate all my variables at this stage in my scripts. Can anyone help?

THanks
Dan

View Replies !

---

Multi-column IN Clause
 

Hi,
 
I have a [TestTable] table with three rows. The pair of columns [Test1] and [Test2] are id, the [Test3] is a data column. First, I get a table variable with list of id pairs. Next, I would like to update the rows of that ids. However, I have not found the elegant way how to do it. For one column it is simple, just IN clause, which does not work (or I could not find how) for multi-columns. Does someone have a hint?
 
Thanks,
 
Martin
 
Note: The example bellow is dummy; on the other hand, I hope it shows the important points. Please, do not beat me on syntax errors.
 



Code Block
DECLARE @MyTableVar table(
 Test1 int NOT NULL,
 Test2 int NOT NULL
);
 
SELECT [Test1],[Test2] INTO @MyTableVar FROM [TestTable] WHERE [Test3] = '%dd%';
 
UPDATE [TestTable] SET [Test3] = [Test3] + 'ds'
WHERE ([Test1], [Test2]) IN (SELECT [Test1], [Test2] FROM @MyTableVar);
 


 

View Replies !

---

How To Check If A Column Is Numeric Or Not
 I want to fin out the number of records that are not numeric on my database, how can I fin that using an SQL query?

View Replies !

---

Varchar Column Check
Can't seem to get my head around this: I'm looking for a way to select only those varchar(10) values that soley consist of numbers. Leading spaces are allowed.

I tried using isnumeric, but it also allows those with periods, comma's etc.
Also tried using like, but the length of the varchar column varies too much to do a like '[0-9][0-9]....'.

As a solution I currently do a combo of isnumeric, not like '%.%', not like '%,%' etc. I need to do a conversion to an int to join another table, but the convert still fails. Not sure where and why.

I'm thinking there should be a better way than create a hughe list of "not like " but it looks like I'm in the woods here...

View Replies !

---

How To Check For A Numeric Value In A Column
Hello All,

Can someone pls help me with how to check for a numeric value in a varchar column?

For example I have a column called client_id , it has values "AB" , "CD" , "18", "19" . I need to delete those client_id where the values are 18 and 19. How would I do that?

Thanks in advance!!

View Replies !

---

Check To See Atleast One Column Has Value
 

I have 5 to six id columns from my excel input.they can be empty in some cases but of the five columns atleaset one column should have some value....All the five columns should not be empty.
 
How do i check this scenario...
 
eg:Input column
ID1   ID2    ID3         ID4    ID5 
12    NULL   NULL     67     78
 
I should check to see atleast one column should have some value
 
 
How can i achieve this???
 
Please let me know
 

View Replies !

---

How To: Check If A Column Exists, And If Not, Add It?
Well, actually, as in the title. I have a table. The script should add a column if that column doesn't exist already. I use VB to combine the two queries. So what I want:

Query1: Does the column exists:

if No,

query2: create the column

How can I achieve this?

View Replies !

---

Check For Column && Insert
Can you tell me if this is possible? (and how to do it!!)

The application is VS2005, with sql database.

I want to check if a specific column exists in a specific table in the database and if not then add it, all via my application.

I'm happy knowing how to connect to the database & pass sql commands (as I'm doing that anyway to set off backups), but not the actual queries I'd need.

View Replies !

---

Multiple Column Check
I am trying to query multiple columns for a specific value. I have 8 columns (values are either 1 or 0)and I want to query the table to find out which rows contain zero's in ALL of the 8 columns. Whats the best way to do this? I can create a lenghty select statement where column1 =0 and column2 =0 and column3=0 and column4 =0 and column5 =0 .... etc. I was wondering if there was an easier way to do this?


Thanks in Advance
Shankar.N

View Replies !

---

Problem With WHERE Clause When Column Length &> 255
Hello, I am using the JDCB-ODBC driver for my app, but one of the columns in my WHERE clause is 255 characters long. Because of this no rows are returned even though the statement should return 1 or 2 rows. I've tried other JDBC-ODBC drivers too and they have the same problem. Additionally, I've tried using RTrim(), Substring(), etc and it still will not work....any ideas? Is it a driver bug? Anyone know of a driver that will work?

Sample code....

ResultSet rs = dbRetrieve.getStatement().executeQuery( sql ) ;
if (rs.next()) {
// Never gets here
}

Thanx!

View Replies !

---

Computed Column In Where Clause - QUERY Help
Hi
I need suggestion for a query. Consider following 2 tables.

Table-1 "T1"
-----------
|ID|Name
|1 |abc
|2 |def
|3 |erw
|4 |rwg
|5 |her


Table-2 "T2"
----------
|ID|Qty
|1 |12
|1 |2
|2 |22
|3 |10
|2 |14


I want a query which displays ID, Name and MAX(Qty) for each item where Max(Qty)>=10 i.e. result should be


Result
----------
|ID|Name|Qty
|1 |abc |12
|2 |def |22
|3 |erw |10

I tried:

Select t1.*, (Select Max(Qty) From T2 where ID=t1.ID) as MaxQty
FROM T1 t1
WHERE MaxQty>=10

But it fails as computed or inline query columns can not be added in where clause.


However following works:
Select t1.*, (Select Max(Qty) From T2 where ID=t1.ID) as MaxQty
FROM T1 t1
WHERE (Select Max(Qty) From T2 where ID=t1.ID) >=10

BUT IS IT OPTIMIZED?
Please suggest an optimized way to handle such scenarios.

View Replies !

---

Column Name Conflicts With T-SQL Clause Keyword
I am busy extending a VB6 app to talk to SQL Server Express 2005 and have come across a naming conflict. Some of the columns in the application's Access 97 tables is "index" which obviously exists as a T-SQL keyword and therefore any queries I perform including this column throws a syntax error. I tried prefixing the columns with their table names as in TableName.Index, but this still throws up the syntax error which I thought was a bit odd. I preferably want a fix that will be Access as well as T-SQL compliant but if that's not possible I will just write a string converter that does the job based on anyone's suggestions. TIA

View Replies !

---

WHERE Clause Using Different Column Passed By Parameter
Hi all,
 
I'd think this is possible but I'm having trouble getting data returned from the query.  Fields PART_NUMBER and INTERNAL_SKU exist in the SKU table.  This will be inside a SP.  If user passes 'PN' as first parameter then I'd need to have the WHERE clause by PART_NUMBER, if he passes 'SK' (or anything else for now) then the WHERE clause shold be by SKU.
 
Can't I just build the WHERE by replacing @SearchField with its value ?  I've looked up the CASE statement but I don't think it does what I need.
 

DECLARE @strSearchType varchar(2)

DECLARE @strSearchValue varchar(15)

DECLARE @SearchField varchar(15)

set @strSearchType = 'PN'

set @strSearchValue = '1234567'

 
IF @strSearchType = 'PN'

begin

set @SearchField = 'PART_NUMBER'

end

ELSE

begin

set @SearchField = 'INTERNAL_SKU'

end

SELECT

SKU                    as 'SKU',

PART_NUMBER  as 'PartNumber',

DESCRIPTION     as 'Description'

FROM SKUs

WHERE @SearchField = @strSearchValue

FOR XML PATH('SKU'), ROOT('Response')

 
Thanks,
 
ST

View Replies !

---

Any Way To Check If All The Values In A Column Returned Are The Same Value?
say i have a table, and in it are two columns, column1 and column2 and i do the following query:

SELECT column1, column2 FROM table WHERE column2 = '12345'

i want to check if column1 has all the same values, so in the first case, no


column1 column2
------- --------
4 12345
9 12345
5 12345




column1 column2
------- --------
9 12345
9 12345
9 12345


in the 2nd case, column1 contains all the same values, so yes

is there anyway i can check this? i would be doing this in a trigger.. say when a new row is inserted, the value of column1 is inserted, but col 2 is null.. so when they try to fill in the value for col2 of that row, the trigger checks to see if the value they put for col 2 is already in the table.. if it isn't, then everything is ok. but if it is already in teh table, then it checks col1 to see if all the values of col1 are the same

i hope this makes sense

thanks

View Replies !

---

Check Constrain Based On Another Column
Hi All,

I want to create a check constrain on a column based on the values in another column.

Following is the sample data

Col_A Col_B
AA A
AA B
BB C
BB D

I want a check constraint on col_B such that it's value is A or B if Col_A has AA and likewise col_B has C or D if Col_A has BB.

Can anybody tell me how to accomplish this.

Thanks in Advance
Rajesh

View Replies !

---

ALTERing A Column CHECK Constraint
How do I alter a column check constraint?

I have the table:

CREATE TABLE Mytable(
mykey integer,
mycol integer
CHECK(mycol BETWEEN 1 AND 2)
PRIMARY KEY(mykey))

How do I change the constraint to
CHECK(mycol BETWEEN 0 AND 2)

...without losing any data?

Thanks!
Jim

View Replies !

---

CHECK Constraint - Referencing Another Column
I receive the following error when creating a CHECK constraint that references another column. According to the good old Wrox SQL Server book, I'm using the correct syntax. Anyone have any ideas???

Thanks in advance!

Server: Msg 8141, Level 16, State 1, Line 1
Column CHECK constraint for column 'end_date' references another column, table 'Session'.

Here's an example of the script that I'm using:
CREATE TABLE Session (
session_key char(18) NOT NULL,
course_key char(18) NOT NULL,
site_key char(18) NOT NULL,
instructor_key char(18) NOT NULL,
start_date smalldatetime NULL,
end_date smalldatetime NULL
CHECK (end_date >= start_date)
)


.

View Replies !

---

Check Constraint On Part Of Column
Can anyone please tell me how can i create a uniqueness contraint on part of column and index that part too. i.e.
consider the following table.

table A

Col1
furadfaf
fsradfasd
dddafadsf
hjfhdfjakdj


now i want only left three characters of the Col1 to be unique and indexed.

any idea ??????

View Replies !

---

How To Check Numeric In Derived Column?
I would like to know how can i use derived column  to covert the scrip that use to transformation column in DTS 2000(i've migrate my dts to ssis) i didn't know what function can check the numeric in ssis.

My scrip in transformation in dts 2000 as follow.
Function Main()
if isnumeric(DTSSource("Col012")) then
 DTSDestination("SH") = int( DTSSource("Col012"))
else
 DTSDestination("SH") = DTSSource("Col012")
end if
 Main = DTSTransformStat_OK
End Function


Any suggestion please let's me know.

Thanks.

Best Regards,

Kus

View Replies !

---

Check Constraint On Character Column
When generating a check constraint to guarantee that a character column cannot be blank is it best to use comparison operators such as col1 <> '' or to use LEN(col1) > 0? Note that the column in marked as not nullable.

View Replies !

---

How To Check The Existence Of A Column In A Table
Dear All,

 

I wanted to know how do I know or check that whether a column exists in a table. What function or method should I use to find out that a column has already exists in a table.

 

When I run a T-SQL script which i have written does not work. Here is how I have written:

IF Object_ID('ColumnA') IS NOT NULL
        ALTER TABLE [dbo].[Table1] DROP COLUMN [ColumnA]
  GO

 

I badly need some help.

View Replies !

---

Table/column Dependency Check
Hi All,

We have a very convoluted ETL system which is pulling unnecessary data. First thought is to restrict everything so that only the columns/tables that are necessary are brought back. We have a tons of reporting stored procedures that depend on ETL tables, is there anyway we can find out which column/tables these stored procedures are using?

Thanks!

View Replies !

---

How To Check That Column Does Not Exists Before Adding It?
How to check to make sure a column does not exist before adding it?  Be nice to do this in t-sql code instead of using ado.net.  It seems as if the IF NOT EXISTS is not supported in SqlCe 3.1?  I am trying to do this but I get the token error:

IF NOT EXISTS (
select *
from Information_SCHEMA.columns
where Table_name='authors' and column_name='NewColumn'
)
select 'no'


Is there a list of all CE supported t-sql commands?

Thanks,

Dan

View Replies !

---

Multiple Values For Single Column In Where Clause
how does one specify multiple values for a single column in a where clause?

example:

SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')


if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:

View Replies !

---

Where Clause Changing Based On NULL Column(s)
Hello DBAs:

I am having difficulty constructing a where clause. I have 4 columns in a table. Col1 is never NULL. Col2,3,4 may or may not contain NULL values. Here is the situation.

1. Col2,3,4 are NULL
WHERE Col1 = condition

2. Col2 is NULL, 3 may or may not be NULL. When Col3 is NOT NULL, Col4 may or may not be NULL

where col1 and (col3(Not Null values) OR Col4(Not Null values)) = condition.

How do I construct this logic. Please help

View Replies !

---

How To Check Non-english Char Value In A Table Column
I have a table column can save english(single byte) or chinese(double byte) char, how to distinguish the records containing chinese(double byte) char thru sql command

thx.

View Replies !

---

How To Check If Column Exists In Temporary Table
I am trying
IF OBJECT_ID(''tempdb..#tempTable.Column'') IS NOT NULL



But its returning me a null every time. Is there any other way to check if column exists in temporary table.

View Replies !

---

Using The AS Clause In A SQL Stored Proc -- Problem Using Datetime Column
I'm trying to concatenate fields in SQL stored proc for use in textfield in asp.net dropdownlist. I'm running into a problem when I tryto use a DateTime field, but can't find the answer (so far) on theInternet. Was hoping someone here would know?My sql stored proc:SELECT AnomalyID, DateEntered + ', ' + Station + ', ' + Problem As'SelectInfo'FROM tblAnomalyWHERE WorkOrder=@varWO AND Signoff=NullORDER BY DateEnteredbut I get the error:The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I'm not the brightest bulb on the block, so any clues greatlyappreciated!Thanks, Kathy

View Replies !

---

Check For Column Existance Before Alter Table Statement
Hello all,

I am trying to add columns to several tables in a singular database. I want to check to see if the column exists in a given table first before executing the alter table command so that I can skip that command if the column already exists, and avoid aborting my script prematurely. I can't seem to find a way to check for the column's existance. Any suggestions?

View Replies !

---

Please Help Unpivoted Pivot Check Each Column Succession-and Add Result Row At END
nedd help from the from the wonderful people here
 i am stuck in !
i need to check each column succession
the chacking is for 3 columns (day1+day11+day111)  (day2+day22+day222) (day3+day33+day333) (day4+day44+day444) (day5+day55+day555)
and if i don't have the value
than add to a new result row
 





if not show the missing value in the result row at END

if i have in the   fields columns     A B C than




ok-A
ok-B
ok-C

 

if i don't have the value A B C than than show it like this 




no-A
no-B
no-Cif i don't have the value  c than than show it like this  no-C (on the  left )



if i don't have the value  a than than show it like this  no-A (on middle)



if i don't have the value  b than than show it like this  no-B (on right)



IF have duplicity a or b or c show in the result row at END  2-C=(thre is tow time the C)



ignore the  character '*'  and '-' and Q and Y




id
fname

val

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

111
aaaa

2


 
 
 
 
 
 
 
 
 
 
 
 
 
 

111
aaaa

1

A
NULL
NULL
B
 
 
C
NULL
NULL
-
C
-
-
-
C

222
bbbb

2


 
 
 
 
 
 
 
 
 
 
 
 
 
 

222
bbbb

1

B
NULL
NULL
C
 
 
B
NULL
NULL
B
 
 
C
NULL
NULL

333
cccc

2


 
 
 
 
 
 
 
 
 
 
 
 
 
 

333
cccc

1

C
NULL
NULL
*
 
 
 
 
 
 
 
 
 
 
 

444
dddd

2


 
 
 
 
 
 
 
 
 
 
 
 
 
 

444
dddd

1

*
 
 
A
 
 
 
 
 
 
 
 
B
NULL
NULL

555
EEE

2

-
-
-
 
 
 
 
 
 
 
 
 
-
 
Q

555
EEE

1
*
*
*
 
 
 
 
 
 
 
 
 
Q
 
 

 
 

0

ok-A
ok-B
ok-C
ok-A
ok-B
ok-C
ok-A
ok-B
NO-C
NO-A
ok-B
ok-C
NO-A
NO-B
2-C
i have  similar code  i send it
TNX for the help

View Replies !

---

Using The ORDER BY Clause When The Ordered Column Is Not Needed In The SELECT Statement
Greetings,
 
I have a C# application that calls a stored procedure to query the database (MSSQL 2005).   I only have one field/column returned from the query but I need that column ordered.
 
How do I use the ORDER BY clause without returning the index column which does the sorting?  The first example is NOT what I want.  I want something that works like the second example which only returns the 'Name' column.
 

ALTER PROCEDURE [dbo].[MyProcedure]



AS

BEGIN

SELECT DISTINCT A.Name, A.index

FROM
...
...
ORDER BY A.[Index], A.Name ASC

END
 
 

ALTER PROCEDURE [dbo].[MyProcedure]



AS

BEGIN

SELECT DISTINCT A.Name
FROM
...
...
ORDER BY A.[Index]

END
 
Thanks

View Replies !

---

Newbee Help Needed, I Need To Find Column Names If Any After 2 “check” Columns.
I need to find column names if any after 2 “check” columns.

Scenario: I have a database, with approx 400-1500 tables, depending on installation of software. The software is structured so that, when it synchronizes the SQL database it will create all the columns e.g. custacc, custname etc. and then it will always put in two check columns “CheckOne” and “CheckTwo” these two columns has to be the two last ones. In 99.9 this always works fine, but sometime if the users creates a new field in the software, when it synchronizes the new field “lands” behind the two checkfields, which is not good.

So what I am after is a script, which can run through all user tables, tell me if there are columns after the two checkfields and list those tables if any.

Any help would be greatly appreciated.
Cheers
Henrik.

View Replies !

---

Please Help Iain J Unpivoted Pivot Check Each Column Succession-problem X
hi i need help problem common character "X"or"Z"
the problem is that i must use the character "X"or"Z"
i explain i need to do this
if i put the day1 = "X"or"Z" instead of  day1 = 'A'  in  day1 it be calculated as day1 = 'A' 
if i put the day11 ="X"or"Z" instead of  day11 = 'B'  in  day11 it be calculated as day11 = 'B' 
if i put the day1 = "X"or"Z" instead of  day111 = 'C'  in  day111 it be calculated as day111 = 'C' 
.....
i put example below
 





 Iain J wrote:









Here's a query that will get you the result row you desire
 



Code Snippet
-- Set up sample data
declare @data table (id int, fname nvarchar(10), val int, day1 nvarchar(1), day11 nvarchar(1), day111 nvarchar(1), day2 nvarchar(1), day22 nvarchar(1), day222 nvarchar(1), day3 nvarchar(1), day33 nvarchar(1), day333 nvarchar(1), day4 nvarchar(1), day44 nvarchar(1), day444 nvarchar(1), day5 nvarchar(1), day55 nvarchar(1), day555 nvarchar(1))
 
insert into @data
select 111, 'aaaa', 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
union all select 111, 'aaaa', 1, 'A', null, null, 'B', null, null, 'C', null, null, '-', 'C', '-', '-', '-', 'C'
union all select 222, 'bbbb', 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
union all select 222, 'bbbb', 1, 'B', null, null, 'C', null, null, 'B', null, null, 'B', null, null, 'C', null, null
union all select 333, 'cccc', 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
union all select 333, 'cccc', 1, 'C', null, null, '*', null, null, null, null, null, null, null, null, null, null, null
union all select 444, 'dddd', 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
union all select 444, 'dddd', 1, '*', null, null, 'A', null, null, null, null, null, null, null, null, 'B', null, null
union all select 555, 'eeee', 2, '-', '-', '-', null, null, null, null, null, null, null, null, null, '-', null, 'Q'
union all select 555, 'eeee', 1, '*', '*', '*', null, null, null, null, null, null, null, null, null, 'Q', null, null
 
-- Query starts here
select null as id, null as fname, 0 as val,
  case day1A when 0 then 'NO' when 1 then 'ok' else cast(day1A as nvarchar) end + '-A' as day1,
  case day1B when 0 then 'NO' when 1 then 'ok' else cast(day1B as nvarchar) end + '-B' as day11,
  case day1C when 0 then 'NO' when 1 then 'ok' else cast(day1C as nvarchar) end + '-C' as day111,
  case day2A when 0 then 'NO' when 1 then 'ok' else cast(day2A as nvarchar) end + '-A' as day2,
  case day2B when 0 then 'NO' when 1 then 'ok' else cast(day2B as nvarchar) end + '-B' as day22,
  case day2C when 0 then 'NO' when 1 then 'ok' else cast(day2C as nvarchar) end + '-C' as day222,
  case day3A when 0 then 'NO' when 1 then 'ok' else cast(day3A as nvarchar) end + '-A' as day3,
  case day3B when 0 then 'NO' when 1 then 'ok' else cast(day3B as nvarchar) end + '-B' as day33,
  case day3C when 0 then 'NO' when 1 then 'ok' else cast(day3C as nvarchar) end + '-C' as day333,
  case day4A when 0 then 'NO' when 1 then 'ok' else cast(day4A as nvarchar) end + '-A' as day4,
  case day4B when 0 then 'NO' when 1 then 'ok' else cast(day4B as nvarchar) end + '-B' as day44,
  case day4C when 0 then 'NO' when 1 then 'ok' else cast(day4C as nvarchar) end + '-C' as day444,
  case day5A when 0 then 'NO' when 1 then 'ok' else cast(day5A as nvarchar) end + '-A' as day5,
  case day5B when 0 then 'NO' when 1 then 'ok' else cast(day5B as nvarchar) end + '-B' as day55,
  case day5C when 0 then 'NO' when 1 then 'ok' else cast(day5C as nvarchar) end + '-C' as day555
from (select
  sum(case when character = 'A' then 1 else 0 end)
    + sum(case when day11 = 'A' then 1 else 0 end)
    + sum(case when day111 = 'A' then 1 else 0 end) as day1A,
  sum(case when day1 = 'B' then 1 else 0 end)
    + sum(case when day11 = 'B' then 1 else 0 end)
    + sum(case when day111 = 'B' then 1 else 0 end) as day1B,
  sum(case when day1 = 'C' then 1 else 0 end)
    + sum(case when day11 = 'C' then 1 else 0 end)
    + sum(case when day111 = 'C' then 1 else 0 end) as day1C,
  sum(case when day2 = 'A' then 1 else 0 end)
    + sum(case when day22 = 'A' then 1 else 0 end)
    + sum(case when day222 = 'A' then 1 else 0 end) as day2A,
  sum(case when day2 = 'B' then 1 else 0 end)
    + sum(case when day22 = 'B' then 1 else 0 end)
    + sum(case when day222 = 'B' then 1 else 0 end) as day2B,
  sum(case when day2 = 'C' then 1 else 0 end)
    + sum(case when day22 = 'C' then 1 else 0 end)
    + sum(case when day222 = 'C' then 1 else 0 end) as day2C,
  sum(case when day3 = 'A' then 1 else 0 end)
    + sum(case when day33 = 'A' then 1 else 0 end)
    + sum(case when day333 = 'A' then 1 else 0 end) as day3A,
  sum(case when day3 = 'B' then 1 else 0 end)
    + sum(case when day33 = 'B' then 1 else 0 end)
    + sum(case when day333 = 'B' then 1 else 0 end) as day3B,
  sum(case when day3 = 'C' then 1 else 0 end)
    + sum(case when day33 = 'C' then 1 else 0 end)
    + sum(case when day333 = 'C' then 1 else 0 end) as day3C,
  sum(case when day4 = 'A' then 1 else 0 end)
    + sum(case when day44 = 'A' then 1 else 0 end)
    + sum(case when day444 = 'A' then 1 else 0 end) as day4A,
  sum(case when day4 = 'B' then 1 else 0 end)
    + sum(case when day44 = 'B' then 1 else 0 end)
    + sum(case when day444 = 'B' then 1 else 0 end) as day4B,
  sum(case when day4 = 'C' then 1 else 0 end)
    + sum(case when day44 = 'C' then 1 else 0 end)
    + sum(case when day444 = 'C' then 1 else 0 end) as day4C,
  sum(case when day5 = 'A' then 1 else 0 end)
    + sum(case when day55 = 'A' then 1 else 0 end)
    + sum(case when day555 = 'A' then 1 else 0 end) as day5A,
  sum(case when day5 = 'B' then 1 else 0 end)
    + sum(case when day55 = 'B' then 1 else 0 end)
    + sum(case when day555 = 'B' then 1 else 0 end) as day5B,
  sum(case when day5 = 'C' then 1 else 0 end)
    + sum(case when day55 = 'C' then 1 else 0 end)
    + sum(case when day555 = 'C' then 1 else 0 end) as day5C
  from @data) x
 
 




Hope that helps
 
Iain
 

View Replies !

---

A T-SQL Function To Check Column Level Data For Special Charaters
I need to create a function that will check data inputted by a user into a column anc check for special charaters.  If any of these exist then block the insert.

Any help will be appreciated!

Paul

View Replies !

---

A Table/column To Table/column Data Check (was &"Help Please, SQL Something Simple&")
Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.

I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.

Easy Peasy for you guys, any help would be appreciated.

View Replies !

---

Can &&"hide Duplicates&&" Check If Another Column Is Visible Before Hiding?
My 3 leftmost columns are "hiding duplicates".  The columns are download date, download id and date part of event datetime.  Can the 3rd column be made visible any time the 2nd column is visible?
 

View Replies !

---

CHECK CONSTRAINT And WITH CHECK Option: SQL 2000 Vs SQL 2005
Hi

I have got this script and I was comparing the execution plan query at the end on both SQL 2000 and SQL 2005.

 

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'VW_PTN')

    DROP VIEW VW_PTN

GO

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TB_PTN_1')

    DROP TABLE TB_PTN_1

GO

-- CREATE PARTITION TABLE 1

create table [dbo].[TB_PTN_1]

(

    [PTN_ID] int IDENTITY (0, 1) NOT FOR REPLICATION NOT NULL ,

    [DATE_TIME] DATETIME NOT NULL,

    [PTN_NUMBER] int NOT NULL

) ON [PRIMARY]

GO

-- ADD PRIMARY KEY

ALTER TABLE [dbo].[TB_PTN_1] WITH CHECK

    ADD CONSTRAINT [PK_TB_PTN_1] PRIMARY KEY CLUSTERED

    ([PTN_ID], [PTN_NUMBER])

ON [PRIMARY]

GO

-- ADD CHECK CONSTRAINT ON PTN_NUMBER

ALTER TABLE [dbo].[TB_PTN_1] WITH CHECK

    ADD CONSTRAINT [CK_TB_PTN_1] CHECK (PTN_NUMBER=1)

GO

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TB_PTN_2')

    DROP TABLE TB_PTN_2

GO

-- CREATE PARTITION TABLE 2

create table [dbo].[TB_PTN_2]

(

    [PTN_ID] int IDENTITY (0, 1) NOT FOR REPLICATION NOT NULL ,

    [DATE_TIME] DATETIME NOT NULL,

    [PTN_NUMBER] int NOT NULL

) ON [PRIMARY]

GO

-- ADD PRIMARY KEY

ALTER TABLE [dbo].[TB_PTN_2] WITH CHECK

    ADD CONSTRAINT [PK_TB_PTN_2] PRIMARY KEY CLUSTERED

    ([PTN_ID], [PTN_NUMBER])

ON [PRIMARY]

GO

-- ADD CHECK CONSTRAINT ON PTN_NUMBER

ALTER TABLE [dbo].[TB_PTN_2] WITH CHECK

    ADD CONSTRAINT [CK_TB_PTN_2] CHECK (PTN_NUMBER=2)

GO

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'VW_PTN')

    DROP VIEW VW_PTN

GO

-- CREATE PARTITIONED VIEW

CREATE VIEW VW_PTN

AS

    SELECT * FROM TB_PTN_1

    UNION ALL

    SELECT * FROM TB_PTN_2

GO

set showplan_text off

go

set showplan_all on

go

SELECT * FROM VW_PTN WHERE PTN_NUMBER = 2

go

set showplan_all off

go

 

This is what I see in SQL server 2000

 

SELECT * FROM VW_PTN WHERE PTN_NUMBER = 2
  |--Concatenation
       |--Filter(WHERESTARTUP EXPR(Convert([@1])=1)))
       |    |--Clustered Index Scan(OBJECT[Testerdatabase].[dbo].[TB_PTN_1].[PK_TB_PTN_1]), WHERE[TB_PTN_1].[PTN_NUMBER]=Convert([@1])))
       |--Filter(WHERESTARTUP EXPR(Convert([@1])=2)))
            |--Clustered Index Scan(OBJECT[Testerdatabase].[dbo].[TB_PTN_2].[PK_TB_PTN_2]), WHERE[TB_PTN_2].[PTN_NUMBER]=Convert([@1])))


This is what I see in SQL server 2005

 

SELECT * FROM VW_PTN WHERE PTN_NUMBER = 2
  |--Compute Scalar(DEFINE[TestDatabase].[dbo].[TB_PTN_2].[PTN_ID]=[TestDatabase].[dbo].[TB_PTN_2].[PTN_ID], [TestDatabase].[dbo].[TB_PTN_2].[DATE_TIME]=[TestDatabase].[dbo].[TB_PTN_2].[DATE_TIME], [TestDatabase].[dbo].[TB_PTN_2].[PTN_NUMBER]=[TestDatabase].[dbo].[TB_PTN_2].[PTN_NUMBER]))
       |--Clustered Index Scan(OBJECT[TestDatabase].[dbo].[TB_PTN_2].[PK_TB_PTN_2]), WHERE[TestDatabase].[dbo].[TB_PTN_2].[PTN_NUMBER]=(2)))

 

Whats the difference?

 

Why does it scan both the tables in SQL server 2000 and just one table in SQL server 2005?

 

It seems to be that SQL server 2000 is ignoring the WITH CHECK option whilst creating the check constraint on each of the tables for PTN_NUMBER column.

 

Any clues?

View Replies !

---

GROUP By Clause Or DISTINCT Clause
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1

View Replies !

---

Filtering Results In The Where Clause Vs A Having Clause
I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.
 
When on SQL Server 2000 the following statement ran without issue:

UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN

(


SELECT TrackID

FROM dbo.Track_ID

GROUP BY TrackID

HAVING MAX(LegNum) = 1 AND


TrackID + 'x1' IN


(


SELECT

dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions


ON  dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)

)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.
 
I changed the SQL to the following, SQL Server now runs it in under a second,  but now the app is not functioning correctly.  Are the above and the following semantically the same?
 

UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN
(



SELECT TrackID

FROM dbo.Track_ID

WHERE TrackID + 'x1' IN


(


SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions


ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)
GROUP BY TrackID

HAVING MAX(LegNum) = 1

)
 
 

View Replies !

---

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
 

ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause

 
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
 

View Replies !

---

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
 

ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause

 
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.

View Replies !

---

Having Clause Without GROUP BY Clause?
Hi,

What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

eg :

SELECT SUM(col1) from test HAVING col2 < 5

SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

I want the equivalent query in MSSQLServer for the above Oracle query.

Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

Thanks,
Gopi.

View Replies !

---

SQL Inner Join Clause And The Where Clause
Hi everyone,
I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?

Thanks

View Replies !

---

Diff In On Clause And Where Clause?????
hi..
i have basic question like

what is differance between conditions put in ON clause and in WHERE clause in JOINS????

see conditions that shown in brown color

select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)


select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)

another thing...

if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove
d1.ImplicitID >= d2.ImplicitID from WHERE clause????

View Replies !

---

Top Clause With GROUP BY Clause
How Can I use Top Clause with GROUP BY clause?

Here is my simple problem.

I have two tables

Categories
Products

I want to know Top 5 Products in CategoryID 1,2,3,4,5

Resultset should contain 25 Rows ( 5 top products from each category )

I hope someone will help me soon.
Its urngent


thanks in advance

regards
Waqas

View Replies !

---