Converting SQL Server Transact-SQL To DB2
May 5, 2008
I am trying to convert the following working SQL Server query to query a DB2 database. I am getting the following error: An ON clause associated with a JOIN operator or in a MERGE statement is not valid. SQLSTATE=42972. Can anyone help me convert it? Thanks for your help!
SELECT a.clmssn,
a.cossn,
a.clm_seq_num,
clmchrs.chrs_seq_num
FROM
(SELECT a.clmssn,
a.cossn,
Max(a.clm_seq_num) as clm_seq_num,
Max(b.chrs_seq_num) as chrs_seq_num
FROM mt16ic_Claim a
LEFT OUTER JOIN mt16ic_clmchrs b
ON a.clmssn = b.clmssn
AND a.cossn = b.cossn
AND a.clm_seq_num = b.clm_seq_num
Group By a.clmssn, a.cossn
) a
left outer join mt16ic_clmchrs clmchrs
ON a.clmssn = clmchrs.clmssn
AND a.cossn = clmchrs.cossn
AND a.clm_seq_num = clmchrs.clm_seq_num
AND clmchrs.chrs_seq_num =
(Select max(chrs_seq_num)
from mt16ic_clmchrs
where mt16ic_clmchrs.clmssn = a.clmssn
and mt16ic_clmchrs.cossn = a.cossn
and mt16ic_clmchrs.clm_seq_num = a.clm_seq_num)
Order By a.clmssn, a.cossn
View 7 Replies
ADVERTISEMENT
Jul 10, 2015
What is the best way to convert 3/1/1/12/0 into xxx-xx-xx-xx-xx format by appending zeros if required. final result should be
300/10/10/12/00.
View 9 Replies
View Related
Jun 4, 2015
Table contains field defined as text. First 12 characters will be date format 'mm/dd/yyyy'. then the next 3 characters could be:
9A, 12P, 3P, 7A or LTL
I would like to convert '9A' to be 09:00AM, so that I can sort by that converted field.
'12P to be 12:00PM and so on....
I have tried various form of case with substring and convert commands, but cant' get the solution. I can not change text field to varchar in table.
View 13 Replies
View Related
Jun 22, 2015
I have a varchar(512) data type on my INVOICE_DATE field and it is in the following format DD/MM/YYYY.
I need it to be in MM/DD/YYYY and have tried with no luck.
SELECT CONVERT(VARCHAR(512), INVOICE_DATE, 101) AS [MM/DD/YY]
FROM F0AInvoices_Tags
This does not swap the numbers as I would have hoped for reporting purposes.
View 8 Replies
View Related
Jul 6, 2015
how to convert -2.0120 to -2 or 3,6789 as 3 ignoring the decimal places.
The input -2.0120 is a varchar.
View 8 Replies
View Related
Oct 7, 2015
I am new to SQL and trying my hand at what I thought would be a simple query to bring back the loan products that are not HMDA reportable - hence the NOT IN and I get an error message after adding the NOT IN...I have read a lot of responses to the issue of an error message of 'Conversion failed when converting the nvarchar value' to try and solve the error - and have had no luck. Before I throw my laptop out the window..Here is the error message along with what I thought was a simple query; Conversion failed when converting the nvarchar value '80/10/10' to data type int.Here is the query;
SELECT
P.ProductID,
P.Name,
PC.Name AS [Product Group],
LP.Name AS [Loan Purpose],
CASE WHEN LP.IsHMDAReportableFL = 0 THEN 'No' ELSE 'Yes' END AS [HMDA Reportable],
[code]...
View 5 Replies
View Related
Aug 22, 2015
I'm a Power Builder (PB) developer. I've migrated PB from 10 .5 to 12.5, and now I need to migrate the stored procedures from SQL Server 2005 to 2012 as well.I get a invalid expression error when I run the procedures in Power Builder. We have previously been using a lower database compatibility model in 2005, which allowed the procedures to work there. I have learned that =* is the old way to write right outer joins.
For example:
select *
from A
right outer join
B
[code]...
Is my approach correct, or do I need to add a WHERE condition to it? I don't have access to the production database to check. The development database is in SQL Server 2012 too, sO I will not be able to run the old version there to check.
View 8 Replies
View Related
Jul 22, 2015
I have a sql table like this
event_id timestamp event_name event_score
1 3000 alarm 0.95
10 3500 alarm 0.85
5 4000 alarm 0.93
20 4200 alarm 0.87
30 5000 alarm 0.87
30 8000 alarm 0.97
40 9000 alarm 0.98
13 9700 alarm 0.98
And I am expecting output like this(With multiple rows)
event_id1 event_id2 event_id3 event_name event_score1 event_score2 event_score3
1 10 5 alarm 0.95 0.85 0.93
30 40 13 alarm 0.97 0.98 0.98
Also I have certain conditions like the timestamp difference between event_Id1 and event_Id3 < 3600 eg 1 hour
View 6 Replies
View Related
Nov 24, 2015
I have written below query.
-- AssignedToTasks 32,'from (Select Distinct wft.*','
OVER(Order by task.TaskId desc)','','where IsActive=1 AND RecurrenceRule is null',0,5
Alter PROCEDURE [dbo].[AssignedToTasks]
@UserId varchar(15) ,
@selectClause Nvarchar(max),
[Code] ....
View 4 Replies
View Related
Sep 25, 2015
I have tried all possible combinations of changing this. But was not able to make the results tally.I am giving you a part of the query, there are others queries involving 4 tables which are based on this same temporary table query.
SELECT c.juris_id, b.jrnl_mo_yr
FROM a_trueup a,
#t_mths b, r_rj c
WHERE a.rlzd_mo_yr
=* b.jrnl_mo_yr
AND
a.juris_id
=* c.juris_id
[code]....
I tried using left outer join as mentioned in blogs but got a different result (14 rows).I also used set null off/on options but no luck ..
View 15 Replies
View Related
Apr 17, 2015
This is a common error for SQL Server, but I got it in a uncommon way.I have a table called - tblIDNumber where there are two columns - IDN_Number [NVarchar(200)] and Temp [BigInt]
If I run,
SELECT *
FROM dbo.tblIDNumber
WHERE IDN_IDNumberTypeStaticValue = 33
AND IDN_Removed = 0
AND CAST(IDN_Number AS BIGINT) = 1
SQL Server give me the error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to bigint.
I first thought IDN_Number in type 33 has characters, but it doesn't, becasue the below query works!!!
UPDATE dbo.tblIDNumber
SET Temp = CAST(IDN_Number AS BIGINT)
WHERE IDN_IDNumberTypeStaticValue = 33
AND IDN_Removed = 0
To workaround, I ran the query,
UPDATE dbo.tblIDNumber
SET IDN_Number = '123'
WHERE IDN_IDNumberTypeStaticValue = 33
AND IDN_Removed = 0
and then I ran the first query, and SQL Server does NOT give me the same error - Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to bigint.
Second query approved there is nothing wrong from converting the value in IDN_Number to a BigInt, but the third query gave the hint that data might be the cause?????
finally, I found the root cause to be an index that the first query uses :
CREATE NONCLUSTERED INDEX [IX_tblIDNumber_Covering] ON [dbo].[tblIDNumber]
(
[IDN_Removed] ASC,
[IDNumberCode] ASC
)
INCLUDE ( [IDN_Number],
[IDN_Reference]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
GO
If I remove the index, the first query works without the error.
View 10 Replies
View Related
Aug 20, 2015
I have some old code consisting of over 30 Union statements, unioning 7/1/15-8/1/15 data according to the below code. Each block is the same except for the date. How could I rewrite these 30 union statements into a more concise query?
Each client (people_id) has a distinct agency_id_number. So the code below totals the number of distinct clients/day and groups them by programs and facilities.
Sample data that you can use to test this report:
IF OBJECT_ID(N'tempdb..#sample_census') IS NOT NULL drop table #sample_census
create table #sample_census
(
start_date datetime,
program_name varchar(25),
facility_name varchar(25),
agency_id_number varchar(25)
)
[Code] ....
View 3 Replies
View Related
Sep 3, 2015
CASE WHEN NULLIF(NHSNo2, '') IS NULL THEN 1
WHEN NULLIF(Surname, '') IS NULL THEN 2
WHEN NULLIF(Forename, '') IS NULL THEN 3
WHEN NULLIF(DOB, '') IS NULL OR DOB < '01/01/1900' THEN 4
WHEN NULLIF(AddressLine1, '') IS NULL THEN 5
[code]...
The above code worth great but ideally instead of returning a number Id like to return text for example
1 = NHS
2= SUR
3=FOR
4=DOB
5=ADD
6=PCO
7=GPN
8=PCZ
9=GPD
10=CCG
View 3 Replies
View Related
Nov 17, 2015
Below is garbage data and structure (I think enough to get point across). How can I perform calculations needed?
Create Table #1234
(
abcd decimal(16,4)
,defg decimal(16,4)
,hijk decimal(16,4)
,logon datetime
,logoff datetime
[code]....
View 2 Replies
View Related
Jun 4, 2015
DECLARE @i BIGINT
SET @i = 20150315
DECLARE @S VARCHAR(MAX)
SET @S = ''
SELECT @S = @S + '
DECLARE @Count BIGINT
SET @Count = '+@i+' + 1
SELECT @Count'
EXEC(@S)
I am trying to execute the above query but it is throwing me an error.
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to bigint.
View 16 Replies
View Related
Sep 24, 2015
In the following code I want to compare 2 values: AccessVal and SQLVal. The values are stored as nvarchars, so I'm isolating the numeric values in a subquery. Notice I'm only selecting 1 row. The commented line, where I compare the values, is throwing the error.
SELECT QA_AutoID, AccessVal, SQLVal
,ROUND(ABS(CONVERT(float, AccessVal,1)),0) as AccessFloat
,ROUND(ABS(CONVERT(float, SQLVal,1)),0) as SQLFloat
FROM QA
WHERE QA_AutoID in (
SELECT TOP 1 QA_AutoID
FROM QA
WHERE ISNUMERIC(SQLVal) = 1 AND ISNUMERIC(AccessVal) = 1
)
--AND ROUND(ABS(CONVERT(float, AccessVal,1)),0) <> ROUND(ABS(CONVERT(float, SQLVal,1)),0)
ORDER BY ROUND(ABS(CONVERT(float, AccessVal,1)),0) DESC
,ROUND(ABS(CONVERT(float, SQLVal,1)),0) DESC
Here is the output with the comparison commented out...
Here's what I get with the comparison line activated:
I've tried converting to numeric, int and bigint instead of float. I've tried CAST instead of CONVERT. Nothing works.
View 13 Replies
View Related
Nov 13, 2015
i am trying to run to get a list of computers from a table based on the available free space. I'd like to group them based on the available space, for example, up to 1 GB, 1-2 GB, etc.
With Disk_Space (ResourceID, ComputerName, Description, DiskName, VolumeName, FileSystem, Size, FreeSpace)AS
(
SELECT distinct
SYS.ResourceID,
SYS.Name,
LDISK.Description0,
LDISK.DeviceID0,
LDISK.VolumeName0,
LDISK.FileSystem0,
[code]...
However when i run this query, i get an error :
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '1GB To 2GB' to data type int.
View 10 Replies
View Related
Oct 16, 2013
this query is running fine in 2008 , but its not working in 2005 below is the error Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int.there is some problem in converting date in cte
with a
as
(
SELECT dbname = DB_NAME(database_id) ,
[DBSize] = CAST( ((SUM(ms.size)* 8) / 1024.0) AS DECIMAL(18,2) )
,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'01/01/1900') AS LastBackUpTime
FROM sys.master_files ms
inner join msdb.dbo.backupset bus ON bus.database_name = DB_NAME()
[code]....
View 9 Replies
View Related
Sep 2, 2015
I am using T-SQL I have a column (ColA)that has datetime format and I simply want to pull the next day but date only into ColB
ColA
3/12/2014 12:00AM
3/19/2014 12:00AM
ColB
3/13/2014
3/20/2014
I have been trying the command below but keep getting the error "Conversion failed when convertint the varchar value '03-03-2014' to data type int."
Convert (varchar(10), "StartDate", 110)+1 as Next Day
View 8 Replies
View Related
Jul 20, 2015
I am getting this error when I run the case statement below. I think I have to convert at some point, but not sure where or how.
Case when m.dispo = 2 then 'Inactive' else m.value end 'Score'
View 9 Replies
View Related
Jun 5, 2015
In my inner query,
I have a column "Cost" (data type=varchar) and another column "Qty" (data type=int)
Now in outer query, I need to multiply this 2 columns Cost*Qty.But after trying some cast operation still getting "Error converting data type varchar to numeric."
View 7 Replies
View Related
Nov 16, 2015
I've imported a CSV file into a table in SQL Server 2012. It's a large file, 140,000+ rows, so I couldn't covert it to Excel first to preserve the date format due to Excel's row limit. In the CSV file, there were 3 column with date data in "31-Aug-09" format, and the import automatically transformed these in "31AUG09" format (varchar(50)) in SQL Server. Now I need to convert these 3 columns from varchar to datetime so I could work with them in date format.
I've tried several things,e.g,
select
convert(datetime,
right(admdate,4)+left(admdate,2)+substring(admdate,3,3))
or
select
convert(datetime,
substring(admdate,6,4)+substring(admdate,1,2)+substring(admdate,3,3))
but keep getting the same error message (Msg 241) "Conversion failed when converting date and/or time from character string".
View 4 Replies
View Related
Sep 18, 2015
I am trying to write a query that give me how many items were filed per year.
This is the error:
Conversion failed when converting the varchar value 'YEAR(1999)' to data type int.
Here is the query I am using:
SELECT COUNT (*),
CASE
WHEN ChargesFiledDate > 20141231 THEN 'YEAR (2015)'
WHEN ChargesFiledDate > 20131231 THEN 'YEAR (2014)'
WHEN ChargesFiledDate > 20121231 THEN 'YEAR (2013)'
ELSE YEAR (0000)
[Code] ...
And got the same error. I know there is probably a better way to get this data (being that I need it from 2007 to present), but don't know how to write it.
View 8 Replies
View Related
Nov 16, 2015
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
View 24 Replies
View Related
Apr 21, 2015
SELECT
CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance
o/p
indate 04/18/2015
time part :17:45:00
I need to convert this 17:45:00 to 12 hours date format...
View 8 Replies
View Related
Feb 17, 2007
Hi folks,I was working on MS sql server 2005 evalution where i have built a number of databases. However, i came to discove that the evalution version has expried before i finished my work. Now i have disinstalled the sql server 2005 and installed the Sql express edition.My concern here is how can i keep my databases so they can work with sql express edition?Thank you very much in advance.
View 1 Replies
View Related
Mar 27, 2007
Hi,
I am developing a project that using the one of the starter kits which use the MS SQL EXPRESS database.The project is almost ready to be launch.
few questions:
I am looking for a good host with good support reasonable paid.
What is my options if I would like to convert from the current database, to other databases like MySql, MS SQL Server or any? which tools can help with this convertions?
thats all, thanks.
View 2 Replies
View Related
Jul 26, 2004
So where I work is thinking about one day moving to SQL server. Right now they have indexed files that aren't normalized with repeating fields in them and lots of repeat data and blank space (so a customer number in one file may be stored literally in 10 other files that are easily realted). In the intrest of saving time and money I think that they will not normalize, index, or anything to any of these files. From what I hear it will be a straight field by field creation for the most part and preserving the primary keys.
My question: I keep thinking this is going to be massive hit on performance and maintaince. How much would converting in such a manner hurt the performance of their database and how much could it potentially add to maintaince?
View 1 Replies
View Related
Jul 27, 2004
So where I work is thinking about one day moving to SQL server. Right now they have indexed files that aren't normalized with repeating fields in them and lots of repeat data and blank space (so a customer number in one file may be stored literally in 10 other files that are easily realted). In the intrest of saving time and money I think that they will not normalize, index, or anything to any of these files. From what I hear it will be a straight field by field creation for the most part and preserving the primary keys.
My question: I keep thinking this is going to be massive hit on performance and maintaince. How much would converting in such a manner hurt the performance of their database and how much could it potentially add to maintaince?
View 1 Replies
View Related
Dec 29, 2005
I'm new to SQL server.
I've been working with oracle and now need to convert this sentence to SQL Server but I get this error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'A'.
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'where'.m_proceso;
My sentence is:
/*****************
update data_grup A
set
new_grup_code =( select cod_grup_2
from data_grup_new B
where A.cod_grup = B.cod_grup )
where zone = '001' ;
/*****************
Can anyone help me to convert this to SQL server, I don't know why I get this error.
Thanks.
View 1 Replies
View Related
Jul 21, 2006
Columns from database have values like 1,5 etc and I'm getting the error: Syntax error converting the nvarchar value '1,5' to a column of data type int. when trying to convert from nvarchar to int:
SELECT CONVERT(int, MyNVarChar_column) FROM MyTable
What I'm doing wrong? Thanks a lot
View 1 Replies
View Related
Apr 11, 2006
Hi Folks!
I have the following sql script I wrote using Explicit Option to convert data from SQL Server tables into a single XML file. I am aware of the tedious nature of the select statements, but this seems to the only option I have to depict parent-child nature of the data in XML format and also to schedule it as a job to run via SQL Server Agent.
My problem is that as I run this using the following command, I get "There is insufficient system memory to run this query" error.
I am using the following commnd:
exec master..xp_cmdshell 'bcp "EXEC swr_cv2..sproc_BuildXMLTree" queryout "C: est.xml" -U -P -c -r -t'
Is there any way I can tune my query to fix that error?
Thanks so much for your help!
-Parul
View 10 Replies
View Related
Mar 29, 2004
hi.. currently i need to convert from mysql to sql
how am i to convert it ? below are my previous codes
thanks!
Imports System.Configuration.ConfigurationSettings
Imports System.Web.HttpContext
Imports System.Data.Odbc
Public Class DB
Dim myDB As New OdbcConnection
Dim myCMD As New OdbcCommand
Public Sub New()
Dim db_server = AppSettings("db_server")
Dim db = AppSettings("db")
Dim db_user = AppSettings("db_user")
Dim db_pwd = AppSettings("db_pwd")
Dim DBConnection As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=" & db_server & ";" & _
"DATABASE=" & db & ";" & _
"UID=" & db_user & ";" & _
"PASSWORD=" & db_pwd & ";" & _
"OPTION=3;"
myDB.ConnectionString = DBConnection
myCMD.Connection = myDB
End Sub
Public Function q(ByVal mySTR As String) As OdbcDataReader
myCMD.CommandText = mySTR
Try
myDB.Open()
q = myCMD.ExecuteReader(Data.CommandBehavior.CloseConn ection)
Catch ex As Exception
Err(ex.ToString)
End Try
End Function
Public Sub c(ByVal mySTR As String)
Try
myCMD.Connection.Open()
myCMD.CommandText = mySTR
myCMD.ExecuteNonQuery()
myCMD.Connection.Close()
Catch ex As Exception
Err(ex.ToString)
End Try
End Sub
Private Sub Err(ByVal strError As String)
Current.Response.Write("<h1>An error has occurred.</h1><br>" & vbCrLf)
Current.Response.Write("Please report to us the error message<br>" & vbCrLf)
Current.Response.Write(strError)
Current.Response.Flush()
Current.Response.End()
End Sub
End Class
View 1 Replies
View Related
