Trying To Select Info With If Exists Statement With No Avail
Jan 6, 2004
Im trying to select rows on the following criteria
My app has a user that can have x jobs, each job has related entries which are marked has unread or read. I need to return any jobs that contain any unread entries also I need to return other jobs that have no entries marked as read.
I am able to return the following:
Comment UnReadMy Job 164
Comment UnReadFor Cam 166
Comment ReadThe Job 157
Comment ReadThird 159
Comment ReadMy Job 164
On Site Visit ReadMy newest job for log test 167
but as you can see jobid 164 appears in both groups and I need it to be one or the other
I tried using the if exists statement and was only able to return one group either unread or read, not both. I've tried everything but I'm new and I figure that theres got to be a more elegant way. Heres my sql:
CREATE PROCEDURE spGetJobsByUnreadAndReadByUserID
@UserID INT
AS
BEGIN
IF EXISTS(SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.
Store Type Sales 11 A 1000 23 A 1980 23 B 50 5 B 560
I want to filter the store in "where clause" where
1)- if the store has type A and B, then assign only A 2)- if the store has type A associated with it then assign A 3)- if the store has type B associated with it, then assign B.
Select Store, sum(sales), Type from table1 where (TYPE]= (case when [TYPE] in ('A','B') then 'A' when [TYPE]='A' then 'A' else 'B'end)) GROUP BY [store], [TYPE]
The above statement is not working for when store has only Type B associated with it.
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'm trying to gather some user statistics based on 3 conditions. First I want to check if the referring querystring is already in the database. If not insert it into the db. Second, if the querystring is already in the database, then check if the ip-address of the user is already in the database. If it is, then check if the ip address was inserted today. If not, update the "refCountIn" field with +1. The problem lies in the third condition where we check if the ip-address was inserted today and if false, update the "refCountIn" field with +1 and if true, do nothing.Below is the code I have until now: 1 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 2 3 ' *** Declare the variables 4 Dim getStatCmd As String 5 Dim updStatCmd As String 6 7 Dim myRef As String = Request.QueryString("ref") 8 Dim myQueryString As String = Request.ServerVariables("QUERY_STRING").Replace("ref=", "") 9 Dim myRemoteAddr As String = Request.ServerVariables("REMOTE_ADDR") 10 Dim myHttpReferer As String = Request.ServerVariables("HTTP_REFERER") 11 12 Dim dtNow As Date = DateTime.Now 13 Dim dtToday As Date = DateTime.Today 14 15 ' *** Conditional INSERT command 16 getStatCmd = _ 17 "IF EXISTS(SELECT 'True' FROM tblReferers WHERE robotName = '" & myQueryString & "' AND refIpAddress = '" & myRemoteAddr & "' AND refTime = '" & dtToday & "') " & _ 18 "BEGIN " & _ 19 "SELECT 'This clickin has already been recorded!'" & _ 20 "END ELSE BEGIN " & _ 21 "SELECT 'Clickin recorded' " & _ 22 "INSERT INTO tblReferers(robotName, refIpAddress, refReferer, refTime) " & _ 23 "VALUES(" & _ 24 "'" + myQueryString + "'," & _ 25 "'" + myRemoteAddr + "'," & _ 26 "'" + myHttpReferer + "'," & _ 27 "'" + dtToday + "')" & _ 28 "END " 29 30 31 ' *** Conditional UPDATE command 32 updStatCmd = _ 33 "IF EXISTS(SELECT 'True' FROM tblReferers WHERE robotName = '" & myQueryString & "' AND refIpAddress = '" & myRemoteAddr & "' AND refTime <> '" & dtToday & "') " & _ 34 "UPDATE tblReferers " & _ 35 "SET refCountIn = refCountIn + 1, refTime = '" & dtNow & "' " & _ 36 "WHERE refIpAddress = '" & myRemoteAddr & "' AND robotName = '" & myRef & "'" 37 38 Dim insConnCmd As New SqlCommand(getStatCmd, New SqlConnection(connSD)) 39 Dim updConnCmd As New SqlCommand(updStatCmd, New SqlConnection(connSD)) 40 41 insConnCmd.Connection.Open() 42 insConnCmd.ExecuteNonQuery() 43 insConnCmd.Connection.Close() 44 45 updConnCmd.Connection.Open() 46 updConnCmd.ExecuteNonQuery() 47 updConnCmd.Connection.Close() 48 49 End Sub Anyone with an idea on how to solve this one? I think I need to write a subquery for the third condition, but I don't have a clue on how to handle this.Thanks in advance for your help!
Ok I have a table called allstocks, with a primary key of ticker, I also have a todaysstocks table with a primary key of ticker.
Todaysstock table updates the allstocks table with this statement,
insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under]) select exchange,[todays date],ticker,[opened date], [closed date],[over/under] from todaysstocks
this works fine, unless the ticker already exists. How do I write the statement, that if the ticker already exists, then update the rest of the fields with the new info, or delete the row and recreate it?
I would like to set a variable within my if exists statement, however SQL is throwing and error stating:
Incorrect syntax near '='.
If I remove the if exists, the query runs fine. Is there a reason why this is not working the way I have it and what suggestions can I use to accomplish what I am trying to do, which is store the ID into the permissionID variable
Here is my code block:
Code Snippet
declare @permissionID int;
if exists(select @permissionID = Id from Permission
Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?
something like:
SELECT TABLE_NAME ,HAS_PRIMARY_KEY ,KEY_HAS_IDENTITY_SPECIFICATION FROM ...this is where you come in ORDER BY TABLE_NAME
Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?
something like:
SELECT
TABLE_NAME ,HAS_PRIMARY_KEY ,KEY_HAS_IDENTITY_SPECIFICATION FROM
I'm trying to create a batch sql script which first alters some existing tables via the ALTER TABLE command, I then want to alter some existing stored procedures via the ALTER PROCEDURE command within the same batch. I have found that I can encompas the alter table scripts within a conditional IF EXISTS (Begin/End) but not the alter procesdure scripts. I have looked in reference material and have found nothing to suggest this type of operation is not possible. Is this possible? Is this a know bug fixed by a service pack?
I'm using MSSQL and PHP. I've got the following sql statement:
$msquery = IF NOT EXISTS (SELECT SerienNr FROM tbl_Auftrag a WHERE a.SerienNr='PC8') INSERT INTO tbl_Auftrag (BMS_AuftragsNr, SerienNr, AuftraggNr, Zieltermin, Kd_Name) VALUES ('455476567','PC8','1','2006-3-2','Fritz')
The Statement itself works fine, but i've got a problem getting a return value whether the insert has succeed, or not. :confused: mssql_query() always returns true if there occured no error in the statement. But i need to know if the insert procedded or not. I tried:
IF EXISTS(Select ApplicationID from Application Where Application = '&_') Insert Into PCApp(ApplicationID, SystemNetName) Values( , $HoH->{Host}{SystemNetName})
I am not sure what to put in the blank within the Values parenthesis. I need to obtain the ApplicationID that is checked in the IF EXISTS section. But I cannot put a select statement into the Values() section.
When I created an SP in Enterprise Manager, I didn't manually type in the existence check at the inception, a la "if exists (select * from sysobjects...)". I just started with the CREATE PROC AS statement.
I noticed that if I generate a SQL script for the SP, SQL2000 automatically generates the existence check statement.
My question is, can I assume that when the SP is actually executed, SQL2000 does the exists check on its own? I EXEC'd the proc in Query Analyzer with no errors.
I just want to make sure that I don't need to enter the exists statement if it's already being done behind the scenes.
In MS Access I can do in one SQL statement a update if exists else ainsert.Assuming my source staging table is called - SOURCE and my targettable is called - DEST and both of them have the same structure asfollowsKeycolumns==========MaterialCustomerYearNonKeyColumns=============SalesIn Access I can do a update if the record exists else do a insert inone update SQL statement as follows:UPDATE DEST SET DEST.SALES = SOURCE.SALESfrom DEST RIGHT OUTER JOIN SOURCEON (DEST.MATERIAL = SOURCE.MATERIAL ANDDEST.CUSTOMER = SOURCE.CUSTOMER ANDDEST.YEAR = SOURCE.YEAR)This query will add a record in SOURCE into DEST if that record doesnot exist in DEST else it does a update. This query however does notwork on SQL 2000Am I missing something please share your views how I can do this inSQL 2000.ThanksKaren
I have to generate a report on the fly. The display fields, parameters and sort conditions would be user specified at run time in a ASP.NET web form. There will be a superset of the display, filter and sort fields out of which the user cans select one or more.
From the web form, i am taking these three parts as three strings and sending them as parameters to a Stored Procedure. The Stored Procedure will read each string, and identify what are the individual fields and generates the result accordingly.
So here my requirement is that Reporting Services must read the Stored Procedure, create a dataset and even create the User Interface all at run-time as we do not know what fields are displayed at design time. The Headers for each field come from the Stored Procedure. I have to show the report based on what are the fields in the Stored procedure at that instance of time.
Greetings!I have the 3 raw data tables below, and would like to extract somedata. My current query is really cumbersome, does anyone know a betterway to get all the info (attach the right state to the lowest zip code)in one step? Thanks a lot! Here are the details.- Ideal Output, for each person , keep(1) person_id (From Table A)(2) The earliest open_date of accounts starting with 2 (From Table A)(3) ssn (From Table B)(4) Zip and state info (From Table C), but only keep the lowest zip andstate.-Table A -person_idaccountopen_date10001220000015/15/200310001220000026/20/200410001300000012/2/200210002220000038/12/200410002220000049/15/200410002300000022/16/2005-Table B -person_idzip state1000111111AA1000122222CC1000233333BB--Table C -person_idssn100011234567100022345678-Ideal Output-person_idmin_openssnzipstate100015/15/2003123456711111AA100028/12/2004234567833333BBHere is what I did:Select a.person_id, min(a.open_date), b.ssn, min(c.zip) as zip,cast (0 as varchar) as stateInto output[color=blue]>From TableA as a[/color]Join TableB as bon a.person_id=b.person_idJoin TableC as con a.person_id=c.person_idWhere a.account like '2%'Group by a.person_id, b.ssnOrder by a.person_idUpdate outputSet output.state=b.state[color=blue]>From output as a[/color]Join TableC as bon a.person_id=b.person_idand a.zip=b.zip
I am using SQL2014 with HA/AG groups and was adding an extra listener when I used the incorrect IP address. i had thought the 'remove' button would work, but it does not. Also I tried deleting it manually:
delete from sys.availability_group_listener_ip_addresses where listener_id = '26d37cdb-5a6f-43a0-ab0c-f4343eeac0f2'
delete from sys.availability_group_listeners where group_id = '6B060505-CDDC-44B6-A9AD-38FC6AD4A7AA'
Ad hoc updates to system catalogs are not allowed.How can I remove the invalid IP?
I'm trying to write a script that would only update a column if it exists.
This is what I tried first:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Enrollment' AND COLUMN_NAME = 'nosuchfield') BEGIN UPDATE dbo.Enrollment SET nosuchfield='666' END
And got the following error:
Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'nosuchfield'.
I'm curious why MS-SQL would do syntax checking in this case. I've used this type of check with ALTER TABLE ADD COLUMN commands before and it worked perfectly fine.
The only way I can think of to get around this is with:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Enrollment' AND COLUMN_NAME = 'nosuchfield') BEGIN declare @sql nvarchar(100) SET @sql = N'UPDATE dbo.Enrollment SET nosuchfield=''666''' execute sp_executesql @sql END
which looks a bit awkward. Is there a better way to accomplish this?
I have a table that has the following fields:tblECHECKID (autonumber identity column)PTIDBatchnumPageDataPointDPValueThere are many other tables on which I run a script that tests to makesure the data meets certain entry requirements (I don't want to putrestrictions on the forms because the data entry personel have toenter what is on the survey).Anything that fails is copied to the tblECHECK table. All the datacopied into the table are identified by a batchnumer (batchnum).I want to write a script that will look at all the records in thetblECHECK table, and delete any duplicates (the batch numbers are theonly fields that would be different).I have tried the following with no success (it returns 0 records):Select *From tblECHECKWhere batchnum ='batch8' and not exists (select * from tblECHECK wherebatchnum = 'batch7')Any help would be appreciated.
IF( NOT( EXISTS( SELECT 1 FROM updatetable WHERE ScriptName = 'MY_Scripts' ))) RAISERROR('script_dateupdates is required prior to running this script.', 30,1);
This is the Exists statment on the top. I was trying to find out what does it read. Can someone reat that for me.
Also why Not is before the brackets why it is not like If NOT EXISTS (Select....... ) acutally this is my concern to find out.
Dallas: A 2 node Windows 2008 Cluster running SQL 2012 ENT cluster Wash: A 2 node Windows 2008 Cluster running SQL 2012 ENT cluster
and I'm mirroring (synchronous, no witness) a database from Dallas to Wash.Crappy set up. I know.Now customer wants to have the database mirror to another server in Dallas.What are the high level steps to transform my two clusters to use Availability Groups and Always On?Do I need to basically start over and build a new environment?or can I transform my two disconnected cluster?
I have a Select statement that was working just fine: string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName) SELECT Silo_ID,'" + Keyword + "', '" + strUsername + "' FROM SiloNames WHERE Silo_Name = '" + Uri + "'"; the PM now wants to capture the internal User, which I have done but I am having real problems with the syntax if the internal User exists:
string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName, IsInternal) SELECT Silo_ID,'" + Keyword + "', '" + strUserName + "', if EXISTS(SELECT * FROM InternalUsersList WHERE Alias = '" + arrResult[1] + "') 1 ELSE 0 FROM SiloNames WHERE Silo_Name = '" + Uri + "'"; I am completely lost here.
I tried this:IF Exists (Select 1 from TESTA..POP10100 where PONumber=@PONumber) and(Select 1 from TESTA..POP30300 where POPType<>2 andPOPRCTNM=@ReceiptNo)BEGINENDIt says Incorrect Syntax error? I tried to cover the whole thing withan extra parantheses. It wont work eitherWhat am i doing wrongThanks, Girish
I am trying to use the following statement/query to delete any rows in tblcntslist table where tblcntslist.Company2 = tblCompany.Name.
Basically I am trying to look in the tblCompany table, look at the 'name' column, see if there are any entries in there that match entries in the tblcntslist.Company2 column. If there are matches, I want to delete them from the tblcntslist table.
I already "massaged" both table columns and removed all special characters, spaces, and other meaningless info that could result in incorrect matching. This way it is a match based on pure letter/number strings. I even set all as UCase in both table columns.
Code:
Delete from tblcntslist where exists (select * from tblCompany INNER JOIN tblCompany ON tblCompany.Name = tblcntslist.Company2 where tblCompany.Name = tblcntslist.Company2)
I am getting the following error: Server: Msg 1013, Level 15, State 1, Line 4 Tables or functions 'tblCompany' and 'tblCompany' have the same exposed names. Use correlation names to distinguish them.
I am trying to determine the existence of at least one row in my Detail table using EXISTS in my SELECT list from my Main table.SELECT M.ID,EXISTS(SELECT 1 FROM Detail D WHERE D.ID = M.ID) as HasDataFROM Main MCan this be done this way?I was hoping that using EXISTS would find a row and move on thus increasing performance.
Hopefully someone can help me with what I'm sure is a very simple question (new to the XML thing). I receive an XML file from "someplace" that I need to parse out using the XML Source in SSIS. I have SSIS generate me an XSD document, as one isn't provided for me. However, after I do this, SSIS does not show any available external columns to pull data from- the "Columns" section of the source is just blank. I'm pretty sure this has to do with a syntax error in either the XML file that is being provided to me, or the XSD doc that SSIS is generating. Below are both (obviously with data dummied up). Can someone take a look and let me what needs to be changed in either file to get this up and running? I'm looking to grab the AccountNumber, RecordNumber, ProcessedDate, Status, and StatusMessage elements.
I have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
Hi, When i try to save my stored procedure.. i am getting the above error and this is my sproc 1 INSERT INTO Statement..ClientSources 2 ( 3 ClientId, 4 ClientSourceId, 5 SourceName 6 ) 7 Select Distinct 8 @ClientId, 9 SOURCE_NUM, 10 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME 11 WHEN 2 Then SRC2NAME 12 WHEN 3 THEN SRC3NAME 13 WHEN 4 THEN SRC4NAME 14 WHEN 5 THEN SRC5NAME 15 WHEN 6 THEN SRC6NAME 16 WHEN 7 THEN SRC7NAME 17 WHEN 8 THEN SRC8NAME 18 WHEN 9 THEN SRC9NAME 19 WHEN 10 THEN SRC10NAME 20 WHEN 11 THEN SRC11NAME 21 WHEN 12 THEN SRC12NAME 22 WHEN 13 THEN SRC13NAME 23 WHEN 14 THEN SRC14NAME 24 WHEN 15 THEN SRC15NAME 25 END 26 FROM 27 PlanDBF p 28 Where 29 p.PLAN_NUM = s.PLAN_NUM 30 ) as SourceName 31 FROM 32 SourceDBF s 33 Where 34 SOURCE_NUM NOT IN ( 35 SELECT DISTINCT 36 ClientSourceId 37 --SourceName 38 FROM 39 Statement..ClientSources 40 Where 41 ClientId = @ClientId 42 )
I am getting the error in Line number 35 .. the inserts works fine... and if use * instead of the field name or use more than 1 field name i get this error Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Any help will be appreciated. Regards Karen
I am getting error [[Msg 116, Level 16, State 1, Line 7 .Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.]] for the below script.
I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know. What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in. This is the relationship of the data. I have at the highest group, an industry code, then a customer, then the part and then the fiscal year. What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!
industry Customer Year OCT NOV DEC 001 - Signposts M12345 Part 1 2006 5 6 2 2007 0 3 1
