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"
ReplyDeletehttp://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
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
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
I 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
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
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