Match Field And Return To 1
May 14, 2007
hi..i'm new in sql progaming,
i try to make make a query that in table field "match" return to "1"
if no member record in another table and return to "0" if there is any
record member :
ex
table member:
member id
A 12
B 14
Table Incoming.
member note match
C bla..bla 1
A bla..bla 0
D bla..bla 1
...... ....... .....
can anyone help me please?
D
View 3 Replies
ADVERTISEMENT
Oct 24, 2007
I created a function to call my CLR Object (See below). I keep getting the following error (failed because T-SQL and CLR types for return value do not match) no matter what data type I try in the function. The Dll is passing back a string data type. What am I doing wrong?
CREATE FUNCTION [dbo].[fnGenerateTheoreticalValue]
(@LiborSpread float,
@Maturity int,
@LGD float,
@OID int,
@ForwardEDF1Yr float,
@ForwardEDF2Yr float,
@ForwardEDF3Yr float,
@ForwardEDF4Yr float,
@ForwardEDF5Yr float,
@LIBOR float)
Returns nvarchar(Max)
AS
EXTERNAL NAME [SQLCLRTheoretical_Values].[SQLCLRTheoretical_Values.Theoretical].[CalcValue]
View 11 Replies
View Related
Mar 20, 2015
I am trying to create a CLR function to call a webservice, the CLR function return data type is double, whether I try to create this as a table valued funcion or a scalar to return a distance travelled value I am receiving the error below.
I've tried changing data types around in the CLR side and the SQL side but keep receiving the same error message:
[Microsoft.SqlServer.Server.SqlFunction(Name = "DistanceCalc")]
public static Double DistanceCalc(Double SrcLat, Double SrcLong,
Double DestLat, Double DestLong)
{
MileageWS ws = new MileageWS();
[Code] ....
Error received when try to Create function ...
1, Level 16, State 2, Procedure pcMiler, Line 6
CREATE FUNCTION for "pcMiler" failed because T-SQL and CLR types for return value do not match.
View 2 Replies
View Related
Jun 10, 2004
How can I make my search button have the "Any part of field" match as a default? with a simple query...
I have a field in MS access with hundreds of words (cv)... I want to be able to find a word in "Any part of field"
my try:
WHERE ((([cv].[detail cv])=[detail]));
detail is nowhere to find... i am prompt to give a value. fine.but it
equals whole field; detail must be the sole value of the field detail cv...
help!
mchel
View 7 Replies
View Related
Aug 25, 2015
I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
View 8 Replies
View Related
Oct 31, 2013
I need some store procedure script , from two table i want to pickup matching field and insert into main table ,give some sample store procedure coding...
If 1st table as cheque no and 2nd table as cheque no - matching then data would be inserted in to main table using two table...
View 2 Replies
View Related
Mar 5, 2007
Hi,
I wonder if anyone can help. I have installed the adventureWorksDW and when I run this query
select * from sys.all_columns where object_id = 85575343 and column_id = 2
The max_length filed value is 50 but if I view the column in explorer view the max_length is 25 I.e. ProductAlternateKey(nvarchar(25) ,null)
I don;'t know if I am looking at the correct table value?
View 3 Replies
View Related
Jan 12, 2006
I have a customer who wants to SELECT records based on a partial match in atext field. For example, in a list of telephone numbers they want to searchfor all records that contain the digits '777' in any part of the string. Howdo I formulate such a query?Many thanks.
View 2 Replies
View Related
Feb 20, 2007
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
View 1 Replies
View Related
May 15, 2008
If I have a column named "Login" in a SQL Table (I am sharing with another application) that I am using a stored procedure to acquire the information from, how can I trranspose its name to match code already written in a Web App to get the data.
There is a web app already created that has the followig code to get the data from the database
Dim strSQL ast string = "UsersSelectCommand"
intLoginID = objDataReader("LoginID")
My stored procedure is the following:
CREATE PROCEDURE UsersSelectCommand/* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT )*/AS Select Lastname, FirstName, Login from Users Order by LastName
GO
The stored procedure will return "Login" instead of "LoginID" that I am wanting. How can I modify the Stored Procedure to change the LoginID to Login.
View 2 Replies
View Related
Jul 20, 2005
Right this has to be a Micro$oft mess-up surely...?I'm running SQL 2k standard with SP3. I have a table which I'm tryingto query using a LIKE operator on a varchar field as follows....WHERE dbo.tbl_pm_projects.SeniorManagerID LIKE '%'....In actual fact the % is passed in by the application when the userselects "All managers" from the drop down list used to select theManager to filter by. If they select a manager's name from the listit becomes LIKE 'ajames' or whatever.BUT - the table currently contains 2972 records. If I take out theWHERE clause the SELECT returns all records - fine - but if I put thewhere clause in it returns only 1682!! I thought the % was meant tomatch, and I quote the SQL server Books Online files here; "Any stringof zero or more characters." Anyone explain to me whats going onhere?TIANiall
View 12 Replies
View Related
Aug 1, 2007
Could anyone help of how to match the exact characters in a data field in SQL 2005 Developer.
For example, if one has a password "GooD", then when he or she enters "GOOD", "good", etc, the database will not match the password. And he or she must enter the exact characters, which is "GooD".
Thanks.
View 2 Replies
View Related
Aug 1, 2007
Could anyone help of how to match the exact characters in a data field in SQL 2005 Developer.
For example, if one has a password "GooD", then when he or she enters "GOOD", "good", etc, the database will not match the password. And he or she must enter the exact characters, which is "GooD".
Thanks.
View 3 Replies
View Related
Jun 1, 2005
Howdy all,I'm wishing to write a stored proc to return only the field names from a table. What I've tried gets the field names but also returns all of the data in each row. I only want the field names. Is this possible?Thanks!JP
View 7 Replies
View Related
Feb 11, 2008
I have a field in a table that has sql statements as follows;
'Low_Quote_Mortgage_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 10),'-','') + '.txt'
I am trying to create a proc/view to return the data in an executed form ie;
Low_Quote_Mortgage_021108.txt
I tried using a function ;
create FUNCTION [dbo].[DynamicFileConversion] (
@dynamic_filename VARCHAR(100)
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Static_filename VARCHAR(100)
SET @Static_filename = (@dynamic_filename)
RETURN @Static_filename
END
SELECT suptype_id, oen.dbo.DynamicFileConversion(REPLACE(dynamic_filename, '%day%', 'GETDATE()')) AS Link
FROM abc.dbo.ondemand
Didn't work...the only way i can think to do this is by first inserting the data into a temp table then using a cursor to go through each record and linking it back to the table ...just seems really inefficient anyone know a better way?
View 7 Replies
View Related
Aug 2, 2006
I am in need of help to develop a query
I have two tables Exams and Exams_lab, that are joined by a field id_exame. I want to return The Exams that has all the dependent rows in Exames_lab with the same value in the status_int field of Exames_lab. Can anyone Help Me?
Lets see an example
tb_exame
id_exame
date_exame
1
07/01/2006
2
08/01/2006
3
09/01/2006
tb_exame_lab
id_exame_lab
id_exame
desc_exame
status_exame
1
1
NORMAL
1
2
1
0
3
2
NORMAL
1
4
2
PROBLEMS
1
5
2
OK
1
6
3
OK
0
in this exemple my query must return only id_exame 2 and 3 because id_exame 1 has two different values on id_status on tb_exame_lab
can anyone help me?
View 5 Replies
View Related
Feb 17, 2004
Hi!
I have a question that might sound obvious to some of you but (obviously) not to me.
I have a stored procedure that adds a new record to a table that has an [Identity] field (TableID).
I want to return the value of the TableID of the newly created record. Sound simple ?
Thanks.
View 4 Replies
View Related
Mar 29, 2004
hi
i have connected my ms sql 2000 with C using ODBC
can u help me to return the utf 8 string from nvarchar field ??
how should i do it
please help!!!!!!!
View 6 Replies
View Related
Mar 11, 2008
how do you un-salt the password field and return it ? and how can you view the password field inside sql server management studio express in the un-salted form ?
View 1 Replies
View Related
Jun 5, 2014
Query to return values from XML field
create table #temp
(
id int identity (1,1)
,FieldSet XML
)
INSERT INTO #temp
VALUES ('<Fields> <Field Key="Column1" value="value1" > </Field> <Field Key="Column2" value="value2"> </Field> </Fields>')
,('<Fields> <Field Key="Column3" value="value3" > </Field> <Field Key="Column4" value="value4"> </Field> </Fields>')
SELECT * FROM #temp
Expected Output :
Id Column1Column2Column3Column4
1value1value2nullnull
2nullnullvalue3value4
View 4 Replies
View Related
Jun 18, 2014
I am using a monitoring system where I can monitor a numeric SQL result assuming the result is one field and one row.I would like to do this to say monitor the free available space or percentage on say the Master database. DBCC SQLPERF gives me a few columns and results for all databases on the server.
View 2 Replies
View Related
Dec 20, 2007
Hi,
I need to implement some thing like this.
I have a query like
SELECT table1.col1
,€™n/a€™ _response
FROM table1
INNER JOIN table2
This is the query that get the report data for my report. Now I need to replace the second column with an actual response like €˜accepted€™ and €˜rejected€™. And these values should be a result of evaluation of this form
Statusarray[] = ResponseStoredProcedure(table1.col1)
If(Statusarray.count < 1)
Set _response = €˜accepted€™
Else
Get Statusarray[1]
Compare this to Statusarray[0]
Set _response = some result based on comparision.
The _response returned in the query should return the actual response based on this evaluation where ResponseStoredProcedure is sent the current row value for table1.col1.
How can this be done in sql(using SQL Server 2005)
PLZZZ HELP!!!!!!!!!
Thanks,
Hari
View 2 Replies
View Related
Mar 22, 2006
I have a table in my MS SQL 2000 database called News which has a field caled NewsDate. This is a standard Date field which stores the info in this format: 3/1/2001.
I want to create a query that returns one row for each year that there is a story.
For example, if I had this data...
3/1/2001, 6/27/2003. 9/17/2003, 1/1/2006, 4/5/2006
the query would return this result:
2001
2003
2006
This is the query I've started with:
SELECT DISTINCT NewsDate FROM News ORDER BY NewsDate DESC
What modifier can I apply to the NewsDate field to extract JUST the year from the table? If this were ASP I would try something like Year(Date), but, of course, I can't do that here.
Is this even possible? I've been looking up date functions, but haven't found anything that will work in a select statement. ANY and ALL advice will be greatly appreciated.
View 1 Replies
View Related
Nov 10, 2014
I recently ran into an issue with an issue with a query against our Data Warehouse. When attempting to sum revenue from a table, and using a WHERE clause on a field that contains NULL values, the records with the NULL values are suppressed (in addition to whatever the WHERE clause specified). I believe this is because a NULL value is unknown so SQL doesn't know if it does or doesn't fit the criteria of there WHERE clause so it is suppressed.
That being said, is there a way to avoid this instead of having to add an ISNULL function in the WHERE clause which is going to kill performance?
Code:
create table #nullTest (
name varchar(50)
,revenue int)
INSERT INTO #nullTest
Values ('Tim',100)
,('Andrew', 50)
,(null, 200)
SELECT sum(revenue) as Revenue FROM #nulltest WHERE name <> 'tim'
Ideally, I would want the SELECT statement above to return 250, not 50. The only way I can think to accomplish this is with this query:
Code:
SELECT sum(revenue) as Revenue FROM #nullTest WHERE isnull(name,'') <> 'tim'
View 4 Replies
View Related
Apr 15, 2008
Is there a way to return the fields collection of a dataset using the Reporting services Web Service? I can get to the reports but not the datasets. Thank you in advance for any support you can provide.
View 1 Replies
View Related
Nov 24, 2004
how can i insert a carriage return when i update the field?
say i want to put the following inside a field:
firstline
secondline
how can i update/insert a column to have a return carriage inside it?
UPDATE table SET column = 'firstline secondline'
the reason i want this is because when using a program (Solomon, by microsoft, purchasing software) to grab a field out of the database and when it displays that field in the programs textbox, i want it to be displayed on two separate lines
i tried doing
UPDATE table SET column = 'firstline' + char(13) 'secondline'
but when in the solomon program, it displays an ascii character between firstline and secondline like: firstline||secondline
thanks
View 3 Replies
View Related
Apr 16, 2015
I'm pulling individual address fields into my script, some of which are null.
I would like to add in a carriage return after ONLY those fields that are NOT NULL.
Current script is:
SELECT
ISNULL(SAD.HOUSENUMBER,'') + ISNULL(SAD.ADDRESS1,'') + ISNULL(SAD.ADDRESS2,'') + ISNULL(SAD.ADDRESS3,'') + ISNULL(SAD.CITY,'') + ISNULL(SAD.COUNTRY,'') + ISNULL(SAD.ZIP,'') as 'FULL_ADDRESS'
FROM
SFAADDRESS SAD
View 5 Replies
View Related
May 11, 2015
I have table in which month & year are stored, Like this
Month Year
10 2014
11 2014
12 2014
1 2015
2 2015
3 2015
4 2015
I wanted a query in which it should return the value in a status field which has latest year & month.
View 9 Replies
View Related
Jun 12, 2007
I am trying to write a user defined function that will allow me tostrip off the last carriage return and line feed from a text field.We have address fields stored in a text field for our ERP system andsome of them have an extra carriage return and line feed at the end ofthem. This causes havoc when we sync between our ERP system and CRMsystem. If anyone knows a way to solve this problem the help would beappreciated.Examples:Existing Text field with CR:1234 Blah Street<CR>Suite 2345<CR>Corrected Text field:1234 Blah Street<CR>Suitr 2345
View 4 Replies
View Related
Oct 26, 2006
Hi,
As you can see, I'm totally new at Sql Server.
I have a problem, I store text into a nvarchar field (could be a 200 or 20,000 characters long string), inside the text there are several carriage returns which I would like to preserve to later presentation, but when retreiving the data from sql server I got the "cr" as "?", also I opened the database from Sql Managment and all cr's were saved as "?".
What can I do to preserve the cr inside each field ?
Thanks in advance.
View 6 Replies
View Related
Aug 31, 2007
I get this error when I look at the state of my SQLresults object. Have I coded something wrong?Item = In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user. conn.Open()
Dim strSql As String
strSql = "INSERT INTO contacts (companyId, sourceId, firstName, lastName, middleName, birthday, dateCreated)" _
& "VALUES ('" & companyId & "', '" & sourceId & "', '" & firstName & "', '" & lastName & "', '" & middleName & "', '" & birthday & "', '" & now & "') SELECT @@IDENTITY AS 'contactId'"
Dim objCmd As SqlCommand
objCmd = New SqlCommand(strSql, conn)
Dim aSyncResult As IAsyncResult = objCmd.BeginExecuteReader()
If aSyncResult.AsyncWaitHandle.WaitOne() = True Then
Dim sqlResults As SqlClient.SqlDataReader
sqlResults = objCmd.EndExecuteReader(aSyncResult)
Dim cid As Integer
cid = sqlResults.Item("contactId")
Me.id = cid
conn.Close()
Return cid
Else
Return "failed"
End If
View 3 Replies
View Related
Jul 20, 2006
I need to replace Access Val() functions with similiar function in sql.
i.e. Return 123 from the statement: SELECT functionname(123mls)
Return 4.56 from the satement: SELECT functionname(4.56tonnes)
Any one with ideas please
Thanks
George
View 1 Replies
View Related
Nov 1, 2007
I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
View 7 Replies
View Related
