|
digvijay dobhal wrote: BillingTerminationDate = NULL
You can't use = to compare something to NULL ; use Is Null instead.
digvijay dobhal wrote: If BillingTerminationDate is null then ... BillingTerminationDate >= @MyeffectiveDate
If BillingTerminationDate is NULL , then it's not going to be greater than, less than, or equal to anything else.
digvijay dobhal wrote: AND
CASE WHEN BillingTerminationDate = NULL THEN
(terminationDate is null or BillingTerminationDate >= @MyeffectiveDate)
ELSE BillingTerminationDate >= @MyeffectiveDate)
You're missing an END on your CASE block. You also can't use a CASE block like this.
What you want is probably something like this:
WHERE
(
(
EffectiveDate <= @MyeffectiveDate
And
(
(BillingTerminationDate Is Null And TerminationDate Is Null)
Or
BillingTerminationDate >= @MyeffectiveDate
)
)
Or
IsAdjustment = 1
)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks Richard Deeming.
I try above query bur not succeed.
Finally I do increase number of query lines and doing below…..
select * from Mytable where ((EffectiveDate <= @MyeffectiveDate and BillingTerminationDate is not null
and (BillingTerminationDate > =@MyeffectiveDate)) OR IsAdjustment=1)
select * from Mytable where ((EffectiveDate <= @MyeffectiveDate and BillingTerminationDate is null
and (TerminationDate > =@MyeffectiveDate)) OR IsAdjustment=1) Right now its working as my expactation. But having doubt that after reviewing, may or may not it will give me pain.
|
|
|
|
|
OK, try:
WHERE
(
(
EffectiveDate <= @MyEffectiveDate
And
(
(BillingTerminationDate Is Not Null And BillingTerminationDate >= @MyEffectiveDate)
Or
(BillingTerminationDate Is Null And TerminationDate >= @MyEffectiveDate)
)
)
Or
IsAdjustment = 1
)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanx.. its working
|
|
|
|
|
CASE is an expression and not an executable statement.
The general form of the predicate of a WHERE clause is expression operator expression where operator is = or <> or > or < etc.
You can use a case as one of the expressions in the predicate.
But you shouldn't need the case. Try this instead:
Select *
FROM Mytable
WHERE (
BillingTerminationDate is null
AND
terminationDate is null)
OR BillingTerminationDate > =@ MyeffectiveDate and we'll see if I understood you correctly.
People say nothing is impossible, but I do nothing every day.
|
|
|
|
|
digvijay dobhal wrote: Dear seniors,
Thats about the most offensive way ive seen to start a question.
|
|
|
|
|
I have a table including 3 fields which are Machine Name, Project Name, Date Time where Date Time indicates the exact time when machine was assigned to a project.
Now I want to find machines which are now in a specific project (in fact it may be that one machine in different times be allocated to different projects).
I want lasted specific project be latest project that machine assigned to it.
|
|
|
|
|
Sounds like a simple query to me. Did I forget something or have I misread the question?
SELECT TOP 1 MachineName
FROM MyTable
WHERE ProjectName = 'Web 8.0'
ORDER BY [DateTime] DESC
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I want all machines that now be in specific project , may machines "a", 2 days ago in project "1" and now be in project "2" and machines "b" now in project "1", when I wand machines in project "1" ,only "b" should be in result and Should not machine "a" be in result
|
|
|
|
|
Member 8407653 wrote: I want all machines that now
Change the Sql accordingly. I want coffee.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Something like this should work:
WITH cteOrderdAssignments As
(
SELECT
MachineName,
ProjectName,
ROW_NUMBER() OVER ( PARTITION BY MachineName ORDER BY [DateTime] DESC ) As RowNumber
FROM
MyTable
)
SELECT
MachineName
FROM
cteOrderedAssignments
WHERE
RowNumber = 1
And
ProjectName = 'Web 8.0'
ORDER BY
MachineName
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Row_number() function in combination with common table expression is the best way indeed
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
Hi everyone! I need to get a list of databases mapped to a login in Sql Server 2008. I have two databases which I have mapped both databases to a login.
What is the required script to return the login mapped databases? I'm able to do this in Management Studio by right-clicking on the login->Properties->User Mapping which shows the mapped databases as checked.
I'm however writing a client application in Windows Forms and I would like to know the script that can return the login mapped databases. Please help.
|
|
|
|
|
Dan_K wrote: What is the required script to return the login mapped databases?
No idea, but hang on, and we'll introduce a way to figure it out.
Dan_K wrote: I'm able to do this in Management Studio by right-clicking on the login->Properties->User Mapping which shows the mapped databases as checked.
The SMS uses Sql-commands, and one can intercept[^] those. Meaning, you can "look" at what command the application is executing to get those results.
Enjoy
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy. But provided links on the specified page were not found.
|
|
|
|
|
Aaaah, the open source profiler has become a commercial product. There's also a profiler included with Sql Server, but it would not be installed for a Sql Express version - that's why I linked an open-source version. You might want to try their trial-version[^].
Alternatively, there's an implementation of a TSql profiler here[^]. Guess I'll be using that one from now on, as I do not like to recommend trial-versions.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I just hit this problem today when recompiling an ADO-enabled project in VS2005 after installing Windows 7 SP1. After an hour of frantic Googling I found this[^] and this[^] and managed to get it to compile again, with testing on XP machines to follow soon.
As MS is apparently no longer recommending we use ADO, what should I be changing my C++ programs to use to talk to SQL Server?
|
|
|
|
|
viaducting wrote: As MS is apparently no longer recommending we use ADO
I did not read that in the links you provided. Can you point it out to me? It'd be a small game-changer if all older software would suddenly stop to work
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Please note that VB6 and .NET ADO interop are not supported anymore. This forum post does not imply any future support of these out-of-support technologies.
and
we drastically underestimated the number of customers who were recompiling ADO applications on Windows 7 SP1. Even worse, when I say drastically, I really mean DRASTICALLY."
suggest to me that MS aren't expecting people to still be actively developing ADO-based projects.
|
|
|
|
|
viaducting wrote: Please note that VB6 and .NET ADO interop are not supported anymore
That's saying something about VB6 and ADO interop. Not a statement on the future of ADO.
viaducting wrote: suggest to me that MS aren't expecting people to still be actively developing ADO-based projects.
Second statement merely explains how a specific bug surprised the team.
viaducting wrote: suggest to me that MS aren't expecting people to still be actively developing ADO-based projects.
VB6 is deprecated, and yes, I imagine that they extend that to the combination of ADO and VB6. There is no official statement that ADO is being phased out.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I'm confused here on Oracle 11.g Express. This is my first day using it, I read some of the documentation, but some things are not clear.
I was trying to create a database, but it seems like you just create connections, and that XE is the default database.
My question is XE the only database you can work with because it's the express edition?, or am I just completely missing something here.
It's been over 5 years since I have worked with anything other that SQL Server 2005+, so I'm kind of fuzzy here on the Oracle. I was able to setup user accounts, and write my stuff for asp,net to connect using the Oracle DLL.
modified 30-Oct-12 16:30pm.
|
|
|
|
|
[SOLVED]
I think I figured it out. I think XE is the sample database.
So I was able to create a new database, and then a tablespace, and made my tables, and was able to connect to it using asp.net, and read the data.
So far so good, until something else comes up.
|
|
|
|
|
You're on the right track.
But Oracle has a different archtecture than SQLServer. You are not supposed to create several databases on one computer, that's just a waste of resources.
So a few concepts:
Quote: René Nyffenegger In Oracle terminology, a user is someone who can connect to a database (if granted enough privileges) and optionally (again, if granted the appropriate privileges) can own objects (such as tables) in the database.
The objects a user owns are collectively called >schema. A schema, on its part, is always bound to exactly one user. Because there is obviously a 1 to 1 relationship between a user and a schema, these two terms are often used interchangeable.
The tablespace is a logical storage area containing of one or more physical datafiles. There can be one or more Schema's stored in one tablespace.
The tablespace is the equivalent to the database in a SQLServer, except that that would be a lie. It's just not the same thing
Now you have all the keywords you need to google all the info you need.
What you should do when you create a new application is to create a tablespace for it, to separate the data for easier handling when exporting for example.
Then create a new user having this new tablespace as the default tablespace. And using this user, create your objects, and you will have a new schema that your application can use.
People say nothing is impossible, but I do nothing every day.
|
|
|
|
|
Thank you, for the clear explanation of differences between Oracle and SQL Server.
I just wasn't able to put the relationships together, but now I get it. It seemed so different, and when I searched the terms, I would get fragments of information.
I never did understand the schema, and sort of ignored it in exchange for hey it works, but I can see how I need to learn that now.
That was a well written explanation that I'm sure others will stumble upon in their search.
|
|
|
|
|
Thanks!
I just didn't feel that it was very clear myself. But if you know the keywords and concepts you can google it.
If you don't know what to google for, you can be stuck.
Here's[^] a comparison that I find very useful when I need to "translate" functionality between SQLServer and Oracle.
People say nothing is impossible, but I do nothing every day.
|
|
|
|