Conditional Return Of A Value From A Set Of Records As A Field Value In A Query
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
ADVERTISEMENT
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 17, 2008
Hi
I am looking to write a query that returns all records Inserted in the last hour.
The problem, as I see it, is that the column I need to refer to is a VARCHAR() datatype. Can I convert from varchar (example 14:04:31)to a time value and calculate from this ?
I would like to subtract 1 hour from current_timestamp or similar, so that the query dynamically changed.
Many thanks
View 16 Replies
View Related
Dec 1, 2007
The topic pretty much sums up my question.
What would the sql query be to return only all the records in a table that are 45 days old from today.
Thank you for any help in this matter
Al
View 5 Replies
View Related
Nov 9, 2006
Hi,
I'm trying to retrieve some records from an SQL database.
I've a table named CustomerOrder with three fields - custID , productname and quantity
No keys in the table. it's row based approach. every custID can have multiple entries for different products.
Example:
CustID ProductName Quantity
1 Product1 2
1 Product2 3
2 XXX 1
2 Product1 2
1 Product3 4
I would like to write a query that gives the result as follows :
CustID ProductName Quantity
1 Product3 4
2 Product1 2
Meaning that, query has to retrieve only one record per custID based on highest quantity.
select custId,Productname,quantity from customerorder where quantity = (select max(quantity) from customerorder)
the above query returns only one record. (ofcourse..)
Kindly help me to get the desired.
Thank You.
View 5 Replies
View Related
Jul 21, 2015
What I would like to do is to have a TSQL Select return the number of records in the Result as if TOP (n) had not been used. Example:I have a table called Orders containing more than 1.000 records with OrderDate = '2015/07/21' and my client application has a threshold for returning records at 100Â Â and therefore the TSQL would look like
SELECT TOP (100) *Â FROM Orders Where OrderDate = '2015/07/21'Â ORDER by OrderTime Desc
Now I would like to "tell" the client that only 100Â of 1.000 records are shown in the client application grid. Is there a way to return a value indicating that if TOP (100) had not been used the resultset would have been 1.000. I know I could create the same TSQL using COUNT() (SELECTÂ COUNT(*)Â FROM Orders Where OrderDate = '2015/07/21'Â ORDER by OrderTime Desc) and return that in a variable in the SELECT statement or even creating the COUNT() as a subquery and return it as a column, but I would like to avoid running multiple TSQL's. Since SQL Server already needs to select the entire recordset and sort it (ORDER BY) and return only the first 100 the total number of records in the initial snapshot must somehow be available.
View 6 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
Jan 1, 2008
Problem is that if the [Receiving] table doesn't have a match then no records are return. I want all matches from the [Orders Subtable] and any matches from the [Receiving] Table. If no [Receiving] table matches then I still want all matches from the [Orders Subtable]. Attached is the query.
Note: The query has to run in Access 2000 and I will be coding it in VB.
SELECT Orders.[Orders ID],
[Orders Subtable].ID,
[Orders Subtable].Quantity,
Receiving.Quantity,
Receiving.[Component #]
FROM (Orders
LEFT JOIN Receiving ON Orders.[Orders ID] = Receiving.[Orders ID])
INNER JOIN [Orders Subtable] ON Orders.[Orders ID] = [Orders Subtable].[Orders ID]
GROUP BY Orders.[Orders ID], [Orders Subtable].ID,
[Orders Subtable].Quantity, Receiving.Quantity,
Orders.[Project #], [Orders Subtable].On_Order,
[Orders Subtable].[Component #],
Receiving.[Component #]
HAVING (((Orders.[Project #])="Speed1aaaaa") AND
(([Orders Subtable].On_Order)=True) AND
(([Orders Subtable].[Component #])="R02101A") AND
((Receiving.[Component #])="R02101A"));
View 2 Replies
View Related
Mar 6, 2008
I have received some data out of a relational database that is incomplete and I need to find where the holes are. Essentially, I have three tables. One table has a primary key of PID. The other two tables have PID as a foreign key. Each table should have at least one instance of every available PID.
I need to find out which ones are in the second and third table that do not show up in the first one,
which ones are in the first and third but not in the second,
and which ones are in the first and second but not in the third.
I've come up with quite a few ways of working it but they all involve multiple union statements (or dumping to temp tables) that are joining back to the original tables and then unioning and sorting the results. It just seems like there should be a clean elegant way to do this.
Here is an example:
create table TBL1(PID int, info1 varchar(10) )
Create table TBL2(TID int,PID int)
Create table TBL3(XID int,PID int)
insert into TBL1
select '1','Someone' union all
select '2','Will ' union all
select '4','Have' union all
select '7','An' union all
select '8','Answer' union all
select '9','ForMe'
insert into TBL2
select '1','1' union all
select '2','1' union all
select '3','8' union all
select '4','2' union all
select '5','3' union all
select '6','3' union all
select '7','5' union all
select '8','9'
insert into TBL3
select '1','10' union all
select '2','10' union all
select '3','8' union all
select '4','6' union all
select '5','7' union all
select '6','3' union all
select '7','5' union all
select '8','9'
I need to find the PID and the table it is missing from. So the results should look like:
PID
MISSING FROM
1
TBL3
2
TBL3
3
TBL1
4
TBL2
4
TBL3
5
TBL1
6
TBL1
6
TBL2
7
TBL2
10
TBL1
10
TBL2
Thanks all.
View 5 Replies
View Related
Oct 26, 2006
Can Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!
View 6 Replies
View Related
Dec 25, 2007
Hi,
I got a problem.
I installed Microsoft SQL Server Management Studio Express 2005 version.
And I created a Compact database.
I created an connection in SSMSE to connect the database and opened a query form.
then, i run the following sql:
Select * from Table1
It returned 3 records to me.
After that, I used program to insert record into this table.
Then i ran this sql again, it still show me 3 records.
I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.
Why? It's very strange and difficult to operate, right?
Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?
Thanks a lot!
And Merry X'max!!!
View 4 Replies
View Related
Mar 25, 2008
How can I format the background color of a data-region field when I just want it to be "silver" If it is a subtotal value???
View 3 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
May 18, 2012
I really need creating a query that will retreive all records from a table where the dbo.CorpAdv.AcctNum field equals a specific value (for this example "0023"), the TranCode = "R" and the sum of the records, starting with the latest, equals the value of a field in another table (dbo.Master.TotalAdv)
dbo.Master.TotalAdv is numeric (dollar amount) and in this example the value is $1,850.00
dbo.CorpAdv.pID is an integer and unique ID for each record, later records have higher numbers
dbo.CorpAdv.AcctNum is text field
dbo.CorpAdv.AdvAmt is numeric (dollar amounts)
[code]....
View 3 Replies
View Related
Dec 10, 2002
I'm getting syntax errors that just aren't helping me at all, so I thought maybe what I'm trying to do can't be done. I'm creating a UDF with 4 parameters, and I want it to return a result set (i.e. a table). But I want a different result set depending upon the value of one of the parameters. This works totally fine as a SP, but I can't tell where to put the RETURN clause(s) on the UDF.
I've got:
CREATE FUNCTION myFunction(@param1,...,@param4)
RETURNS TABLE
AS
BEGIN
IF @param1='x'
BEGIN
RETURN(SELECT columns FROM TableX)
END
ELSE
BEGIN
RETURN(SELECT columns FROM TableY)
END
END
I get an "Incorrect syntax near the keyword 'IF'" error. I also tried using just one RETURN() wrapped around the outside of the IF construction (right after the very first BEGIN and before the last END), but to no avail. I get no errors when I run this logic as an SP. Is this type of construct not allowed in a UDF? Is there an alternative? I can't just leave this as a proc because I'm going to have to call these results from several views. Help!
Thanks,
-Ed H.
View 3 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
Feb 13, 2008
I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.
Thanks in advance,
Mark
View 5 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 4, 2015
I'm trying to convert the query immediately below into a function with the conditional logic to return a VARCHAR value with the gender: male, female or unknown.
SELECT empid, firstname, lastname, titleofcourtesy,
CASE
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;
GO
Below is the conditional logic function I'm trying to create to replicate the logic above.
CREATE FUNCTION dbo.Gender
(
@male AS VARCHAR(10),
@female AS VARCHAR(10),
@unknown AS VARCHAR(10)
)
RETURNS VARCHAR(10)
[Code] .....
View 6 Replies
View Related
Mar 7, 2007
I have to 2 texboxes in the detail line of a table
TextBoxA = IIF( Fields!Total_Amount.Value >0, Fields!Total_Amount.Value,0)
TextBoxB =IIF( Fields!Total_Amount.Value <0, Fields!Total_Amount.Value,0)
I would like to total thoses textboxes in the group footer (Loan_Group1)
I tried this for
=Sum(IIF( Fields!Total_Amount.Value > 0, Fields!Total_Amount.Value,0))
Or
=RunningValue(
IIF( Fields!Total_Amount.Value >0, Fields!Total_Amount.Value,0) ,sum,"Loan_Group1")
for both I get this error:
The value expression for the textbox €˜textbox190€™ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.
Thanks
Elias
View 3 Replies
View Related
Aug 5, 2000
Dear All,
I am having a problem in adding new records in SQL 7.0 Database.
I have two databases - one with table name DB1..WTEST and the other with
table name DB2..TEST. I want to update records in DB2..TEST table when
records are already in TEST table (by comparing primary key - with ignore
duplicate option) & secondly, INSERT records in DB2..TEST table from
DB1..WTEST table if the records do not exist in DB2..TEST table. The first
option which is Updation, works fine but with second option I have problem.
It works and records are INSERTED but I don't know the statistics of
inserted records. Instead it gives a warning message - Duplicate records
were Ignored -
Can somebody help me out or suggest some better solution for conditional
record insertion. I used CURSOR option but it is only for Updation &
Deletion.
Here is my Transact-SQL Script.
--------------------------------------------------------------
IF EXISTS (SELECT * FROM DB1..WTEST
INNER JOIN DB2..TEST
ON DB1..WTEST.Name1 = DB2..TEST.Name1)
BEGIN
UPDATE DB1..TEST
SET add1 = DB1..WTEST.Add1
FROM DB1..WTEST
Print 'Updated'
END
WHILE NOT EXISTS(SELECT * FROM DB1..WTEST
INNER JOIN DB2..TEST
ON DB1..WTEST.Name1 = DB2..TEST.Name1)
BEGIN
INSERT DB2..TEST
SELECT DB1..WTEST.Name1,
DB1..WTEST.Add1
FROM DB1..WTEST
CONTINUE
END
------------------ Alternate Script ---------------------
IF EXISTS (SELECT * FROM DB1..WTEST, DB2..TEST
WHERE DB1..WTEST.Name1 = DB2..TEST.Name1)
BEGIN
UPDATE DB2..TEST
SET add1 = DB1..WTEST.Add1
FROM DB1..WTEST
WHERE DB1..WTEST.Name1 = DB2..TEST.Name1
END
INSERT DB2..TEST
SELECT DB1..WTEST.Name1,
DB1..WTEST.Add1
FROM DB1..WTEST
WHERE NOT EXISTS(SELECT * FROM DB2..TEST
WHERE DB1..WTEST.Name1 =
DB2..TEST.Name1)
END
----------------------------------------------------------------------------
------------
Ibrar Ahmed
System Projects Controller
Olayan Saudi Holding Company
Ph:- +966-3-8871000 x 1122
Fax:- +966-3-8872000
E-Mail: i.ahmed@oshco.com
View 2 Replies
View Related
Jun 21, 2007
I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually.
Thanks
View 8 Replies
View Related
Dec 15, 2005
Hi!
I have a table called DB1 that contains this:
MID
IIN
NUM_EVENTS
DATE
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
View 6 Replies
View Related
Dec 15, 2005
Hi!
I have a table called DB1 that contains this:
MID
IIN
NUM_EVENTS
DATE
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
$3.99/yr .COM!
http://www.greatdomains4less.com
View 3 Replies
View Related
Mar 17, 2007
Hi,
I have a report in which there is a field called "Returned Qty" and there is a parameter called Show Qty now I want this field returned Qty to only appear if this show Qty parameter is set to yes. Can Anyone suggest me how to do this?
I guess It could be done by using the properties option and writing some expression. I appreciate the response,
Thanks,
Rashi
View 10 Replies
View Related
Apr 11, 2008
Can I build an expression that allows me to change the field size of a column or row in SSRS2005?
View 5 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Mar 11, 2008
There was a similar question to mine a few time ago about conditional execution, but I'm having some problems with it to which I couldn't find any answer, could anyone help?
My condition is checking if a DateTime from a DB table is null. I use the Execute SQL Task to retrieve the record (SELECT TOP 1 * FROM myTable WHERE conditions = TRUE), then I map 2 fields to their respective global variables.
Then I created the green arrow connector and used a precedence expression based on sucess. I used the expression IsNull(@variable). For some reason, even if the @variable comes as null - I checked the info in the DB - it still comes out as '1999-11-30'
I've looked around and the IsNull method can be used to also change the value in the event of it being null, could it be changing my variables value to this even if I didn't pass that value as a parameter to the function? If so, how else can I test it for null? I've tried "== null", "= null" and even ".equals(null)" - which I know is absurd, but I'm running out of options here...
Could you enlighten me, please???
Thanks in advance!
View 4 Replies
View Related
Sep 9, 2015
Conditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.
View 13 Replies
View Related
Jan 10, 2013
I have table that I need to retrieve the top 2 records, the issue is I have 3 records with the same date, but I only want the first 2. Each record looks something like this.
id, team, date, setnr, series
1, 3, 1/1/2013, 1, 1102
1, 3, 1/1/2013, 2, 1231
1, 3, 1/1/2013, 3, 1023
1, 3, 1/5/2013, 4, 1024
1, 3, 1/5/2013, 5, 1123
1, 3, 1/5/2013, 6, 1232
2, 2, 1/1/2013, 1, 1032
2, 2, 1/1/2013, 2, 1221
2, 2, 1/1/2013, 3, 1023
2, 2, 1/5/2013, 4, 1231
2, 2, 1/5/2013, 5, 1112
2, 2, 1/5/2013, 6, 1231
I have to be able to add the series up of only the first two records for each id based on date. Here is a sample query
select sum(series), date from table group by date order by sum(series) desc
This gives me the total for all three and gives it to me in descending order. I need the records for set 1 and 2 of each of the Id. There are many records but the date and the setnr doesn't duplicate.
View 1 Replies
View Related
Feb 27, 2007
Hello experts,I'm trying the run the following query with specific intentions.I would like the query to return 5 results; i.e., 4 distinct and oneduplicate. I am only getting, however, 4 distinct records. I wouldlike the results from the '007' id to spit out twice.I'm not using 'distinct,' and I've tried 'all.' I realize that Icould put my 5 employee id's in a table and do a left or right join; Iwould like to avoid that, however. Any thoughts?SelectEmployee_last_name,Employee_first_name
Quote:
View 3 Replies
View Related
Feb 15, 2007
Hi,
I need some help with this please.
I have a database table which contains customer orders. I am trying to code my SQL select statement to:
1) Only return records where the record orderdate is within the last 30 days
2) Between two dates, selected from the datepicker control.
With regards to issue 1, I could fill a table with all the records for the account in question and then for each record do a datediff between the records order date and the current date to determine if the number of days is within 30 days. If yes then add this record to a temp table and then set this table as the datasource for the datagridview.
There must be a more efficient way?
With regards to issue 2) ?
View 1 Replies
View Related
Jun 25, 2007
I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?
Or is it possible to conditional run other control flow items according the the status of SQL task execution?
View 1 Replies
View Related
