CASE and INSERT statements

Hi,
I need to write a simple script which i am getting rather on how to
write. Basically, what i need to do is:
If no records exist for a particular condition i.e. TableID=3 and
TableTypeID=4, then insert a row into Tabel1.
I have tried all sorts but I think i have got the order mixed up and that’
s
why it is not working.
This is what I have tried so far:
select * ,
case
when not exists (select * from Table1 where TableID=3 and TableTypeID=14)
then insert into Table1 (TableID, TableTypeID) values (3,14)
end
from Table1
I have tried the above in various different formats but with no success.
Any help/advice much appreciated.
Thanks,
JJens
> If no records exist for a particular condition i.e. TableID=3 and
> TableTypeID=4, then insert a row into Tabel1.
IF NOT EXISTS (SELECT * FROM Table WHERE TableID=3 and TableTypeID=4)
INSERT INTO Table balblabala alaba
ELSE
Do it something else here
"JenC" <JenC@.discussions.microsoft.com> wrote in message
news:57ED03B3-B064-4793-B1AC-C5F4E891EA5E@.microsoft.com...
> Hi,
> I need to write a simple script which i am getting rather on how
> to
> write. Basically, what i need to do is:
> If no records exist for a particular condition i.e. TableID=3 and
> TableTypeID=4, then insert a row into Tabel1.
> I have tried all sorts but I think i have got the order mixed up and that
s
> why it is not working.
> This is what I have tried so far:
> select * ,
> case
> when not exists (select * from Table1 where TableID=3 and TableTypeID=14)
> then insert into Table1 (TableID, TableTypeID) values (3,14)
> end
> from Table1
> I have tried the above in various different formats but with no success.
> Any help/advice much appreciated.
> Thanks,
> J
>|||
This will never evaluate to true
-TableId can=B4t be 3 AND 14 at the same time
select * from Table1 where TableID=3D3 and TableTypeID=3D14
For an inline Query (with an OR rather than the AND which is causing
the above issue)
insert into Table1
(
TableID,
TableTypeID
)
SELECT 3,14
WHERE NOT EXISTS
(
select * from Table1 where TableID=3D3
OR TableTypeID=3D14
)=20
HTH, Jens SUessmeyer.

Cascading stock values?

Hi,
I have a database containing products, the tables of which are basically as
follows:
table_PRODUCTS
-->(products may or may not have colours)
table_PRODUCT_Colours
-->(colours may or may not have sizes)
table_PRODUCT_Colour_Sizes
- The tables currently hold stock values at all 3 levels, and the sum of
stock at Colour_Sizes level for each product must equal the sum of the stock
at Colours level, which must also equal the stock level held at the main
Product level.
- Some Products may not have Colour_Size records, and some may not have
Colours either.
I am trying to find out what is the best way to keep the 3 levels of stock
consistent, but I think the best way to do it would be:
- Using Triggers, and
- Always only allow stock to be adjusted at the highest level for a
particular product, i.e. Check for higher levels, and if found don't allow
updates to the level in question
- If an UPDATE, DELETE or INSERT operation occurs at the highest level, then
use the trigger to adjust the stock at the lower level automatically.
Please can you tell me whether this is the best way to do it, and if so any
pointers about how I would go about setting the triggers up - Although I hav
e
many years experience of TSQL, I have not used triggers before.
Thanks, Mike.You probably already know that these aggregate values violate database
normalization, and that problems you are talking about come from that
violation..
If these values are not used frequently OR there are few rows... I would NOT
stored the aggregates . Instead provide views over the tables which provide
the aggregate values..
If you MUST denormalize AND the upper level must always be the sum of the
lower levels, then there is a consistency problem I do not understand...If
the parent is always the sum of the children, but there may not be children
rows, then is the parent value 0 ( or null)...? If you allow the values to
be adjusted ONLY at the highest level, how does that allocate to the lower
levels?...
It seems to me you would ONLY allow changes at the lowest level, and the
higher levels would be calculated...
In the end if you mus do this... you might need to post more details ( with
some sample rows)...
By the way, it IS normal to maintain denormalized fields via Triggers... So
you are on the right technology track..
Good luck
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Mike Owen" wrote:

> Hi,
> I have a database containing products, the tables of which are basically a
s
> follows:
> table_PRODUCTS
> -->(products may or may not have colours)
> table_PRODUCT_Colours
> -->(colours may or may not have sizes)
> table_PRODUCT_Colour_Sizes
>
> - The tables currently hold stock values at all 3 levels, and the sum of
> stock at Colour_Sizes level for each product must equal the sum of the sto
ck
> at Colours level, which must also equal the stock level held at the main
> Product level.
> - Some Products may not have Colour_Size records, and some may not have
> Colours either.
> I am trying to find out what is the best way to keep the 3 levels of stock
> consistent, but I think the best way to do it would be:
> - Using Triggers, and
> - Always only allow stock to be adjusted at the highest level for a
> particular product, i.e. Check for higher levels, and if found don't allow
> updates to the level in question
> - If an UPDATE, DELETE or INSERT operation occurs at the highest level, th
en
> use the trigger to adjust the stock at the lower level automatically.
> Please can you tell me whether this is the best way to do it, and if so an
y
> pointers about how I would go about setting the triggers up - Although I h
ave
> many years experience of TSQL, I have not used triggers before.
>
> Thanks, Mike.|||You'd be in a lot less trouble if you had normalized the data model correctl
y.
As I see it:
1) Entities:
Products
2) Attributes:
Colour
Size
3) Relationships:
Products <-- Colour (one to zero or many)
Products <-- Size (one to zero or many)
With proper normalization nothing can stop you.
Consider changing the schema and just maybe the question you were trying to
ask will be answered as if by itself.
ML
http://milambda.blogspot.com/|||Thanks for the quick response Wayne.
Yes, you are right it is not necessarily a good / normalised design.
In answer to your 3rd paragraph " If you MUST denormalize AND ...", it
simply comes down to the fact that all products have the highest
(table_PRODUCT) level record, e.g. A toaster, some products also have the
second level, e.g. A car (blue, red, green etc), and a few have all 3 levels
,
e.g. A pair of trousers (blue, green, red) in various sizes (blue 32" waist,
blue 34" waist etc), so not all parent records have children.
It seems as though from your comment I was thinking along the right lines.
So it seems that as you have partly suggested I would probably need the
following triggers/rules:
- Child stock can always be updated, but when it is always update the parent
stock by adding up all of the childs peers stock
- If a parent has any children, don't let the stock be updated apart from by
a trigger from a child stock change.
Would you think this covers it?, and if so what would the triggers roughly
look like?
Thanks, Mike.
"Wayne Snyder" wrote:
> You probably already know that these aggregate values violate database
> normalization, and that problems you are talking about come from that
> violation..
> If these values are not used frequently OR there are few rows... I would N
OT
> stored the aggregates . Instead provide views over the tables which provid
e
> the aggregate values..
> If you MUST denormalize AND the upper level must always be the sum of the
> lower levels, then there is a consistency problem I do not understand...If
> the parent is always the sum of the children, but there may not be childre
n
> rows, then is the parent value 0 ( or null)...? If you allow the values t
o
> be adjusted ONLY at the highest level, how does that allocate to the lower
> levels?...
> It seems to me you would ONLY allow changes at the lowest level, and the
> higher levels would be calculated...
> In the end if you mus do this... you might need to post more details ( wit
h
> some sample rows)...
> By the way, it IS normal to maintain denormalized fields via Triggers...
So
> you are on the right technology track..
> Good luck
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Mike Owen" wrote:
>|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:DDECA756-D376-4F0D-845B-1C68D229A344@.microsoft.com...
> ...With proper normalization nothing can stop you.
So essentially, "proper normalization" makes you invincible. :-)|||Unfortunately it's a system that I inhereted, but surely you would still hav
e
the same problem even if you did it as you indicated below, e.g.
Keeping it simple you might have a situation where a particular product has
no colours, very simple you would simply update the stock directly against
it, but another product may have colours, in which case you would either hav
e
to:
- ignore the stock at product record level altogether, or
- use triggers at the colour level to keep the stock value at product level
up to date
If you chose the first option you would then have to write application level
code for anything that looks at product level stock in this case, so it is
either not seen, or is swapped for colour level stock.
Cheers, Mike.
"ML" wrote:

> You'd be in a lot less trouble if you had normalized the data model correc
tly.
> As I see it:
> 1) Entities:
> Products
> 2) Attributes:
> Colour
> Size
> 3) Relationships:
> Products <-- Colour (one to zero or many)
> Products <-- Size (one to zero or many)
> With proper normalization nothing can stop you.
> Consider changing the schema and just maybe the question you were trying t
o
> ask will be answered as if by itself.
>
> ML
> --
> http://milambda.blogspot.com/|||Of course all combinations should be considered:
Product : Colour : Size
value null null
value value null
value value value
value null value
This way a specific combination of values represents an instance of a produc
t.
Is this correct? Maybe you should post some representative data, so that we
can understand the issue correctly.
ML
http://milambda.blogspot.com/|||Absolutely. :) Have you never heard of the RDBMS-Man? He's fully normalized
and bullet-proof.
ML
http://milambda.blogspot.com/|||Thanks for all of your help and support.
Following this I had a go at doing my first set of triggers, and they all
seem to work fine how ever many levels I have got.
Cheers, Mike.
"ML" wrote:

> Of course all combinations should be considered:
> Product : Colour : Size
> value null null
> value value null
> value value value
> value null value
> This way a specific combination of values represents an instance of a prod
uct.
> Is this correct? Maybe you should post some representative data, so that w
e
> can understand the issue correctly.
>
> ML
> --
> http://milambda.blogspot.com/

Cascading parameters and multi-value parameters

I have a dataset where a user can select yard, well, or both yard and
well. (basically a parameter with yard and well as values, but I made
it a multi-value parameter so they can choose both if needed). The
problem I am having is that the next paramater is based off of the
first one. If yard is selected I run a certain query, if well is
selected a run a different query. How can I say if all is selected
join both the yard and well query to get the data results. Any ideas?
thanks in advance.Use an If in the datatab and use In ('Yard', 'Well') in your query.
Amarnath
"aggiechick717" wrote:
> I have a dataset where a user can select yard, well, or both yard and
> well. (basically a parameter with yard and well as values, but I made
> it a multi-value parameter so they can choose both if needed). The
> problem I am having is that the next paramater is based off of the
> first one. If yard is selected I run a certain query, if well is
> selected a run a different query. How can I say if all is selected
> join both the yard and well query to get the data results. Any ideas?
> thanks in advance.
>

Cascade parameter and sharepoint 2007

Hi,

I have an issue with my report when deploy to sharepoint 2007. Basically my report have 3 parameters, start date date picker, end date date picker and event dropdown. The data displayed in the event dropdown will depends on the start date and end date specified by user.

The problem is the event dropdown some how get "cached". I have to manually click on apply in sharepoint report veiwer for the event list to be updated. This doesn't happend in Visual Studio and also report server but only happend when I deloyed to sharepoint thus I suspect it's the sharepoint issue.

Anyone encounter similar problem before?

Ok. Seems like it's related to the date picker used by sharepoint 2007. If I manual change the date by editing the date text box, the post back works. But if i change the date using the popup calendar, the post back doesn't work. Can someone from Microsoft confirm that there are issues with the calendar control?

Capturing a variable in ASP.NET

Hi all,

I am new to .NET, after many years with classic ASP I am struggling a little with something that I am sure is really easy to do.

Basically what I want to do, is execute an SQL statement, that will return a single value. I then need to store this value as a variable, so that I can pass it into another query later.

It seems easy to output the record, but how do I store it as a variable !!

This is my code to connect and run the SQL... all I need to do as retreive the value, and put it aganist a variable...

Function higher_manager_ein() As System.Data.IDataReader

Dim connectionString As String = "server='myserver'; user id='userid'; password='pwd'; database='DB'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT distinct MEASURE FROM [CCC_MEASURE] where ein = '" & request("man_ein2") & "'"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader

End FunctionSince it sounds like all you are doing is returning one value, you might want to look at the dbCommand.ExecuteScalar method instead. That is made to return one value.

Now to explain the way you are currently doing it, you can think of the DataReader as an old ADO recordset that you used in ASP. So when this function returns the datareader, you then need to get the value out of the recordset. The firsts difference is that the DataReader object doesn't not start out at the first record. It starts right before it. So you need to call the DataReader.Read() method. That will return true or false depending on if it has read the next record. To get the value, you need to invoke the appropriate method depending on what type of value it is. So DataReader.GetString will return the value as a string. You can pass either the ordinal position of the column in the datareader or the column name.

Since what you are saying above though, I would go with the ExecuteScalar method off of the datareader instead. So again, if this is returning a string value, then you would do the following in place of the Dim dataReader As System.Data.IDataReader command above:

Dim myStringValue As String = CType( dbCommand.ExecuteScalar(), String )

You need to change the type to a string since the ExecuteScalar method returns the type Object.|||and use parameterized queries.

hth

cant use functions on linked servers??

Hello

I'm having some problems with a couple of linked SQL servers. Basically I can get queries and SPs to work just fine, aklthough there is a little overhead. But I can't get functions to work! Can it really be that linked servers don't support functions? And if so, just out of curiosity, why on earth is it so? Linked servers only handle result sets, not scalar values?

I hope somebody can help me with this
MNJCan you explain "can't get functions to work" in more detail? I've never had it fail, so I'm probably missing something really basic here.

-PatP|||I thought you could never access the SPs and UDFs through a linked server. If you could, I would be interested to know how. Is this syntax valid. Can I execute this from Server 2
linkedserver1.database1.owner1.SP1|||EXECUTE linkedserver.master.dbo.sp_who

Works just fine for me. Does anyone else have trouble with it?

-PatP|||This is working for me:

select * from OPENQUERY ( linkedserver , 'select * from testDB2.dbo.testfunction()' )

but not working:

select * from linkedserver.testDB2.dbo.testfunction()|||Pat,

I get

Server: Msg 7411, Level 16, State 1, Line 1
Server 'QA' is not configured for RPC.|||Well that was easy..

Just go to properties in EM and select that you want to do RPC's

Why would that even be an option...

Wonder what the code is to enable it...|||Originally posted by snail
This is working for me:

select * from OPENQUERY ( linkedserver , 'select * from testDB2.dbo.testfunction()' )

but not working:

select * from linkedserver.testDB2.dbo.testfunction() Can you elaborate just a bit on what constitutes "not working" ?

-PatP|||I think you can't use the next for a function

select * from any_function(params)

Shouldn't this be:

select any_function(params)

Maybe this will solve the problem?|||Welcome Johan!

It depends. If they are using a table valued function, the original syntax would be fine as long as it had at least a two-part name.

-PatP|||Just one question from me ...

though from your post it seems the linked server is to a SQL server ...

is it really to a SQL server or some other db.|||Originally posted by Pat Phelan
Can you elaborate just a bit on what constitutes "not working" ?

-PatP
I got:

Server: Msg 170, Level 15, State 31, Line 1
Line 2: Incorrect syntax near '('.|||Pat: an off-topic question: what's a table valued function?|||Originally posted by jora
Pat: an off-topic question: what's a table valued function? You can think of a table valued function as a view that takes parameters. You can read about them in the CREATE FUNCTION (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp) documentation, or an article at SQL Team (http://www.sqlteam.com/item.asp?ItemID=1955).

-PatP|||Thanks for the input everybody, and sorry I didn't respond until now. I've been looking further into the thing, and has come to the conclusion that my problem is really about linked servers and INSERTS.

I've started a new thread here:
http://www.dbforums.com/showthread.php?p=3673457#post3673457

Thanks
MNJ