Hi All
I am updating a local table based on inner join between local table
and remote table.
Update LocalTable
SET Field1 = B.Field1
FROM LinkedServer.dbname.dbo.RemoteTable B
INNER JOIN LocalTable A
ON B.Field2 = A.Field2
AND B.Field3 = A.Field3
This query takes 18 minutes to run.
I am hoping to speed up the process by writing in OPENQUERY syntax.
Thanks
RS
Does anyone know how to use the openquery() method with dynamic SQL? I've tried these two different approaches with no success. DECLARE @sql nvarchar(4000)SET @sql = 'select producer_id from producer where producer_id = ' 'A' ' ' select producer_id from openquery([sybtest], @sql) -------------------------------------------DECLARE @producer_id char(1)SET @producer_id = 'A' select producer_id from openquery([sybtest], 'select producer_id from producer where producer_id = ' ' ' + @producer_id + ' ' ' ' )
View 4 Replies View RelatedI have an openquery statement with a parameter embeded as a variable:
declare @product varchar(3)
set @product= 'ABC'
select * from openquery(SomeServer,'
SELECT Description, Size
FROM Products
WHERE
Group = ''XY'' AND
Code = ''' + @product + '''')
When I run it I get the following message:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '+'.
When I hard code the "Code" value, like so:
Code = ''ABC''')
...it works fine.
I am at a loss and would appreciate any help on this.
Thanks in advance
SQL Servant
Currently we are running SQL Server 7 with SP1 installed on an NT box. I need to update a field in a table in Oracle. I setup a linkedserver in SQL Server using the Microsoft OLE DB Provider for ODBC (MSDASQL) and didn't have any problems selecting data from the linked Oracle tables using OPENQUERY. For example: SELECT * FROM OPENQUERY(STATSDEV, "Select * from CR_EXPORT") This query works fine.
However, now I need to update a field in the CR_EXPORT table. So I have written the following query and I tried to run it:
UPDATE OPENQUERY(STATSDEV,
"SELECT * FROM CR_EXPORT WHERE REQUEST_NUMBER = 1")
SET STATUS = 2
I got the following error message:
Server: Msg 7352, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' supplied inconsistent metadata. The object '(user generated expression)' was missing expected column 'Bmk1000'.
The Oracle table has a unique index, so I think that the issue might be associated with the version of the OLE DB provider or the Service Pack, but I can not find any documentation to support my theory. Any help you could give would be VERY appreciated!
All,Can someone help me with the following SQL and help me write it in anOPENQUERY format. I am running the following code from a SQL Server 7box, trying to update a table in an Oracle Linked Server. The coderuns fine, except it takes almost an hour to complete. I know if I runvia OPENQUERY,I can get the same done in much less time.Some of the relevant information is as follows:ORACLE_HBCPRD04 is a linked Oracle Server.SITEADDRESS is a table in Oracle#SiteAddress_New is a table in SQL Server.UPDATE ORACLE_HBCPRD04...SITEADDRESSSETCUST_ADDR1 = CASE WHEN SiteAddress_New.CUST_ADDR1 = '' THEN NULLELSE SiteAddress_New.CUST_ADDR1 END,CUST_ADDR2 = CASE WHEN SiteAddress_New.CUST_ADDR2 = '' THEN NULLELSE SiteAddress_New.CUST_ADDR2 END ,CUST_ADDR3 = CASE WHEN SiteAddress_New.CUST_ADDR3 = '' THEN NULLELSE SiteAddress_New.CUST_ADDR3 END,CUST_ADDR4 = CASE WHEN SiteAddress_New.CUST_ADDR4 = '' THEN NULLELSE SiteAddress_New.CUST_ADDR4 END ,CTY_NM = CASE WHEN SiteAddress_New.CTY_NM = '' THEN NULL ELSESiteAddress_New.CTY_NM END,ST_ABBR = CASE WHEN SiteAddress_New.ST_ABBR = '' THEN NULL ELSESiteAddress_New.ST_ABBR END,POST_CD = CASE WHEN SiteAddress_New.POST_CD = '' THEN NULL ELSESiteAddress_New.POST_CD END,CNTY_NM = CASE WHEN SiteAddress_New.CNTY_NM = '' THEN NULL ELSESiteAddress_New.CNTY_NM END,CNTRY_NM = CASE WHEN SiteAddress_New.CNTRY_NM = '' THEN NULL ELSESiteAddress_New.CNTRY_NM END,ADDR_STAT = NULL ,LAST_UPDATE_DATE = SiteAddress_New.LAST_UPDATE_DATEFROMORACLE_HBCPRD04...SITEADDRESS SiteAddress INNER JOIN#SiteAddress_New SiteAddress_New ONSiteAddress.LEGACY_ADDR_ID = SiteAddress_New.LEGACY_ADDR_IDWHEREUPPER(SiteAddress_New.PROCESS_CODE) = 'U'Best Regards,addi
View 2 Replies View RelatedHello,
I have an application that uses a MS SQL 2005 database. When data is changed in certain tables, that data needs to be pushed to a MySQL box. I've added the MySQL server as a linked server in SQL 2k5 and I can delete and insert data with no problem, however when I try to update I get the following error (query included):
Code Snippet
with RemoteTable(r_AccountID, r_Name)
as (select AccountID, Name from openquery(RACS_TEST, 'select AccountID, Name from accounts'))
update RemoteTable
set r_Name = ex_Name
from Export_RACS_Accounts join remotetable
on r_AccountID = ex_AccountID
OLE DB provider "MSDASQL" for linked server "RACS_TEST" returned message "Row cannot be located for updating. Some values may have been changed since it was last read.".
Msg 7343, Level 16, State 4, Line 33
The OLE DB provider "MSDASQL" for linked server "RACS_TEST" could not UPDATE table "[MSDASQL]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.
What am I doing wrong ?
I need to update an Oracle table from SQL Server. I am trying to use Openquery Update statement. I need to pass a integer as a parameter. I will be updating a date field and a status field.
This is the gist of what I need to do in a stored procedure
DECLARE @ID1 INT,
@SQL1 VARCHAR(8000),
@STATUS VARCHAR(10),
@DATE DATETIME;
SET @ID1 = 350719;
SET @STATUS = 'COMPLETED';
SET @DATE = GETDATE();
SELECT @ID1;
SELECT @SQL1 = 'UPDATE OPENQUERY(NGDEV2_LINK2, ''select DM_IMPORT_STATUS, DM_IMPORT_DATE FROM NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID = ' + CAST(@ID1 as nvarchar(30)) + ''')'
SET DM_IMPORT__STATUS = @STATUS, DM_IMPORT_DATE = @DATE;
EXEC (@SQL1);
After running the following OpenQuery...
UPDATE OPENQUERY(LINKEDSERVER, 'SELECT START_ORDER_NO FROM "OECTLFIL" WHERE "FILE_KEY" = 1')
SET START_ORDER_NO = 0
----The START_ORDER_NO field contains a 48
SET START_ORDER_NO = 1~9
----The START_ORDER_NO field contains 49~57 respectively.
SET START_ORDER_NO = 10
---The START_ORDER_NO field contains 12337
SET START_ORDER_NO = 11
---The START_ORDER_NO field contains 12593
SET START_ORDER_NO = 12
---The START_ORDER_NO field contains 12849
incrementing by 256 as I increase the value passed...
ASCII 48-57 are the characters 0-9. The string '10' consists of the two bytes with the values 49 (0x31) and 48 (0x30). It is being viewed in reverse byte order as the value 0x3031 which equals 12337 (48*256 + 49).
The LinkedServer is Pervasive SQL 2000i using 'OLE DB Provider for ODBC'
The START_ORDER_NO field is a Numeric(8,0)
I'm thinking some kind of Unicode, or translation or code page issue, but I haven't had any luck yet.
I'm not sure how difficult this is, I don't think I'm a neophyte but I'm feeling like one...
I was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
View 7 Replies View RelatedHi, I have a simple problem that I need a quick solution for. I hope someone can help.I have a list of email addresses that contain many different email providers (hotmail, yahoo, gmail, etc)What I'm trying to do, it select all emails that are not from a select list of providers...Here is the code I have so far... (this isnt giving me what I want)SELECT EmailAddress as Email FROM TBL_EmailAddresses WHERE EmailAddress <> '%aim.com%' OR EmailAddress <> '%hotmail.com%'OR EmailAddress <> '%msn.com%'OR EmailAddress <> '%yahoo.com%'OR EmailAddress <> '%gmail.com%'OR EmailAddress <> '%aol.com%'I want to select all emails that are not of the above types. Thanks in advance.
View 5 Replies View RelatedI am querying a SQL Server 2000 server remotely and have couple of small syntax problems using the WHERE Clause in a Select statement
1. I wish to extract all records where the first 2 characters of a Varchar field (postcode) do not start with 2 specific characters (BT)
2. I wish to extract all records where a Char field is not blank (ie has at least 1 character in it)
Many thanks
Mike
I cannot work out what I need to do to fix the query attached. I am getting the error Line 105: Incorrect syntax near ')'.
Thank you for your helpCREATE PROCEDURE spMHCPall
(
@type int
)
AS
Select
fname, lname,
dtmenroll,
ysnenroll,
lngtype,
lngprimaryprovider ,
Type,
strreason,
provider,
lngenrollmentid,
encounterdate,
encounterdate2,
countencounter,
countlocus,
counttplan,
countmrpt,
counttplanCBT,
counttplanCBT16,
countgroupattenance,
dtmbaseline,
dtmenrollment,
satsurvey,
encountercount,
countcontact,
contactdate,
psymdconsults,
countQOLSurvey,
lngpatientid,
Case When
( Case When
(
Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01')
then IsNull(Date1,'1900-01-01')
Else IsNull(Date2,'1900-01-01')
End )>IsNull(Date3,'1900-01-01')
Then
(
Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01')
then IsNull(Date1,'1900-01-01')
Else IsNull(Date2,'1900-01-01')
End )
Else
IsNull(Date3,'1900-01-01')
End)>IsNull(Date4,'1900-01-01')
Then
( Case When
(
Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01')
then IsNull(Date1,'1900-01-01')
Else IsNull(Date2,'1900-01-01')
End )>IsNull(Date3,'1900-01-01')
Then
(
Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01')
then IsNull(Date1,'1900-01-01')
Else IsNull(Date2,'1900-01-01')
End )
Else
IsNull(Date3,'1900-01-01')
End)
Else
IsNull(Date4,'1900-01-01')
End MaxDate,
Case When COALESCE(Date1,Date2,Date3,Date4) > DATEADD(M,-1,CONVERT(CHAR(8),GETDATE(),112)) then 'false' When COALESCE(Date1,Date2,Date3,Date4) is null then 'false' else 'true' end ysncontact
From
( Select A.lngpatientid, ysnenroll,lngtype, lngprimaryprovider, strreason,dtmenroll, lngenrollmentid,
'Date2'=(Select max(dtmcontact) FROM tblMHCPcontact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type ),
'Date4'=(Select max(B.dtmenroll) FROM tblMHCPEnrollment B where A.lngpatientid=B.lngpatientid and B.lngtype = @type),
'Date3'=(Select max(visitdate) FROM tblMHCPEncounter B where A.lngpatientid=B.lngpatientid and A.lngtype = @type ),
'psymdconsults'=(Select count(visitdate) FROM tblMHCPEncounter B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and B.providerid = '1'),
'countlocus'=(Select case when count(dtmdate)= 0 then 'N' else 'Y' end FROM tblMHCPLOCUS B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmdate between dateadd(month, -6, getdate()) and getdate() ),
'counttplan'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPtreatmentplan B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmstart between dateadd(month, -6, getdate()) and getdate()),
'countmrpt'=(Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and dtmdate between dateadd(month, -1, getdate()) and getdate()),
'counttplanCBT'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and dtmintake between dateadd(month, -1, getdate()) and getdate()),
'counttplanCBT16'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and dtmintake between dateadd(month, +3, getdate()) and getdate()),
'countgroupattenance'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPGroupAttenance B where A.lngpatientid=B.lngpatientid and dtmgroup between dateadd(day, +7, getdate()) and getdate()),
'contactdate'=(Select max(dtmcontact) FROM tblMHCPContact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmcontact between dateadd(day, -7, getdate()) and getdate()),
'dtmbaseline'=(Select max(dtmintake) FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and ysnbaseline = 1),
'dtmenrollment'=(Select max(dtmenroll) FROM tblMHCPenrollment B where A.lngpatientid=B.lngpatientid and A.lngtype=b.lngtype and ysndisenroll is null),
'contactcount'=(Select case when count(dtmcontact)= 0 then 0 else 1 end FROM tblMHCPContact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmcontact between dateadd(day, -7, getdate()) and getdate()),
'countcontact'=(Select count(dtmcontact) FROM tblMHCPContact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmcontact between dateadd(day, -7, getdate()) and getdate()),
'satsurvey'=(Select case when count(dtmSurvey)= 0 then 'N' else 'Y' end FROM tblMHCPClientSat B where A.lngpatientid=B.lngpatientid and dtmSurvey between dateadd(day, -70, getdate()) and getdate()),
'encountercount'=(Select case when count(visitdate)= 0 then 0 else 1 end FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()),
'encounterdate'=(Select Max(visitdate) FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()),
'countencounter'=(Select count(visitdate)FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()),
'countSatSurvey'=(Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPSurveyQOL B where A.lngpatientid=B.lngpatientid and dtmdate between dateadd(month, -6, getdate()) and getdate()),
'countQOLSurvey'=(Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPSurveyQOL B where A.lngpatientid=B.lngpatientid and dtmdate between dateadd(month, -6, getdate()) and getdate()),
'Date1'=(Select max(dtmgroup) FROM tblMHCPGroupAttenance B where A.lngpatientid=B.lngpatientid and A.lngtype = @type),
'fname' = (Select strfname FROM tblPatient as P where A.lngpatientid=P.lngpatientid ) ,
'lname' = (Select strlname FROM tblPatient as P where A.lngpatientid=P.lngpatientid ) ,
'Type' = (Select strtype FROM tbllkpMHCPEnrollment as P where A.lngtype=P.lngtype ),
'Provider' = (Select fname + ' ' + lname as pName FROM tbllkpMHCPprovider as P where A.lngprimaryprovider=P.staffid)
FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()))
From tblMHCPEnrollment A where A.lngtype = @type and ysnDisEnroll IS NULL) AS lngpatientidDate
HiCan someone please tell me whats wrong with the last line of the querybelow. The first three lines work fine but when i add the fourth line i getan error message (see text at ERROR MESSAGE)sql_HTTermijnRecords = "select * from Orders where FaktuurGeprint ='J'" & _"AND dathergestuurd Is Not Null " & _"AND PerBankKas Is Null " & _"AND " & HTdatumMinAantalDagen & " > " & dathergestuurdThe last line is a comparison between two dates.ERROR MESSAGE============================================Run-time error 3075Syntax error (missing operator) in query expression 'FaktuurGeprint ='J' AND dathergestuurd Is Not Null AND PerBankKas Is Null AND22-11-2005 >'========= end error message==============================The first date (22-11-2005) is visible in the error message but the seconddate is missing.As you can see there is nothing after the > but there should be dateinformation from a DB-cell named dathergestuurd.I tried a lot of things but i keep getting the same message.What is wrong with the syntax of the last line ???T.i.a.RegardsTino WintershovenThe Netherlands
View 4 Replies View RelatedI have the following SQL Statement and I am trying to pass in a wildcard for the "Branch Parameter". The Branch Parameter is a numeric field.
I need (Branch = %) wildcard to select all the records. Can someone tell me the proper syntax for this?
SELECT EmployeeID, CountryCode, FName, LName, Branch, Title, Status, Required, Total, PercentageFROM dbo.vw_Compliance_Percentage_EmployeeWHERE (Branch = @branch)ORDER BY Branch
I receive the message failed to convert parameter value from string to an int32
Hi all,I am a newbie to asp and have been using VWD to make a database for an assignment but I am having big problems trying to extract some data to a datalist view. I intend to use this page to display all information of hotel rooms. I know its probably really obvious to some of you but its driving me mad!!! Any help would be greatly appreciated. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Dim ds As New DataSet() Dim sGetRooms As String = "SELECT RoomID, RoomType, " _ & "RoomName FROM Rooms2 " _ & "WHERE RoomType LIKE @RoomType " _ & "ORDER BY RoomType" Dim sGetroomsizeandprice As String = "SELECT ID, RoomSize, RoomPrice, @RoomType " _ & "FROM roomprices JOIN Rooms2 ON Rooms2.ID = roomprices.ID " _ & "WHERE RoomType LIKE @RoomType " _ & "ORDER BY RoomPrice" Dim sConnect As String = ConfigurationManager.ConnectionStrings("White Sand's Hotel - Dan MahilConnectionString").ConnectionString Using con As New OleDbConnection(sConnect) Dim da As New OleDbDataAdapter(sGetRooms, con) Dim param As New OleDbParameter("RoomType", OleDbType.VarChar, 10) param.Value = Request.QueryString("RoomType") & "%" da.SelectCommand.Parameters.Add(param) Try da.Fill(ds, "Rooms2") da.SelectCommand.CommandText = sGetroomsizeandprice da.Fill(ds, "roomprices") Catch ex As Exception Label4.Text = "ERROR: " & ex.Message Exit Sub End Try End Using Dim pkcol As DataColumn = ds.Tables("Room2").Columns("RoomID") Dim fkcol As DataColumn = ds.Tables("roomprices").Columns("ID") Dim dr As New DataRelation("MenuLink", pkcol, fkcol) ds.Relations.Add(dr) DataList1.DataSource = ds DataList1.DataMember = "Rooms2" DataList1.DataBind() End Sub
View 2 Replies View RelatedI am getting the following syntax error to my query below. This query is similar to many I currently use and I have not been able to track down the error. Can anyone spot the error?
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 43
Incorrect syntax near 'data'.
Select
e.account_number,
epi.Provider_Role = [1],
epi.Provider_Role = [2],
epi.Provider_Role = [3],
epi.Provider_Role = [4],
epi.Provider_Role = [5],
epi.Provider_Role = ,
epi.Provider_Role = [7],
epi.Provider_Role = ,
epi.Provider_Role = [9],
epi.Provider_Role = [10],
epi.Provider_Role = [11]
from (SELECT e.account_number,
row_number() over (partition by epi.PROVIDER_ROLE order by e.account_number asc) as rownum,
e.medrec_no,
e.account_number,
Isnull(ltrim(rtrim(pt.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(pt.patient_sname)), '')
AS SRM_PatientName,
CONVERT(int,pm.PatientAge),
left(e.admission_date,11) as Admit_Date,
left(e.episode_date,11) as Disch_Date,
(CASE WHEN DATEDIFF(DAY, e.admission_date,e.episode_date) = 0 Then 1
ELSE DATEDIFF(DAY, e.admission_date,e.episode_date) END) AS LOS,
pm.PrinOpPhys,
epi.PROVIDER_CODE,
epi.PROVIDER_ROLE,
pe.PERSON_NAME as physician_name
From srm.episodes e inner join
dbo.PtMstr pm on pm.accountnumber=e.account_number inner join
srm.ITEM_HEADER ih ON ih.ITEM_KEY = e.EPISODE_KEY INNER JOIN
srm.PATIENTS pt ON pt.PATIENT_KEY = ih.LOGICAL_PARENT_KEY inner join
srm.CDMAB_PROV_EPI epi on epi.episode_key=e.episode_key inner join
srm.providers p on p.provider_key = epi.provider_key inner join
srm.person_element pe on pe.item_key = p.provider_key
Where e.episode_date is not null and pm.AnyProc like '%4495%'
) data
PIVOT
(max(epi.Provider_Role) for rownumber
in ( [1], [2], [3], [4], [5], , [7], , [9], [10], [11] )) pvt
order by e.account_number
Thanks!
Hello experts!
I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using....
Code Snippet
WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime)
>= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR
datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore)
Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected.
Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter.
Any help is appreciated.
How do I write an update query to update a column in TabA with the information from other records in TabA?
View 2 Replies View RelatedI have a table called emp
1eno enamedesgsal
1aaamanager20000
2bbbexecutive15000
3ccchr25000
4dddceo45000
using single update statement , how can i update the salary based on the desg .i have to give hike if its manager sal =sal+(sal*0.10) .like this
Thanks & Regards
Zakeer
I am having a heck of a time getting an UPDATE statement to work. Can anyone point out what it is I'm doing wrong? Here is my statement......
strSQL = "UPDATE tbl-Pnumber_list SET Project_Title = 'success' WHERE ID = @IDParam"
Thanks!
Eugh
Hello, I'm new to the site, I've been trying to find help and i hope this is the right place.
In my MySQL database i have a table 'A' with a colum called 'Description'. Inside 'Description' there is some text and a link to a page. I have over 2500 Description fields and all have the same link inside but i need to change that link to a new one and i dont know how to update the description column with the new link. I know how to change just everything in it but its like this:
text text text text link
so how do i just change the link in the description column.
Anyone?
thanks in advance.
Below is my table structure:
CREATE TABLE [reports].[MDC_DRG](
[InsDrgMdc] [nvarchar](50) NULL,
[InsDrgMdcDesc] [nvarchar](50) NULL,
[MDC] [nvarchar](100) NULL,
[Drg] [nvarchar](50) NULL,
[DrgDesc] [nvarchar](255) NULL,
[AnyDx] [nvarchar](100) NULL,
[Cases] [decimal](18, 0) NULL,
[IntOOS] [int] NOT NULL,
[ChronicOther] [nvarchar](50) NULL
) ON [PRIMARY]
Here is a sample select from the AnyDx field:
AnyDx
,85225,85225,5304 ,5181 ,80501,E8190,3485,
,1983 ,1983 ,49121,1625 ,34550,79381,78079,30001,V1,
,2396 ,2396 ,78039,311,
,33390,33390,31401,49390,
,3410,3410,2768,3051,72293,V146,V4579,V1041,
,43491,43491,25000,4019 ,V1581,
,43491,43491,25000,4919 ,3569 ,4019 ,72252,78650,71,
,43491,43491,2768,30590,34290,4019,V146,7845,2724,2,
,43491,43491,34290,40290,25000,7843,7872,41401,4278,
,43491,43491,412 ,25000,41401,4019 ,2724 ,29680,V1,
I need to issue an update statement to the ChronicOther field. If the range below is in the AnyDx field, then I want the ChronicOther field to say "Chronic", if not then I want it to say "Other".
where AnyDx between '25000' and '25091'
or AnyDx between '41400' and '41406'
or AnyDx between '42800' and '42890'
or AnyDx between '40100' and '40291'
or AnyDx between '34200' and '34292'
or AnyDx between '14000' and '19910'
or AnyDx between '49600' and '49600'
or AnyDx between '27240' and '27240')
My problem is what is the best way to look for this range (below) in the AnyDx field without multiple like statements?
example:
Update reports.MDC_DRG
SET ChronicOther = CASE When AnyDx like ('%,25000,%') Then 'Chronic' Else 'Other'
etc., etc.....
hi,
I have my database stored in the sqlserver 2005. Using the table name i am retrieving the table and it is displayed to the user in the form of datagridview.I am allowing the user to modify the contents of the table, including the headers. Is it possible for me to update the table straightway rather than giving a sql update command for each and every row of the table .
Pls reply asap....
-Sweety
Per my client, when it creates the qualifier for 'HCPS-DEN"' it needs to create the qualifier as 'AD' not 'HC' . Currently, with the present update script its inserting 'HC' on all. I need it to say when the 'HCPS-DEN' is used, insert the 'AD' not 'HC'. Everything else should be the HC.
I tried using a CASE statement:
CASE WHEN Description = 'HCPC - DEN' THEN 'AD' ELSE 'HC' END
however I get this back:
Msg 207, Level 16, State 1, Line 10
Invalid column name 'Description'.
I was trying to plug this part in where HC is being entered. I think its missing an obvious Join or maybe there is a better way to write this. Any help is greatly appreciated!!
declare @qualid int
if 1=1
BEGIN
if not exists (select * from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN')
begin
declare @listorder int
select @listorder=max(listorder)+1 from medlists where tablename='ProcedureCodeQualifier'
insert into medlists
select 'ProcedureCodeQualifier',NULL,'HC','HCPC - DEN',@listorder,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@listorder,getdate(),'CYSScript',getdate(),dbo.GetLogonId()
update medlists set dotid=medlistsid where medlistsid=scope_identity()
end
select @qualid=medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN'
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
ELSE
BEGIN
select @qualid =(select top 1 medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description like 'Health Care Financing Administration Common Procedural Coding System (HCPCS) Codes%')
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
Hello,
I am trying to update a table based on values that I have imported into a temporary table. Basically I have a list of lab codes (EMR_temp_labtest_configupdate) and each lab has a zseg code tied to it. The definitions for zseg code are in a separate table called (EMR_zseg_code_descriptions)
I need to update the lab_test_add_conf to add in each lab code that does not have any configuration information (not exists in lab_test_add_conf) based on the zsegcode.
For example a zsegcode (defined in the emr_zseg_code_descriptions table) is ZBL and the lab code 003277 fits into the zseg category according to the temp table. For each lab code that first into the ZBL category a row needs to be inserted for
Example of table data:
emr_temp_labtest_config
labtestcode, zsegcode
003277, ZBL
emr_zseg_code_descriptions
zsegcode, valuecode
ZBL, PATRAC
ZBL, HERITG
I want to look at the data in the temp table and determine which category it is in and then insert into the lab_test_add_conf table a row for each lab test each zseg table value code that exists.
My Final Goal:
lab_test_add_conf:
lab test code, valuecode
003277, PATRAC
003277, HERITG
I know I need to do an update statement but I am not sure how to set up the SET statement or if there is anythign that I need to take into consideration. Here is my code so far.....any thoughts?
select a.labtestcode
from EMR_temp_labtest_configupdate a
Where Not Exists
(Select *
From lab_test_add_conf b
where a.labtestcode = b.labtest_key)
update table lab_test_add_conf
select a.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,
b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
Per my client, when it creates the qualifier for 'HCPS-DEN"' it needs to create the qualifier as 'AD' not 'HC'. Currently, with the present update script its inserting 'HC' on all. I need it to say when the 'HCPS-DEN' is used, insert the 'AD' not 'HC'. Everything else should be the HC.
I tried using a CASE statement:
CASE WHEN Description = 'HCPC - DEN' THEN 'AD' ELSE 'HC' END
however I get this back:
Msg 207, Level 16, State 1, Line 10
Invalid column name 'Description'.
I was trying to plug this part in where HC is being entered. I think its missing an obvious Join or maybe there is a better way to write this. Description is being used after the first BEGIN. I know it needs to also go into my insert somehow, but I'm not sure how or if there is an easier way to do this. Any help is greatly appreciated!!
declare @qualid int
if 1=1
BEGIN
if not exists (select * from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN')
begin
declare @listorder int
select @listorder=max(listorder)+1 from medlists where tablename='ProcedureCodeQualifier'
insert into medlists
select 'ProcedureCodeQualifier',NULL,'HC','HCPC - DEN',@listorder,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@listorder,getdate(),'CYSScript',getdate(),dbo.GetLogonId()
update medlists set dotid=medlistsid where medlistsid=scope_identity()
end
select @qualid=medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN'
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
ELSE
BEGIN
select @qualid =(select top 1 medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description like 'Health Care Financing Administration Common Procedural Coding System (HCPCS) Codes%')
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
Hi guys,
I am new to SQL server, but I need to develop a "big" application with it. Here's my application requirements:
1)Create a table, say TableA, in SQL server 7 or 2000 database,say DB1, based on another Oracle database,say DB2, on a remote server. Update TableA based on DB2 every day in some specific time.
2) If new records are inserted into the TableA,some other tables needed to update in DB1. At the same time, an e-mail will send to three receipts.
I have no idea of which function or method I can use to fulfill the requirements. Can any experts here give me some advice, detail explanation would be grateful.
I appreciate any help you provide.
Dogli
Hi all,
I have a situation where my Visual C# application presents a number of fields. In order to update a student object, I wish to call a stored proc. 1 or more fields can be updated... And If one is left null, then I don't want to update it, but instead I want to keep the old value.
I am really wondering if I am approaching this the right way. The following stored proc does what I want it to do, however I'm thinking there may be a faster way...
Here it is:
-- Update a student, by ID.
DROP PROCEDURE p_UpdateStudent
CREATE PROCEDURE p_UpdateStudent
@ID INT,
@NewFName VARCHAR(25),
@NewOName VARCHAR(25),
@NewLName VARCHAR(25),
@NewDOB DATETIME,
@NewENumber VARCHAR(10),
@NewContactAID INT,
@NewContactBID INT
AS
BEGIN
SET NOCOUNT ON;
-- DECLARE THE OLD VALUES
DECLARE @FName AS VARCHAR(25)
DECLARE @OName AS VARCHAR(25)
DECLARE @LName AS VARCHAR(25)
DECLARE @DOB AS DATETIME
DECLARE @ENumber AS VARCHAR(10)
DECLARE @ContactAID AS INT
DECLARE @ContactBID AS INT
-- Get all of the old values
SELECT @FName = FName FROM TBL_Student WHERE ID = 10000
SELECT @OName = OName FROM TBL_Student WHERE ID = 10000
SELECT @LName = LName FROM TBL_Student WHERE ID = 10000
SELECT @DOB = DOB FROM TBL_Student WHERE ID = 10000
SELECT @ENumber = ENumber FROM TBL_Student WHERE ID = 10000
SELECT @ContactAID = ContactAID FROM TBL_Student WHERE ID = 10000
SELECT @ContactBID = ContactBID FROM TBL_Student WHERE ID = 10000
-- USE ISNULL to set all of the new parameters to the provided values only if they are not null
-- Keep the old ones otherwise.
SET @NewFName = ISNULL(@NewFName, @FName)
SET @NewOName = ISNULL(@NewOName, @OName)
SET @NewLName = ISNULL(@NewLName, @LName)
SET @NewDOB = ISNULL(@NewDOB, @DOB)
SET @NewENumber = ISNULL(@NewENumber, @ENumber)
SET @NewContactAID = ISNULL(@NewContactAID, @ContactAID)
SET @NewContactBID = ISNULL(@NewContactBID, @ContactBID)
-- Do the update
UPDATE TBL_Student
SET FName = @NewFName,
OName = @NewOName,
LName = @NewLName,
DOB = @NewDOB,
ENumber = @NewENumber,
ContactAID = @NewContactAID,
ContactBID = @NewContactBID
WHERE
ID = @ID
END
GO
So yeah it works. But As you can see I wish to keep an old copy of the values to perform checks pre update....
Is there any faster way, or am I on the right track? I need a pro's advice :) (before i write all of my procs!!)
Thanks all.
Chris
Hi (again)
I know that it's very simple question but since I know that many gurus are here, I want to ask abt it.
Ssql = "Update Holder set PID='temp_PID',CMSN=sMSN,CSN=sMSN1 where HID= " & temp_HID
when i run that sql from VB, it gives error as INVALID COLUMN name(sMSN/sMSN1).., it is for both sMSN and sMSN1 . But both of these sMSN and sMSN1 are numeric in the table. so I think I don't need for quotes.
pls tell me what is the correct syntax for it?
Hello
I am trying to update a column containing URL's and include the "www." which had previously been omitted on many URL's in the column. But I get an error when trying to UPDATE
I have tried:
UPDATE table_nameSET URL = http://www.a2zdom.com/*WHERE URL = http://a2zdom.com/*
I have tried and left out the http: and also the /* but nothing works. Is this type of update not possible?
Thanks
string cmdTxt = "Update penberry_SubjectName set SubjectLeaderId IN (
SELECT userid FROM aspnet_users WHERE username = @subjectLeaderName)
where SubjectCode = @subjectCode";
SqlConnection sqlconn = new SqlConnection(sqlConnStr);
SqlCommand sqlCmd = new SqlCommand(cmdTxt, sqlconn);
sqlCmd.Parameters.AddWithValue("@subjectLeaderName", subjectLeaderName);
sqlCmd.Parameters.AddWithValue("@subjectCode", subjectCode);
sqlconn.Open();
sqlCmd.ExecuteNonQuery(); hi guys i got this error from my program.Can anyone help me out?
Hi here's a bit of code. What am I doing wrong here? Visual Studio isn't even accepting the Set word on line 56. It deletes it everytime. What am I doing wrong here? Why is Visual studio putting the parenthese around the table name in 55? I generated an update query for my Websitetableadapter. Here it is:
UPDATE [tblWebSite] SET [Rating] = @Rating, WHERE (([WebSiteID] = @Original_WebSiteID))
How do I use this to update the Rating column after I've done my calculation below?
1 Imports RatingsTableAdapters2 3 4 Partial Class admin_ratings5 Inherits System.Web.UI.Page6 7 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load8 Dim I As Integer = 09 Dim J As Integer = 010 Dim Rating As Integer11 Dim Rate As Decimal12 Dim tblwebsiteAdapter As New tblWebSiteTableAdapter13 Dim tblWebsite As ratings.tblWebSiteDataTable14 tblWebsite = tblwebsiteAdapter.GetData()15 For Each tblwebsiteRow As ratings.tblWebSiteRow In tblWebsite16 Rate = 017 Dim tblLinkAdapter As New tblLinkTableAdapter18 Dim tblLink As ratings.tblLinkDataTable19 Dim tblLinkTot As ratings.tblLinkDataTable20 tblLink = tblLinkAdapter.GetSuccessfulExchanges(tblwebsiteRow.WebSiteID)21 tblLinkTot = tblLinkAdapter.GetTotalLinks(tblwebsiteRow.WebSiteID)22 For Each tbllinkRow As ratings.tblLinkRow In tblLink23 If tbllinkRow.LinkID < 1 Then24 I = 0.125 Else : I = I + 126 End If27 Next28 If I <> 0 Then29 For Each tbllinktotrow As ratings.tblLinkRow In tblLinkTot30 If tbllinktotrow.LinkID < 1 Then31 J = 0.132 Else : J = J + 133 End If34 Next35 End If36 If I <> 0 And J <> 0 Then37 38 Rate = I / J39 If Rate <= 0.3 Then40 Rate = 041 End If42 If Rate <= 0.5 Then43 Rate = 144 End If45 If Rate <= 0.65 Then46 Rate = 247 End If48 If Rate <= 0.75 Then49 Rate = 350 End If51 End If52 53 Response.Write(tblwebsiteRow.WebSiteID & " " & tblwebsiteRow.SiteURL & " Rating: " & Rate & "54 I = 055 J = 056 Update(tblWebsite)57 Rating = Rate58 where(tblwebsiteRow.WebSiteID <> 0)59 Next60 End Sub61 End Class
Hi All,The following code runs without error but does not update the database. Therefore I must be missing something.I am sure that this a simple task but as newbie to asp.net its got me stumpted. Protected Sub updatePOInfo_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles updatePOInfo.Updating Dim quantity As Integer Dim weight As Integer Dim packed As Integer quantity = CInt(bagsOnPallet.Text) weight = CInt(lbsInBags.SelectedValue) packed = weight * quantity e.Command.Parameters("@packedLbs").Value += packed e.Command.Parameters("@AvailableLbs").Value -= packed End Sub
View 4 Replies View Related