INNER JOIN Query - Conversion Error

Feb 6, 2015

Trying to run a query to get total number of direct mail campaigns by company. Would like the totals to be (1) total number of direct mail campaigns (there are 2 types) (2) total number of direct mail campaigns type direct mail (3) total number of direct mail campaigns type publication

Below is the query I put together with INNER JOIN:

SELECT Agency.PrimaryAgencyName, COUNT(Distinct Campaigns.DirectMailID) AS TotalCampaigns, COUNT(Distinct DirectMail.Type) AS TotalDirectMail

FROM Agency
INNER JOIN (Clients
INNER JOIN (Campaigns
INNER JOIN DirectMail
ON DirectMail.DirectMailID = Campaigns.DirectMailID)
ON Clients.ClientID = Campaigns.CampaignID)
ON Agency.AgencyID = Clients.AgencyID

GROUP BY Agency.PrimaryAgencyName, DirectMail.Month, DirectMail.Type
ORDER BY Agency.PrimaryAgencyName

When I run the query, I recent the below error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '1683 1-15017' to data type int.

The value mentioned in the above error message is the ClientID.

Below are the tables for reference (bold for entity name and underline for PK) and datatypes

Agency
AgencyID - nvarchar(255)
PrimaryAgencyName - nvarchar(255)

Campaigns
CampaignID- int
ClientID- nvarchar(255)
DirectMailID- nvarchar(255)

Clients
ClientID - nvarchar(255)
AgencyID - nvarchar(255)

DirectMail
DirectMailID - nvarchar(255)
Campaign - nvarchar(255)
Type - nvarchar(255)
Month - float

View 1 Replies


ADVERTISEMENT

Error With A Simple JOIN Query....

Apr 12, 2007

Hi!



I've a big problem by using the following query :






Code Snippet

public SqlCeResultSet selectRSQuery(String query)

{

SqlCeResultSet resultSet = initializeCommand(query).ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

return resultSet;

}



SqlCeResultSet resultSet = sgb.selectRSQuery(

"SELECT p.pId, p.pLogin FROM Profiles p, ProfilesGroups pg, Groups g " +

"WHERE g.gId = pg.tpGroupId " +

"AND p.pId = pg.tpProfileId " +

"AND g.gProfileID = '" + app.Settings.Default.id + "'");



It return me this error :

Cannot generate an updatable cursor for the query because there is a non-standard join expression.



What can I do??



Thxx

View 9 Replies View Related

Left Join Insert Query Error

May 8, 2008

I have been working on this for a little bit and have gotten to this point. Below is the query in blue, the error in red. Now, from what I gather the error is telling me I can't insert a duplicate product code into tblProduct. Isn't that what the left join is exactly not doing, as in, the left join is inserting all records from Complete_records into tblProduct where the code is null(does not exist) in tblProduct? Or, is this an issue where the identity number, productID in tblProduct, isn't starting at the next number in turn?

Thanks.

Set Identity_Insert tblProduct on
DECLARE @MaxId int

SELECT @MaxID=MAX(productID)
FROM tblProduct

SELECT IDENTITY(int,1,1) AS ID,Complete_products.APNum, Complete_products.Title, Complete_products.CategoryID, Complete_products.Mountable, Complete_products.price,
Complete_products.Height, Complete_products.Width, Complete_products.IRank, Complete_products.frameable, Complete_products.Typ INTO #Temp
FROM Complete_products LEFT OUTER JOIN
tblProduct AS tblProduct_1 ON Complete_products.APNum = tblProduct_1.productCode
where tblProduct_1.productCode IS NULL

INSERT INTO tblProduct
(productID,productCode, productName, productNavID, CanBeMounted, productRetailPrice, productHeight, productWidth, Rank, CanBeFramed, ProductType)
SELECT @MaxID + ID, APNum, Title, CategoryID, Mountable, price,
Height, Width, IRank, frameable, Typ
FROM #Temp

(236752 row(s) affected)
Msg 2601, Level 14, State 1, Line 13
Cannot insert duplicate key row in object 'dbo.tblProduct' with unique index 'IX_tblProductt_productCode'.
The statement has been terminated.

View 18 Replies View Related

Multi-table JOIN Query With More Than One JOIN Statement

Apr 14, 2015

I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.

For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.

The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.

The number of rows returned should be the same as the number of rows in OrderDetails.

View 2 Replies View Related

Why Does My Query Timeout Unless Force Join To Hash Join?

Jul 25, 2007

I'm using SQL Server 2005.



A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).



The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).

If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.



If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.



So, this works:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC



and this works:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC



But this does't:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC



What should I be looking for here to understand why this is happening?



Thanks,

john















View 1 Replies View Related

Burning Issue - Conversion Of SQL Query To Semantic Query

Apr 7, 2007

Hi Everyone,



Currently, we are facing a serious problem of conversion from Sql Query to Semantic Query and we are looking for any API or Component that handles this conversion process.



Requirement Details:

I'd like to tell the story happened which lead us to this requirement. We want to convert Shazam Reports (SRW) to Reporting Services (RDL) reports that support Report Builder (semantic queries). So, we planned to convert the SRW query structure into a SQL query structure and later to Semantic Query, which is recognized by the Report Builder.



In that process, we've successfully developed a parser component that parses the SRW's query structure into standard Sql query structure but now we struggling, as we don't know how to convert an sql query into semantic query.



We are running out of time and any information towards the solution is a great help to us.



Hope I'd get some positive reply from you.



Regards,

Sinha

View 3 Replies View Related

Flat File Source Error Output Conversion Error With UNICODE Files

May 14, 2008

i have a weird situation here, i tried to load a unicode file with a flat file source component, one of file lines has data like any other line but also contains the character "ÿ" which i can't see or find it and replace it with empty string, the source component parses the line correctly but if there is a data type error in this line, the error output for that line gives me this character "ÿ" instead of the original line.


simply, the error output of flat file source component fail to get the original line when the line contains hidden "ÿ".

i hope you can help me with issue.

Thanks in advance.

View 5 Replies View Related

Why Am I Getting An Error Runtime Error Conversion String Type Pr_h_reqby To Int

Jan 17, 2007

here is my code the falue of pr_h_reqby is "Test" 
Dim strconn As New SqlConnection(connstring)
Dim myReqdata As New DataSet
Dim mycommand As SqlDataAdapter
Dim sqlstr As String = "select pr_H_reqby from tbl_pr_header where pr_h_recid = " & recid & ""
mycommand = New SqlDataAdapter(sqlstr, strconn)
mycommand.Fill(myReqdata, "mydata")
If myReqdata.Tables(0).Rows.Count > 0 Then
'lblReqID.Text = myReqdata.Tables(0).Rows("reqid").ToString
lblNameVal.Text = myReqdata.Tables("mydata").Rows("pr_H_reqby").ToString()
lblEmailVal.Text = myReqdata.Tables("mydata").Rows("pr_h_reqemail").ToString()
lblReqDateVal.Text = myReqdata.Tables("mydata").Rows("pr_h_reqdate").ToString()
lblneedval.Text = myReqdata.Tables("mydata").Rows("pr_h_needdt").ToString()
lblDeptval.Text = myReqdata.Tables("mydata").Rows("pr_h_dept").ToString()
txtbxReqDesc.Text = myReqdata.Tables("mydata").Rows("pr_h_projdesc").ToString()
End If

View 1 Replies View Related

SQL Query Question - JOIN Or Not To JOIN

Jan 2, 2006

I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.

View 3 Replies View Related

Access Query Conversion

Feb 15, 2005

I have this query:
SELECT Trim(Left([Display Name],InStr([Display Name],",")-1))+Trim(Mid([Display Name],InStr([Display Name],",")+2,1)) AS UserName, dbo_Employee.[Display Name]
FROM dbo_Employee;
Works good in access, but when trying to put it un sql server, it comes up with errors.
I know mid is substring, I just don't know what trim and instr are in mssql.

Any ideas?

View 3 Replies View Related

Datatype Conversion Query

Mar 17, 2005

Have a simple question -

Will it be possible to convert a varchar data type column (having values that are numbers or null only) to a numeric data type column having the number values and the null values not being replaced by 0.

Col A Col A
(varchar) (int or numeric)
123 123
124 124
NULL 0 <expected blank>
125 125

I tried using CAST(Col A as int) and it converts the NULL to 0. Will
CAST(ISNULL(ColA,'') as int) work? The reason for this requirement is the value 0 will give different meaning to the data. Any insights will be appreciated.

View 2 Replies View Related

Query Conversion Issue

Oct 17, 2007

i am trying to execute the following query but I am getting an error because the data types are different:

SELECT DISTINCT CurrentlyOnline.USERID, CurrentlyOnline.IPADDRESS, aspnet_Users.UserName
FROM CurrentlyOnline INNER JOIN
aspnet_Users ON CurrentlyOnline.USERID = aspnet_Users.UserId


Msg 8169, Level 16, State 2, Line 1Conversion failed when converting from a character string to uniqueidentifier.

In the CurrentlyOnline table the USERID field is defined as varchar (150)
In the aspnet_Users table the UserId field is defined as uniqueidentifier

Any ideas on how do make the query work?

View 8 Replies View Related

Int Conversion Error.

Nov 6, 2007

  Hi,I keep getting the error:System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '@qty' to data type int. When I initiate the insert and update.I tried adding a: Convert.ToInt32(TextBox1.Text), but it didn't work.. I also tried fiddling with the update code, but I think it is to do with the insert bool as the update works at the moment..  Could someone help?My code:private bool ExecuteUpdate(int quantity){  SqlConnection con = new SqlConnection(); 
con.ConnectionString = "Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated
Security=True;User Instance=True";  con.Open();  SqlCommand command = new SqlCommand();  command.Connection = con;  TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");  Label labname = (Label)FormView1.FindControl("Label3");  Label labid = (Label)FormView1.FindControl("Label13");  command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable - '@qty' WHERE productID=@productID";  command.Parameters.Add("@qty", TextBox1.Text);  command.Parameters.Add("@productID", labid.Text); command.ExecuteNonQuery();  con.Close();  return true;}    private bool ExecuteInsert(String quantity)    {        SqlConnection con = new SqlConnection();       
con.ConnectionString = "Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated
Security=True;User Instance=True";        con.Open();        SqlCommand command = new SqlCommand();        command.Connection = con;        TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");        Label labname = (Label)FormView1.FindControl("Label3");        Label labid = (Label)FormView1.FindControl("Label13");       
command.CommandText = "INSERT INTO Transactions
(Usersname,Itemid,itemname,Date,Qty) VALUES
(@User,@productID,@Itemsname,@date,@qty)";         command.Parameters.Add("@User", System.Web.HttpContext.Current.User.Identity.Name);        command.Parameters.Add("@Itemsname", labname.Text);        command.Parameters.Add("@productID", labid.Text);        command.Parameters.Add("@qty", Convert.ToInt32(TextBox1.Text));        command.Parameters.Add("@date", DateTime.Now.ToString());        command.ExecuteNonQuery();        con.Close();        return true;    }protected void Button2_Click(object sender, EventArgs e){  TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;  ExecuteUpdate(Int32.Parse(TextBox1.Text) );}protected void Button2_Command(object sender, CommandEventArgs e)    {        if (e.CommandName == "Update")        {            TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;            ExecuteInsert(TextBox1.Text);        }    }  Thanks so much if someone can!Jon

View 3 Replies View Related

Conversion Error

May 3, 2006

Please help.I have an aspx page with a drop down list(ddlCategories), and a datalist(dlLinks).  The drop down lists data property is a uniqueidentifier from a  table.When an item in the list is selected it fires the following:SqlLinks.SelectParameters("CategoryID").DefaultValue = ddlCategories.SelectedValuedlLinks.DataBind()The sqldatasource for the datalist runs a stored procedure (below)sp_GetLinks (@CategoryID ?) ASselect * from links where category = @categoryMy question is, what should @Category be declared as if the category column in the table is a uniqueidentifier?  And what conversion do I need to do I just can't work it out, as I keep getting the following error:Implicit conversion from data type sql_variant to uniqueidentifier is not
allowed. Use the CONVERT function to run this query. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: Implicit conversion from data type
sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run
this query.Source Error:



Line 5: Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCategories.SelectedIndexChangedLine 6: SqlLinks.SelectParameters("CategoryID").DefaultValue = ddlCategories.SelectedValueLine 7: dlLinks.DataBind()Line 8: End SubLine 9: End ClassSource File:
C:Documents and SettingsKarl WallsMy DocumentsMy
WebsAFRAlinks.aspx.vb    Line: 7 Stack Trace:



[SqlException (0x80131904): Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2305 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31 System.Data.SqlClient.SqlDataReader.get_MetaData() +62 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +294 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +20 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +107 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +10 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1659 System.Web.UI.WebControls.BaseDataList.GetData() +53 System.Web.UI.WebControls.DataList.CreateControlHierarchy(Boolean useDataSource) +267 System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +56 System.Web.UI.WebControls.BaseDataList.DataBind() +62 links.DropDownList1_SelectedIndexChanged(Object sender, EventArgs e) in C:Documents and SettingsKarl WallsMy DocumentsMy WebsAFRAlinks.aspx.vb:7 System.Web.UI.WebControls.ListControl.OnSelectedIndexChanged(EventArgs e) +75 System.Web.UI.WebControls.DropDownList.RaisePostDataChangedEvent() +124 System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() +7 System.Web.UI.Page.RaiseChangedEvents() +138 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4507

View 2 Replies View Related

Conversion ERROR

Aug 23, 2004

This is the error message I get: :(
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

This is the query:
Select Qual_ins.CompanyCode, Qual_ins.ParticipantCode, Qual_ins.Ins_Code, Qual_ins.Plan_Code,
dbo.PremiumRate(Qual_Ins.Crit,Qual_Ins.PQB_Spec,Pl an_Mas.Extend_Fee,
Qual_Ins.Adjpremium,Qual_Ins.Adjpremiumper,Qual_In s.Adjpremend,
GetDate(),Qual_Ins.Cover_Amt, Plan_Mas.CR_A, Plan_Mas.CR_B,
Plan_Mas.CR_C, Plan_Mas.CR_D, Plan_Mas.CR_E, Plan_Mas.CR_F,
Plan_Mas.CR_G, Plan_Mas.CR_H, Plan_Mas.CR_I, Plan_Mas.CR_J,
Plan_Mas.CR_K, Plan_Mas.CR_L, Plan_Mas.CR_M, Plan_Mas.CR_N,
Plan_Mas.CR_O) AS PremiumRate
FROM Qual_ins, Plan_Mas
WHERE Qual_Ins.CompanyCode = 'ACME'
AND Qual_ins.ParticipantCode = 4
AND Plan_Mas.CompanyCode = Qual_ins.CompanyCode
AND Plan_Mas.Ins_Code = Qual_ins.Ins_Code
AND Plan_Mas.Plan_Code = Qual_ins.Plan_Code
Order BY Qual_ins.Ins_Code

Please let me know if you need to see my PremiumRate (User Defined Function) in order to help me elimate this error message.
Any help is appreciate!
I'm new to this.... "Hello" to all!

Shuvi

View 1 Replies View Related

Dts Conversion Error

Apr 20, 2006

I have a dts package that imports data from a comma delimited .csv file.

I'm getting a conversion invalid for datatypes on column pair 1 (source 'col0012' (DBTYPE_STR), destination column 'latitude' (DBTYPE_R8)).

So, the source file is populated as a string for 'col002' and I have that field specified as a float within my table. Float is the correct type for the value.

How can I make the conversion correctly during the dts execution.

Thank you.

View 2 Replies View Related

Conversion Error

May 21, 2008

Using SQL 2005. Getting the following error. Using a cursor to update a table. I need to pass through the table two different times. The fields in the table update fine on the first time through, but on the 2nd time through I get the following error:
Conversion failed when converting datetime from character string.


Thank you for your help. David

FETCH NEXT FROM DEQ INTO
@GRGR_ID,
@SGSG_ID,
@SBSB_ID,
@PASS1_GENERATION_DATE,
@PASS1_TOTAL_AMOUNT_DUE,
@STATUS_FLAG,
@STATUS_FLAG_INSERT_DT

WHILE @@FETCH_STATUS = 0

BEGIN
UPDATE dbo.RPT_DELINQUENCY_TEST
SET PASS1_GENERATION_DATE = GETDATE()
WHERE SBSB_ID=@SBSB_ID AND GRGR_ID=@GRGR_ID

UPDATE dbo.RPT_DELINQUENCY_TEST
SET STATUS_FLAG = 'B'
WHERE SBSB_ID=@SBSB_ID AND GRGR_ID=@GRGR_ID

UPDATE dbo.RPT_DELINQUENCY_TEST
SET STATUS_FLAG_INSERT_DT = GETDATE()
WHERE SBSB_ID=@SBSB_ID AND GRGR_ID=@GRGR_ID

FETCH NEXT FROM DEQ INTO
@GRGR_ID,
@SGSG_ID,
@SBSB_ID,
@PASS1_GENERATION_DATE,
@PASS1_TOTAL_AMOUNT_DUE,
@STATUS_FLAG,
@STATUS_FLAG_INSERT_DT
END

View 4 Replies View Related

Conversion Error

Apr 19, 2007

Hi, can anyone please shed some light on this error:



[OLE DB Destination [466]] Error: There was an error with input column "Price" (518) on input "OLE DB Destination Input" (479). The column status returned was: "Conversion failed because the data value overflowed the specified type.".



The column "price" is a numeric (9)



In the flat file connection manager, the datatype for the price column is a float [dt r4]. I've also tried numeric, etc.



How do I resolve this error?



Thanks much



View 11 Replies View Related

Conversion Error

Dec 21, 2005

Hi all,

Basically I am trying to create a package that will

(A) Create a table with specified datatypes

(B) Use a text Source file for the data

(C) on Success Completion of the "Execute SQL" transform the data from the text into the table.

Connect to DB <-- [TRANSFROM]-- Text (Source) <-- Execute SQL (Create Table)

It all seems to work now but when I run the package I get the following error

The number of failing rows exceeds the maximum specified.

TransformCopy 'DTSTransformation_6'conversion error: Conversion invalid for datatypes on column on pair 1 (source column 'Col007' (DBTYPE_STR),destination column 'Rec_Amt' (DBTYPE_CY)).

But when I go into the TransformDataTask, under transformation and test that column it all works fine, infact I tested all the columns and they all seem to work fine.

It also seems to be creating the same table twice first in the " Execute SQL" task and then again for some reason in the "DataTransform" task. I dont know if that is realted to the problem or not though.

Any idea's or suggestions I could try ?

Im very new to SQL 2000 & DTS so dont rule out any very newbie errors :)

Thanks

View 3 Replies View Related

Help With A SQL Union Query Please. Conversion Errors!

Aug 29, 2007

Hi, I've created a SQL union query that grabs the top three values of a test and puts the results together on one line item by a recordnumber.
The only problem is, one value is generating the error: " Syntax error converting the varchar value '=' to a column of data type int."
This field is an operator field, so it will only display operator values ( <, >, = ).  Everything seems to work BUT the union join on that column, which is a varchar data type.  I need this union to work, and Im frustrated that I can't seem to figure out why I can't get the logic to work.  Can someone take a look at this and help me with it?
The union query looks like this:

exec('select
RecordNumber,Sum(rank1) as HbA1CRank1,max(Operator1) as HbA1COperator1,sum(contentValue1) as HbA1CContentvalue1,max(dos1) as HbA1CDOS1,Sum(rank2) as HbA1CRank2,max(Operator2) as HbA1COperator2,sum(contentValue2) as HbA1CContentvalue2,max(dos2) as HbA1CDOS2,Sum(rank3) as HbA1CRank3,max(Operator3) as HbA1COperator3,sum(contentValue3) as HbA1CContentvalue3,max(dos3) as HbA1CDOS3
from
(SELECT DISTINCT                       TOP 100 PERCENT recordnumber, Rank AS rank1, cast(Operator as varchar) as Operator1, contentValue AS contentvalue1, DOS AS DOS1, 0 AS rank2, 0 as Operator2,  0 AS contentvalue2, 0 AS DOS2,                       0 AS rank3, 0 as Operator3, 0 AS contentvalue3, 0 AS DOS3FROM         (SELECT     (SELECT     COUNT(*)                                               FROM         tblDiabetic_HgbA1C_Total vw1                                               WHERE     vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *                       FROM          tblDiabetic_HgbA1C_Total vw2) vw3WHERE     (Rank = 0)ORDER BY RecordNumber
UNION
SELECT DISTINCT                       TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1, 0 AS contentvalue1, 0 AS DOS1, Rank AS rank2, cast(Operator as varchar) as Operator2, contentValue AS contentvalue2, DOS AS DOS2,                       0 AS rank3, 0 as operator3, 0 AS contentvalue3, 0 AS DOS3FROM         (SELECT     (SELECT     COUNT(*)                                               FROM         tblDiabetic_HgbA1C_Total vw1                                               WHERE     vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *                       FROM          tblDiabetic_HgbA1C_Total vw2) vw3WHERE     (Rank = 1)ORDER BY RecordNumber
UNION
SELECT DISTINCT                       TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1,  0 AS contentvalue1, 0 AS DOS1, 0 AS rank2, 0 as operator2, 0 AS contentvalue2, 0 AS DOS2, rank AS rank3,  cast(Operator as varchar) as operator3,                      contentvalue AS contentvalue3, DOS AS DOS3FROM         (SELECT     (SELECT     COUNT(*)                                               FROM         tblDiabetic_HgbA1C_Total vw1                                               WHERE     vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *                       FROM          tblDiabetic_HgbA1C_Total vw2) vw3WHERE     (Rank = 2)ORDER BY RecordNumber )tblHgA1C
group by RecordNumber
')GO
Can anyone help?  It looks right to me, I just can't figure out why the error keeps coming up =
Thank you!

View 4 Replies View Related

New To Mssql Problem With Query Conversion

Jul 22, 2004

im managing most queries without any problems (im converting from access to mssql) but this one is causing me grief - how do i put this into mssql ?



SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead, dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted, dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown, dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticipated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusiness.OtherBusinessCommissionAnticipated] AS Expr1,

[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommissionReceived]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBusinessCommissionReceived] AS Expr2

, IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS Expr3
, IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS Expr4

FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID = dbo_Lead.ID) LEFT JOIN dbo_Mortgage ON dbo_Personal.ID = dbo_Mortgage.ID) LEFT JOIN dbo_OtherBusiness ON dbo_Personal.ID = dbo_OtherBusiness.ID) LEFT JOIN dbo_BuildingsAndContents ON dbo_Personal.ID = dbo_BuildingsAndContents.ID) LEFT JOIN dbo_Commissions ON dbo_Personal.ID = dbo_Commissions.ID) LEFT JOIN dbo_Life ON dbo_Personal.ID = dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));

View 9 Replies View Related

Implicit Conversion In Query Analyzer

Dec 8, 2004

I am trying to construct a query in Query Analyzer (with SQL Server 2000), but am getting an error regarding "implicit conversion."

Here is the query:

SELECT dbo.AUCTION.EbayNum, dbo.AUCTION.EndDate,
DATENAME([month], dbo.AUCTION.EndDate) + ' ' + DATENAME([year], dbo.AUCTION.EndDate) AS [PmtMonth],
dbo.LOT.Description, dbo.AUCTION.WinBid,
PaidStat = CASE dbo.AUCTION.PaidStatus
WHEN 0 THEN ''
ELSE 'PAID'
END,
PaidAmt = CASE dbo.AUCTION.PaidStatus
WHEN 0 THEN ''
ELSE dbo.AUCTION.WinBid
END
FROM dbo.AUCTION INNER JOIN
dbo.LOT ON dbo.AUCTION.LotNum = dbo.LOT.LotNum
WHERE (LEN(dbo.AUCTION.Winner) > 0)


The error occurs in the PaidAmt CASE statment:
"Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query."

Why is there an implicit conversion going on? And how can I fix it? :mad:

View 2 Replies View Related

Conversion Of Query From Oracle To SQL Server

Sep 12, 2007

Hi Friends,
Need ur help desperately. I am stuck with one of the queries which i had written in Oracle and need the same in SQL Server.Please have a look at the following query :

select * from r_tin_1099_info where instr(translate( nm_ctrl_cd , '~!@#$%^&*()_+}{":?><`-=]['''';/., ', '*******************************' ),'*') > 0;

Basically my purpose is to replace the values in column NM_CTRL_CD having wild card characters with '*' and then select this rows to display.

However i am not able to run the same query in SQL Server since TRANSLATE is not a built in func. I have tried a lot to replace it but could only one func : REPLACE . But the same will not replace any one of the above wild characters but will replace the entire pattern.Please note that it should be able replace even if one of the wild card characters are present in the string and not necessarily the entire pattern shown above.

please reply ASAP since i am working and need this query to fix a defect.


Thanks in advance.

View 9 Replies View Related

Conversion From Text To Money In A Query

Jun 21, 2006

Hi...

I've this type of data: 148.667,31 or 344.207.6 ... type text.
I need to convert it to money in a query...

How I can do this?

Thanks...

Paolo Manfrin

View 6 Replies View Related

Datetime Conversion Error?

Jan 17, 2008

 Hi,
I am getting the following error when
executing the ExecuteInsert in the code below..:
 
Conversion failed when converting
datetime from character string.



    private bool
ExecuteInsert(String quantity)   
{[snip]       
con.Open();       
SqlCommand command = new SqlCommand();       
command.Connection = con;       
TextBox TextBox1 =
(TextBox)FormView1.FindControl("TextBox1");       
Label 1 = (Label)FormView1.FindControl("Label3");       
Label 2 = (Label)FormView1.FindControl("Label13");       
command.CommandText = "INSERT INTO Transactions (etc,Date,etc)
VALUES (etc,@date,@etc)";        
command.Parameters.AddWithValue([snip]);       
command.Parameters.AddWithValue([snip]);        command.Parameters.AddWithValue("@date",
DateTime.Now.ToString());        
command.Parameters.AddWithValue([snip]);       
command.Parameters.AddWithValue([snip]);       
command.ExecuteNonQuery();       
con.Close();       
command.Dispose();       
return true;    }    protected
void Button2_Click(object sender, EventArgs e)   
{        TextBox TextBox1 =
FormView1.FindControl("TextBox1") as TextBox;       
bool retVal = ExecuteUpdate(Int32.Parse(TextBox1.Text));       
if (retVal)           
Response.Redirect("~/URL/EXTENSION.aspx");       
Insert();    }    private
void Insert()    {       
TextBox TextBox1 = FormView1.FindControl("TextBox1") as
TextBox;       
ExecuteInsert(TextBox1.Text);    }}  Thanks if someone can help!Jon

View 2 Replies View Related

Strange Conversion Error

Nov 16, 2004

I have a function that retrieves a data set from a passed SQL string. I'm getting this weird error.


Input string was not in a correct format

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:


Line 205: sSQL = "Select * From LedgerDetails Where LedgerID = " & _LedgerID.ToString()
Line 206: msgbox(sSQL)
Line 207: TransactionDetails = GetDataSet(sSQL)
Line 208:
Line 209: _TransactionsIndex = CShort(Val(GetDataValue("Select Count() From LedgerDetails Where LedgerID = " & CStr(_LedgerID)))) -1


Source File: C:Documents and SettingsOwnerMy DocumentsUniversityFall 2004DSSASP WorkAlgorithmTest.aspx Line: 207

Stack Trace:

[FormatException: Input string was not in a correct format.]
Microsoft.VisualBasic.CompilerServices.DoubleType.Parse(String Value, NumberFormatInfo NumberFormat) +184
Microsoft.VisualBasic.CompilerServices.IntegerType.FromString(String Value) +96

[InvalidCastException: Cast from string "Select * From LedgerDetails Wher" to type 'Integer' is not valid.]
Microsoft.VisualBasic.CompilerServices.IntegerType.FromString(String Value) +211


I have no idea why, but it seems to trying to convert the string into an integer. Both the argument and the parameter in the function are strings. I checked on the internet and the usual response is this is because the SQL is incomplete, but I have it showing me the compiled SQL string, and it is not imcomplete. I've tried hotwiring the query to match something I know will work, and I still get the same error. I've also tried compile the string using .ToString() on my number portion, storing the converted number into a string and only combining strings on the call, using another string variable as a temporary holder, using the String.Concat function, and even CStr. There is no way possible that this thing is an integer when the call is sent...

Any ideas?

Shawn

View 1 Replies View Related

SQL Data Conversion Error

Jul 12, 2005

I'm trying to create a jbo to run in SQL that will update my table DeviationMaster in crcwebauth table, with the value from qvqote in table invoice_tbl in database crcbrio...I get an error in the job that says...SQLSTATE 42000 Error 8114 Error converting data type varchar to numericThe field DNumber in the DeviationMaster table is numeric 9, and qvqote is char 6.I know about the cast/convert, but I havent been able to successfully do this. I was hoping someone could show me how to get around this problem.Here is my current SQL statement that triggers the above error:update crcwebauth.dbo.deviationmaster set ldate =  (select max(qvdate) from crcbrio.dbo.invoice_tbl where DNumber = qvqote)

View 11 Replies View Related

Annoying Conversion Error

Feb 18, 2000

Can someone please help me.

I am running a 'simple' stored procedure from the Query Analyser (command - FindCustomer L18239, [ ]) which should return a name from a customer table from the input id. It does return the name but I keep getting the message
'Server: Msg 245, Level 16, State 1, Procedure FindCustomer, Line 9
Syntax error converting the varchar value 'Kevin ' to a column of data type int.' - Any ideas what I've done wrong?, thanks.

Kevin.

stored-proc (FindCustomer)-

CREATE PROCEDURE FindCustomer
(@CustID [char](30),
@CustName [char](15) OUTPUT)

AS

BEGIN
SELECT @CustName = (SELECT FirstName from Customer where CustId = @CustID)
RETURN @CustName
END

View 2 Replies View Related

Datatype Conversion Error In DTS

Aug 25, 2000

I am using DTS to import a DB2 table from the mainframe and export the table in text format to a shared folder. I want to convert two fields to a date format yyyy-mm-dd. RELSE_Date is in this format and Updtts is a timestamp coming from the mainframe. The text file is all vchar. In dts here is my query pulling from the mainframe
SELECT A.PROD_ORDER_NUMBER, A.DYE_SEQUENCE, A.STYLE,
A.COLOR, A.SIZE, A.STATUS_IND, A.STATUS_CODE,
A.QUANTITY_REC_DC, B.SHIP_OTFQ_QTY,
A.MRP_PACK_CODE, A.LABELS_PRINTED,
date(A.RELSE_DATE) as RELSE_DATE,
date(A.UPDTTS) as UPDtts, A.LOCATION
FROM DB2.WP1_PO_CUST_REQ A,
DB2.WP1_DYE_LOT_REQ B
WHERE A.PROD_ORDER_NUMBER = B.PROD_ORDER_NUMBER
AND A.DYE_SEQUENCE = B.DYE_SEQUENCE
AND A.STYLE = B.STYLE
AND A.COLOR = B.COLOR
AND A.SIZE = B.SIZE
AND (A.PROD_ORDER_NUMBER LIKE '__K____')
When I parse the query it accepts it. When I run the DTS I get an error stating (SQL STATE 220077 SQLCODE -180) The sting representation of a datetime value has invalid syntax. Does anyone have a suggestion on how to convert these fields before the text file is exported or another output format that is similar to .dat in comma delimited format? Thank you.

View 1 Replies View Related

Why Am I Getting The Following Error During A SQL Server 6.5 Conversion?

Apr 9, 2001

Why am I getting the following error during a SQL Server 6.5 conversion?

##### Monday, April 09, 2001 - 21:06:36 #####
LOGINSID: Unable to open SQL Server registry key

View 1 Replies View Related

T-SQL Syntax Error (conversion

May 17, 2004

Hi,

New to the forum here, it seems like this is a good SQL Server resource...

I'm still somewhat in the learning phases of T-SQL coding, and so far what I've learned has been pretty beneficial...a problem i'm having at the moment though, is with some conversion. I created a stored procedure that basically takes all the records from one table to another. I'll paste the code onto here and show you what's flagging on me (comment about the error within code). It probably seems a bit convoluted, but the UserProfileValue table is something that I TOTALLY wish Microsoft had second-guessed (it's from SharePoint Portal Server 2003)


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


ALTER procedure profile_ImportUserProfileData

as

declare profile_curs cursor for
select UserProfileValue.RecordID, UserProfileValue.PropertyID, UserProfileValue.PropertyVal, PropertyList.PropertyName, PropertyList.DataType
from PropertyList
inner join UserProfileValue
on PropertyList.PropertyID = UserProfileValue.PropertyID
order by UserProfileValue.RecordId,
UserProfileValue.PropertyId

declare @RecordId int, @PropertyId int, @OldRecordId int
declare @PropertyVal sql_variant
declare @PropertyName nvarchar(20), @DataType nvarchar(20)
declare @table nvarchar(25)

set @table = 'UserProfileReportTable'

open profile_curs
fetch next from profile_curs into @RecordId, @PropertyId, @PropertyVal, @PropertyName, @DataType

if (@@fetch_status = -1)
begin
print 'End of table'
close profile_curs
deallocate profile_curs
return
end

while (@@fetch_status = 0)
begin
SET IDENTITY_INSERT UserProfileReportTable ON
insert UserProfileReportTable(RecordId)
values(@RecordId)
SET IDENTITY_INSERT UserProfileReportTable OFF

set @OldRecordId = @RecordId

fetch next from profile_curs into @RecordId, @PropertyId, @PropertyVal, @PropertyName, @DataType

if @DataType like 'nvarchar'
set @DataType = @DataType + '(20)'

while (@RecordId = @OldRecordId)
begin
declare @sql nvarchar(1000)
set @sql = 'update ' + @table
-- this next line is the one that's crapping out, saying "Incorrect syntax near @DataType"
set @sql = @sql + ' set ' + @PropertyName + ' = ' + convert(@DataType, @PropertyVal)
set @sql = @sql + ' where RecordId = ' + @RecordId
execute sp_executesql @sql
fetch next from profile_curs into @RecordId, @PropertyId, @PropertyVal, @PropertyName, @DataType
end
end

close profile_curs
deallocate profile_curs


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


I also tried cast(@PropertyVal as @DataType) but got the same results. I know it probably isn't the best idea to use a cursor, considering there are 3000+ records in UserProfileValue, but more than likely this procedure will only be run once, and I couldn't think of any better way to do it. Any help would be appreciated.

Thanks!

View 9 Replies View Related

Data Conversion Error

May 18, 2004

I'm importing data from a text table, into a temp table, and then on to a final working table. I'm running into difficulty converting data from text into a 'datetime' format. I'm getting the following error:

"Arithmetic overflow error" when trying to convert into a column with the data type "DateTime"

I half expected it to reject all conversions into a Date format because of the source file being in text format, but it allows this conversion in other columns.

If I switch the Data type to 'nvarchar' instead of 'datetime' it converts and pops up with a date format.

My question is: Will this nvarchar that looks like a date behave like a date? For example, if someone tries to perform a calculation with a date format, will the nvarchar suffice, or would it cause problems?

Any ideas?

View 1 Replies View Related

Conversion Error - SQL Package

Jun 12, 2008

Hello to all,

We've been running into this problem and it is becoming very urgent and critical to resolve it.

I am running a package called "Inventory", using a system (for both commercial and accounting operations, called "Sage") having "CBase" as database.

When we run the package to given date parameter "To Date", let's say "18/03/2008", it runs successfully.
However, when we run it to "31/03/2008", we got a error message (the message is translated from french), as following:
Simba ODBC Driver:
Error in a predicate
Conversion error into date type
Backup Memory pool invoked. Please report this error to customer support.

I will really appreciate any hint or feedback, helping me to resolve this issue.
Thank you in advance.

Leïla

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved