Database

Re: how to create the databse??

10-Apr-13 0:35

Have a HOWTO[^].

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Shanalal Kasim12-Apr-13 0:03

Shanalal Kasim

12-Apr-13 0:03

Check Creating an Oracle Database[^]

Re: how to create the databse??

indra kurnia13-May-13 6:57

indra kurnia

13-May-13 6:57

Firstly, you have to download the Oracle Database Software. go to oracle.com and download the software.

I suggest to download 11g R2 windows version. It's more easier to install on windows than on Linux.

nothimg

Table Design help with 2 computed columns

Simon_Whale10-Apr-13 0:14

10-Apr-13 0:14

Using SQL Server 2008 r2

I am trying to design a table, which has 2 computed column based on the product of other columns including a computed column, which SQL Server doesn't like.

SQL

CREATE TABLE [dbo].[BaseRate2](
	[ProductType] [varchar](100) NOT NULL,
	[LinkToRate] [decimal](18, 0) NULL,
	[CopyOfRates] [decimal](18, 0) NULL,
	[BCdiscountLoading] [decimal](18, 0) NULL,
	[OtherLoading] [decimal](18, 0) NULL,
	[ClaimsLoading] [decimal](18, 0) NULL,
	[NoClaimsDiscount3Yr] [decimal](18, 0) NULL,
	[NoClaimsDiscount4Yr] [decimal](18, 0) NULL,
	[NoClaimsDiscount5Yr] [decimal](18, 0) NULL,
	[ConstructionLoading] [decimal](18, 0) NULL,
	[DiscretionaryDiscount] [decimal](18, 0) NULL,
	[MinPremiumLoading] [decimal](18, 0) NULL,
	[SecurityDiscount] [decimal](18, 0) NULL,
	[ProductLoading]  AS ((((((([ClaimsLoading]*[NoClaimsDiscount3Yr])*[NoClaimsDiscount4Yr])*[NoClaimsDiscount5Yr])*[ConstructionLoading])*[DiscretionaryDiscount])*[MinPremiumLoading])*[SecurityDiscount])
) ON [PRIMARY]

Now when I try to add the second computed column when uses the first computed column (ProductLoading) this is where SQL throws an error as it should do.

RateUsed as ([CopyOfRates] * [BCdiscountLoading] * [OtherLoading] * [ProductLoading])

Is there some other technique that I could design this table without manually calculating these product columns (ProductLoadings and RateUsed).

Thanks
Simon

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Eddy Vluggen10-Apr-13 0:23

10-Apr-13 0:23

It blows, because you're using a value in your calculation that might not be available yet. Create the table, and a view - add the second computed field to the view.

Alternatively, you could use a normal (non-calculated) field, and update it's contents using a trigger.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Simon_Whale10-Apr-13 1:28

10-Apr-13 1:28

Eddy,

Thanks for that suggestion (views) it worked, but I decided against the idea after following through on the documentation of the table / view as it made it more complex to maintain in this situation.

So sadly I adopted the manual calculations before entering into the table.

Thanks again Big Grin | :-D

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Mycroft Holmes10-Apr-13 1:50

10-Apr-13 1:50

As Eddie suggested the Product loading may not exists.

This is not going to make you happy. You can replace [ProductLoading] in your second field with the ENTIRE formula from the first field.

ducks and runs I told you it would not make you happy!

Never underestimate the power of human stupidity
RAH

Simon_Whale10-Apr-13 1:55

10-Apr-13 1:55

I understood the why it isn't working and was trying to see if there was another way to do this computationally.

while I did consider the approach you suggested, I also have to write detailed documentation around this insurance rating API and was looking for a easy / elegant solution that would make documentation easy and clear.

With the documentation in mind I decided that it would be easier for me to explain the columns and how they are calculated manually. Frown | :(

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Mycroft Holmes10-Apr-13 2:23

10-Apr-13 2:23

Simon_Whale wrote:
easy / elegant solution

Does not describe the way sql works some times.

Never underestimate the power of human stupidity
RAH

Simon_Whale10-Apr-13 2:24

How To Use Pivot in SQL SERVER

10-Apr-13 2:24

Hence I came here to tap into the vast knowledge of others Big Grin | :-D

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

srikrishnach9-Apr-13 23:55

srikrishnach

9-Apr-13 23:55

How to use pivot in sql server here am having 5 colleges in that 5 programs runnig. in that one college runnig 5 programs and another college runnig 3 programs, another college running 2 programs. when am using pivot in sql server each college displaying 5 programs but i want one college runnig 5 programs and another college runnig 3 programs, another college running 2 programs like this. how to use only one select command

Re: How To Use Pivot in SQL SERVER

Eddy Vluggen10-Apr-13 0:35

Re: How To Use Pivot in SQL SERVER

10-Apr-13 0:35

Documentation is here[^]. What have you tried?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Mycroft Holmes10-Apr-13 1:45

Re: How To Use Pivot in SQL SERVER

10-Apr-13 1:45

Ah hah this calls for a little self promotion - work through this Pivot two or more columns in SQL Server 2005[^]

Never underestimate the power of human stupidity
RAH

Shanalal Kasim12-Apr-13 0:06

Shanalal Kasim

12-Apr-13 0:06

Refer below link

Using PIVOT and UNPIVOT[^]

Using PIVOT and UNPIVOT[^]

problem in join and update

mhd.sbt9-Apr-13 7:34

mhd.sbt

9-Apr-13 7:34

hi
i wanna update some rows in files table but i face with error.
can every one help me?

my code is :

SQL

UPDATE [Common]..[com_Files] SET
      [ReferenceGuid] = (SELECT [Guid] FROM [Personnel]..Missions
            WHERE  [PersonnelSystem]..Missions.ID= [Common]..[com_Files].ReferenceID)
WHERE
      [ReferenceFlag] = 72 AND
      [ReferenceGuid] IS NULL AND
      [ReferenceSoftwareGuid] = '66944D9C-31F4-4594-9407-4E1F64C079D9' and
      [ReferenceID]= [PersonnelSystem]..Missions.ID
GO

thanks for any help

Sandeep Mewara9-Apr-13 8:13

Sandeep Mewara

9-Apr-13 8:13

mehdi.sabet wrote:
[Common]..[com_Files]

mehdi.sabet wrote:
[PersonnelSystem]..Missions.ID

mehdi.sabet wrote:
[Common]..[com_Files].ReferenceID

Why two dots? Does not look correct.

Sandeep Mewara
Microsoft ASP.NET MVP 2012 & 2013

[My Blog]: Sandeep Mewara's Tech Journal!
[My Latest Article]: HTML5 Quick Start Web Application

mhd.sbt9-Apr-13 8:28

mhd.sbt

9-Apr-13 8:28

this is ok in my frame work
and you use this query with one dot
my problem is in join but not in dots
thank you for reply

Richard Deeming9-Apr-13 8:43

Richard Deeming

9-Apr-13 8:43

You forget to include the error, and a hint as to which DBMS you're using.

Assuming MS SQL, try:

SQL

UPDATE
    F
SET
    [ReferenceGuid] = M.[Guid]
FROM
    [Common]..[com_Files] As F
    INNER JOIN [PersonnelSystem]..Missions As M
    ON F.ReferenceID = M.ID
WHERE
    F.[ReferenceFlag] = 72
AND
    F.[ReferenceGuid] Is Null
AND
    F.[ReferenceSoftwareGuid] = '66944D9C-31F4-4594-9407-4E1F64C079D9'
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

mhd.sbt9-Apr-13 9:36

mhd.sbt

9-Apr-13 9:36

ok thanks for reply
i solved this problem with this code:

SQL

UPDATE  mycompany.dbo.Files
SET     [ReferenceGuid] = ( SELECT  mission.[GUID]
                            FROM    dbo.Mission mission
                            WHERE   mission.ID = [ReferenceID]
                          )
WHERE   [ReferenceSoftwareGuid] = '66944D9C-31F4-4594-9407-4E1F64C079D9'

thanks anyway

SQL

nukalarajesh8-Apr-13 3:43

nukalarajesh

8-Apr-13 3:43

in my database Table i have a column name BookDate(nvarchar) 22/03/2013 01:25 PM stored data in this format
now i need to match only with date out of date time it should be matched with current date please help with a query

Thanking you

Richard Deeming8-Apr-13 4:01

Richard Deeming

8-Apr-13 4:01

nukalarajesh wrote:
BookDate(nvarchar)

Don't store dates as strings. Change the column to datetime (or datetime2(0) if you're using SQL 2008 or higher), and then query for BookDate >= @Today And BookDate < @Tomorrow.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Eddy Vluggen8-Apr-13 7:45

8-Apr-13 7:45

If you are wondering why your other post was removed (in less than 10 minutes!), it was because of the "urgency". We're volunteers, answering questions in our spare time. Had you included it with your question, then the question would have been gone too.

If you want an answer "fast", then you better make sure that there's a clear problem description, and some example-code showing what you tried to achieve. You might also want to include a subject-line that's a bit more descriptive; posts in the database-forum usually have some SQL, and it doesn't give a clue of what you're doing.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Mycroft Holmes8-Apr-13 12:59

8-Apr-13 12:59

Just in case you did not take Richard seriously - DO NOT STORE DATES AS TEXT, USE THE DATETIME DATATYPE. This is the basis of your problem.

How do I make that red, can I make it flash, can I make it jump through the monitor and thump it into the desk?

Never underestimate the power of human stupidity
RAH