3 Apr 2008

SQL is in Fact a Programming Language

While I was updating my blog recently, I read the top part of it which said "My adventures in database developing". I realized that I have really neglected database developing with all my research into other programming languages.
So I was trying to play around with SQL statements to brush up on my skills.

Luckily (or unluckily), someone at my work, a java developer, needed to do a data export and thought that I can do it in 1 SQL line. He said.... and I quote "You only need 1 SQL line. Whats the problem?".
Whenever I hear the "whats the problem" line, there usually is one.
So, with him having great confidence in my SQL-ing ability or a complete misunderstanding of database developing, I was forced to do the data export.

I realized early on that it would take a long time. Just to start off, I needed like 20+ columns, but each column was a row somewhere in 4 different tables.


To make a long story short, I did complete the task and to simply pat myself on the back, I will paste it here. Just to show that, yes, you can program in SQL.

(but next time, please give me rights on the server to do it with several views).


The SQL statement is about 2-3 pages long, by the way and you don't really need to read all of the statement






SELECT
CONTACT.CON_ID as contact_id,
contact.CON_EMAIL_LOWER as email,
contact.CON_CREATION_DATE as creation_date,
lastname.CONATTR_VALUE as givenname,
firstname.CONATTR_VALUE as familyname,
languages.CONATTR_VALUE as "LANGUAGE",
gender2.SELVAL_VALUE as gender,
mobile.CONATTR_VALUE as mobilephone,
office.CONATTR_VALUE as officephone,
home.CONATTR_VALUE as home,
address.CONATTR_VALUE as address,
zipcode.CONATTR_VALUE as zipcode,
province2.SELVAL_VALUE as province,
city2.SELVAL_VALUE as city,
intendedpurchase2.SELVAL_VALUE as intendedpurchase,
ownedcarbrand2.SELVAL_VALUE as ownedcarbrand,
minimodel3.SELVAL_VALUE as minimodel,
dealer3.SELVAL_VALUE as dealer,
subject3.SELVAL_VALUE as subject,
SUBSCRIPTION.sub_id as subscription_id,
brochure13.SELVAL_VALUE as brochure1,
brochure23.SELVAL_VALUE as brochure2,
brochure33.SELVAL_VALUE as brochure3,
message2.CAMATTR_VALUE as message
from CONTACT contact
left join CONTACT_ATTRIBUTE lastname on lastname.CONATTR_CON_ID = CONTACT.CON_ID and lastname.CONATTR_ATTR_ID = 1010
left join CONTACT_ATTRIBUTE firstname on firstname.CONATTR_CON_ID = CONTACT.CON_ID and firstname.CONATTR_ATTR_ID = 1020
left join CONTACT_ATTRIBUTE languages on languages.CONATTR_CON_ID = CONTACT.CON_ID and languages.CONATTR_ATTR_ID = 1000
left join
(SELECTION_VALUE gender2 INNER JOIN CONTACT_ATTRIBUTE gender on gender.CONATTR_SEL_ID=gender2.SELVAL_SEL_ID and gender2.SELVAL_LANGUAGE = 'zh' )
on gender.CONATTR_CON_ID = CONTACT.CON_ID and gender.CONATTR_ATTR_ID = 1030
left join CONTACT_ATTRIBUTE mobile on mobile.CONATTR_CON_ID = CONTACT.CON_ID and mobile.CONATTR_ATTR_ID = 1100
left join CONTACT_ATTRIBUTE office on office.CONATTR_CON_ID = CONTACT.CON_ID and office.CONATTR_ATTR_ID = 1110
left join CONTACT_ATTRIBUTE home on home.CONATTR_CON_ID = CONTACT.CON_ID and home.CONATTR_ATTR_ID = 1120
left join CONTACT_ATTRIBUTE address on address.CONATTR_CON_ID = CONTACT.CON_ID and address.CONATTR_ATTR_ID = 1200
left join CONTACT_ATTRIBUTE zipcode on zipcode.CONATTR_CON_ID = CONTACT.CON_ID and zipcode.CONATTR_ATTR_ID = 1210
left join
(SELECTION_VALUE province2 INNER JOIN CONTACT_ATTRIBUTE province on province.CONATTR_SEL_ID=province2.SELVAL_SEL_ID and province2.SELVAL_LANGUAGE = 'zh' )
on province.CONATTR_CON_ID = CONTACT.CON_ID and province.CONATTR_ATTR_ID = 510
left join (SELECTION_VALUE city2 INNER JOIN CONTACT_ATTRIBUTE city on city.CONATTR_SEL_ID=city2.SELVAL_SEL_ID and city2.SELVAL_LANGUAGE = 'zh' )
on city.CONATTR_CON_ID = CONTACT.CON_ID and city.CONATTR_ATTR_ID = 520
left join (SELECTION_VALUE intendedpurchase2 INNER JOIN CONTACT_ATTRIBUTE intendedpurchase on intendedpurchase.CONATTR_SEL_ID=intendedpurchase2.SELVAL_SEL_ID and intendedpurchase2.SELVAL_LANGUAGE = 'zh' )
on intendedpurchase.CONATTR_CON_ID = CONTACT.CON_ID and intendedpurchase.CONATTR_ATTR_ID = 1300
left join (SELECTION_VALUE ownedcarbrand2 INNER JOIN CONTACT_ATTRIBUTE ownedcarbrand on ownedcarbrand.CONATTR_SEL_ID=ownedcarbrand2.SELVAL_SEL_ID and ownedcarbrand2.SELVAL_LANGUAGE = 'zh' )
on ownedcarbrand.CONATTR_CON_ID = CONTACT.CON_ID and ownedcarbrand.CONATTR_ATTR_ID = 1400

left join SUBSCRIPTION on CONTACT.CON_ID=SUBSCRIPTION.SUB_CON_ID
left join
(SELECTION_VALUE minimodel3 INNER JOIN
(CAMPAIGN_ATTRIBUTE minimodel2 INNER JOIN SUBSCRIPTION minimodel on minimodel2.CAMATTR_SUB_ID=minimodel.SUB_ID)
on minimodel2.CAMATTR_ATTR_ID=52001)
on minimodel2.CAMATTR_SEL_ID = minimodel3.SELVAL_SEL_ID and minimodel3.SELVAL_LANGUAGE = 'zh' and minimodel.SUB_CON_ID=CONTACT.CON_ID
and SUBSCRIPTION.SUB_ID in (select distinct(CAMATTR_SUB_ID) from CAMPAIGN_ATTRIBUTE group by CAMATTR_SUB_ID)
left join
(SELECTION_VALUE dealer3 INNER JOIN
(CAMPAIGN_ATTRIBUTE dealer2 INNER JOIN SUBSCRIPTION dealer on dealer2.CAMATTR_SUB_ID=dealer.SUB_ID)
on dealer2.CAMATTR_ATTR_ID=52002)
on dealer2.CAMATTR_SEL_ID = dealer3.SELVAL_SEL_ID and dealer3.SELVAL_LANGUAGE = 'zh' and dealer.SUB_CON_ID=CONTACT.CON_ID
and SUBSCRIPTION.SUB_ID in (select distinct(CAMATTR_SUB_ID) from CAMPAIGN_ATTRIBUTE group by CAMATTR_SUB_ID)
left join
(SELECTION_VALUE subject3 INNER JOIN
(CAMPAIGN_ATTRIBUTE subject2 INNER JOIN SUBSCRIPTION subject on subject2.CAMATTR_SUB_ID=subject.SUB_ID)
on subject2.CAMATTR_ATTR_ID=50001)
on subject2.CAMATTR_SEL_ID = subject3.SELVAL_SEL_ID and subject3.SELVAL_LANGUAGE = 'zh' and subject.SUB_CON_ID=CONTACT.CON_ID
and SUBSCRIPTION.SUB_ID in (select distinct(CAMATTR_SUB_ID) from CAMPAIGN_ATTRIBUTE group by CAMATTR_SUB_ID)
left join
(SELECTION_VALUE brochure13 INNER JOIN
(CAMPAIGN_ATTRIBUTE brochure12 INNER JOIN SUBSCRIPTION brochure1 on brochure12.CAMATTR_SUB_ID=brochure1.SUB_ID)
on brochure12.CAMATTR_ATTR_ID=51001 and brochure12.CAMATTR_SEL_ID=5100101)
on brochure12.CAMATTR_SEL_ID = brochure13.SELVAL_SEL_ID and brochure13.SELVAL_LANGUAGE = 'zh' and brochure1.SUB_CON_ID=CONTACT.CON_ID
and SUBSCRIPTION.SUB_ID in (select distinct(CAMATTR_SUB_ID) from CAMPAIGN_ATTRIBUTE group by CAMATTR_SUB_ID)
left join
(SELECTION_VALUE brochure23 INNER JOIN
(CAMPAIGN_ATTRIBUTE brochure22 INNER JOIN SUBSCRIPTION brochure2 on brochure22.CAMATTR_SUB_ID=brochure2.SUB_ID)
on brochure22.CAMATTR_ATTR_ID=51001 and brochure22.CAMATTR_SEL_ID=5100102)
on brochure22.CAMATTR_SEL_ID = brochure23.SELVAL_SEL_ID and brochure23.SELVAL_LANGUAGE = 'zh' and brochure2.SUB_CON_ID=CONTACT.CON_ID
and SUBSCRIPTION.SUB_ID in (select distinct(CAMATTR_SUB_ID) from CAMPAIGN_ATTRIBUTE group by CAMATTR_SUB_ID)
left join
(SELECTION_VALUE brochure33 INNER JOIN
(CAMPAIGN_ATTRIBUTE brochure32 INNER JOIN SUBSCRIPTION brochure3 on brochure32.CAMATTR_SUB_ID=brochure3.SUB_ID)
on brochure32.CAMATTR_ATTR_ID=51001 and brochure32.CAMATTR_SEL_ID=5100103)
on brochure32.CAMATTR_SEL_ID = brochure33.SELVAL_SEL_ID and brochure33.SELVAL_LANGUAGE = 'zh' and brochure3.SUB_CON_ID=CONTACT.CON_ID
and SUBSCRIPTION.SUB_ID in (select distinct(CAMATTR_SUB_ID) from CAMPAIGN_ATTRIBUTE group by CAMATTR_SUB_ID)

left join
(CAMPAIGN_ATTRIBUTE message2 INNER JOIN SUBSCRIPTION message on message2.CAMATTR_SUB_ID=message.SUB_ID and message2.CAMATTR_ATTR_ID=50002)
on message.SUB_CON_ID=CONTACT.CON_ID
and SUBSCRIPTION.SUB_ID in (select distinct(CAMATTR_SUB_ID) from CAMPAIGN_ATTRIBUTE group by CAMATTR_SUB_ID)



I apologize in advance if I get anyone's feed-reader stuck.

Thank you for reading my blog.