normalize data run

I have an table (DDT) that I have ID, Name as columns. Second table (DDC) I
have added the FK-ID column and I now want to update that null value with
the ID from DDT.
update DDC
set tableid =
(
select ddt.tableID from ddc
left join ddt
on ddc.Name = ddt.Name
)
select * from ddc
That gives to many returns, so adding Top 1 only gives #1 to all rows?
What do I need to do to update 30,000 rows from a set of 600+?
TIA
__Stephen
I got it straight.
(
select tableID from ddt
where ddt.tablename = ddc.tablename
)
"_Stephen" <srussell@.electracash.com> wrote in message
news:esvWGJpTGHA.1576@.tk2msftngp13.phx.gbl...
>I have an table (DDT) that I have ID, Name as columns. Second table (DDC)
>I have added the FK-ID column and I now want to update that null value with
>the ID from DDT.
> update DDC
> set tableid =
> (
> select ddt.tableID from ddc
> left join ddt
> on ddc.Name = ddt.Name
> )
> select * from ddc
> That gives to many returns, so adding Top 1 only gives #1 to all rows?
> What do I need to do to update 30,000 rows from a set of 600+?
> TIA
> __Stephen
>

normalize data run

I have an table (DDT) that I have ID, Name as columns. Second table (DDC) I
have added the FK-ID column and I now want to update that null value with
the ID from DDT.
update DDC
set tableid =
(
select ddt.tableID from ddc
left join ddt
on ddc.Name = ddt.Name
)
select * from ddc
That gives to many returns, so adding Top 1 only gives #1 to all rows?
What do I need to do to update 30,000 rows from a set of 600+?
TIA
__StephenI got it straight.
(
select tableID from ddt
where ddt.tablename = ddc.tablename
)
"_Stephen" <srussell@.electracash.com> wrote in message
news:esvWGJpTGHA.1576@.tk2msftngp13.phx.gbl...
>I have an table (DDT) that I have ID, Name as columns. Second table (DDC)
>I have added the FK-ID column and I now want to update that null value with
>the ID from DDT.
> update DDC
> set tableid =
> (
> select ddt.tableID from ddc
> left join ddt
> on ddc.Name = ddt.Name
> )
> select * from ddc
> That gives to many returns, so adding Top 1 only gives #1 to all rows?
> What do I need to do to update 30,000 rows from a set of 600+?
> TIA
> __Stephen
>

normalize data run

I have an table (DDT) that I have ID, Name as columns. Second table (DDC) I
have added the FK-ID column and I now want to update that null value with
the ID from DDT.
update DDC
set tableid = (
select ddt.tableID from ddc
left join ddt
on ddc.Name = ddt.Name
)
select * from ddc
That gives to many returns, so adding Top 1 only gives #1 to all rows?
What do I need to do to update 30,000 rows from a set of 600+?
TIA
__StephenI got it straight.
(
select tableID from ddt
where ddt.tablename = ddc.tablename
)
"_Stephen" <srussell@.electracash.com> wrote in message
news:esvWGJpTGHA.1576@.tk2msftngp13.phx.gbl...
>I have an table (DDT) that I have ID, Name as columns. Second table (DDC)
>I have added the FK-ID column and I now want to update that null value with
>the ID from DDT.
> update DDC
> set tableid => (
> select ddt.tableID from ddc
> left join ddt
> on ddc.Name = ddt.Name
> )
> select * from ddc
> That gives to many returns, so adding Top 1 only gives #1 to all rows?
> What do I need to do to update 30,000 rows from a set of 600+?
> TIA
> __Stephen
>

Non-queried parameter giving 'Invalid Column Name' error

I have created a non-queried boolean parameter called HideOptionalColumns.
This field doesn't exist in my query result set. I use this value in the
Hidden attribute of the Visiblity property for the column to hide or display
certain columns based on the user's runtime selection. It works fine when I
View Report in VS - the columns are hiddden/displayed appropriately. But
when I run the report, I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'myDset'.
Invalid column name 'HideOptionalColumns'.
This seems to imply that it does need that parameter in the result set
itself. But that configuration doesn't work since the rows are then filtered
out based on that parameter. Is there another way to accomplish this hiding
of columns?
Thank you!On Apr 30, 4:04 pm, marian <mar...@.discussions.microsoft.com> wrote:
> I have created a non-queried boolean parameter called HideOptionalColumns.
Instead of using a parameter, you could place another field/textbox
right in your table or matrix. For each column you want to hide, set
the Hide option to trigger from the field or text box you add. I use
this a lot and it works without any problems.
> This field doesn't exist in my query result set. I use this value in the
> Hidden attribute of the Visiblity property for the column to hide or display
> certain columns based on the user's runtime selection. It works fine when I
> View Report in VS - the columns are hiddden/displayed appropriately. But
> when I run the report, I get the following error:
> An error has occurred during report processing.
> Query execution failed for data set 'myDset'.
> Invalid column name 'HideOptionalColumns'.
> This seems to imply that it does need that parameter in the result set
> itself. But that configuration doesn't work since the rows are then filtered
> out based on that parameter. Is there another way to accomplish this hiding
> of columns?
> Thank you!sql

Non Usesfull error Message

ERROR MSG:

Msg 515, Level 16, State 2, Server SQL\CIS, Procedure ssp_gas_CustomerCancellation_A, Line 161

Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.

The statement has been terminated.

Why didn't it actually tell me the Column and table name? i've never seen this before?

Anyone can help

Thanks

Marios

Oh I know this isn't the right forum but i couldn't find the right one to post in.

How is the procedure executed?

Not sure about why the error message is like that. Are there many fields in the table that do not allow nulls, I'd start checking those.

|||

It is called from another Stored Procedure the Stored Procedure that it rasises the error in is about 600lines of code and there are serval places where it Inserts int Tables and Temp Tables. I've looked at the data passed and which fields they are passed in to but i don't see where this error happends. It has only happend in our Production Database i can not so far reproduce it in our development environment. I don't understand why it doesn't show me the column and table

Thanks

Marios

|||

Do you explicity create your temporary tables or create/populate them through select into statments? Maybe that is why it doesn't show the table/column name (the fact that that it is a temporary table).

Nevermind, just tested that and it doesn't seem to be the case.

|||

no i explicitly defne the tables for temp tables before using them and but they are declare

CREATE #TableName

(

)

|||The only other thing I can think of, and I realize it may be impractical due to the large amount of code within the stored procedure is to use try...catch statements.|||i can't because it's a SQL 2000 server

Non aggregate case

Hello,
In the same fact table, I have real values and min/max values. The type of value (real, min, max) is a dimension. So, I want to aggregate value only for real value, because for min/max values, all the leaf are filled by my program.
So, it's possible to say "I want the not aggregate value in the database" in MDX query?
Thank you,
Guillaume.

Hi Guillaume,

Can you create a view (or named query, if you're using AS 2005) on the fact table, such that 3 separate measure fields can be created for the real, min and max values? In that case, appropriate aggregation functions of sum(), min(0 and max() can be applied to each measure. Something like:

create view splitfact as

select fact.dim1, fact.dim2, fact.dim3, ..,

sum(case fact.typedim when 'real' then fact.valfield else 0 end) as realval,

sum(case fact.typedim when 'min' then fact.valfield else 0 end) as minval,

sum(case fact.typedim when 'max' then fact.valfield else 0 end) as maxval

from fact

group by fact.dim1, fact.dim2, fact.dim3, ..

|||

If you look at the attribute in the cube editor, you will see that there is an aggregation usage property. Change it from default to none.

_-_-_ Dave

|||Thank you !

Ok, now I created 2 NamedCalculation on my DataSourceView. The first one is for 'real' value with aggregate function = SUM, and for the second NamedCalculation, I put 'min/max' values, with aggregate function = NONE.

But when I browse data, I can't see my data with aggregate function=None. But when I change the aggregate function, I can see my data. It's like if the none aggreagte function doesn't work correctly. Do you know this problem?

nodes does not seem to work when xml value is created through a select

Dear all,
While
DECLARE @.x xml
SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
SELECT (select @.x).query('//a')
works fine, a very similar query
DECLARE @.x xml
SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
SELECT t.c.query('.') from (select @.x).nodes('//a') t(c)
fails with
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Anyone knows whether this is expected/documented behaviour?
Thanks
PeterDerived table syntax in FROM clause requires table alias to follow it
(unlike scalar sub-query in SELECT).
You can achieve what you want with CROSS APPLY:
DECLARE @.x xml
SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
SELECT t2.c.query('.') from (select @.x) t1(x) CROSS APPLY t1.x.nodes('//a')
t2(c)
Best regards,
Eugene
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter" <pgp.coppens@.gmail.com> wrote in message
news:1138820267.235233.39270@.f14g2000cwb.googlegroups.com...
> Dear all,
> While
> DECLARE @.x xml
> SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
> SELECT (select @.x).query('//a')
> works fine, a very similar query
> DECLARE @.x xml
> SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
> SELECT t.c.query('.') from (select @.x).nodes('//a') t(c)
> fails with
> Msg 102, Level 15, State 1, Line 3
> Incorrect syntax near '.'.
> Anyone knows whether this is expected/documented behaviour?
> Thanks
> Peter
>|||That helps,
Thanks,
Peter.

nodes does not seem to work when xml value is created through a select

Dear all,
While
DECLARE @.x xml
SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
SELECT (select @.x).query('//a')
works fine, a very similar query
DECLARE @.x xml
SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
SELECT t.c.query('.') from (select @.x).nodes('//a') t(c)
fails with
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Anyone knows whether this is expected/documented behaviour?
Thanks
Peter
Derived table syntax in FROM clause requires table alias to follow it
(unlike scalar sub-query in SELECT).
You can achieve what you want with CROSS APPLY:
DECLARE @.x xml
SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
SELECT t2.c.query('.') from (select @.x) t1(x) CROSS APPLY t1.x.nodes('//a')
t2(c)
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter" <pgp.coppens@.gmail.com> wrote in message
news:1138820267.235233.39270@.f14g2000cwb.googlegro ups.com...
> Dear all,
> While
> DECLARE @.x xml
> SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
> SELECT (select @.x).query('//a')
> works fine, a very similar query
> DECLARE @.x xml
> SET @.x = '<ROOT><a>111</a><a>222</a><a>333</a><a>444</a></ROOT>'
> SELECT t.c.query('.') from (select @.x).nodes('//a') t(c)
> fails with
> Msg 102, Level 15, State 1, Line 3
> Incorrect syntax near '.'.
> Anyone knows whether this is expected/documented behaviour?
> Thanks
> Peter
>
|||That helps,
Thanks,
Peter.

No value given for one or more required parameters.

HI everyone, pls can u tell me whats the error in my code?

I am connecting to an oledb database

sql_edit_record = "UPDATE Anamnese SET ";

sql_edit_record += "ID=?,";

sql_edit_record += "Sexe = ?,";

sql_edit_record += "[Date de Naissance] = ?,";

sql_edit_record += "Classe = ?,";

sql_edit_record += "Ecole = ?,";

sql_edit_record += "Adresse = ?,";

sql_edit_record += "Telphone = ?,";

sql_edit_record += "[Cot de signalement] = ?,";

sql_edit_record += "[Motif de consultation] = ?,";

sql_edit_record += "[Histoire familale] = ?,";

sql_edit_record += "[Histoire mdicale] = ?,";

sql_edit_record += "[Histoire dveloppementale] = ?,";

sql_edit_record += "[Histoire scolaire] = ?,";

sql_edit_record += "[Situation socio-conomique] = ?,";

sql_edit_record += "[Histoire comportementale] = ?";

sql_edit_record += "WHERE Nom = ?";

//sql_edit_record = "UPDATE Anamnese SET Sexe = 'M' WHERE Nom = 'Dory'";

db_adapter.UpdateCommand.Connection = oledb_connection;

db_adapter.UpdateCommand.CommandText = sql_edit_record;

using (db_adapter.UpdateCommand)

{

textBox1.Text = sql_edit_record;

db_adapter.InsertCommand.Parameters.Add("@.ID", OleDbType.VarChar).Value = current_row.ToString();

db_adapter.InsertCommand.Parameters.Add("@.SEXE", OleDbType.VarChar).Value = sexe;

db_adapter.UpdateCommand.Parameters.Add("@.DATEDENAISSANCE", OleDbType.VarChar).Value = date_de_naissance;

db_adapter.UpdateCommand.Parameters.Add("@.CLASSE", OleDbType.VarChar).Value = classe;

db_adapter.UpdateCommand.Parameters.Add("@.ECOLE", OleDbType.VarChar).Value = ecole;

db_adapter.UpdateCommand.Parameters.Add("@.ADRESSE", OleDbType.VarChar).Value = adresse;

db_adapter.UpdateCommand.Parameters.Add("@.TELEPHONE", OleDbType.VarChar).Value = telephone;

db_adapter.UpdateCommand.Parameters.Add("@.SIGNALEMENT", OleDbType.VarChar).Value = signalement;

db_adapter.UpdateCommand.Parameters.Add("@.MOTIF", OleDbType.VarChar).Value = consultation;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_FAM", OleDbType.VarChar).Value = his_familiale;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_MED", OleDbType.VarChar).Value = his_medicale;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_DEV", OleDbType.VarChar).Value = his_developpementale;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_SCOL", OleDbType.VarChar).Value = his_scolaire;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_SOCIO_ECO", OleDbType.VarChar).Value = his_socio_eco;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_COMPORTEMENTALE", OleDbType.VarChar).Value = his_comportementale;

db_adapter.UpdateCommand.Parameters.Add("@.NOM", OleDbType.VarChar).Value =cbox_name.Text;

db_adapter.UpdateCommand.Connection.Open();

db_adapter.UpdateCommand.ExecuteNonQuery();

db_adapter.UpdateCommand.Connection.Close();

}

Without seeing any error messages it's hard to tell, however the first thing that stands out is the following:

sql_edit_record += "[Histoire comportementale] = ?";

sql_edit_record += "WHERE Nom = ?";

You should insert a space after the question mark on the first line, like so:

sql_edit_record += "[Histoire comportementale] = ? ";

sql_edit_record += "WHERE Nom = ?";

You may also find that you have to wrap the placeholders in single quotes where your table columns are of a textual datatype, for instance:

sql_edit_record += "[Histoire comportementale] = '?' ";

sql_edit_record += "WHERE Nom = '?'";

Is there a reason that you aren't using stored procedures to perform this task?

Chris

|||

Thanks for ur reply, the error is :

OleDBException was Unhandled

No value given for one or more required parameters.

Allthough I put the space after the ?.

|||

Sorry to state the obvious, but could the problem be that you haven't assigned values to all of the parameters?

Thanks
Chris

|||

Would an empty string("") raise such an error?