I have the qry, which is suppose to assigned records to active user (almost 15 users) equal, but it doesn’t- sometime it assigned more to some users and less to others. How could I modify my qry to make sure it assigns the records equals to each user? Please, see the qry below. I will appreciate any help
Thk
UPDATE
TBLFRAUDFINDER
SET
DATE_ASSIGNED=GETDATE(),
FRAUDANALYSTASSIGNED=@FRAUDANALYST
WHERE
FRAUDID=@FRAUDID OR
(FRAUDANALYSTASSIGNED IS NULL AND
((INQUIRY_GOVT_NUMBER=@INQUIRY_GOVT_NUMBER) OR
(CUST_NM=@CUST_NM) OR
(ALERT_IDENTIFIER=@ALERT_IDENTIFIER) OR
(ACCT IN (SELECT
ACCT
FROM
TBLFRAUDFINDER
WHERE
INQUIRY_GOVT_NUMBER=@INQUIRY_GOVT_NUMBER OR
CUST_NM=@CUST_NM OR
ALERT_IDENTIFIER=@ALERT_IDENTIFIER))
Hey gang, I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query') I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling. Here's my current Query this works fine: Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#') What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note. I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No) Any advice would be greatly appreciated.
I am new to SQL Server (coming from Oracle background) and have a large table I need to loop thru and assign a number 1 thru 5 on each record.
below is an example of how I would do it in Oracle.
declare num number := 1; cursor c_rec is SELECT rowid, t.* FROM temp t order by t.column; begin for d_rec in c_rec loop update temp set column = to_char(num)
In my report, I am having Listbox for users to choose Country, City & Company. The user can choose Country. Based on the country selection, cities will be listed out. Based on the city selection, Companies will be listed out. They can choose companies.
Now, I have to show two set of results.
A. List of Companies as per selection ( dataset with equal to selection )
B. List of Companies which are not selected ( ie dataset with not equal to selection )
I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.
Can someone tell me why these do not equal? The rec_day is 10 ? WHERE (CAST(DATEPART(Q, GETDATE()) * 3 - 2 AS VARCHAR(2)) + '/' + CAST(rec_day AS VARCHAR(2)) + '/' + CAST(YEAR(GETDATE()) AS VARCHAR(4))) = GETDATE()
hi does http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584332.html = http://www.wrox.com/WileyCDA/WroxTitle/productCd-076457955X.html ?? and what is the difference ?? and which one of these you advice me to read for beginner ?? thank you
SELECT website FROM dbo.wce_contact WHERE (NOT (Mail1Date IS NULL)) AND (Mail2Date IS NULL) AND (Mail3Date IS NULL) AND (Mail4Date IS NULL) AND (Mail5Date IS NULL) AND (Mail6Date IS NULL) AND (Mail7Date IS NULL) AND (Mail8Date IS NULL) AND (Mail9Date IS NULL) AND (Mail10Date IS NULL) AND (Mail11Date IS NULL) AND (Mail12Date IS NULL) AND (Mail13Date IS NULL) AND (Mail14Date IS NULL) AND (Mail15Date IS NULL) AND (Mail16Date IS NULL) AND (IDStatus IS NULL OR IDStatus = '') AND (NOT (Task LIKE '%x%') OR Task IS NULL) AND (NOT (ExpressEmail IS NULL OR ExpressEmail = '')) AND (NOT (WebSite IS NULL OR WebSite = '')) AND (Mail17Date IS NULL OR Mail17Date = '') AND (Mail18Date IS NULL) AND (Mail1Date <= '20080421') AND (NOT (RECORDOWNER = 'lbm') OR RECORDOWNER IS NULL)
How would i get the part in red to say mail1date less than or equal to 42 days back from todays date?
I need to select all database names that are not contained in table tsm.dbo.DB_NOT_to_Backup, but are present in master..sysdatabases. Can this be done using not equal joins?
I tried the query below, but it got me nowehere:
select a.name from master..sysdatabases a inner join tsm.dbo.DB_NOT_to_Backup c on a.name<>c.DBname order by a.name
I have table 'A' containing Company information including the company zip code. I have table 'B' which is a table of ZipCodes. How can I get a result set of all the Zip Codes that exist in 'A' but not in 'B'? Thank you
Hi, I have a stored procedure that returns a string. How do I assign it to a variable? I have currently exec myStoredProc '2' and I want something like declare @myVar varchar(25) set myVar = exec myStoredProc '2'
But I can' seem to figure out the syntax for assignment. Thanks
I'm having a problem with what should be a simple TSQL statement. I have a table which has a datetime field updated. After the update if I type
select * from patient_medication where rec_status_date = '11/10/2000' it returns the rows I want.(All the dates have a time of 00:00:00.000. But if I type select * from patient_medication where rec_status_date <> '11/10/2000' or select * from patient_medication where rec_status_date != '11/10/2000'
The rows that have a value are returned, but none of the null values are returned. Will nulls not work with this comparison? Thanks
I am trying to update one table on server A/database A with new records from a server B/database B.
Both databases use the same collation. The key-fields of both table do use the same collation, but not the server.
Help: I was thinking, when both databases and the fields inside the tables do both use the same collation, then there will not upcome any conflict ?
Do I have to adapt the database collation, too ? This would'nt be nice as I am retreiving data from different servers with different server collations... Anybody else got the same problem ?
The SUBSTRING function returns @domain.com. I don't want any results to be displayed from my sql statement if it equals @myemail.com. I have other cases too that are not relevant to my question. I'm getting an error that says invalid syntax near <
SELECT DISTINCT CASE CUST_EMLAD_TX WHEN SUBSTRING(CUST_EMLAD_TX, CHARINDEX('@',CUST_EMLAD_TX), LEN(CUST_EMLAD_TX)) <> '@myemail.com' THEN CUST_EMLAD_TX END FROM ...
comparing a value, my sql statement fails at the last bit i.e. )<>0I'm trying to compare the last three characters i.e. not equal to 0? error message
Msg 102, Level 15, State 1, Procedure StoredProcedure, Line 137 Incorrect syntax near '<'.
Basically I'm trying to validate the field name Code to have a letter followed by 3 numbers..
if LEFT (@Code, 1) NOT LIKE '[a-Z]%' OR ISNUMERIC (RIGHT(@Code,3)<> 0
Create PROCEDURE dbo.InsertAccount @AccRisAsse bit, @AccHldBll bit, @Acctaxexm bit AS insert into TblAccount(AccRisAsse,AccHldBll,Acctaxexm) values(@AccRisAsse,@AccHldBll,@Acctaxexm)
return scope_identity()
GO
And here is the code in .Net to run the proc and get back the scope_identity:
I delcare my sqlCommand sqlcmdInsNeAcc and connection from system.data.Sqlclient, I open my connection. all goes well then I do: sqlcmdInsNeAcc.type=commandtype.storedprocedure sqlcmdInsNeAcc.CommandText = "InsertAccount" pm = sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@AccountNumber", SqlDbType.BigInt)) pm.Direction = ParameterDirection.ReturnValue Dim kAccountNumber as integer
then when I try to display the value of my returned scope identity from the variable: kAccountNumber ALL I GET IS ZERO although my row is created in the table with the right Account number
I have a if statement and a table with 2 atributes. They are description and date. I will check if there is in the table some date like 01-01-2008.String test "01-01-2008";if (test.Equals(here I will a SELECT query like SELECT date FROM appointmentTable)){ textbox1.text = "The description on this date is: " + Here I will the descripton of the selected date
What I need to do is get all of the records from T2 that don't match EXACTLY to a record in T1. So I figured a LEFT OUTER JOIN should work:
SELECTT2.* FROMT2 LEFT OUTER JOIN T1 ONT2.C1 = T1.C1 ANDT2.C2 = T1.C2 ANDT2.C3 = T1.C3 WHERET1.C1 IS NULL
But this statement returns the DENISE record when I do this (which has an EXACT match). So, my thoughts took me to the NULL values in T1.C2 and T2.C2 for this record and I thought that, perhaps, the NULL values aren't being recognized as being equal (as they are UNKNOWN). So I started digging around and found SET ANSI_NULLS OFF. I tried it but with no luck. Can you offer any insight on this one? What can I do to have NULL values recognized as being equal?
This is the result set that I would like to have returned in this example:
I have two select statements; one for open purchase orders, one for open customer orders. I would like to be able to combine the query based on i.item in the top statement joined with c.item from the bottom statement. The i.item is related to a specific c.item, but they do not have the same values. In this case I want to join based on.
p.item=i.item where 1001099548=1001099550 84162359=84198545 84532300=84532293 47547523=47547951 305545A3=87433653 87444977=87444975
left side coming from p.item = right side coming from c.item.
Here are my statements.
--#1 OPEN PO's SELECT p.item ,(p.qty_ordered-p.qty_received) as POQtyRemaining ,i.item ,i.qty_on_hand ,p.po_num
I have a case statement I need to refer to several times in my select statement and it's quite long. I would like to just refer to if by name. I tried to create a variable for the Case statement (see below) but I get an error that says, " Sub query returned more than 1 value. This is not permitted when the sub query follows =, !=, <, <= , >, >= or when the sub query is used as an expression."
USE GP05 GO
DECLARE @EmpID nvarchar (6)
SET @EmpID = (SELECT CASE WHEN NOT(dbo.BE010130.EMPID_I IS NULL) THEN dbo.BE010130.EMPID_I WHEN dbo.BE010130.EMPID_I IS NULL AND NOT(dbo.UPR00500.EMPLOYID IS NULL) AND dbo.UPR00600.EMPLOYID IS NULL THEN dbo.UPR00500.EMPLOYID WHEN dbo.BE010130.EMPID_I IS NULL AND dbo.UPR00500.EMPLOYID IS NULL AND NOT(dbo.UPR00600.EMPLOYID IS NULL) THEN dbo.UPR00600.EMPLOYID WHEN dbo.BE010130.EMPID_I IS NULL AND NOT(dbo.UPR00500.EMPLOYID IS NULL) AND NOT(dbo.UPR00600.EMPLOYID IS NULL) THEN dbo.UPR00500.EMPLOYID END FROM dbo.BE010130 FULL OUTER JOIN dbo.UPR00500 ON dbo.BE010130.EMPID_I = dbo.UPR00500.EMPLOYID AND dbo.BE010130.BENEFIT = dbo.UPR00500.DEDUCTON FULL OUTER JOIN dbo.UPR00600 ON dbo.BE010130.BENEFIT = dbo.UPR00600.BENEFIT AND dbo.BE010130.EMPID_I = dbo.UPR00600.EMPLOYID)
SELECT @EmpID
I can get around this by simply retyping the case statement when I refer to it in the where clause, but I would prefer not to do this. Can someone point a newbie in the right direction? I have 5 case statements in this query and it starts to look pretty ugly when you have to retype them multiple times.
I have created and deployed a report model by using BI development Studio. When trying to build a tabular report with Report Buider I have to use the "not equal" condition as a filter. The query does not give the expected results.
let's suppose the values for a filed I want to filter on are: 1,2,3,4,5
If i run the filter as: field NotEqual to 6 then the results are not correct
But if instead of using the NotEqual condition I use field equals to 1 or field equals to 2 or field equals to 3 or field equals to 4 or field equals to 5
Then it gives the correct results. Ok I now it sounds weird but anyone with similar experience using ther NotEqual condition in a filter?
This morning we had an issue with a simple join between 2 tables e.g. table1 and table2. Both tables have 1 column called 'fielda' with datatype varchar.
Let's assume the following values: Table1: hello Hello
Table2: hello
This statement joins the tables: select a.fielda from table1 a inner join table2 b on a.fielda = b.fielda
It returns: hello Hello
???????????????????????????????????????????????????????? What the $#@#$ Why doesn't it return 1 value: hello
Any ideas? The outcome is exactly what we want but I would expected to have to use the following: select a.fielda
from table1 a inner join table2 b on UPPER(a.fielda) = UPPER(b.fielda)
What if I wanted to return all EXACT matches? Then what? Change collation?
In database persons I have a table named persons_class1 as follows: person_id char(13) not null [primary key] name varchar(20) not null
In database cars I have a table named cars_blue as follows: car_id char(13) not null [primary key] model varchar(20) not null person_id char(13) not null
In my program the initial catalog is: cars and I´m executing the following SQL:
select cars_blue.model,persons.dbo.persons_class1.name from cars_blue,persons.dbo.persons_class1 where cars_blue.person_id=persons.dbo.persons_class1.person_id
and the following error occurs: Cannot resolve collation conflict for equal operation
I also tried like instead of = and the error is: Cannot resolve collation conflict for like operation
I want a search of name ='Jose' to find both Jose and José, but with
SELECT * FROM Users WHERE name LIKE '%Jose%'
i only get Jose, in "Inside SQL SQL" an article shows this:
If you want a search of name ='Jose' to find both Jose and José, you should choose accent insensitivity. Such a sort order defines all E-like characters as equal:
E=e=è=É=é=ê=ë
but how shut i do this ???
MSSQL 2000 SP 4 Server Collation: Latin1_General_CI_AS
In database persons I have a table named persons_class1 as follows: person_id char(13) not null [primary key] name varchar(20) not null
In database cars I have a table named cars_blue as follows: car_id char(13) not null [primary key] model varchar(20) not null person_id char(13) not null
In my program the initial catalog is: cars and I´m executing the following SQL:
select cars_blue.model,persons.dbo.persons_class1.name from cars_blue,persons.dbo.persons_class1 where cars_blue.person_id=persons.dbo.persons_class1.per son_id
and the following error occurs: Cannot resolve collation conflict for equal operation
I also tried like instead of = and the error is: Cannot resolve collation conflict for like operation
