|
Let's assume I have such table:
Pictures:
p_id
p_name
p_path
p_userId_mobile_addition [int]
p_userId_mobile_modification [int]
p_userId_www_addition [uniqueidentifier]
p_userId_www_modification [uniqueidentifier]
p_userId_mobile keys reference UsersMobile table.
p_userId_www keys reference UsersWWW table.
These keys give us information about the user that added or modified the picture. There are 4 keys as addition and modification can be performed with www or PocketPC interface.
How to create a view presenting:
1. p_id,
2. p_name,
3. p_path
4. username (from UsersMobile or UsersWWW) for addition
5. username for modification through www
6. username for modification through PocketPc
Platform: MS SQL Server 2005
Best regards,
|
|
|
|
|
where is the problem? 
what did you try?
we make no query for you 
|
|
|
|
|
Try something similar to this:
select p_id,
p_name,
p_path,
case when uw.username is not null then uw.username
when um.username is not null then um.username
else null
end creator,
uw.username,
um.username
from pics
left join usersmobile um
on pics.p_userId_mobile = um.p_userId_mobile
left join UsersWWW uw
on pics.p_userId_www = uw.p_userId_www
But your problem is really bigger than the query, your datamodel isn't very good.
You need to normalize the tables to avoid corruption of your data
Here's a suggestion (with room for improvement):
Image
imageid PK
name
path
users
userid PK
username
accesstype
accesstypeid PK
accesstype
useraccess
useraccessid PK
userid FK
accesstypeid FK
unique key (userid,accesstypeid)
imagecreated
imagecreatedid PK
imageid FK
useraccessid FK
createdate
unique key (imageid,useraccessid)
imagechanged
imagechangedid PK
imageid FK
useraccessid FK
changedate
As this allows storage of several changes per image you need to rethink your original query, or use max(changedate) to get the last change
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Dear All,
My question is: where the actual files stored in the database, i.e: if i have records of pdf files, are these files saved in the database tables of application it self (oracle, sql2008), or it will be some where on the H.D. and the path will be recorded in the database tables, lets say Oracle D.B??
Any clarification guys,
Thanks in advance.
0 will always beats the 1.
|
|
|
|
|
Depends on the datatype. If it's a BLOB the file is stored directly in the database. If it's BFILE a reference to the filesystem is stored in the table.
This answer is valid for Oracle. I would be surprised if SqlServer didn't have the same possibilities.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Thanks Jörgen
0 will always beats the 1.
|
|
|
|
|
You're welcome.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Hy, im evaluating how to implement Audit trail in a multi-tier .NET 3.5 Database driven Webapplication based on nettiers and SQL-Server 2008.
I read a lot about the subject, but im still not sure whats the easiest way to do it.
Is it possible at all to implement it (without refactoring everything) with the entity framework if the rest of the application never used the EF?
Or do you guys think, that triggers would do the job just fine?
Ah, and a 2nd question: Do I get this right, that frameworks like log4net are just for logging debug-infos and not for audittrail?
thanx a lot for any input!
Andreas
|
|
|
|
|
andreas.schaerer wrote: that triggers would do the job just fine
Sorry, stopped reading at this point. Anyone who has positive thoughts about triggers needs to be taken out and boiled in oil, shot, drawn and quartered and then made to support the abomination they have created. I hate triggers.
I know they are the "recommended" solution to this problem but I still hate em. I have also never had to implement a really stringent auditing solution. You also need to define the level of auditing you need to do. We usually settle for a log of who changed the record and when, tracking from and to data is whole nother level of nasties.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I worked on a system that had some quite strict auditing requirements and the way we managed it was to lock down the tables in the database so that only DBAs had direct update access to the tables. Any updates to the tables by anyone else had to be via stored procedures. Built in to every stored proc was a bit of code that wrote out the audit history of what was happening (which table, which columns, old data, new data, date and time, user id, etc.).
But it sounds as if you want to keep the impact on your existing system to a minimum, in which case you may end up having to use triggers as the least painful option.
As for log4net, it is a generic logging system that can log pretty much anything you want it to log about the behaviour of your system. You can use it to log debug info, you can also use it to log error conditions for later investigation, you can also use it to log configuration information at system start up, anything you want. I suppose you could use it as part of an audit system in your application, depending on what your requirements were and how strict it needed to be.
|
|
|
|
|
|
we can get an encrypted text by using the below keyword
DBMS_UTILITY.GET_HASH_VALUE in oracle
I need to decript the particular string passed to this keyword
for Eg:
SELECT LTRIM (TO_CHAR ( DBMS_UTILITY.GET_HASH_VALUE ( 'ADMINISTRATOR',2,1048576))) FROM dual;
this will return the encrypted text of 'ADMINISTRATOR' as 121216
But to get the Decryption of that 121216 as 'ADMINISTRATOR'.. is there any keyword used in oracle??
Please help me out..
Thanks in advance..
Stanly.S
|
|
|
|
|
Hashing is not encryption, hashing is a reduction in size, somewhat like an executive summary; it implies throwing away information, hence a one-way operation.
A common practice is to hash passwords and store the result, not the password itself. To check an incoming password, calculate its hash, and compare against the expected hash. No way to retrieve the password, it isn't there at all.
|
|
|
|
|
|
Hi guys
I have two fields, FirstName and LastName. I am able to concatnate both names but if one of the name is null then I don't get the results. For example, First name is John and there is no last name then upon concatanting both names i get null.
I have set the database "Concatenate Null yeilds Null" to True but still no success. Perhaps, could anyone of you help me.
Thanks
|
|
|
|
|
|
netJP12L wrote: First name is John and there is no last name then upon concatanting both names i get null.
netJP12L wrote: I have set the database "Concatenate Null yeilds Null" to True
It sounds like this is doing exactly what you told it to. You could:
1) follow i.j.russell's suggestion and use COALESCE, [edit - added]
2) try changing the setting,
3) write code that tests for Null and handles the various cases, or
4) don't allow Null values in these fields.
Looking through the list, i.j.russell's suggestion sounds very good to me.
Please do not read this signature.
|
|
|
|
|
For MS SQL Server I use ISNULL like ISNULL(FirstName + ' ', '') + ISNULL(LastName, '') I put a space after the first name but if it is null then the space does not get added.
|
|
|
|
|
please go through the below query.
SELECT DR.id
, case PC.id when 4 then sum(amount) end
, case PC.id when 5 then sum(amount) end
, case PC.id when 3 then sum(amount) end
, case PC.id when 6 then sum(amount) end
, case when pc.id = 2 and DP.id = 211 then sum(amount) end
, case when pc.id = 2 and DP.id = 210 then sum(amount) end
, case when pc.id = 2 and DP.id = 212 then sum(amount) end
, case when pc.id = 2 and DP.id = 213 then sum(amount) end
, case when pc.id = 2 and DP.id = 214 then sum(amount) end
, case when pc.id = 2 and DP.id = 197 then sum(amount) end
, case when PC.id = 1 and DP.id3 = 45 then sum(amount) end
FROM
table1 DR
LEFT JOIN table2 DP ON DP.id1 = DR.id1
LEFT JOIN table3 PL ON DP.id2 = PL.id
LEFT JOIN table4 PC ON PL.id1 = PC.id
WHERE
DR.date = GETDATE()
group by
DR.id, PC.id, DP.id, DP.id3
hope you understood my requirement.
i didn't think this is the right way to do.
please help any one can do this in a proper manner.
thanks in advance.
|
|
|
|
|
poornas wrote: hope you understood my requirement.
What is your requirement??
Regards,
Prakash Kalakoti
|
|
|
|
|
SELECT DR.id,
SUM(case PC.id when 4 then amount else 0 end) AS PCID4,
SUM(case PC.id when 5 then amount else 0 end) AS PCID5,
SUM(case PC.id when 3 then amount else 0 end) AS PCID3,
SUM(case PC.id when 6 then amount else 0 end) AS PCID6,
SUM(case when pc.id = 2 and DP.id = 211 then amount else 0 end) AS DPID211,
SUM(case when pc.id = 2 and DP.id = 210 then amount else 0 end) AS DPID210,
SUM(case when pc.id = 2 and DP.id = 212 then amount else 0 end) AS DPID212,
SUM(case when pc.id = 2 and DP.id = 213 then amount else 0 end) AS DPID213,
SUM(case when pc.id = 2 and DP.id = 214 then amount else 0 end) AS DPID214,
SUM(case when pc.id = 2 and DP.id = 197 then amount else 0 end) AS DPID197,
SUM(case when PC.id = 1 and DP.id3 = 45 then amount else 0 end) AS DPID45
FROM
table1 DR
LEFT JOIN table2 DP
ON DP.id1 = DR.id1
LEFT JOIN table3 PL
ON DP.id2 = PL.id
LEFT JOIN table4 PC
ON PL.id1 = PC.id
WHERE
DR.date = GETDATE()
group by
DR.id
modified on Tuesday, March 23, 2010 11:24 AM
|
|
|
|
|
I had thought about answering this as I knew something was wrong but could not think what was. i.j.russell you have hit it on the head.
|
|
|
|
|
Hi everOne
I have used switch case statment before but not sure the way I want to use it is implementable or not. If it is then please could you convert the following code into switch Case statement.
create proc Details_GetBYID
(
@Id int,
@TypeId nvarchar(max)
)
AS
IF @TypeId = 'P1'
BEGIN
SELECT motot.Completed,motot.Hours,motot.Subject
FROM MasterT as motot
INNER JOIN PSALevel as psal on psal.Id = motot.Id
WHERE motot.Id = @Id
END
ELSE IF @TypeId = 'P2'
BEGIN
SELECT motot.Completed,motot.Hours,motot.Subject
FROM MasterT as motot
INNER JOIN PSALevel as psal on psal.Id = motot.Id
WHERE motot.Id = @Id
END
ELSE
BEGIN
SELECT motot.Completed,motot.Hours,motot.Subject
FROM MasterT as motot
INNER JOIN PSALevel as psal on psal.Id = motot.Id
WHERE motot.Id = @Id
END
any help would be appriciated.
thanks in adv.
regards
learner
|
|
|
|
|
|
Hey,
why did you use "ELSE IF" when "ELSE" is same???
thats like
if a = 1 then
"abc"
else if a = 2 then
"222"
else
"222"
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
