"where" statement negates join

it seems when i use a udf within a where clause my left join statement is ignored. for example...

Code Snippet

select errorsandstatus.typeid, errorandstatustypes.name, errorsandstatus.value, errorsandstatus.description, count(cartonerrorandstatus.cartonentrynumber) as [count]

from errorsandstatus

join errorandstatustypes on errorsandstatus.typeid = errorandstatustypes.typeid

left outer join cartonerrorandstatus on errorsandstatus.typeid = cartonerrorandstatus.errorandstatustypeid and errorsandstatus.value=cartonerrorandstatus.errorandstatusvalue

left outer join cartonhistory on cartonerrorandstatus.cartonentrynumber = cartonhistory.entrynumber

where errorandstatustypes.availforrpt=1

group by errorsandstatus.typeid, errorandstatustypes.name, errorsandstatus.value, errorsandstatus.description

order by errorsandstatus.typeid, errorsandstatus.value

returns

id name value desc count 1 Induction 0 Inducted 140 1 Induction 1 Too Close 0 1 Induction 2 Too Small 1 1 Induction 3 Too Big 0 2 Tracking 0 Tracked 141 2 Tracking 4 Missing 0 3 Destinize 1 Valid Barcode 3 3 Destinize 2 InValid Barcode 0

now when i use the exact same query only this time include 3 udf's within the where clause the left join seems to be ignored

Code Snippet

select errorsandstatus.typeid, errorandstatustypes.name, errorsandstatus.value, errorsandstatus.description, count(cartonerrorandstatus.cartonentrynumber) as [count]

from errorsandstatus

join errorandstatustypes on errorsandstatus.typeid = errorandstatustypes.typeid

left outer join cartonerrorandstatus on errorsandstatus.typeid=cartonerrorandstatus.errorandstatustypeid and
errorsandstatus.value=cartonerrorandstatus.errorandstatusvalue

left outer join cartonhistory on cartonerrorandstatus.cartonentrynumber = cartonhistory.entrynumber

where errorandstatustypes.availforrpt=1 and dbo.udf_IsDateInRange(CartonHistory.Completed, @.DateRangeStart, @.DateRangeEnd) = 1 AND dbo.udf_IsDayInShift(CartonHistory.Completed, @.ShiftDaysOfWeek) = 1 AND dbo.udf_IsTimeInShift(CartonHistory.Completed, @.ShiftTimeStart, @.ShiftTimeEnd) = 1

group by errorsandstatus.typeid, errorandstatustypes.name, errorsandstatus.value, errorsandstatus.description

order by errorsandstatus.typeid, errorsandstatus.value

returns

id name value desc count 1 Induction 0 Inducted 26 2 Tracking 0 Tracked 26 3 Destinize 1 Valid Barcode 3 3 Destinize 4 No Read 4 4 Divert 1 Validated 6 4 Divert 3 Lane Full 1 5 WtStatus 1 Valid Weight 2 7 Barcode 1 Valid Read 3 7 Barcode 2 No Read 4

why does it ignore the left join when i evaluate the udf's in the where clause?

thanks!

Try to move parts of your WHERE statement into ON clause of desired left join|||

amazing...moved it into the second join and presto! thank you!

select errorsandstatus.typeid, errorandstatustypes.[name], errorsandstatus.value, errorsandstatus.[description],
count(cartonerrorandstatus.cartonentrynumber) as [count]
from errorsandstatus
join errorandstatustypes on errorsandstatus.typeid = errorandstatustypes.typeid
left outer join cartonerrorandstatus on errorsandstatus.typeid=cartonerrorandstatus.errorandstatustypeid and
errorsandstatus.value=cartonerrorandstatus.errorandstatusvalue
left outer join cartonhistory on cartonerrorandstatus.cartonentrynumber = cartonhistory.entrynumber
and dbo.udf_IsDateInRange(CartonHistory.Completed, @.DateRangeStart, @.DateRangeEnd) = 1
AND dbo.udf_IsDayInShift(CartonHistory.Completed, @.ShiftDaysOfWeek) = 1
AND dbo.udf_IsTimeInShift(CartonHistory.Completed, @.ShiftTimeStart, @.ShiftTimeEnd) = 1
where errorandstatustypes.availforrpt=1
group by errorsandstatus.typeid, errorandstatustypes.[name], errorsandstatus.value, errorsandstatus.[description]
order by errorsandstatus.typeid, errorsandstatus.value

|||The key to remember is that anytime you have a field from the right table in the WHERE clause, you effectively convert into an 'equi-JOIN'. But if what you are really attempting to do is filter the right table, do so in the JOIN conditions.|||thanks for the clarification arnie!sql

"where" statement in list

I have one dataset and two lists with one table in each list. the two tables should get their data from the same dataset. but the tables need different "where" statements. in the dataset I only can define one "where" statment. it is possible to displace the "where" statment into the two lists? they should access to the dataset with their own "where" statement.

Depending on the complexity of your where clause you could remove the where clause to retrieve the data for both lists in one dataset and then use the filter tab of the table to make it display just the data you want

|||

My where statement for the first table is

WHERE (dbo.endgeraet.ansprechpartner IN (@.usernr))

when I remove it in the dataset and put it in the table as filter, the preview gives me an error

"the processing of filterexpression for the table 'table1' cannot be performed. cannot compare data of types system.int32 and system.string. please check the data type returned by the filterexpression"

I don't understand that, because dbo.endgeraet.ansprechpartner is an INT and usernr is an INT, too.

|||

Check the following:

make sure the parameter in not multivalue|||

Ok, my parameter is not multivalue and the filter in the table properties looks like this:

What else could be wrong?

|||

*deleted*

|||In the value column put Parameters!usernr.Value instead @.usernr|||Ok, I changed it, but the error is still there. what else could be wrong? |||why dont you use 2 different datasets?
one for each list

greets gerhard|||

A valid suggestion and definately the easiest to implement.

I'll persevery merely to see what the problem is with jori0001's report. Have you created a report parameter called "usernr". If so can you post a screenshot of the definition of this parameter in the parameters pane?

|||

@. Gerhard

when I use two different datasets, my report would totally have 32 datasets. if I use one dataset for two tables my report will only have 16 datasets.

@. Adam

Thank you, the parameters pane was the right hint. the parameter "usernr" had the data type "string". I changed it into "integer" and now it works. but I had to do one more change. the filter must be set in the list properties, not in the table properties.

"The statement did not return a result set" error on SPs with exec

I'm trying to modify our application to use the SQL Server 2005 JDBC driver. After making the appropriate changes most stuff seems to work OK, except for any stored procedures that contain an exec statement and return a result set. When the stored procedure is called with executeQuery it throws a SQLServerException with the error "The statement did not return a result set". Other query SPs that don't have an exec work just fine. It doesn't matter if the exec is calling another SP, or if it's executing a constructed SQL string. Also, this worked OK with the DataDirect JDBC drivers that we used to use. I'm running this on Windows XP with SQL Server 2000.

Hi Richard,

Could you post a sample stored procedure that demonstrates this behavior and the syntax you are using to call it? It sounds like the driver thinks the stored procedure is returning an update count rather than a result set as the first result.

Thanks,

--David Olix

JDBC Development

|||

This took a while to pinpoint. It turns out that the problem doesn't have anything to do with exec, instead it looks like a problem with UpdateText. I can reproduce the problem with the following sp:

create procedure spTest
@.id_list ntext
as
declare @.txtptr binary(16),
@.list_length integer

Create Table #parse
( IDList ntext )

Insert Into #parse
Select @.id_list

Select @.txtptr = TextPtr(IDList)
from #parse with (nolock)

Select @.list_length = DataLength(@.id_list)
if ( @.list_length > 0 AND Substring(@.id_list,@.list_length,1) <> ',')
Begin
UpdateText #parse.IDList @.txtptr NULL 0 ','
End
select * from #parse -- comment out this line to have no result set

Return 0

GO

and call it with the following:

PreparedStatement stmt = null;
ResultSet rs = null;
try {
Connection con = this.getConnection();
stmt = con.prepareCall("{call spTest(?)}");
stmt.setString(1, "12,15");
rs = stmt.executeQuery();
}
catch( SQLException e )
{
logger.severe(e.getMessage());
e.printStackTrace();
}
finally {
closeVars(null,stmt,rs);
}

If I replace the "UpdateText" with anything else, no exception is thrown.

|||

I'm wondering if UpdateText is returning a value that the driver interprets as an update count. Could you try calling this sp with execute() rather than executeQuery() and let me know if you get an update count followed by the result set that you expect?

Thanks again,

--David Olix

JDBC Development

|||If I call execute followed by getUpdateCount, it returns 1 for the update count. If I call getResultSet after that, it returns null.|||

I forgot to mention it above, but you need to call getMoreResults between getUpdateCount and getResultSet. I think the result set should be there.

Regardless, this gives me enough information to start on a fix to your problem. You may want to file this as a bug through the MSDN Product Feedback Center http://lab.msdn.microsoft.com/productfeedback/default.aspx so that you can track it.

Thanks!

|||

Even after I added a getMoreResults call between getUpdateCount and getResultSet, getResultSet still returned a null ResultSet.

I'll file a bug report. Thanks for your help.

"The statement did not return a result set" error on SPs with exec

I'm trying to modify our application to use the SQL Server 2005 JDBC driver. After making the appropriate changes most stuff seems to work OK, except for any stored procedures that contain an exec statement and return a result set. When the stored procedure is called with executeQuery it throws a SQLServerException with the error "The statement did not return a result set". Other query SPs that don't have an exec work just fine. It doesn't matter if the exec is calling another SP, or if it's executing a constructed SQL string. Also, this worked OK with the DataDirect JDBC drivers that we used to use. I'm running this on Windows XP with SQL Server 2000.

Hi Richard,

Could you post a sample stored procedure that demonstrates this behavior and the syntax you are using to call it? It sounds like the driver thinks the stored procedure is returning an update count rather than a result set as the first result.

Thanks,

--David Olix

JDBC Development

|||

This took a while to pinpoint. It turns out that the problem doesn't have anything to do with exec, instead it looks like a problem with UpdateText. I can reproduce the problem with the following sp:

create procedure spTest
@.id_list ntext
as
declare @.txtptr binary(16),
@.list_length integer

Create Table #parse
( IDList ntext )

Insert Into #parse
Select @.id_list

Select @.txtptr = TextPtr(IDList)
from #parse with (nolock)

Select @.list_length = DataLength(@.id_list)
if ( @.list_length > 0 AND Substring(@.id_list,@.list_length,1) <> ',')
Begin
UpdateText #parse.IDList @.txtptr NULL 0 ','
End
select * from #parse -- comment out this line to have no result set

Return 0

GO

and call it with the following:

PreparedStatement stmt = null;
ResultSet rs = null;
try {
Connection con = this.getConnection();
stmt = con.prepareCall("{call spTest(?)}");
stmt.setString(1, "12,15");
rs = stmt.executeQuery();
}
catch( SQLException e )
{
logger.severe(e.getMessage());
e.printStackTrace();
}
finally {
closeVars(null,stmt,rs);
}

If I replace the "UpdateText" with anything else, no exception is thrown.

|||

I'm wondering if UpdateText is returning a value that the driver interprets as an update count. Could you try calling this sp with execute() rather than executeQuery() and let me know if you get an update count followed by the result set that you expect?

Thanks again,

--David Olix

JDBC Development

|||If I call execute followed by getUpdateCount, it returns 1 for the update count. If I call getResultSet after that, it returns null.|||

I forgot to mention it above, but you need to call getMoreResults between getUpdateCount and getResultSet. I think the result set should be there.

Regardless, this gives me enough information to start on a fix to your problem. You may want to file this as a bug through the MSDN Product Feedback Center http://lab.msdn.microsoft.com/productfeedback/default.aspx so that you can track it.

Thanks!

|||

Even after I added a getMoreResults call between getUpdateCount and getResultSet, getResultSet still returned a null ResultSet.

I'll file a bug report. Thanks for your help.

"NOT IN" SQL Statement

Hi,

I need to create a SQL statement as a datasource. I need the SQL statement to exclude the records that exists in another table!

I tried to use the where clause

SELECT X.A,X.B,X.C,X.D From X

where X.B,X.C,X.D NOT IN (

SELECT Y.E,Y.F,Y.G

From Y

)

The SQL Statement works fine when I have only one column in the NOT IN part but gives me syntax error when I have more than one column in the NOT IN part.

Please advise how can I write this SQL statement in T-SQL

Thanks,

Aref

select a, b, c, d, e, f

from table1 as t1

where not exists(select * from table2 as t2 where t2.a = t1.a and t2.b = t1.b and t2.c = t1.c)

|||For more information about the provided syntax by joeydj look for the keyword correlated subqueries.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

"ConstraintException" error while filling dataset?

Hi,

I always get a "ConstraintException" error when trying, at beginning of application, to run following statement (within "Form1_Load" routine called by "this->Load" EventHandler):

"this->TauxTaxeTableAdapter->FillByPays(this->TauxTaxeDataSet->TauxTaxe,Cur_GrdView_SlPays);"

Also curiously if, while application is still running, I invoke again that same statement by means of a pushbutton clickevent, everything is running smootly without error... Looks to me that it is bugging only when running the first time...

Within Dataset, I tried to see what constraint could give me such trouble. Here is the only constraint I could find:

"this->Constraints->Add((gcnew System::Data::UniqueConstraint(L"Constraint1", gcnew cli::array< System::Data::DataColumn^ >(2) {this->columnPays, this->columnProvince_Etat}, true)));"

I then tried to find within "TauxTaxe" table if there could be any trace of records where "Pays" and "Province_Etat" columns would show any null value as well as any duplicate key values but there wasn't any...

Any place I should start to look for? BTW, I'm using a SQL Express database.

Thanks for your help,

Stphane

Here is the detailed error log I got:

System.Data.ConstraintException: Impossible d'activer les contraintes. Une ou plusieurs lignes contiennent des valeurs qui violent les contraintes de type non null, unique ou de cl externe.
à System.Data.DataSet.FailedEnableConstraints()
à System.Data.DataSet.EnableConstraints()
à System.Data.DataSet.set_EnforceConstraints(Boolean value)
à System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
à System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
à System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
à System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
à Test_ADO.DS_TauxTaxeTableAdapters.TA_TauxTaxe.FillByPays(TauxTaxeDataTable dataTable, String Pays) dans d:\projet\visual c++ 2005\projets c++cli\test_ado\ds_tauxtaxe.h:ligne 1247
à Test_ADO.Form1.Sel_Pays_NewSel(Object sender, EventArgs e) dans d:\projet\visual c++ 2005\projets c++cli\test_ado\form1.h:ligne 1887
à System.Windows.Forms.RadioButton.OnCheckedChanged(EventArgs e)
à System.Windows.Forms.RadioButton.set_Checked(Boolean value)
à Test_ADO.Form1.Init_Form1() dans d:\projet\visual c++ 2005\projets c++cli\test_ado\form1.h:ligne 1480
à Test_ADO.Form1.Form1_Load(Object sender, EventArgs e) dans d:\projet\visual c++ 2005\projets c++cli\test_ado\form1.h:ligne 1446
à System.Windows.Forms.Form.OnLoad(EventArgs e)
à System.Windows.Forms.Form.OnCreateControl()
à System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
à System.Windows.Forms.Control.CreateControl()
à System.Windows.Forms.Control.WmShowWindow(Message& m)
à System.Windows.Forms.Control.WndProc(Message& m)
à System.Windows.Forms.ScrollableControl.WndProc(Message& m)
à System.Windows.Forms.ContainerControl.WndProc(Message& m)
à System.Windows.Forms.Form.WmShowWindow(Message& m)
à System.Windows.Forms.Form.WndProc(Message& m)
à System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
à System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
à System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Finally, SQL Statement for "TA_TauxTaxe::FillByPays" is the following:

SELECT Pays, Province_Etat, Taxe1_Appl, Taxe1_Dsc, Taxe1_Taux, Taxe2_Appl, Taxe2_Dsc, Taxe2_Taux
FROM TauxTaxe
WHERE (Pays = @.Pays)

In debug mode, I double-checked and could verify that @.Pays didn't have any null value but a valid string value at time "Fill" routine was invoked. Any clue?

Hi again,

Seems that I found myself the solution to my problem. Let me explain. I first deleted all records in SQL table the error message was targetting. I then added a few records, in fact all states of U.S. and provinces of Canada and double-checked step by step how the SQL database was reacting. While entering a quite long province name, I finally discovered the problem which seemed to be a field length problem. I checked in Server Explorer the length of corresponding field but everything was OK. I then suspected that maybe the Dataset used by application didn't get updated. Bingo! It was effectively the case. In DataSet, the corresponding field still had the original length value and didn't get updated when I modified field length within Server Explorer. I think I learned a new thing... Any tricks you guys would maybe share on this issue? Must I absolutely make this "double" step each time I modify any table layout within Server Explorer?

Stphane

>= in Case Statement - Stuck

I'm struggling with a Case statement. The problem I has is with doing
>= I can use any value in there, but need to check if it's greater or
equal to 1. I'm sure I'm missing something but can't figure out what.

I've put the line below in case anyone has any suggestions. I've
limited it to the offending line, but can add more if needed.

This version works
------
CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN 0
THEN SUM(3*m.Premium/100) - (Introducer *
(SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
Bond2,

This version fails
------
CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN
>=1 THEN SUM(3*m.Premium/100) - (Introducer *
(SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
Bond2,

Thanks in advance.

Ryanryanofford@.hotmail.com (Ryan) wrote in news:7802b79d.0311190628.73e93bb0
@.posting.google.com:

> I'm struggling with a Case statement. The problem I has is with doing
>>= I can use any value in there, but need to check if it's greater or
>>equal to 1. I'm sure I'm missing something but can't figure out what.
> I've put the line below in case anyone has any suggestions. I've
> limited it to the offending line, but can add more if needed.
> This version works
> ------
> CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN 0
> THEN SUM(3*m.Premium/100) - (Introducer *
> (SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
> Bond2,
> This version fails
> ------
> CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN
>>=1 THEN SUM(3*m.Premium/100) - (Introducer *
> (SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
> Bond2,

From BOL:

Syntax
Simple CASE function:

CASE input_expression
WHEN when_expression THEN result_expression
[...n]
[
ELSE else_result_expression
]
END

Searched CASE function:

CASE
WHEN Boolean_expression THEN result_expression
[...n]
[
ELSE else_result_expression
]
END

Don't try to mix the two. Use the second form, i.e.

... CASE WHEN m.Sacrifice>=1 THEN ...

HTH|||What error are you getting?

You are mixing scalar and aggregate values, which is fine if you are
grouping the data appropriately but erroneous otherwise.

An error message would help us to help you...

HTH

Steve

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Ryan,

CASE actually has 2 flavors. Those tricky microsofties. I think you
want the second flavor. - Louis

a) CASE variable WHEN literal value THEN this expression ...
b) CASE WHEN expression THEN this expression...|||The error message is.

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.

The full version of the query is as follows (for those who want to
read it).

The only parts that cause me problems is using the greater than or
equal to expression. Each offending line bar the first problem is
commented out, but it's the same problem on each. At least I'm
consistent :-)

SELECT
CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Rebate WHEN
NULL THEN 0 ELSE SUM(3*m.Premium/100) END END AS Bond1,
CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN
>=1 THEN SUM(3*m.Premium/100) - (Introducer *
(SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
Bond2,
-- CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Rebate WHEN
>=1 THEN SUM(3*m.Premium/100) - (Introducer *
(SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
Bond3,
-- CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Introducer
WHEN >=1 THEN SUM(3*m.Premium/100) - (Introducer *
(SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
Bond4,
0 AS Bond2,
0 AS Bond3,
0 AS Bond4,
CASE m.Freq WHEN '' THEN m.Gross ELSE 0 END AS ISA1,
-- CASE SUM(m.Gross * 60) WHEN (SUM(m.Gross * 60) >= 360) THEN 360
ELSE 0 END AS ISA2,
0 AS ISA2,
0 AS ISA3, /*Empty*/
0 AS ISA4, /*Empty*/
CASE m.Freq WHEN '' THEN m.Gross ELSE 0 END AS AccSick1,
-- CASE m.PaymentMethod WHEN 'L' THEN CASE SUM(Gross*12) WHEN
(Gross*12) = 360 THEN 360 ELSE m.Gross END ELSE 0 END AS AccSick2,
0 AS AccSick2,
CASE m.PaymentMethod WHEN 'L' THEN 0 ELSE SUM(Gross-Payaway)*100 END
AS AccSick3,
0 AS AccSick4, /*Empty*/
SUM(m.Gross-m.Payaway) * 100 AS General1,
0 AS General2, /*Empty*/
0 AS General3, /*Empty*/
0 AS General4, /*Empty*/
CASE m.PaymentMethod WHEN 'L' THEN SUM(m.Gross*36) ELSE 0 END AS
Pensions1,
CASE m.PaymentMethod WHEN 'L' THEN 0 ELSE SUM((m.Gross - m.Payaway)
* 100) END AS Pensions2,
0 AS Pensions3, /*Empty*/
0 AS Pensions4, /*Empty*/
m.YearNo,
m.PeriodNo,
m.Weekno,
m.Auditref,
m.AuditReflocation,
m.Adjustaudit,
m.Element,
m.Elementlocation,
m.Elementtype,
m.AssignDate,
m.EarnComm,
m.Introducer,
m.Gross,
m.Provision,
m.Rebate,
m.RetBranch,
m.Sacrifice,
m.Payaway,
m.Premium,
m.MyCredit,
m.Credit,
m.freq,
m.Costcode,
m.Product_group,
m.TransStatus,
m.Splinter_status,
m.Provider_ref,
m.Brand_ref,
m.Source_ref,
m.Source_desc,
m.Client_ref,
m.client,
m.Provider,
m.Holding_ref,
m.ProdDesc,
m.Prodcode,
m.Class,
m.Classdesc,
m.ConHierLevRef1,
m.ConHierLevDes1,
m.ConHierLevRef2,
m.ConHierLevDes2,
m.ConHierLevRef3,
m.ConHierLevDes3,
m.ConHierLevRef4,
m.ConHierLevDes4,
m.RetHierLevRef1,
m.RetHierLevDes1,
m.RetHierLevRef2,
m.RetHierLevDes2,
m.RetHierLevRef3,
m.RetHierLevDes3,
m.RetHierLevRef4,
m.RetHierLevDes4,
m.Analysis_1_reference,
m.analysis_2_reference,
m.StreamType,
m.Start_date,
m.Leave_date,
m.Pia_adv_comp_achieve,
m.Effectivedate,
m.Statusatcreate,
m.Policy_contract_number,
m.Commencementdate,
m.User_ID,
m.Date_time,
m.Estimated_initial_comm_amt,
m.Admin_Status,
m.Admin_Status_Date,
m.Campaign_ref,
m.Campaign,
m.ManualJournalRef,
m.Notes,
m.UserID,
m.JournalDate,
m.FormulaApplied,
m.UpliftApplied,
m.Date,
m.Retainer,
m.CommInitialPeriod,
m.PaymentMethod

FROM
CBFA_MISData_Local m

WHERE
m.AssignDate = 20031031 AND
m.Element = 183519 AND
m.ConHierLevRef1 = 6093138 AND
m.Gross = 625.0

GROUP BY
m.YearNo,
m.PeriodNo,
m.Weekno,
m.Auditref,
m.AuditReflocation,
m.Adjustaudit,
m.Element,
m.Elementlocation,
m.Elementtype,
m.AssignDate,
m.EarnComm,
m.Introducer,
m.Gross,
m.Provision,
m.Rebate,
m.RetBranch,
m.Sacrifice,
m.Payaway,
m.Premium,
m.MyCredit,
m.Credit,
m.freq,
m.Costcode,
m.Product_group,
m.TransStatus,
m.Splinter_status,
m.Provider_ref,
m.Brand_ref,
m.Source_ref,
m.Source_desc,
m.Client_ref,
m.client,
m.Provider,
m.Holding_ref,
m.ProdDesc,
m.Prodcode,
m.Class,
m.Classdesc,
m.ConHierLevRef1,
m.ConHierLevDes1,
m.ConHierLevRef2,
m.ConHierLevDes2,
m.ConHierLevRef3,
m.ConHierLevDes3,
m.ConHierLevRef4,
m.ConHierLevDes4,
m.RetHierLevRef1,
m.RetHierLevDes1,
m.RetHierLevRef2,
m.RetHierLevDes2,
m.RetHierLevRef3,
m.RetHierLevDes3,
m.RetHierLevRef4,
m.RetHierLevDes4,
m.Analysis_1_reference,
m.analysis_2_reference,
m.StreamType,
m.Start_date,
m.Leave_date,
m.Pia_adv_comp_achieve,
m.Effectivedate,
m.Statusatcreate,
m.Policy_contract_number,
m.Commencementdate,
m.User_ID,
m.Date_time,
m.Estimated_initial_comm_amt,
m.Admin_Status,
m.Admin_Status_Date,
m.Campaign_ref,
m.Campaign,
m.ManualJournalRef,
m.Notes,
m.UserID,
m.JournalDate,
m.FormulaApplied,
m.UpliftApplied,
m.Date,
m.Retainer,
m.CommInitialPeriod,
m.PaymentMethod

-------

Stephen Hendricks <happy@.londonfg.com> wrote in message news:<3fbba41e$0$202$75868355@.news.frii.net>...
> What error are you getting?
> You are mixing scalar and aggregate values, which is fine if you are
> grouping the data appropriately but erroneous otherwise.
> An error message would help us to help you...
> HTH
> Steve
> =======================================
> Everyone here speaks SQL; some are more fluent, others less. When
> describing your SQL object (table, etc.), do so in the language that we
> all understand - SQL, not English. It makes it easier to understand
> your issue and makes it more likely that you will get the assistance
> that you are asking for.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Ah ha ! That does it. Knew it was something simple. Thanks for your help !

Chris Cheney <cjc1@.nospam%ucs.cam.ac.uk%no%spam%please> wrote in message news:<Xns9438ACB8BFE0Ccjc1nospamucscamacuk@.131.111.8.69>...
> ryanofford@.hotmail.com (Ryan) wrote in news:7802b79d.0311190628.73e93bb0
> @.posting.google.com:
> > I'm struggling with a Case statement. The problem I has is with doing
> >>= I can use any value in there, but need to check if it's greater or
> >>equal to 1. I'm sure I'm missing something but can't figure out what.
> > I've put the line below in case anyone has any suggestions. I've
> > limited it to the offending line, but can add more if needed.
> > This version works
> > ------
> > CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN 0
> > THEN SUM(3*m.Premium/100) - (Introducer *
> > (SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
> > Bond2,
> > This version fails
> > ------
> > CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN
> >>=1 THEN SUM(3*m.Premium/100) - (Introducer *
> > (SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
> > Bond2,
> From BOL:
> Syntax
> Simple CASE function:
> CASE input_expression
> WHEN when_expression THEN result_expression
> [...n]
> [
> ELSE else_result_expression
> ]
> END
> Searched CASE function:
> CASE
> WHEN Boolean_expression THEN result_expression
> [...n]
> [
> ELSE else_result_expression
> ]
> END
> Don't try to mix the two. Use the second form, i.e.
> ... CASE WHEN m.Sacrifice>=1 THEN ...
> HTH

'concatenating' several columns together for Insert into an XML column

Hi,

I am inserting a row into a Table. One of the columns in this table is of XML datatype.

I am using a Select statement to provide the values that will be inserted into the row. However, I would like to combine some of these values and parse them into XML for insertion into the XML column.

An example might help:

The Table to insert into has the following columns:

Table name: myTable

ID int

Data1 string

Data2 string

Data3 string

DataXml xml

I am inserting into this table using the following:

Code Snippet

insertinto myTable

(Data1, Data2, Data3, DataXml)

select Data1, Data2, Data3,

(select Data4, Data5, Data6)from myOtherTable as inside

where inside.ID = outside.ID forxmlraw)

from myOtherTable as outside

This works but it strikes me as inefficient. There is an additional lookup for each row which seems unnecessary as we are already at that row of the same table.

If it was a simple concatenation, I would do something like 'Data4 + Data5 + Data6', and it seems this is not much different except that instead of concatenation everything is being wrapped in Xml.

Does anyone have any ideas of a better way to do this?

Any help much appreciated.

Holf:

I am definitely not an expert at XML. I too am learning. This is also pretty much the way that I do it. I am also interested in seeing if Martin has a better way of doing this. To me, it looks like you are doing it right.

Kent

|||

Kent,

Thanks for the response. Well, I've checked the query plan generated and I can confirm that there is defintely some nested looping going on. I was wondering if the optimizer would realize what is happening and do some shortcuts.

Given this, I'm thinking it is going to be more efficient to concatenate the XML bits in, e.g. '<row Data4="' + Data4 + '" />' etc.

I don't want to do this because to manipulate XML I'd like to use XML tools, but I'll happily concatenate if it works out to be faster.

Thanks again for the reply. I'm learning XML too, and it does seem as though there's a lot to learn just now!

Holf

|||Be aware that FOR XML will do necessary escaping (e.g. a value like "foo & bar" will be escaped as "foo &amp; bar") while SQL string concatenation will not do any escaping that XML requires.|||Thank you, Martin, I had forgotten about that particular side effect; I have experienced pain from this particular problem a couple of times.|||

Ah yes, that is a very good point.

Well, I may write a function to which I can pass my XML elements and which will respond with some nicely crafted XML. I am considering compiling and importing a .NET assembly specifically for this task, as in .NET there are some nice tools for building up valid XML from raw elements, and these tools take care of escaping invalid characters.

I know this sounds like overkill but I still think it will be more efficient than the subquery approach above. Of course, I will be testing to find out.

Better check that my hosting provider allows upload of .NET assemblies to SQL Server...

Thanks Martin and Kent for your thoughts on this.

|||

Well, I've checked with my hosting provider and they do not allow upload of .NET assemblies. This is not surprising really, given it is a shared database and with the rights to upload assemblies, users could compromise the entire database.

So, I've written a very simple function to escape the necessary characters (of which there are not many from what I read):

Code Snippet

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

ALTERFUNCTION [dbo].[fn_EscapeForXml]

(

@.stringToEscape asnvarchar(256)

)

RETURNSnvarchar(256)

AS

BEGIN

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'&','&amp;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'<','&lt;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'>','&gt;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'''','&apos;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'"','&quot;')

RETURN(@.stringToEscape)

END

Note that the '&' has to be escaped first, or otherwise the '&'s resulting from the other replace operations are themselves replaced.

When I am concatenating my strings to make an XML document, I pass anything which I know may have invalid characters through this function first.

I have had some XML that I have treated in this way returned to a .NET environment. When the XML is deserialized these characters are resolved as they should be into their original form, so it all seems to be working.

#Re: manipulate field value from select statement

Hi all,

any assistance will be much appreciated on this one .... a bit clueless at the mo!

I've been trying to execute the code below in which part of my select statement is a calculated value i.e. Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE

code:

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) & "/" & SUBSTRING([SD],5,2) & "/" & Left([SD],4) AS STARTDATE,
Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE, vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

however this error message keeps coming up at runtime:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ENDDATE'.

My guess is it's happening when I try to get the date difference (DATEDIFF)....

help!!

Try this..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

|||

Sh... should have seen that one.

Cheers mate .. however I'm still having an error from that code:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_spend'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_dates'.

Is there some sort of restriction on selecting from a view in sql server?

|||

Bolugbe wrote:

My guess is it's happening when I try to get the date difference (DATEDIFF)....

You're right, the problem is in the DATEDIFF statement.
You cannot use just assigned aliases in calculations, so you should either copy/paste the formula for getting ENDDATE into DATEDIFF function or use nested select statements|||

Try this one..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), Convert(datetime,Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4))) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

#of rows updated

Is there a command that will tell me the number of rows that are updated in a statement. I would like to put this in an Stored Procedure and pass the #rows updated back out.you can just look at @.@.ROWCOUNT after you do a statment and it will tell you how many rows were affected by the last SQL statement.

i.e., SELECT @.@.ROWCOUNT

or better yet, put it into a local variable such as:

DECLARE @.RowCountToReturn int

SELECT * FROM dbo.MyTable
SET @.RowCountToReturn = @.@.ROWCOUNTKeep in mind that pretty much EVERY SQL statement changes the value of @.@.ROWCOUNT, so you need to save it off if you have any code that may change it before you return it to your calling procedure.

#Error when printing report

I have a field on a Group Footer which is a Sum field based of an IIF statement.

Here it is.

=Sum(IIF(Fields!EXPR1.Value=2 and Fields!Type.Value=1,Fields!Quantity.Value,0))

If I do not use the AND, by by only checking on one field it works fine and it returns a result, however if I use the AND Operator I get the #Error when previewing the report.

Does anyone have any idea why this is happening. Any suggestion would help/

THanks

Can you try to get more information about the #Error? The output window should have something listed about your error.

Jarret

|||

I figured this one out.

I used the cddl to convert the values to double values.

Thanks