CASE SQL Construct
Sep 27, 2005
I am using the following code to construct an SQL 7.0 View Column
"CASE WHEN [DailyHours] > SUM([TransAmt]) THEN 0 ELSE 1 END"
and it works great!!
However, when I try the same line in SQL 2000, I get the message "The query designer does not support the case SQL construct"
The help screen is no help as all it says is "the syntax you entered is valid but is not supported visually by Query Designer. Be sure the verify your syntax before saving."
It will not let me save, so I'm not sure what to do from here now????
View 8 Replies
ADVERTISEMENT
Dec 19, 2006
Hi all,
I had a question regarding SQL construct.
I had a table named "info" and I would like to show Lot_Id as distinct. Besides that , I would also like to show wafer_id and wafer_starttime. May I know how to construct this using select statement?
Thanks
View 1 Replies
View Related
Oct 18, 2005
My head hurts...it might be an easy one for many people, but cant seem to figure a way to do it.Ok, I have a table with a field called Ad_Price. This field is a nvarchar one, theres many different value in it, all of them corresponding to a price. Now, what I want to do is only get the row that are not written in a good price format (like123$ is wrong, but 123.00 is good, but only value following this exemple)For exemple, if I have all these value145.876785.34654$45 to negociate1bvcaa0.01876.556the value I want to have are:654$45 to negociate1bvcaa876.556because they dont follow the format I wantso im not sure if my explication was clear enough..all in all what I want to complete is the WHERE part of my SQL instruction...what should I put in:SELECT *FROM AdsWHERE ???????thansk for taking the time to read this
View 2 Replies
View Related
Mar 16, 2006
Can you contruct a variable name from another variable? For example, I want the following to PRINT 10
DECLARE @var1 INT
DECLARE @var2 INT
SET @var1 = 10
SET @var2 = 20
PRINT '@var' + '1'
This prints the variable name, not the contents of the var. I tried to parse it with square brackets, but no luck.
Thanks,
Carl
View 6 Replies
View Related
Jun 18, 2008
Dear Group,
I'm upgrading a "classic" ASP app to ASP.NET 2.0. The database is stored on a SQL Server box in-house. I'm looking to inject a bit of flexibility into the SQLDataSource SelectCommand property.
What I mean is this: depending on a selection a user makes in the form, I'd like to present a GridView object that displays data filtered by the user's selection.
So I took the original long query string from ASP and broke it in two pieces. The first piece contains the bulk of the query and it ends just after the WHERE clause. The second piece is the ORDER BY clause. I'd like to have the ability to inject an additional term at the end of the WHERE clause (using AND). That would be the filter.
For example,SELECT LastName, FirstName, InOut, TimeEntered, PhoneExt
FROM TimeClock
WHERE TimeEntered > '6/17/2008'
AND EmploymentState > ''
ORDER BY LastName, FirstName
So, in between the "AND State > '' " and the ORDER BY clause I'd like to insert something like this:AND Department = 'Production'
I'd like the SelectCommand of the SQLDataSource to look like this:SelectCommand="<%= (sql1 & " AND Department = 'Production' " & sql2) %>"
sql1 and sql2 are defined in a <script runat="server"></script> block in the page <head>. The following errors occur even if I have a single variable containing the whole SQL query string (i.e., SelectCommand="<%= sql1 %>").
When I try to run the page I get this error:Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '<'.
If I remove the quotation marks from around the SelectCommand statement I get:Parser Error Message: Server tags cannot contain <% ... %> constructs.
What's funny about this is that the ConnectionString property of the SQLDataSource looks like this:ConnectionString="<%$ ConnectionStrings:PersonnelDatabaseConnectionString %>"
No complaints from the compiler about the ConnectionString.
Any ideas? Am I simply going to be unable to construct a SelectCommand on-the-fly?
View 9 Replies
View Related
Dec 31, 2003
I want to search an entire table for a particular keyword but i'm not sure how, if the keyword was TEST then I want to return rows where any of the fields contain TEST, THIS IS A TEST, PLEASE TEST THIS etc etc i.e. the keyword can be anywhere in the fields value
I believe I need to use the LIKE clause but i'm not sure how.
Thanks
Ben
View 4 Replies
View Related
Nov 15, 2005
Hi,I have three dropdown lists branch, month & year. Based on these I need to retrieve data in my Datagrid.I have written the following SQL statement & code in my Refreshgrid(): SqlCmd.CommandText = " SELECT * " SqlCmd.CommandText += " FROM HSECData" SqlCmd.CommandText += " where branchno=" &Branchno SqlCmd.CommandText += " And yearno=" &YearNo SqlCmd.CommandText += " And monthno=" &monthNoThis is working fine, initially the datagrid is empty and then displays data when I select say, branch- Melbourne, Year-2005, Month-October and other exisiting combinations.The problem I am stuck at is when the page loads, initially I want all the data to be displayed in datagrid, i.e. for 'All' branches, 'All' years and 'All' months and then when I select combinations of these dropwdown lists as above the data should be filtered and displayed in those selected combinations. but as of now I get and empty datagrid when I select 'All' from these 3 drop down lists or when the page opens the first time.Can someone kindly guide me with the correct SQL cinstruct to achieve both the requirements?Thanks,Aartee.P.S. I am sorry if I have posted this question in a wrong category in the forum, I was unsure whether I should post it in Datagrid category or SQL server category, I thought this was my best bet.My aplogies.
View 1 Replies
View Related
Sep 19, 2007
I need to transmit data from a ##table to an .xls file. The data is exported only for the first time. However, after a while the table will be dropped by SQL Server 2005 automatically.
I'm using Execute SQL Task to check for the existence of an ##Table. And, if does not exists the ##table should be created or something like the example below.
Ex:
IF NOT EXISTS (
SELECT *
FROM ##StateProvince
)
SELECT * INTO ##StateProvince
FROM Person.StateProvince
GO
To keep the session of the ## Table active, but the following error is thrown :
The NOT SQL construct or statement is not supported.
View 4 Replies
View Related
Oct 31, 2006
Let say I have 6 tables. I want to autogenerate the PK for each table and that is unique for each table and cant be duplicated on other tables. Let say I have table with PK of 1, so table2 to table6 wouldnt have a PK of 1. If table2 have a PK of 2, table1, table3 to table6 wouldnt have a PK of 2. Same for others. Identity will not be appropriate. Will 'uniqueidentifier' data type suffice? How bout guid? Or what must be my datatype? Or what will I do to implement this? Any links? Thanks
View 8 Replies
View Related
Apr 17, 2008
I am working with a form that I wish to construct a dynamic query from the results of. The forum has a date range and two radio buttons. Each radio button enables a list of items that can be clicked. So for example, if we assume the question,
"What is your favorite food, and what toppings do you like on it?" where the radio buttons are foods, the list boxes are toppings. Assuming the user can choose a Hamburger or a Salad with generic toppings, their choices are as such:
They can choose a Hamburger, with every topping
They can choose a Hamburger with a single topping.
They can choose a Hamburger with multiple toppings.
They can choose a Salad with the same combinations as above.
They cannot choose both a Hamburger and a Salad - mutually exclusive items.
Then, I wish to construct a query that, based on the conditions above, retrieves information relavent to their criteria, such a the number of food items to choose from, their price, etc. - basic information. What is the most efficient way to do this? Should I write a stored procedure with numerous conditionals and all available parameters, constructing the sproc as such:
Code Snippet
CREATE PROCEDURE GetFoodInfo
@from datetime,
@to datetime,
@FoodType varchar(20),
@toppings varchar(20)
AS
BEGIN
DECLARE @query varchar(300)
SET @query = 'SELECT COUNT(DISTINCT ' + @FoodType + ') '
IF @FoodType = 'Hamburger'
SET @query = @query + 'FROM Hamburgers '
ELSE
SET @query = @query + 'FROM Salads '
IF @toppings <> 'ALL'
SET @query = @query ' WHERE Toppings = ' + @toppings
EXEC (@query)
Apologies of this syntax is incorrect, but you get the general idea. Of course, this is a small example - in reality, I would have 5-10 conditional requirements Or, should I generate a stored procedure (or simple query) for each operation? For example, assuming each is a stored procedure:
GetHamburgers <-- would get Hamburgers with all toppings
GetHamburgersWithToppings
GetSalads
GetSaladsWithToppings
What is the best method for what I wish to achieve? What is fastest? Is there a better way than I have listed? Thank you.
Again, this is a small example, but I hope someone can help.
View 3 Replies
View Related
Jun 14, 2007
Hi there,
I'm trying to run following script in sql command mode in OLE DB Source and it giving me "The OVER SQL construct or statement is not supported." error msg.
Code Snippet
Drop
Table #Tmp_EXT_AATransactions
Select row_number() over (partition by A3.jrnentry,A32.aaGLDistID order by A32.aaGLDistID,A33.aaTrxDimID ) as rownum,
A33.aaTrxDimID,
A33.aaTrxCodeID,
A3.jrnentry,
A32.aaGLDistID,
G2.TRXDATE,
A3.GLPOSTDT,
A3.aaTRXType,
A31.ACTINDX,
A32.DEBITAMT,
A32.CRDTAMNT,
(A32.DEBITAMT - A32.CRDTAMNT) AS Amount
into #Tmp_EXT_AATransactions
From dbo.AAG30000 A3
Inner Join dbo.AAG30001 A31
On A3.aaGLHdrID = A31.aaGLHdrID
Inner Join dbo.AAG30002 A32
On A31.aaGLHdrID = A32.aaGLHdrID
And A31.aaGLDistID = A32.aaGLDistID
Right Outer Join dbo.AAG30003 A33
On A32.aaGLHdrID = A33.aaGLHdrID
And A32.aaGLDistID = A33.aaGLDistID
And A32.aaGLAssignID = A33.aaGLAssignID
Inner Join dbo.GL20000 G2
On A3.jrnentry = G2.jrnentry
And A31.ACTINDX = G2.ACTINDX
And A31.SEQNUMBR = G2.SEQNUMBR
--Where A3.jrnentry in ( '54227','54222','54225')
Select [JrnEntry],
[aaGLDistId],
[TrxDate],
[GLPostDT],
[aaTrxType],
Isnull([1],0) as Dim1,
Isnull([2],0) as Dim2,
Isnull([3],0) as Dim3,
Isnull([4],0) as Dim4,
Isnull([5],0) as Dim5,
Isnull([6],0) as Dim6,
Isnull([7],0) as Dim7,
Isnull([8],0) as Dim8,
Isnull([9],0) as Dim9,
Isnull([10],0) as Dim10,
[ActIndx],
[DEBITAMT],
[CRDTAMNT],
[Amount]
into #Tmp_EXT_AATransactions_Final
From
(
Select [aaTrxDimID],
[aaTrxCodeID],
[aaGLDistID],
[JrnEntry],
[TrxDate],
[GLPostDT],
[aaTrxType],
[ACTINDX],
[DebitAmt],
[CRDTAMNT],
[Amount],
Row_Number() Over (Partition By [aaTrxDimID],[aaGLDistID],[JrnEntry] Order By [aaTrxDimID],[aaGLDistID],[JrnEntry]) RowId
From #Tmp_EXT_AATransactions
) as Data
Pivot
(
Max([aaTrxCodeID]) For [aaTrxDimID] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) As PVT
Select *
From #Tmp_EXT_AATransactions_Final
I can parse and preview it successfully in OLE DB Source Editor but i can't see any columns in columns mapping. So when i click on Build Query in OLE DB Source Editor i get this error message. Also Its working fine in Management Studio so don't know what's the problem.
Regards,
Vivek
View 3 Replies
View Related
Aug 1, 2007
Hi, All
I'm using Gridview and SqlDataSource to dynamically display the contents in different tables, as followed:
<% dataSource.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["connectionString"]; dataSource.SelectCommand = "SELECT * FROM " + tableName; gridView.DataBind();
dataSource.UpdateCommand = "";%>
<asp:SqlDataSource ID="dataSource" runat="server"></asp:SqlDataSource><asp:GridView ID="gridView" runat="server" DataSourceID="dataSource" AllowPaging="True" AllowSorting="True" AutoGenerateEditButton="True" BorderColor="Silver" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" PageSize="20" DataKeyNames="ID" OnRowDataBound="tableGridView_RowDataBound"> <HeaderStyle BackColor="#C0C0FF" /> <AlternatingRowStyle BackColor="#C0FFC0" /></asp:GridView>
The datasource take the "tableName" as argument to determine which table to display. My problem is I can't figure out a way to programmatically construct the UpdataCommand for the SqlDataSource. I try to get the field names from the HeaderRow, but all the cells are empty in this row.
Does anyone know what causes the problem or how to construct the UpdateCommand properly. Thanks!
View 3 Replies
View Related
Jun 20, 2005
Hi,In the past I build up the query string within VB.NET page and easy to add the filtering statements in the SQL statement since it is just a string. For example, if user selected an option then include it in the filter, otherwise, just return all rows from table:Codes in ASPX.VB: Dim SQL as String="SELECT * From Table1" IF UserOption <> Null then SQL = SQL & " WHERE Column1=" UserOption" End ifNow, since I have a complicated page which need to use Stored Procedure to manapulate a temporary table before the final result. But I found when I want to add some user options similar to above, I found I don't know how to do it in Stored Procedure. In the Stored Procedure Property screen, I can't insert a IF..THEN statement within a SELECT statement. Seems I can only check the user option first and then determine the SELECT statement to use. That is: IF UserOption THEN SELECT statement 1 ELSE SELECT statement 2.But it is impossible for me to do this way since I'm not only one user option on the page. User usually can have several filters/selections on his screen. So if check which user option(s) are selected and write a static SELECT statement for it, I will have to program a complicated store procedure to cater all combinations for all user options (where some options may be null).Hope you can understanding what my mean and give me advices.Regards,Raymond
View 8 Replies
View Related
Nov 6, 1998
Referencing the sample stored procedure
below that updates table 'this_table' containing
3 INT columns: 'key', 'col2', 'col3'.
How do I revise the stored procedure so that *** I do not
have to specify ALL the columns *** to update just one column?
e.g. if I want to update @col3 to 9 (exec sp_update_table, @col3 = 9),
how can I set col2 to its existing value?
create proc sp_update_table
@key int,
@col2 int,
@col3 int
as
update this_table
set col2 = @col2,
col3 = @col3
where key = @key
go
View 1 Replies
View Related
Jul 30, 2007
Hi Guys.
I want to construct a SQL in stored procedure in the following way. Please guide me how to achive this
CREATE PROCEDURE P_SAMPLE
(
@P_ONE NVARCHAR(240)
)
AS
BEGIN
DECLARE
@TMP_CNT INT
BEGIN
EXEC ('SELECT @TMP_CNT = 1')
-- PRINT @TMP_CNT
END
END
Regards
Mani
View 2 Replies
View Related
Oct 25, 2006
I am trying to use a Pivot T-SQL statement in the Table Adapter Configuration Wizard in Visual Studio 2005. I get the error message "The Pivot SQL construct or statement is not supported". Then it executes the SQL statement as if there were no error. Unfortunately, it will not create the table adapter because of the error. Below is the T-SQL statement I am using.
SELECT OrderNumber, OrderDate, custlastname, [1001] AS Dept01, [1002] AS Dept02, [1003] AS Dept03, [1004] AS Dept04, [1005] AS Dept05, [1006] AS Dept06, [1007] AS Dept07, [1008] AS Dept08, [1009] AS Dept09, [1010] AS Dept10, [1011] AS Dept11, [1012] AS Dept12
FROM
(SELECT Orders.Ordernumber, Orders.OrderDate, Customer.custlastname, DeptID, OrderDetail.DetailAmount
FROM OrderDetails od JOIN
Orders ON Orders.OrderNumber = od.OrderNumber JOIN
Customer ON Orders.CustomerID = Customer.CustID
WHERE (DATEPART([Year], OrderDate) = '2006')) p
PIVOT (SUM(OrderDetailAmount) FOR DeptID IN ([1001], [1002], [1003], [1004], [1005], [1006],
[1007], [1008], [1009], [1010], [1011], [1012])) AS pvt
ORDER BY OrderNumber
The statement works fine in Management Studio so I know the syntax is correct.
Any Ideas?
Thanks,
James
View 4 Replies
View Related
Apr 8, 2008
Hello,
I am currently trying to import following Excel Datasheet into my SQL 2005 Database with SSIS:
Column: Created
For example cells: 18.03.2008 17:47:43
So now I want to get the time, the year, the month and the day seperately out of this column for a later OLAP Analysis.
I created a Data Flow Task, where I imported the Excel source and set the SQL database as target. But now I don't know really how to work with the "Data Conversion" where to extract the dates. In Excel this is just a "customized" cell.
Anyone who can help me in this situation?
Thank you very much.
Greets from Germany,
chle
View 1 Replies
View Related
Jul 20, 2007
Here is an example of what I am exactly doing!! CREATE VIEW NamesAS SELECT ID, fName, lName FROM Table1 UNION ALL SELECT id, FirstName, LastName FROM TABLE2. On Executing this...I'm getting the following error.
The Query Designer does not support the UNION SQL construct:
View 2 Replies
View Related
Sep 28, 2007
Hi All!
Could You comment the next situation:I'm configuring my TableAdapter just like Scott Mitchell does in his tutorialhttp://www.asp.net/learn/data-access/tutorial-70-vb.aspxThe only principal difference is that I need Insert/update and deletemethods to be generated (His aim is only SELECT).I'm also using analytic function (ROW_NUMBER) and I'm also gettinwarning “The OVER SQL construct or statement is not supported.� Yousay then that it could be ignored. But, in this case statements tomodify data (insert/update and delete) aren't being generated, thoughafter warning SQL command is executed without errors.
So, the question is obvious - why does this warning occur and how mustI perform configuration of TableAdapter based on SQL query withanalytic function?
View 1 Replies
View Related
Mar 21, 2014
Code snippet for changing '00001001001' into a table value construct containing (64,8,1)?
View 6 Replies
View Related
Aug 10, 2007
Hi All,
Just needed an insight into the IF-ELSE construct w.r.t its implementation in SSIS or a similar methodology that could be adopted in SSIS while executing a Package.
Scenario : I want to Start with importing data from Different sources to SQL Server Destination. For Which, i define 3 different Data Flow Tasks each involved in importing data from an external source to SQL Server Destination.
1] Text File Inbound Task : Source - Flat File Source ; Destination - SQL Server Dest.
2] Excel Inbound Task : Source - Excel Data Source ; Destination - SQL Server Dest.
3] Xml Inbound Task : Source - XML Data Source ; Destination - SQL Server Dest
Finally i want to execute the package with an IF-ELSE Scenario which will Check for the external Source being :
IF External Source = = Excel file => Execute Excel Inbound Task
ELSE
EXIT
IF External Source = = Text File/Flat File = > Execute Text File Inbound Task
ELSE
EXIT
IF External Source = = Xml File => Execute Xml Inbound Task
ELSE
EXIT
View 1 Replies
View Related
Oct 4, 2007
Hi everyone,
sorry to b a pest again! Before I made the decision to change the DB used in my app from SQL Server Express to SSCE, I had no problems with constructing a SELECT statement as laid out in the Title.
Basically, I have 2 tables with a one-many relationship between them. In the Parent table, I had a SQL Statement as follows:
SELECT DeptID, DeptName,
(SELECT DISTINCT COUNT(Active) FROM Documents WHERE (Documents.DeptID = Dept.DeptID) AND
(Documents.Active = 'True') AS CountOfActive
FROM Dept
Now in SSCE 3.1, I get an "Unable to parse query" error message when I construct the same SQL statement in my dataset designer.
Any thoughts on how I may solve this?
Much thanx!
Shalan
View 15 Replies
View Related
Sep 12, 2007
My problem (excuse me for being a novice) is that whenever the query becomes complex
in the @whereclause (and it does) it cant exceed the character limit of a nvarchar. Now if someone could shed some ligfht on this for me I would be
incredibly grateful. I have tried doing the exec(@var1 + @var2) but this did not seem to work.
I have included the stored procedure below.
ALTER PROCEDURE [dbo].[SearchTenderMultiRegions]
(
@whereclause nVarChar(MAX), --4000 chars
@PageIndex int,
@PageSize int
) AS
SET NOCOUNT ON;DECLARE @sql nvarChar(MAX)
SET @sql= 'SELECT * FROM (SELECT DISTINCT TOP (' + CONVERT(nvarchar(10),(@PageIndex*@PageSize))
SET @sql= @sql + ') ROW_NUMBER() OVER (ORDER BY Tenders.ID DESC)AS Row,
Tenders.ID,Tenders.Title,Address.Suburb,Tenders.ClosingDateTime,Tenders.IsApproved,Tenders.TendersSourceID, Tenders.SourceDate,
UserTracking.CreateUserID,UserTracking.CreateDateTime FROM Tenders LEFT JOIN TenderContact ON Tenders.ID = TenderContact.TendersID
LEFT JOIN TenderCategory ON Tenders.ID = TenderCategory.TendersID
LEFT JOIN TenderRegion ON Tenders.ID = TenderRegion.TendersID
LEFT JOIN RegionStateCountry ON TenderRegion.RegionStateCountryID = RegionStateCountry.ID
LEFT JOIN Address ON Tenders.ID = Address.TendersID
LEFT JOIN UserTracking ON Tenders.ID = UserTracking.TendersID
WHERE '
SET @sql= @sql + @whereclause
SET @sql = @sql + ') as TenderSearchEntries
WHERE Row between ('
SET @sql= @sql + CONVERT(nvarchar(10), @PageIndex)
SET @sql= @sql + ' - 1) * '
SET @sql= @sql + CONVERT(nvarchar(10), @PageSize)
SET @sql= @sql + '+ 1 and '
SET @sql= @sql + CONVERT(nvarchar(10), @PageIndex)
SET @sql= @sql + ' * '
SET @sql= @sql + CONVERT(nvarchar(10), @PageSize)
EXEC(@sql)
View 5 Replies
View Related
Aug 17, 2007
Please help, i am really really struggling for a logic to handle 100's of reports we have via button click from asp.net webform.
in the button click i am constructing the url :
**************************************************************************************************************
http://localhost/reportserver?/MyReports/StatusReport&UserID=1&ContractID=1&subcode=null
*************************************************************************************************************
I have a table would like to maintain the parameters required for the chosen report:
when the user chooses from list box on the webform for StatusReport, immedeately it fetches the parameters related to Statusreport and gets everything which is stored with a space in between for each parameter, for this report i have 3 parameters:
UserID ContractID subcode
now how can i construct the string based on the above parameters , i am using vb.net as code behind for my webform(asp.net)
please any ideas will help me achieve the logic. please help thank you all very much.
View 4 Replies
View Related
Aug 31, 2015
How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies
View Related
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
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
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
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
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
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
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
Sep 6, 2007
I am working on a C#/asp.net web application. The application has a text box that allows a user to enter a name. The name is then saved to the database.
Before the name is saved to the database, I need to be able to check if the name already exists in the database. The problem here is that what if the name is in the database as "JoE ScMedLap" and somoene enters the name as "Joe Schmedlap" which already exists in the database,but just differs in case.
In other words how do deal with case sensitiviy issues.
View 2 Replies
View Related
