Avoid sql code redundancy

Need help on avoiding the SQL code redundancy below is the SQL statement in which column dept10 and dept20 have same code but with little bit of difference.

Can someone help on how to avoid it, since due to that statement is taking too long to run.

Need help on avoid such redundancy in the sql code select dname from dept where deptno = xx.

Below is the example for such sql:

select column1, column2, (select dname from dept where deptno=10) dept10, (select dname from dept where deptno=20) dept20 from dual where column1 = xyz;

#

Below is the actual qery which i am trying to avoid the code redundancy:-

The column amt7 and amt8 are the columns have the same sql code

SELECT /*+ GATHER_PLAN_STATISTICS */ rr.receipt_no, sc.bill_id bill_id, 2 AS sNo, 50 AS pk, rr.receipt_date, a.sac_no, sc.msr_no, Decode(crtd.tax_id, '14', SUM(crtd.tax_colln_amt),0) gpen, (CASE WHEN sc.type_of_bill = 'M' AND CRTD.tax_id = 14 AND SUM(CRTD.tax_colln_amt) > 0 THEN '350300402' WHEN sc.type_of_bill = 'R' AND CRTD.tax_id = 14 AND SUM(CRTD.tax_colln_amt) > 0 THEN '350300304' ELSE '350300104' END) GL_Account_code, Nvl(SUM(CASE WHEN Substr(sc.period, 1, 4) <= '2009' THEN (SELECT (CASE WHEN (SUM( Nvl(bd.bd_r_tax_amt, 0) + Nvl( bd.bd_nr_tax_amt, 0))) = 0 THEN 0 ELSE (SUM( Nvl(bd.bd_r_tax_amt, 0)+ Nvl( bd.bd_nr_tax_amt, 0)) ) END ) FROM abm_bill_detail bd, cv_ptax_outstanding b WHERE bd.bd_bill_id = sc.bill_id AND b.bill_id = bd.bd_bill_id AND b.status = 'A' AND Substr(b.bill_code, 1, 6) = Substr(b.bill_code, 1, 6) AND bd.bd_taxhead_id = 7 AND bd.isdeleted = 'N') ELSE (CASE WHEN (SELECT SUM(bdd.tax_amount) FROM cv_bill_detail bdd WHERE bdd.bill_mas_id = sc.bill_id AND bdd.tax_id = 7 AND bdd.status = 'A') = 0 THEN 1 END ) END), 0) amt7, Nvl(SUM(CASE WHEN Substr(sc.period, 1, 4) <= '2009' THEN (SELECT (CASE WHEN (SUM( Nvl(bd.bd_r_tax_amt, 0) + Nvl( bd.bd_nr_tax_amt, 0)) ) = 0 THEN 0 ELSE ( SUM( Nvl(bd.bd_r_tax_amt, 0) + Nvl( bd.bd_nr_tax_amt, 0))) END ) FROM abm_bill_detail bd, cv_ptax_outstanding b WHERE bd.bd_bill_id = sc.bill_id AND b.bill_id = bd.bd_bill_id AND b.status = 'A' AND Substr(b.bill_code, 1, 6) = Substr(b.bill_code, 1, 6) AND bd.bd_taxhead_id = 8 AND bd.isdeleted = 'N') ELSE (CASE WHEN (SELECT SUM(bdd.tax_amount) FROM cv_bill_detail bdd WHERE bdd.bill_mas_id = sc.bill_id AND bdd.tax_id = 8 AND bdd.status = 'A') = 0 THEN 0 END) END), 0) amt8, CFC.fc_rrloc_id FUND, CFC.fc_fund_center FCTR, CFC.fc_business_area BA, '99100000000' FUNCTIONCODE, '11' FUNDCODE, rr.receipt_date AS CollDate, rr.created_at FROM cv_receipt_sac_detail sc, cv_receipt_master rr, cv_assessment a, cv_fund_collection cfc, cv_receipt_tax_detail crtd WHERE rr.rct_id = sc.rct_id AND a.cvp_id = sc.cvp_id AND cfc.fc_rrloc_id = a.ward_id AND crtd.rct_det_id = sc.rcpt_det_id AND crtd.tax_id = 14 AND rr.status = 'A' AND sc.status = 'A' AND ( a.status = 'A' OR a.status = 'O' ) AND crtd.status = 'A' GROUP BY CFC.fc_rrloc_id, CFC.fc_fund_center, rr.created_at, CFC.fc_business_area, rr.receipt_date, crtd.tax_id, crtd.tax_colln_amt, a.sac_no, sc.msr_no, rr.receipt_no, rr.receipt_date, sc.bill_id, sc.period, sc.type_of_bill;

Below is the Explain plan for it:--

---------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SORT AGGREGATE | | 257K| 1 | 257K|00:00:27.81 | 2153K| 1 | | | | |* 2 | TABLE ACCESS BY INDEX ROWID | ABM_BILL_DETAIL | 257K| 1 | 366K|00:00:24.27 | 2153K| 1 | | | | | 3 | NESTED LOOPS | | 257K| 1 | 4369K|00:01:44.38 | 1694K| 1 | | | | |* 4 | TABLE ACCESS BY INDEX ROWID | CV_PTAX_OUTSTANDING | 257K| 1 | 380K|00:00:06.82 | 924K| 1 | | | | |* 5 | INDEX RANGE SCAN | INDX_BILLID_PO | 257K| 1 | 408K|00:00:03.75 | 516K| 0 | | | | |* 6 | INDEX RANGE SCAN | INDX_ABD_BD_BILL_ID | 380K| 1 | 3730K|00:00:03.91 | 770K| 0 | | | | | 7 | SORT AGGREGATE | | 1679K| 1 | 1679K|00:00:57.53 | 5261K| 0 | | | | |* 8 | TABLE ACCESS BY INDEX ROWID | CV_BILL_DETAIL | 1679K| 1 | 1646K|00:00:52.02 | 5261K| 0 | | | | |* 9 | INDEX RANGE SCAN | INDX_BILL_MAS_ID | 1679K| 11 | 16M|00:00:18.16 | 3397K| 0 | | | | | 10 | SORT AGGREGATE | | 257K| 1 | 257K|00:36:19.20 | 4528K| 414K| | | | |* 11 | TABLE ACCESS BY INDEX ROWID | ABM_BILL_DETAIL | 257K| 1 | 366K|00:36:14.84 | 4528K| 414K| | | | | 12 | NESTED LOOPS | | 257K| 1 | 4369K|05:15:33.92 | 4069K| 292K| | | | |* 13 | TABLE ACCESS BY INDEX ROWID| CV_PTAX_OUTSTANDING | 257K| 1 | 380K|00:14:31.47 | 3299K| 196K| | | | |* 14 | INDEX RANGE SCAN | INDX_BILLID_PO | 257K| 1 | 408K|00:04:58.24 | 2140K| 86191 | | | | |* 15 | INDEX RANGE SCAN | INDX_ABD_BD_BILL_ID | 380K| 1 | 3730K|00:10:32.73 | 770K| 95900 | | | | | 16 | SORT AGGREGATE | | 1679K| 1 | 1679K|00:32:25.01 | 5262K| 571K| | | | |* 17 | TABLE ACCESS BY INDEX ROWID| CV_BILL_DETAIL | 1679K| 1 | 1646K|00:32:18.54 | 5262K| 571K| | | | |* 18 | INDEX RANGE SCAN | INDX_BILL_MAS_ID | 1679K| 11 | 16M|00:08:35.52 | 3398K| 135K| | | | | 19 | HASH GROUP BY | | 1 | 1607K| 680K|00:00:37.33 | 17M| 1251K| 106M| 7852K| 127M (0)| | 20 | VIEW | | 1 | 1607K| 1941K|00:00:30.86 | 17M| 1251K| | | | | 21 | HASH GROUP BY | | 1 | 1607K| 1941K|00:00:28.92 | 17M| 1251K| 283M| 15M| 283M (0)| | 22 | VIEW | | 1 | 1607K| 1941K|00:00:22.40 | 17M| 1251K| | | | | 23 | HASH GROUP BY | | 1 | 1607K| 1941K|00:00:16.57 | 17M| 1251K| 295M| 15M| 301M (0)| |* 24 | HASH JOIN | | 1 | 1607K| 1941K|00:01:10.72 | 314K| 266K| 1011K| 1011K| 1231K (0)| | 25 | TABLE ACCESS FULL | CV_FUND_COLLECTION | 1 | 30 | 32 |00:00:00.02 | 3 | 2 | | | | |* 26 | HASH JOIN | | 1 | 1607K| 1941K|00:01:06.82 | 314K| 266K| 39M| 4846K| 53M (0)| |* 27 | TABLE ACCESS FULL | CV_RECEIPT_MASTER | 1 | 657K| 669K|00:00:00.01 | 9176 | 0 | | | | |* 28 | HASH JOIN | | 1 | 1861K| 1941K|00:01:05.64 | 305K| 266K| 16M| 2506K| 24M (0)| |* 29 | TABLE ACCESS FULL | CV_ASSESSMENT | 1 | 310K| 309K|00:00:00.31 | 6027 | 0 | | | | |* 30 | HASH JOIN | | 1 | 1861K| 1941K|00:00:59.14 | 299K| 266K| 67M| 6159K| 103M (0)| |* 31 | TABLE ACCESS FULL | CV_RECEIPT_TAX_DETAIL | 1 | 1859K| 1942K|00:00:29.15 | 268K| 266K| | | | |* 32 | TABLE ACCESS FULL | CV_RECEIPT_SAC_DETAIL | 1 | 1822K| 1951K|00:00:07.81 | 30755 | 28 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("BD"."BD_TAXHEAD_ID"=7 AND "BD"."ISDELETED"='N')) 4 - filter((SUBSTR("B"."BILL_CODE",1,6)=SUBSTR("B"."BILL_CODE",1,6) AND "B"."STATUS"='A')) 5 - access("B"."BILL_ID"=:B1) 6 - access("B"."BILL_ID"="BD"."BD_BILL_ID") filter("BD"."BD_BILL_ID"=:B1) 8 - filter(("BDD"."TAX_ID"=7 AND "BDD"."STATUS"='A')) 9 - access("BDD"."BILL_MAS_ID"=:B1) 11 - filter(("BD"."BD_TAXHEAD_ID"=8 AND "BD"."ISDELETED"='N')) 13 - filter((SUBSTR("B"."BILL_CODE",1,6)=SUBSTR("B"."BILL_CODE",1,6) AND "B"."STATUS"='A')) 14 - access("B"."BILL_ID"=:B1) 15 - access("B"."BILL_ID"="BD"."BD_BILL_ID") filter("BD"."BD_BILL_ID"=:B1) 17 - filter(("BDD"."TAX_ID"=8 AND "BDD"."STATUS"='A')) 18 - access("BDD"."BILL_MAS_ID"=:B1) 24 - access("CFC"."FC_RRLOC_ID"="A"."WARD_ID") 26 - access("RR"."RCT_ID"="SC"."RCT_ID") 27 - filter("RR"."STATUS"='A') 28 - access("SC"."CVP_ID"=TO_NUMBER("A"."CVP_ID")) 29 - filter(("A"."STATUS"='A' OR "A"."STATUS"='O')) 30 - access("CRTD"."RCT_DET_ID"="SC"."RCPT_DET_ID") 31 - filter(("CRTD"."TAX_ID"=14 AND "CRTD"."STATUS"='A')) 32 - filter("SC"."STATUS"='A')

-------------Problems Reply------------

SELECT *
FROM dual
CROSS JOIN (
SELECT * FROM dept
PIVOT(
max(dname) FOR (deptno) IN (10 as Dept10, 20 as dept20 )
))
/* WHERE column1 = 2 */

Demo: http://sqlfiddle.com/#!4/76c878/1

It is very hard to give a better query to a sample that is just using dual, but basically instead of doing a query in your result set you should make use of joins. That will allow the db to select the lowest cost method of joining the tables. Below is an attempt at an example with what you provided but really doesn't make much sense because dual is being used. Your query above basically just runs 3 independent queries together, normally you would have them joined together by some related column (XXX). If not then why even run them as 1 query, run them separately.

select column1, column2, d10.dname dept10, d20.dname dept20
from dual left outer join dept d10 on dual.XXX = d10.XXX and d10.deptno = 10
left outer join dept d20 on dual.XXX = d20.XXX and d20.deptno = 20
where column1 = xyz;

Running explain plans on your queries really helps you see what the database is actually doing with your query. When you see full range scans on a large table and the query is slow then that could be a signal that you need an index.

query logic is not easy to understand and there is no common way to avoid redundancy. Also there is no way to test result and performance. Here is an example how query could be changed:

WITH
sc as
(select /*+ MATERIALIZE */ bill_id, type_of_bill, Substr(period, 1, 4) period, msr_no, rct_id, cvp_id
FROM cv_receipt_sac_detail
WHERE status= 'A'
GROUP BY bill_id, type_of_bill, Substr(period, 1, 4), msr_no, rct_id, cvp_id
)
qamt1 as
(SELECT /*+ MATERIALIZE */ a.bill_id,
SUM( decode(bd.bd_taxhead_id, 8, Nvl(bd.bd_r_tax_amt, 0) + Nvl( bd.bd_nr_tax_amt, 0)),0) as q1_amt8,
SUM( decode(bd.bd_taxhead_id, 7, Nvl(bd.bd_r_tax_amt, 0) + Nvl( bd.bd_nr_tax_amt, 0)),0) as q1_amt7
FROM abm_bill_detail bd,
cv_ptax_outstanding b,
sc a
WHERE bd.bd_bill_id = cs.bill_id
AND b.bill_id = bd.bd_bill_id
AND b.status = 'A'
AND Substr(b.bill_code, 1, 6) = Substr(b.bill_code, 1, 6)
AND bd.bd_taxhead_id IN (8,7)
AND bd.isdeleted = 'N'
),
qamt2 as
(SELECT /*+ MATERIALIZE */ a.bill_id,
SUM( decode(bd.bd_taxhead_id, 8, SUM(bdd.tax_amount), 0)) q2_amt8,
SUM( decode(bd.bd_taxhead_id, 7, SUM(bdd.tax_amount), 0)) q2_amt7,
FROM cv_bill_detail bdd,
sc a
WHERE bdd.bill_mas_id = a.bill_id
ANDbd.bd_taxhead_id IN (8,7)
AND bdd.status = 'A'
)
SELECT /*+ GATHER_PLAN_STATISTICS */ rr.receipt_no,
sc.bill_id bill_id,
2 AS sNo,
50 AS pk,
rr.receipt_date,
a.sac_no,
sc.msr_no,
Decode(crtd.tax_id, '14', SUM(crtd.tax_colln_amt),0) gpen,
(CASE WHEN sc.type_of_bill = 'M' AND CRTD.tax_id = 14 AND SUM(CRTD.tax_colln_amt) > 0 THEN '350300402'
WHEN sc.type_of_bill = 'R' AND CRTD.tax_id = 14 AND SUM(CRTD.tax_colln_amt) > 0 THEN '350300304'
ELSE '350300104'
END) GL_Account_code,
Nvl(SUM(CASE WHEN sc.period <= '2009' THEN q1_amt7
ELSE (CASE WHEN q2_amt7 = 0 THEN 1 END )
END), 0) amt7,
Nvl(SUM(CASE WHEN sc.period <= '2009' THEN q1_amt8
ELSE (CASE WHEN q2_amt8 = 0 THEN 0 END )
END), 0) amt8,
CFC.fc_rrloc_id FUND,
CFC.fc_fund_center FCTR,
CFC.fc_business_area BA,
'99100000000' FUNCTIONCODE,
'11' FUNDCODE,
rr.receipt_date AS CollDate,
rr.created_at
FROM sc,
qamt1,
qamt2,
cv_receipt_master rr,
cv_assessment a,
cv_fund_collection cfc,
cv_receipt_tax_detail crtd
WHERE rr.rct_id = sc.rct_id
AND sc.bill_id = qamt1.bill_id(+)
AND sc.bill_id = qamt2.bill_id(+)
AND a.cvp_id = sc.cvp_id
AND cfc.fc_rrloc_id = a.ward_id
AND crtd.rct_det_id = sc.rcpt_det_id
AND crtd.tax_id = 14
AND rr.status = 'A'
AND a.status IN ( 'A', 'O' )
AND crtd.status = 'A'
GROUP BY CFC.fc_rrloc_id,
CFC.fc_fund_center,
rr.created_at,
CFC.fc_business_area,
rr.receipt_date,
crtd.tax_id,
crtd.tax_colln_amt,
a.sac_no,
sc.msr_no,
rr.receipt_no,
rr.receipt_date,
sc.bill_id,
sc.period,
sc.type_of_bill;

Category:sql Views:10 Time:2019-01-12

Related post

  • Algorithm to avoid SQL injection on MSSQL Server from C# code? 2008-10-30

    What would be the best way to avoid SQL injection on the C#.net platform. Please post an C# implementation if you have any. --------------Solutions------------- Top 10 things we can do to be safe (No one of these will do it all.) Adopt the notion tha

  • Best way to avoid SQL injection on MSSQL Server from C# code using Linq? 2008-10-30

    What is the best way to avoid SQL injection on MSSQL Server from C# code using Linq? Should you use a function to strip of security issues or is it handled by the framework? --------------Solutions------------- Errr, you cant SQL inject using LINQ. B

  • Avoiding SQL Injection in SQL query with Like Operator using parameters? 2008-10-23

    Taking over some code from my predecessor and I found a query that uses the Like operator: SELECT * FROM suppliers WHERE supplier_name like '%'+name+%'; Trying to avoid SQL Injection problem and parameterize this but I am not quite sure how this woul

  • How can I avoid SQL injection attacks in my ASP.NET application? 2008-11-20

    I need to avoid being vulnerable to SQL injection in my ASP.NET application. How might I accomplish this? --------------Solutions------------- Even though your question is very generic, a few rules always apply: Use parameterized queries (SqlCommand

  • SQL code to insert multiple rows in ms-access table 2009-07-31

    I'm trying to speed up my code and the bottleneck seems to be the individual insert statements to a Jet MDB from outside Access via ODBC. I need to insert 100 rows at a time and have to repeat that many times. It is possible to insert multiple rows i

  • How can I avoid SQL injection attacks? 2010-02-04

    Yesterday I was speaking with a developer, and he mentioned something about restricting the insertions on database field, like, strings such as -- (minus minus). At the same type, what I know is that is a good approach to escape HTML chars like <,

  • How to avoid Java Code in JSP-Files? 2010-07-05

    I'm new to Java EE and I know that something like the following three lines <%= x+1 %> <%= request.getParameter("name") %> <%! counter++; %> is an oldschool way of coding and in JSP version 2 there exists a method to avoid Java code

  • How to avoid SQL Injection when using '[' and ']' characters for schema/table names? 2010-10-23

    I have had several contexts where table names or schemas were not hard-coded, but rather configured by the administrator, or, worse, generated from user input. Since cases were easy (schemas and table names in plain English, without numbers nor symbo

  • how to avoid sql injection in codeigniter 2011-05-02

    In CodeIgniter, how can I avoid sql injection? Is there any method to set in config file to avoid sql injection? I am using this code for selecting values: $this->db->query("SELECT * FROM tablename WHERE var='$val1'"); and this for inserting va

  • The safest way to avoid SQL injection in PHP? 2011-05-06

    I just wonder if this line of code is safe to use to avoid SQL injection? // username and password sent from form $myusername=$_POST['loginUserName']; $mypassword=$_POST['loginPassword']; $myusername = stripslashes($myusername); $mypassword = stripsl

  • Should numbers from user input be quoted in MySQL queries to help avoid SQL injection attacks? 2011-06-07

    Should numbers from user input be quoted in MySQL queries to help avoid SQL injection attacks? Say i have a form on a page asking for someone's age. They enter their age and hit submit. The following php code deals with the form submission: (age is a

  • How to retrieve executed SQL code from SQLAlchemy 2011-06-14

    I am using SQLAlchemy and would like to log executed SQL code (i.e. the code with all bind parameters already quoted and replaced). In case of psycopg2 it was possible using the query attribute of the Cursor object (see psycopg documentation). In cas

  • php - mysql avoid sql injections 2011-08-18

    I have many functions like updateUser($id,$username,$email) updateMusic($id, $music) etc... Is there a generic function to avoid SQL injections ? I just want to avoid using mysql_real_escape_string for each parameter I have $username = mysql_real_esc

  • Organization of SQL code in a PHP library 2011-10-21

    Is it better to put all the SQL code into one library than scatter it about your web site? I.e. effeciency in not loading library compared to knowing where and what the SQL code is? --------------Solutions------------- If you're talking about initial

  • Export to Excel and PDF in PHP, Causing code redundancy 2012-01-27

    I need to create export to excel and export to pdf option for some of my mysql queries and provide it to the user. But also, the user needs to view the query results on my website too. So, in this case, code redundancy occurs , as I have to create th

  • How to validate integer values to avoid SQL injection? 2012-03-12

    The best way to avoid SQL injection for defined value type such as numbers, is to validate the value; since it is easier to do so comparing with mysqli preparation. In PHP, we can do this by. 1. if(!is_numeric($value)) {$value=0;} 2. $value=floatval(

  • Combo box record source sql code correction 2013-07-16

    I have a table Name : EmpTabA - with the following fields EmpFormNo - Number - Primarykey EmpName - Text EmpCode - Text EmpProf - Text It has 40 employee records I have another table name : BookTabA BookFormNo - Number - Primary key BookDate - Date/T

  • What is a good tool to get SQL code completion with SQL Server 2005? 2008-10-21

    I've tried Red Gate's SQL Prompt and like it, but cannot afford it at the moment. I miss the good code completion that this tool provides. Are there any good free tools out there that have SQL code completion (table and column names, etc)? I've tried

  • How to read the PL/SQL code in an Oracle Forms .FMT file? 2008-10-23

    Oracle Forms10g provides a tool to convert the Oracle Forms modules, from the binary format (.FMB) that Oracle Forms Builder works with, to text format (.FMT). For example, if you create a module called mymodule.fmb with Oracle Forms Builder, and the

  • Can I use Visual Studio Snippets to generate SQL code 2008-11-06

    I want to use Visual Studio snippets to generate SQL code, for example we have standard naming conventions for foreign keys etc and it would be great if I could just expand a snippet in my SQL script file. However as far as I can tell the only langua

  • how to translate the SQL code "having" condition into LinqToSQL or LinqToEntites? 2008-11-26

    Could you tell me how to translate the following SQL code to Linq To SQL or Linq To Entites? The correct SQL code is: select CollectId,url,userid,pubtime from Collect group by url,collectid,userid,pubtime having pubtime >= (select max(pubtime) fro

  • Writing SQL code: same functionality as Yell.com 2009-01-22

    Can anyone help me with the trying to write SQL (MS SqlServer) - I must admit this is not by best skill. What I want to do is exactly the same functionality as appears for the search boxes for the Yell website i.e. Search for company type AND/OR comp

  • How do I run SQL code using c# 3.5? 2009-02-13

    I have an HTML textbox that contains some SQL code that I need executed. I am able to retrieve the actual code from the textbox but I am not sure how to go about executing the code. Any simple and elegant ways using c# 3.5? --------------Solutions---

  • Parsing SQL code in C# 2009-02-26

    I want to parse SQL code using C#. Specifically, is there any freely available parser which can parse SQL code and generate a tree or any other structure out of it? It should also generate the proper tree for nested structures. It should also return

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.101 (s). 11 q(s)