This afternoon, while I am doing some Jasper report stuff, I am puzzled by a SQL query that which takes quite a long time to execute. As the time goes, Jasper will easily get a timeout and a blank screen is waiting for me after retrieving data for about 120 seconds.
As it is not appropriate to use the original data table as the example to describe here, I tried to make up a similar scenario which should explain the cause and the solution sufficiently.
Given a table A, with five columns -- Primary Key (pk), Statistics 1A (s1a), Statistics 1B (s1b), Statistics 2A(s2a), Statistics 2B(s2b), we need to select the data out into a form that looks like -- Primary Key (pk), Statistics A(s1a or s2a), Statistics B(s2b or s2b). Essentially, it is trying to convert a row into one column but two rows. Someone may prefer to call it pivot query by the way.
Initially, naively I think the most easiest and intuitive way to do it is by two union queries. That is
select pk, s1a, s1b from table where *** union select pk, s2a, s2b from table where ***.
When the data set is small, and it only depends on the single table, it is fine. However, in my case, the data set is huge, and the union will not be two but eight, and more importantly every union section will consists of another 6 inner join tables. That's the reason why Jasper cannot retrieve what it needs on time.
In this link, as suggested by Scott, introduced a very smart way to handle this issue. What it uses is called: cross join. So basically, we use the existing table to cross join with the different type/criteria to return the expected result.
select
pk,
case
when ite = 's1' then s1a
when ite = 's2' then s2a
end as sa
case
when ite = 's1' then s1b
when ite = 's2' then s2b
end as sb
from
(
select pivoter.ite,
s1a, s1b, s2a, s2b
from
table
cross join (
select 's1' as ite from dual
union all
select 's2' as ite from dual
) pivotel
)
By using the above code, the problem will be solved by the cross join union columns.
Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this "Devops Training in Bangalore"
ReplyDeleteThe effectiveness of IEEE Project Domains depends very much on the situation in which they are applied. In order to further improve IEEE Final Year Project Domains practices we need to explicitly describe and utilise our knowledge about software domains of software engineering Final Year Project Domains for CSE technologies. This paper suggests a modelling formalism for supporting systematic reuse of software engineering technologies during planning of software projects and improvement programmes in Final Year Projects for CSE.
DeleteSoftware management seeks for decision support to identify technologies like JavaScript that meet best the goals and characteristics of a software project or improvement programme. JavaScript Training in Chennai Accessible experiences and repositories that effectively guide that technology selection are still lacking.
Aim of technology domain analysis is to describe the class of context situations (e.g., kinds of JavaScript software projects) in which a software engineering technology JavaScript Training in Chennai can be applied successfully
The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training
http://www.computertraininggeorgia.com/2012/04/10-off-pmp-course-nhatlanta-enroll.html
ReplyDeleteGo with us on the online casino BGAOC and most likely let's win. perfect gambling websites Do not miss the gambling chance to become a great player.
ReplyDeleteThank you for this wonderful post! You are sharing so much of valid information. Keep us updated with more such articles.
ReplyDeletePlacement Training in Chennai
Best Training and Placement institutes in Chennai
Oracle Training in Chennai
Oracle Training institute in chennai
Unix Training in Chennai
Unix Shell Scripting Training in Chennai
Placement Training in Tambaram
Placement Training in Velachery
Thanks to share so many intersting contents.
ReplyDeletehonor mobile service center
honor mobile service centre in chennai
honor mobile service centre
honor service center near me
honor service
Thanks for posting this information. Keep updating.
ReplyDeleteSpoken English Classes in Chennai
Spoken English in Chennai
German Classes in Chennai
Japanese Classes in Chennai
TOEFL Coaching in Chennai
Informatica Training in Chennai
Spoken English Classes in Adyar
Spoken English Classes in Velachery
Excellent post, it will be definitely helpful for many people. Keep posting more like this.
ReplyDeleteSalesforce Training in Chennai
Salesforce Course in Chennai
Salesforce Training
AngularJS Training in Chennai
ccna course in Chennai
ReactJS Training in Chennai
Tally course in Chennai
Salesforce Training in Velachery
Salesforce Training in T Nagar
Salesforce Training in OMR
Delhi Mathura Vrindavan Tour by Bus
ReplyDeleteAgra Mathura Tour Package by Bus
Delhi to Agra tour by Volvo bus
Online Bus Ticket Booking for Agra
Same Day Agra Tour
Same Day Agra Tour by Bus
Informative post indeed, I’ve being in and out reading posts regularly and I see alot of engaging people sharing things and majority of the shared information is very valuable and so, here’s my fine read.
ReplyDeleteclick here for more details
click here for new registration
click here to login
click here 4th std
click here icon
Great blog thanks for sharing Consistent and relevant content is the best ways to drive in massive amounts of traffic to your website through Search Engines. Adhuntt Media’s content creation team has got everything covered for you - from high-grade blogs that engage the reader and hit all the sweet SEO spots, unique design posts for your social media feed and professional videos for video marketing.
ReplyDeletedigital marketing company in chennai
Nice blog thanks for sharing Re-decorate your messy lawn and fix up your boring old garden with Karuna Nursery Gardens. We offer speciality Garden maintenance services in Chennai. It’s time you made a difference to your backyard.
ReplyDeleteplant nursery in chennai
Excellent blog thanks for sharing Run your salon business successfully by tying up with the best beauty shop in Chennai - The Pixies Beauty Shop. With tons of prestigious brands to choose from, and amazing offers we’ll have you amazed.
ReplyDeleteCosmetics Shop in Chennai
And also thanks for sharing this informative. Keep it Sir and I am waiting for your next post on your site blog.
ReplyDeleteBest Training Institute in Bangalore BTM. My Class Training Bangalore training center for certified course, learning on Software Training Course by expert faculties, also provides job placement for fresher, experience job seekers.
Software Training Institute in Bangalore
Thank you so much for the great and very beneficial stuff that you have shared with the world.
ReplyDeleteLearn Hadoop Training from the Industry Experts we bridge the gap between the need of the industry. Softgen Infotech provide the Best Hadoop Training in Bangalore with 100% Placement Assistance. Book a Free Demo Today.
Big Data Analytics Training in Bangalore
Tableau Training in Bangalore
Data Science Training in Bangalore
Workday Training in Bangalore
This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me..
ReplyDeleteDevOps Training | Certification in Chennai | DevOps Training | Certification in anna nagar | DevOps Training | Certification in omr | DevOps Training | Certification in porur | DevOps Training | Certification in tambaram | DevOps Training | Certification in velachery
Wonderful blog with great piece of information. Regards to your effort. Keep sharing more such blogs. Looking forward to learn more from you.
ReplyDeleteangular js training in chennai
angular js online training in chennai
angular js training in bangalore
angular js training in hyderabad
angular js training in coimbatore
angular js training
angular js online training
Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area.
ReplyDeleteCyber Security Training Course in Chennai | Certification | Cyber Security Online Training Course | Ethical Hacking Training Course in Chennai | Certification | Ethical Hacking Online Training Course |
CCNA Training Course in Chennai | Certification | CCNA Online Training Course | RPA Robotic Process Automation Training Course in Chennai | Certification | RPA Training Course Chennai | SEO Training in Chennai | Certification | SEO Online Training Course
Thanks mate. I am really impressed with your writing talents and also with the layout on your weblog. Appreciate, Is this a paid subject matter or did you customize it yourself? Either way keep up the nice quality writing, it is rare to peer a nice weblog like this one nowadays. Thank you, check also event marketing and Strategies on Choosing the Best Event Planning Niche
ReplyDeleteI will share it with my other friends as the information is really very useful. Keep sharing your excellent work.African Cosmetics Online Shop
ReplyDeleteno deposit bonus forex 2021 - takipçi satın al - takipçi satın al - takipçi satın al - tiktok takipçi satın al - instagram beğeni satın al - instagram beğeni satın al - google haritalara yer ekleme - btcturk güvenilir mi - izlenme-satin-al.com - numarasmsonay.com - borsagazete.com - takipcisatinals.com - izlenme-satin-al.com/youtube - google haritalara yer ekleme - altyapısız internet - mikrofiber havlu - forexbonus2020.com - tiktok jeton hilesi - tiktok beğeni satın al - microsoft word ücretsiz indir - misli apk indir - binance güvenilir mi - takipçi satın al - mikrofiber havlu - uc satın al - takipçi satın al - takipçi satın al - finanspedia.com
ReplyDeleteinstagram takipçi satın al
ReplyDeleteinstagram takipçi satın al
takipçi satın al
instagram takipçi satın al
takipçi satın al
aşk kitapları
tiktok takipçi satın al
instagram beğeni satın al
youtube abone satın al
twitter takipçi satın al
tiktok beğeni satın al
tiktok izlenme satın al
twitter takipçi satın al
tiktok takipçi satın al
youtube abone satın al
tiktok beğeni satın al
instagram beğeni satın al
trend topic satın al
trend topic satın al
youtube abone satın al
beğeni satın al
tiktok izlenme satın al
sms onay
youtube izlenme satın al
tiktok beğeni satın al
sms onay
sms onay
perde modelleri
instagram takipçi satın al
takipçi satın al
tiktok jeton hilesi
pubg uc satın al
sultanbet
marsbahis
betboo
betboo
betboo
takipçi satın al
ReplyDeleteinstagram takipçi satın al
https://www.takipcikenti.com
marsbahis
ReplyDeletebetboo
sultanbet
marsbahis
betboo
sultanbet
yasaklı sitelere giriş
ReplyDeletepepsi kodları
wall hack kodu
ücretsiz antivirüs programları
yeni kimlik yenileme ücreti
internetsiz oyunlar
en hızlı dns sunucuları
hız testi
tiktok jeton hilesi
thanks admin great article HDE Bilişim
ReplyDeleteAlışveriş
Compo Expert
Multitek
Seokoloji
Vezir Sosyal Medya
Adak
Maltepe Adak
thanks admin good post marsbahis
ReplyDeletetrendbet
galabet
maltcasino
marsbahis
trendbet
maltcasino
galabet
Casino games for real money, free chips & bonuses - Dr
ReplyDeletePlay the best online 수원 출장안마 casino games 남양주 출장마사지 for real money, free chips & bonuses online! Deposit and play online slots for 서귀포 출장안마 free. How to 여수 출장안마 deposit money into casino games · How to 포커
smm panel
ReplyDeletesmm panel
faturalı hatta kredi
faturalı hatta kredi
instagram takipçi satın al
binance komisyon indirimi
binance indirim kodu
instagram takipçi satın al
tiktok jeton hilesi
bitcoin nasıl alınır
ReplyDeletetiktok jeton hilesi
youtube abone satın al
gate io güvenilir mi
referans kimliği nedir
tiktok takipçi satın al
bitcoin nasıl alınır
mobil ödeme bozdurma
mobil ödeme bozdurma
mmorpg oyunlar
ReplyDeleteinstagram takipçi satın al
tiktok jeton hilesi
Tiktok Jeton Hilesi
SAC EKİMİ ANTALYA
referans kimliği nedir
İnstagram Takipçi Satın Al
MT2 PVP SERVERLER
instagram takipçi satın al
smm panel
ReplyDeletesmm panel
iş ilanları
instagram takipçi satın al
hirdavatciburada.com
beyazesyateknikservisi.com.tr
SERVİS
TİKTOK PARA HİLESİ
Good
ReplyDeletehttps://www.digisnare.com/
Adobe Creative Cloud Crack 2022 all apps download for Mac and Windows.like “Adobe Creative Cloud free download full version crack” and don't share them.Adobe inventive Cloud 5.7.1.1 Crack
ReplyDeleteRadarOpus homeopathic software has an insightful analysis section that helps users to quickly sort out the symptoms as per their requirements.How RadarOpus is the future of homeopathic software?
ReplyDelete