SQL - Write Query with field names and table name stored in a table? - Database Discussion Boards

I guess it's safe to assume that it's monthly payments, and that BETWEEN '2014-02-02'AND '2014-04-02' you'd have two payments per customer_id.

Wrong is evil and must be defeated.
- Jeff Ello[^]

Where do you display the result? In a .Net application written by you / your colleagues? In MySQL Query browser? ...?

am generating EXcel/ PDF file,in .net application written by me,

modified 3-Apr-14 5:19am.

Consequently, the first thing to check is if the data returned from the query to your application are correct, and then where that duplication happens. That's a lot of debugging...

Quote:
passive income online

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

Why not tools like Oracle SQL Developer[^] or Toad[^]?

thatraja

Code converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute

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:

SQL

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).

SQL

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.

SQL

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? Confused | :confused:

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

SQL

DECLARE @Tbl TABLE (IDField INT, RowNo INT)	

--insert the primary key (identity field)
INSERT @Tbl
SELECT lnkStrategyNodeID IDfield,

--partition over the unique constraints - order by is required and logically you should use the lowest ID field
ROW_NUMBER() OVER(PARTITION BY StrategyID,NodeID ORDER BY lnkStrategyNodeID) Rw
FROM lnkStrategyNode


--delete anything that is > 1
--SELECT * 
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

SQL

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.
=========================================================

plz visit here this tutorial may be helpful to you.
http://www.javatpoint.com/sql-delete-duplicate-rows[^]

The following error occurred while trying to connect database
ora-00257 archiver error solution in sql developer

need solution for sql developer Confused | :confused:

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.

Wrong is evil and must be defeated.
- Jeff Ello[^]

VB

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.