Writing Complex SQL Queries

I have had a chance to work with some complex SQL Query in different reports, I will highlight the skillset that you will need writing such a report.

First Thing

When writing a report the field names might not be intuitive or something that you would expect. For example I wanted to include commission in my report but in the database it was call value_no. More ever it was in the table called operation. Was that intuitive? Not at all.

Get some hints from the software that uses this value. In our software it was referring to as operation value. Therefore it made sent to look it up in operation table. First I verified this value matches the value in the software for this particular contract so I knew I have to read this value.

 

The problem

The problem was attaching this value to the contract. But it was not easy. There was not direct relationship between the operation and the contract table. I had to hunt.

Methodologies

In my case, the relationship between the contract and the commission was too complicated. I simplified this bu writing a bock diagram. This block digram established a connection between the two tables. I created this in Excel, you can use Excel for similar table too.

 

Writing the Actual Query

Now that you have established a connection (the weird as it is), the task is to right the query. This part may not be as simple as you would think. Where do you start from? In the middle, left or right?

  1. Start with the left table that contains the value you need in your report.
  2. Include fewer values in the select statement, do not use *. In this case,  you are going to need value_no and comm_calc_id so use SELECT value_no,comm_calc_id from OPERATION.
  3. From this point it is just a smoothe right of just connecting the two adjecentt tables based on primary keys. But there is one problem in table 3 that I will discuss below.

In table 3 Commission_Schedule, there are two fields  that relate to SalesEntityRelate table, this makes the query a little difficult. In fact you have to use subquery at this point.

Assume you are this stage where you already joined Commission_Calcaulation table

[sql]

JOIN COMMISSION_SCHEDULE CS
ON CS.COMM_SCHEDULE_ID = CR.COMM_SCHEDULE_ID

[/sql]
What will you do next to join SalesEntityRelate table? Can I do something like this?

[sql]

JOIN COMMISSION_SCHEDULE CS
ON CS.COMM_SCHEDULE_ID = CR.COMM_SCHEDULE_ID
JOIN SalesEntityRelate SER
ON SER.Sales_Entity_Relate_ID = Relate_ID and Relate_class_nm = ‘cRelateClassName’
/* the above is wrong query because the AND conditiois not part of the join itself but is rather external
[/sql]

Here we have a problem of how to move forward? The solution is to create sort of psedu join which basically does not nothing except give you an extra table that you can use in your join later.

...
JOIN COMMISSION_SCHEDULE CS
ON CS.COMM_SCHEDULE_ID = CR.COMM_SCHEDULE_ID
JOIN (select Relate_ID,Relate_Class_NM from Commission_Schedule where Relate_class_NM = 'cSalesEntityRelate') CS2
ON CS.comm_schedule_id = SER.CS.comm_schedule_ID
/* the above gives you an extra CS2 table that you can use in join later as you move forward */
...

This give you one extra table ‘CS2’ that you can use in your next join.

...
JOIN COMMISSION_SCHEDULE CS
ON CS.COMM_SCHEDULE_ID = CR.COMM_SCHEDULE_ID
JOIN (select Relate_ID,Relate_Class_NM from Commission_Schedule where Relate_class_NM = 'cSalesEntityRelate') CS2
ON CS.comm_schedule_id = SER.CS.comm_schedule_ID
JOIN SalesEntityRelate SER
ON  CS2=Relate_ID = SER.Sales_Entity_Relate_ID
...

As you can see, we successfully moved on to the next table. We can continue this procedure till we read the Contract table. At that point we have contract_ID as well as value_no that we wanted to join together and we successfully did.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s