CASE Does Not Give Correct Result

Aug 31, 2007

How do I automatically assign a new cardcode-number? (according to the following formula: highest existing number + 1)

Scenario:
-There are two types of business partners: Customers and Suppliers.
-Customers have the value 'C' in the colomn CardType.
-Suppliers have the value 'S' in the colomn CardType.
-Customers have the following syntax 'C123456' in the colomn CardCode.
-Suppliers have the following syntax 'S123456' in the colomn CardCode.
-Existing CardCode-values in the DB for the Customers: C000001 - C100599.
-Existing CardCode-values in the DB for the Suppliers: S000001 - S200199.

The idea is that when a user creates a new business partner, the CardCode should be automatically filled when a new assigned number (highest existing number + 1), according to the value that is selected in CardType (either the letter 'C' or 'S').


What's been done so far:
SELECT top 1
(CASE
WHEN CardType='C' THEN (SELECT top 1 'C' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'C%' AND T0. CardType='C'
order BY T0.CardCode desc FOR BROWSE)
WHEN CardType='S' THEN (SELECT top 1 'S' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'S%' AND T0. CardType='S'
order BY T0.CardCode desc FOR BROWSE)
END)
FROM OCRD T0


The current result:
The result that it gives is 'C100600'.
The problem however is that it always gives this result and does not take into account what has been selected in CardType.

When I add the following: "order BY T0.CardCode desc FOR BROWSE" it gives the result 'S200200'.

So, what does work is that it takes the highest existing value and adds 1. But what doesn't work is the taking account what value is selected in CardType.

Does anyone know how I can make this work?

View 4 Replies


ADVERTISEMENT

Why Doesnt BETWEEN Give Me My Desired Result?

Dec 9, 2005

I am trying to select records based upon last name
WHERE     (Last_Name BETWEEN 'A%' AND 'C%')
When I run this I get only last names starting with A and B--no C. ?!?
This is confusing to me....I ran it with lastname >= 'A%' and lastname <='C%'and it returned only names starting with B. Why does SQL ignore the "="
I hope this isnt obvious :/
 
 

View 6 Replies View Related

CASE Function Result With Result Expression Values (for IN Keyword)

Aug 2, 2007

I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.

WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )

I kept on getting errors, like

Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.

View 3 Replies View Related

Create A Query That Will Give Result Set Containing Primary Order On Type

May 14, 2012

I have a table with plant types and plant names. Certain plants are grouped on a custom field, currently called Field. I am trying to create a query that will give me a result set containing the primary order on Type, but need items with the same 'Field' value grouped by each other.For example, the following shows a standard query result with "order by Type", ie select * from plants order by Type

Code:
ID Type Name Field
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
5 Type5Name5(group2) -group2
6 Type6Name6(group6)

But I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)

Code:
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
5 Type5Name5(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
6 Type6Name6(group6)

View 7 Replies View Related

Transact SQL :: Create A Pivot Query That Will Give Result That Is Horizontal

Jul 8, 2015

I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?

View 10 Replies View Related

Problem Using Result From CASE In Another CASE Statement

Nov 5, 2007

I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.

Previously, I had been duplicating the CASE logic for both columns, like so:




Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...






This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.

This is what I'd like to do:



Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,


shipment_status_text =

CASE shipment_status

WHEN 1 THEN 'Condition 1 text'

WHEN 2 THEN 'Condition 2 text'

WHEN 3 THEN 'Condition 3 text'

WHEN 4 THEN 'Condition 4 text'

ELSE 'Error'

END,
...remainder of SQL view...


This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.

Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?

Thanks,

Jason

View 1 Replies View Related

Subtotals In Report Give Infinity Value In Case Of Empty Cells In Column.

Mar 13, 2008

The title, I think, accurately illustrates the situation in my report.
Naturally, this result is unwanted.

How do I get the avg-function to ignore empty cells in the column?

Thanks in advance,

Pluggie

View 5 Replies View Related

Please Help Me To Optimize This Sql Query, It Takes 28 Seconds To Return Result. Please Give Me A Tips Where I Went Wrong?

Aug 21, 2006

SELECT * FROM
( SELECT TOP 15 * FROM
(SELECT TOP 15 CMDS.STOCKCODE AS CODE,CMDS.STOCKNAME AS NAME,CMDS.Sector AS SEC, CMD7.REFERENCE AS REF,T1.HIGHP AS HIGH,
T1.LOW,T1.B1_CUM AS 'B/QTY', T1.B1_PRICE AS BUY,T1.S1_PRICE AS SELL,
T1.S1_CUM AS 'S/QTY', T1.D_PRICE AS LAST,T1.L_CUM AS LVOL,T1.Chg AS CHG,T1.Chgp AS CHGP, T1.D_CUM AS VOLUME,substring(T1.ST,7,6) AS TIME,
CMDS.SERIAL as SERIAL FROM CMD7,CMDS,CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 ,CMDS WHERE
T1.SERIAL=T2.SERIAL
AND CMDS.SERIAL=T2.SERIAL
AND T2.sd='20060821'
AND CMDS.sd='20060821'
AND T2.L_CUM < '1900'
AND CMDS.sector >='1'
AND CMDS.sector <='47')
AND CMDS.SERIAL=T1.SERIAL AND
CMDS.SERIAL=CMD7.SERIAL AND
CMDS.sd='20060821' AND
CMD7.sd='20060821' AND
T1.sd='20060821' AND
T1.L_CUM < '1900' AND
CMDS.sector >='1' AND
CMDS.sector <='47' ORDER BY T1.D_CUM desc)
AS TBL1 ORDER BY VOLUME asc) AS TBL1 ORDER BY VOLUME desc;

View 6 Replies View Related

Is This Case Statement Correct?

Nov 16, 2007



CASE When FundName Is Null Then ShortName

When pf.FundId = 0 Then FundName = Loans

ELSE FundName END as FundNames,



Regards
Karen

View 8 Replies View Related

Correct/detect Improper Case

May 22, 2008

I have been through our database and we have a culprit who has been inputting all names in full capitals which I have always found particularly annoying. I want to convert them all to proper case for which I found the code below. However how can I detect the set where they have inproper case.

CREATE FUNCTION dbo.pCase
(
@strIn VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
IF @strIn IS NULL
RETURN NULL

DECLARE
@strOut VARCHAR(255),
@i INT,
@Up BIT,
@c VARCHAR(2)

SELECT
@strOut = '',
@i = 0,
@Up = 1

WHILE @i <= DATALENGTH(@strIn)
BEGIN
SET @c = SUBSTRING(@strIn,@i,1)
IF @c IN (' ','-','''')
BEGIN
SET @strOut = @strOut + @c
SET @Up = 1
END
ELSE
BEGIN
IF @up = 1
SET @c = UPPER(@c)
ELSE
SET @c = LOWER(@c)

SET @strOut = @strOut + @c
SET @Up = 0
END
SET @i = @i + 1
END
RETURN @strOut
END
GO

View 3 Replies View Related

Why Does AVG Query Not Return The Correct DOUBLE Result

Jan 3, 2008

I'm using AVG in a simple query but the results are being returned as INTEGERs and not the correct DOUBLE form.

AVG is being used on integer fields in a .mdf database eg: "SELECT AVG(intField) AS Results FROM myDB"

Thanks,

Geoff

View 1 Replies View Related

MDX Date Range Report - Didn't Get Correct Result

Aug 5, 2014

I am doing search through Date Range but didn't get correct result.

For Example if i created a Task on 5/8/2014 and when i have take Date Range from 1/8/2014 to 6/8/2014 then didn't get 5/8/2014 data in result but when i taken range from 1/8/2014 to 5/8/2014 then got the 5th Date data.

I am taking Range Inclusive Operator(:).

SELECT ( STRTOMEMBER(@FromDate) : STRTOMEMBER(@ToDate) )

View 1 Replies View Related

CASE With More Than One Result

Feb 8, 2008

Hello,
I have a business rule that says if PROD = ''XCDD' and ProductRollUp = 'XCSH' Then BillingType = 'Express Cash' or 'Payment Services'.

Is there a way to represent that rule with a CASE statement?

Thank you for your help!

cdun2

View 8 Replies View Related

Change Result Names Besides CASE?

Aug 13, 2012

Example:

Select StoreId from tblStores
S1
S2
S3
S4

I want to display a more friendly name. I have been using a case statement:

select case
WHEN storeId = 'S1' THEN 'NY Store'
WHEN storeId = 'S2' THEN 'CA Store'
...
end AS StoreName

If I am doing a simple 1:1 mapping like that, is there a better way than using a case statement (without creating/modifying tables)?

View 2 Replies View Related

How To Pass Result From Case Statement To An Equation?

Dec 28, 2007

How to get the CASE results highlighted in BOLD into this equation; "(LogOut - LogIn) + (LunchBreak) -(AMBreak)  + (PMBreak)  AS TimeWorked" ?
Thank you.
CREATE VIEW dbo.vwu_ReportViewASSELECT   EmployeeID  , LastName  , FirstName  , LocationCode  , UserID  , Today  , Login  , AMBreakOut  , AMBreakIn            , CASE  WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 19    THEN '0'  WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 34    THEN '15'  WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 49    THEN '30'  WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) > = 50 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 64    THEN '45'  ELSE '60' END AS AMBreak , LunchOut             , LunchIn            , CASE  WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0)  >= 0 AND ISNULL(DATEDIFF(Minute,  LunchOut, LunchIn),0) <= 66    THEN '0'  WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0)  >= 67 AND ISNULL(DATEDIFF(Minute,  LunchOut, LunchIn),0) <= 81    THEN '15'  WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0)  >= 82 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 96    THEN '30'  WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0)  >= 97  AND ISNULL(DATEDIFF(Minute,  LunchOut, LunchIn),0) <= 111    THEN '45'  WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0)  >= 112 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 126    THEN '60'  ELSE '75' END AS LunchBreak, PMBreakOut , PMBreakIn            , CASE  WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 19    THEN '0'  WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 34    THEN '15'  WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 49    THEN '30'  WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 50 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 64    THEN '45'  ELSE '60' END AS PMBreak , Logout , Comments , LoginLogon , AMBreakOutLogon , AMBreakInLogon , LunchOutLogon , LunchInLogon , PMBreakOutLogon , PMBreakInLogon , LogoutLogon             ,(LogOut - LogIn) + (LunchBreak) -(AMBreak)  + (PMBreak)  AS TimeWorked 

View 7 Replies View Related

Case Stmt Returns Duplicate Result

Nov 28, 2007

Hi there,

The following is my table whereby i have joined projects table with issue table (this is 1 to many relationship).



I have the following query:
SELECT
odf.mbb_sector sectorid,

SUM(case when odf.mbb_projecttype = 'lkp_val_appl' then 1 else 0 end) total_appl,
SUM(case when odf.mbb_projecttype = 'lkp_val_infrastructure' then 1 else 0 end) total_infra,
SUM(case when odf.mbb_projecttype = 'lkp_val_eval' then 1 else 0 end) total_eval,
SUM(case when odf.mbb_projecttype = 'lkp_val_subproject' then 1 else 0 end) total_subprj,
SUM(case when odf.mbb_projecttype = 'lkp_val_nonit' then 1 else 0 end) total_nonit,
SUM(case when odf.mbb_projecttype = 'lkp_val_adhocrptdataextract' or
odf.mbb_projecttype = 'lkp_val_productionproblem' or
odf.mbb_projecttype = 'lkp_val_maintwoprogchange' then 1 else 0 end) total_others,
COUNT(distinct prj.prid) total_prj

FROM
PRJ_PROJECTS AS PRJ,
SRM_PROJECTS AS SRM,
ODF_CA_PROJECT AS ODF

LEFT JOIN RIM_RISKS_AND_ISSUES AS RRI ON RRI.pk_id = odf.id

WHERE
prj.prid = srm.id
AND srm.id = odf.id
AND srm.is_active =1
AND odf.mbb_projecttype not in ('lkp_val_budget','lkp_val_itpc')
AND odf.mbb_funcunit = 'lkp_val_operation'

GROUP BY
odf.mbb_sector
which returns me the following result :
.

The problem is at the lkp_val_infosystem where it returns 3 instead of 1 in the total_infra column. How do I correct my case stmt to return the correct no of projects breakdown by different project type? Currently, only the total_prj which returns correct data.

Thanks

View 3 Replies View Related

Calculated Column Based On Case Statement Result

Dec 10, 2013

I'm new to SQL Server and would like to add a calculated column to this query from the report writer in our ERP system based on the NextFreq case statement result.

Basically, I want to create a column called service with result as follows:

If IV.meter > NextFreq then the result should be 'OVERDUE'
If (NextFreq - IV.meter) <50 then the result should be 'DUE SOON'
Otherwise the result should be 'NOT DUE'

This is the code from the current report writer query:

Select IV.item, IV.meter, isnull(wt.name,0)as name, case when whh.meterstop is null then 0 end meterstop, whh.rejected, Case when cast(meterstop as int) > 0 then cast(meterstop as int) when meterstop is null then isnull(IV.meter,0) else isnull(IV.meter,0) end EndMeter, ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) as LastFreq,
case when whh.rejected = 1 then ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 0 then 100 when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 100

[Code] ....

View 4 Replies View Related

Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result

Jul 20, 2005

I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg

View 4 Replies View Related

Problem Assigning SQL Task Result To A Variable - Select Count(*) Result From Oracle Connection

Dec 26, 2007



I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".

Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.

Thanks!

View 5 Replies View Related

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

Dec 11, 2007

Hi all,

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

USE AdventureWorks;

GO

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

DROP FUNCTION dbo.ufnGetContactInformation;

GO

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE

(

-- Columns returned by the function

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

)

AS

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

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

ELSE NULL

END;

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'

END;

-- Return the information to the caller

IF @ContactID IS NOT NULL

BEGIN

INSERT @retContactInformation

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

END;

RETURN;

END;

GO

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

Thanks in advance,
Scott Chang

View 1 Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

Feb 13, 2001

HI,
I ran a select * from customers where state ='va', this is the result...

(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes

I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record

Thanks for your help

Ali

View 1 Replies View Related

End Result Is Main Query Results Ordered By Nested Result

May 1, 2008

As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:

SHOWS
showID
showTitle

SHOWACCESS
showID
remoteID

VIDEOS
videoDate
showID

SQL is as follows:

SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;

I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?

thanks

View 3 Replies View Related

SQL Server 2008 :: Change Text Format From Case Sensitive To Case Insensitive?

Aug 31, 2015

How can I change my T-SQL text editor from text sensitive to text insensitive?

View 2 Replies View Related

Case Insensitivity Is On Server Wide: Tables Render Case Sensative...

Jan 6, 2005

Hello:

I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?

Any suggestions?

View 4 Replies View Related

Case Insensitive Searching In Sql Server 2000 When It's Case Sensitive

May 4, 2007

Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
 
thanks in advance.

View 3 Replies View Related

HELP! Case Insensitive Database On Case Sensitive Server

Aug 17, 2005

We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????

View 4 Replies View Related

Doing A Case-sensitive Query In A Case-insensitive Database

May 29, 2008

I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?

Jacob

View 5 Replies View Related

Transact SQL :: Upper Case To Lower Case Conversion

May 4, 2015

I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?

View 7 Replies View Related

Can You Use Replication From A Case Sensitive Db To A Case Insensitive Db?

Aug 19, 2007

I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI.  Will there be any problems with this scenario? Thanks in advance! 

View 2 Replies View Related

I Give Up On This One...

Jul 20, 2005

I have tried and tried to get this to work and I'm doing something dumb.I'm going to include sample data and my format file. Can someone helpme figure out my dumb mistake on a bulk insert using a format file?Don't worry about data types, I'll handle that later. Thanks a lot!!!CSV File:ID,NAME,SPEC_ORIGINAL,SPEC,CATEGORY,ADDRESS1,CITY, STATE,ZIP,PHONE,COMPETITOR,PLAN,HARVESTDATE,EIDs,EIDCount,sourceIds,minS core,maxScore,hasContract,matchFilter,matchFilterBGR,matchFilterCAS,matc hFilterMTV,forceMatch,forceMatchUserId,paymentPlatforms,matchStatusColor ,recordId,dupId,NetworkComparedTo1,name1,Family Practice General Practice,FP,PCP,address1,DeerfieldBeach,FL,33442,phone1,C1,PPO,5/15/2004,1000067851,2,BADG_0874992,9,14.4,Y,Y,Y,Y,Y,0,,3,G,1, ,netcomp12,name2,Family Practice GeneralPractice,FP,PCP,address2,Margate,FL,33063,phone2,C 2,PPO,5/15/2004,1000067851,2,BADG_0874992,9,10,Y,Y,Y,Y,Y,0,,3,G,2, ,netcomp23,name3,General Practice,GP,PCP,address3,DeerfieldBeach,FL,33442,phone3,C3,PPO,5/15/2004,1000067851,2,BADG_0874992(14.4Y)|BADG_1901859,9,14.4,Y,Y,Y,Y,Y,0,,3,G,3, ,netcomp34,name4,ReproductiveEndocrinology,OBEN,OB,address4,Davie,FL,33328,phon e4,C4,PPO,5/15/2004,1000083687,1,CAS_650410436,10.3,10.3,Y,N,N,N,N,0,,0, Y,4, ,netcomp4Format File:8.0311 SQLCHAR 0 1 "
" 0 quote Latin1_General_CI_AS2 SQLCHAR 0 3000 "," 1 Provider_Raw_ID Latin1_General_CI_AS3 SQLCHAR 0 3000 "," 0 none_name Latin1_General_CI_AS4 SQLCHAR 0 3000 "," 0 none_Spec_orig Latin1_General_CI_AS5 SQLCHAR 0 3000 "," 3 SpecialtyCode Latin1_General_CI_AS6 SQLCHAR 0 3000 "," 2 Category Latin1_General_CI_AS7 SQLCHAR 0 3000 "," 0 none_Address Latin1_General_CI_AS8 SQLCHAR 0 3000 "," 0 none_City Latin1_General_CI_AS9 SQLCHAR 0 3000 "," 0 none_State Latin1_General_CI_AS10 SQLCHAR 0 3000 "," 0 none_Zip Latin1_General_CI_AS11 SQLCHAR 0 3000 "," 0 none_Phone Latin1_General_CI_AS12 SQLCHAR 0 3000 "," 0 none_Competitor Latin1_General_CI_AS13 SQLCHAR 0 3000 "," 0 none_Plan Latin1_General_CI_AS14 SQLCHAR 0 3000 "," 0 none_HarvestDate Latin1_General_CI_AS15 SQLCHAR 0 3000 "," 0 none_EIDs Latin1_General_CI_AS16 SQLCHAR 0 3000 "," 5 EIDCount Latin1_General_CI_AS17 SQLCHAR 0 3000 "," 0 SourceIds Latin1_General_CI_AS18 SQLCHAR 0 3000 "," 6 minScore Latin1_General_CI_AS19 SQLCHAR 0 3000 "," 7 maxScore Latin1_General_CI_AS20 SQLCHAR 0 3000 "," 8 hasContract Latin1_General_CI_AS21 SQLCHAR 0 3000 "," 9 matchFilter Latin1_General_CI_AS22 SQLCHAR 0 3000 "," 10 matchFilterBGR Latin1_General_CI_AS23 SQLCHAR 0 3000 "," 11 matchFilterCAS Latin1_General_CI_AS24 SQLCHAR 0 3000 "," 12 matchFilterMTV Latin1_General_CI_AS25 SQLCHAR 0 3000 "," 13 forceMatch Latin1_General_CI_AS26 SQLCHAR 0 3000 "," 14 forceMatchUserId Latin1_General_CI_AS27 SQLCHAR 0 3000 "," 15 paymentPlatforms Latin1_General_CI_AS28 SQLCHAR 0 3000 "," 16 matchStatusColor Latin1_General_CI_AS29 SQLCHAR 0 3000 "," 17 recordId Latin1_General_CI_AS30 SQLCHAR 0 3000 "," 18 dupId Latin1_General_CI_AS31 SQLCHAR 0 3000 "," 4 NetworkComparedTo Latin1_General_CI_ASHere's a script for the table!if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Provider_Results]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[Provider_Results]GOCREATE TABLE [dbo].[Provider_Results] ([Provider_Raw_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[Category] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SpecialtyCode] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[NetworkComparedTo] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[EIDCount] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[minScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[maxScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[hasContract] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilter] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilterBGR] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilterCAS] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilterMTV] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[forceMatch] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[forceMatchUserId] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[paymentPlatforms] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[matchStatusColor] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[recordId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[dupId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThanks again. I am dying on this one. With everything includedhopefully someone (smarter than me) can figure it out.Thanks,Andrew*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

Nu Ger Jag Upp (I Give Up)

Feb 5, 2008



Jag har installerat sql server 2008 (I have installed sql server)

I cannot find enterprise manager - I think that the sql server is useless unless there is a manager.

View 1 Replies View Related

Return Subquery Result For Only First Row In Result

Apr 7, 2015

I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.

declare @start smalldatetime
declare @end smalldatetime
set @start = '2015-03-22 00:00' -- this should be a Sunday
set @end = '2015-03-28 23:59' -- this should be the following Saturday

select di.dticket [Contract], di.ddate [Delivered], di.item [Fleet_No], di.descr [Description], dd.min_chg [Delivery_Chg], dd.last_invc_date [Delivery_Invoiced],

[code]....

In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.

View 6 Replies View Related

Strange Result - Minus Result -1

Mar 2, 2008

help strange result whan i do this



Code Snippet
SELECT unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, GETDATE(), unit_date)) % 4, 0), 4) AS new_unit
FROM dbo.empList




i try to get next unit value to next month
why i get this -1
on date




01/01/2008
1
-1

unit_date unit new_unit



01/02/2008
2
1

01/02/2008
1
4

01/01/2008
1
-1

01/02/2008
1
4

21/01/2008
1
-1

21/01/2008
1
-1

01/02/2008
1
4


TNX

View 3 Replies View Related







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