|
Hi All,
I want to generate a script for an existing database in oracle 10g,
Please let me know how to generate this script using oracle 10g sql navigator interface or using script.
Thank you
|
|
|
|
|
|
I work for an Insurance Company and creating a custom application. When we apply a coverage to a policy it is stored in a table with a Limit, Deductible, and Premium field. However, there are many coverages that do not follow the norm. They use different fields for Limit, Deductible, and Premium and are even stored in different tables. (Don’t ask why and don’t even try to understand the fact that they are STILL doing this so I need to leave logic to allow for additional coverages like this.) There is also no rhyme or reason to what fields they choose so varies greatly.
So, we have decided to create “LookUp” tables that will store all the information we need for a coverage. This avoids numerous joins – we were having issues with speed and performance of the application.
I need to find a way to prepopulate all these fields into a table. I would like the best option performance-wise. This will end up being a Nightly job – so when we receive a new snapshot of data we will run this query and prepopulate/update the values of the table.
Here is an example of my table, it contains the Coverage name (Coverage), the table (File), and the fields for the Limit, Deductible, and Premium. The PolicyNum field is a concatenation of LOB + Policy + Module. I need all three of these fields to join to a policy. I wanted to use a table since they will be adding new coverages. Keep in mind, I created this tables so can add more fields to it.
Here is the table I am referring to:
USE [PolicySummary]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Coverage](
[CoverageID] [int] IDENTITY(1,1) NOT NULL,
[LOB] [nvarchar](255) NULL,
[Coverage] [nvarchar](255) NULL,
[Description] [nvarchar](255) NULL,
[File] [nvarchar](255) NULL,
[Limit] [nvarchar](255) NULL,
[Deductible] [nvarchar](255) NULL,
[Premium] [nvarchar](255) NULL,
[IsMultLimit] [bit] NULL,
[IsMultDeductible] [bit] NULL,
[IsMultPremium] [bit] NULL,
[InsuranceLine] [nvarchar](255) NULL,
[PolicyNum] [nvarchar](255) NULL,
CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
(
[CoverageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
So in a nutshell, a policy may have the coverage BP0713. I then to look at this table and see that I need to run the following query:
SELECT BYAGVA as limit, BYPPTX as Deductible, BYA3VA as Premium FROM ASBYCPP WHERE BYARTX+BYASTX+BYADNB = @PolNum
The table contains the table name, the 3 fields, and the concatenation for the Policy Number.
Currently, I am looping through each coverage in a policy and selecting the values one at a time in a WHLIE Loop. Works but it is slow and I am hitting the query numerous times just to open the app (numerous units per policy so will be ran for each unit).
SELECT @SQL = N'SELECT @Limit=' + ISNULL(Limit, '''''') + N',@Deductible=' + ISNULL(Deductible, '''''') + N',@Premium=' + ISNULL(Premium, '''''') + N' FROM PHI_PIJ.dbo.' + [File] + N' WHERE ' + PolicyNum + N' = ''' + @PolNum +'''' + N' AND ' + @CovField + N'='''+Coverage +''''
FROM PolicySummary.dbo.Coverage
WHERE Coverage = @Coverage
AND LOB = @LOB
EXEC sp_executesql @SQL, N'@Limit nVarChar(255) output,@Deductible nVarChar(255) output,@Premium nVarChar(255) output', @Limit output, @Deductible output, @Premium output
INSERT INTO #OptCov2 VALUES(@Coverage, @Description, @LOB, @Limit, @Deductible, @Premium)
Can I get help with finding a way to populate my “Lookup” table each night so all I would have to do is “SELECT Limit, Deductible, PREMIUM FROM CoverageLookUp WHERE PolicyNum = @PolicyNum”
Here’s what I’ve got so far. It takes nearly 4 minutes to run the top 1000 records and there are a total of 614,497 records to run.
SELECT @SQL = N'SELECT @Limit=' + ISNULL(Limit, '''''') + N',@Deductible=' + ISNULL(Deductible, '''''') + N',@Premium=' + ISNULL(Premium, '''''') + N' FROM PHI_PIJ.dbo.ASBYCPP' + N' WHERE ' + PolicyNum + N' = ''' + @PolicyKey +'''' + N' AND BYAOTX' + N'='''+Coverage +''''
FROM PolicySummary.dbo.Coverage
WHERE Coverage = @Coverage
AND LOB = 'BOP'
EXEC sp_executesql @SQL, N'@Limit nVarChar(255) output,@Deductible nVarChar(255) output,@Premium nVarChar(255) output', @Limit output, @Deductible output, @Premium output
UPDATE PolicySummary.dbo.CoverageLkup
SET Limit = @Limit, Deductible = @Deductible, Premium = @Premium
WHERE CovAbbrev = @Coverage
AND PolicyKey = @PolicyKey
|
|
|
|
|
Where is the table?
It sounds reasonable so far.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
I have edited my question to include the script I use to create the table. Sorry about that.
|
|
|
|
|
Ah, good, how about some sample data? Made up values would be fine of course.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
I had issues pasting my entire query so I just pasted the piece inside the loop. It works but seriously takes ways too long. I need to find a way from looping through them all and probably not using dynamic SQL but can't figure out a way yet.
|
|
|
|
|
I can probably help a bit once I can get other things off my mind.
Yes, removing the looping is likely to help quite a bit.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
Dear if you want to learn sql by online free tutorial. this tutorial may be helpful to you.
www.javatpoint.com/sql-tutorial
post your queries on javatpoint forum.
www.javatpoint.com/forum
|
|
|
|
|
Probably wouldn't help me, but maybe the OP.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
Don't forget to create some indexes too, in order to speed things up.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
hey , i have a query that collects data from 3 tables using one inner join and left join but the problem that there are many duplicate rows
<
SELECT tbl_salles_bill.sb_id, ( tbl_salles_bill.sb_price_after_alll_dicount - tbl_salles_bill.sb_paid_money) as Dept,
tbl_customer.cust_fName + ' ' + tbl_customer.cust_mName + ' ' + tbl_customer.cust_lName AS CustomerName, tbl_customer.cust_city, tbl_receiving_money_receipt.rmr_id,
tbl_receiving_money_receipt.rmr_value AS Creditor
FROM tbl_salles_bill INNER JOIN
tbl_customer ON tbl_salles_bill.cust_id = tbl_customer.cust_id and tbl_salles_bill.sb_status='Paid'
left JOIN
tbl_receiving_money_receipt ON tbl_customer.cust_id = tbl_receiving_money_receipt.cust_id and tbl_receiving_money_receipt.rmr_IsPaidForWhat = 'Paid without recieving goods'
WHERE (tbl_customer.cust_id = 6)
thanks for your time.
>
|
|
|
|
|
Underscore make that unreadable bleh...
What I do is a 2 query pass, the inside query uses ROW_NUMBER() and PARTITION OVER the key fields (those that make up the unique record)
I then delete records with a row number > 1
Here is a snippet I keep around for deduping
DECLARE @Tbl TABLE (IDField INT, RowNo INT)
INSERT @Tbl
SELECT lnkStrategyNodeID IDfield,
ROW_NUMBER() OVER(PARTITION BY StrategyID,NodeID ORDER BY lnkStrategyNodeID) Rw
FROM lnkStrategyNode
DELETE
FROM lnkStrategyNode
WHERE lnkStrategyNodeID IN (SELECT IDField FROM @Tbl WHERE RowNo > 1)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Use the DISTINCT keyword
SELECT DISTINCT tbl_salles_bill.sb_id, ( tbl_salles_bill.sb_price_after_alll_dicount - tbl_salles_bill.sb_paid_money) as Dept,
tbl_customer.cust_fName + ' ' + tbl_customer.cust_mName + ' ' + tbl_customer.cust_lName AS CustomerName, tbl_customer.cust_city, tbl_receiving_money_receipt.rmr_id,
tbl_receiving_money_receipt.rmr_value AS Creditor
FROM tbl_salles_bill INNER JOIN
tbl_customer ON tbl_salles_bill.cust_id = tbl_customer.cust_id and tbl_salles_bill.sb_status='Paid'
left JOIN
tbl_receiving_money_receipt ON tbl_customer.cust_id = tbl_receiving_money_receipt.cust_id and tbl_receiving_money_receipt.rmr_IsPaidForWhat = 'Paid without recieving goods'
WHERE (tbl_customer.cust_id = 6)
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
|
The following error occurred while trying to connect database
ora-00257 archiver error solution in sql developer
need solution for sql developer
|
|
|
|
|
As I remember this error is caused by out-of-space in your storage, so log data can not be stored (archived) while trying to run some transaction...
Please check your disks...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
I have already checked the disk its not full
suggest any other solution.............
|
|
|
|
|
Kornfeld is absolutely correct, you're having space problems.
The simplest and best solution is usually to run a backup, that will cleanup old archived files (depending on your settings).
If you're running Oracle express edition, it's not the harddrive space that's the limit, it's the maximum storage space allowed. 4GB Before 11.2G, 11 GB after.
Also check your settings so that all file locations are correct.
|
|
|
|
|
Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.
Action: Check the archiver trace file for a detailed description of the problem. Also, verify that the device specified in the initialization parameter archive_log_dest is set up properly for archiving.
|
|
|
|
|
I have a table called Inventory with the following schema
ID, ItemID, Name, Value, CollectedDate
All of the columns allow duplicates except ID, so there may be multiple instances of a given Name , but with a different CollectedDate
For a given ItemID , I need to return the Value with the latest CollectedDate for each distinct Name . To add to the complexity, I need to put all of the returned items into a single row, using the Name as the column name.
So, what I'm starting with is this:
ID ITEMID NAME VALUE COLLECTEDDATE
1 12 Test1 abc 01/01/2014 00:00:00
2 12 Test2 def 01/01/2014 00:00:00
3 12 Test3 ghi 01/01/2014 00:00:00
4 12 Test4 jkl 01/01/2014 00:00:00
5 12 Test1 mno 01/03/2014 00:00:00
6 12 Test3 stu 01/05/2014 00:00:00
7 12 Test4 vwx 01/06/2014 00:00:00
8 12 Test4 yz 01/09/2014 00:00:00
9 14 Test1 123 01/09/2014 00:00:00
And my goal is to get to this:
ITEMID Test1 Test2 Test3 Test4
12 mno def stu yz
14 123 NULL NULL NULL
I created the following temp tables:
- @tempNames - contains a list of all of the distinct Name s
- @tempIDs that contains all of the unique ItemID s
- @tempInventory that contains all of the desired schema shown above.
How do I get one row of info for each ItemID to the @tempInventory table?
(I don't even know what to call what I'm trying to do. Otherwise, I'd google it).
".45 ACP - because shooting twice is just silly" - JSOP, 2010 ----- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010 ----- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
you are looking for a pivot query[^]
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Awesome hint - fingered it out.
".45 ACP - because shooting twice is just silly" - JSOP, 2010 ----- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010 ----- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Hello,
The following code returns the results we want.
However, they are being repeated.
For instance, we have ElectionName, Position, CandidateName and TotalCount.
Rather then display the results in the following format (current format)
ElectionName Position Candidate Name TotCount
Election Name 1 Mar 19 2014 President Candidate Name 1 13
Election Name 1 Mar 19 2014 President Candidate Name 2 22
Election Name 2 Mar 19 2014 President Candidate Name 1 13
Election Name 1 Mar 19 2014 President Candidate Name 2 22
Election Name 1 Mar 19 2014 President Candidate Name 1 13
Election Name 1 Mar 19 2014 President Candidate Name 2 22
Election Name 2 Mar 19 2014 1ST Vice President Candidate VP 1 27
Election Name 2 Mar 19 2014 1ST Vice President Candidate VP 8
Election Name 2 Mar 19 2014 1ST Vice President Candidate VP 27
Election Name 2 Mar 19 2014 1ST Vice President Candidate VP 8
Election Name 3 Mar 19 2014 1ST Vice President Candidate VP 27
etc
etc
We would like the results displayed in the following format:
ElectionName Position Candidate Name TotCount
Election Name 1 Mar 19 2014 President Candidate Name 1 13
President Candidate Name 2 22
President Candidate Name 1 13
President Candidate Name 2 22
President Candidate Name 1 13
President Candidate Name 2 22
Election Name 1 Mar 19 2014 1ST Vice President Candidate VP 1 27
1ST Vice President Candidate VP 1 8
1ST Vice President Candidate VP 1 27
1ST Vice President Candidate VP 1 8
1ST Vice President Candidate VP 1 27
etc
etc
with related positions group together with ElectioName.
Below is my code. Any help is greatly appreciated.
Select (e.ElectionName + CAST(e.ClosingDate AS VARCHAR(12))) as [ElectionName],
p.position,
c.candidateName,
COUNT(*) AS TotalCount
from ElectionResults er
inner join candidates c on er.candidateId = c.candidateid
inner join Positions p on er.positionId = p.positionId
inner join Elections e on c.PositionId = c.PositionId
group by grouping Sets(e. ElectionName),p.position,p.positionid,c.candidateName, e.closingDate
order by p.PositionId
|
|
|
|
|
This can be achieved by looping through the resultset and clear the ElectionName if the Position is the same as in the previous row. But this doesn't belong to the database.
|
|
|
|
|