Solved

HQL Join - Subqueries

Posted on 2014-01-15
6
917 Views
Last Modified: 2014-01-15
I am pretty good at doing basic queries but am trying to accomplish the following -

I have an Item master and an Item Detail that are related by item id.    The item detail can have 3 status - Transit, Stock, Invoiced

I would like my query to return how many of each item are in what status even if zero in any so my result would look something like this

                Transit         Stock        invoiced
item 1          5                7                25
item 2          0                0                20
item 3           5                5                0
item 4           0               0                 0
item 5           5              0                 5

tables:  SkuMaster, Tally
FK : Tally.skuId

I want to do something along the following but do not understand how to join properly

SELECT sm.skuId as sku, sm.tlcDescription as item ,

(SELECT count(t.tallyId) as shp FROM Tally t WHERE  t.status='Transit' group by t.skuMaster.skuId ),

(SELECT count(t.tallyId) as stk FROM Tally t WHERE  t.status='Stock' group by t.skuMaster.skuId),

(SELECT count(t.tallyId) as dlv FROM Tally t WHERE  t.status = ('Invoice') group by t.skuMaster.skuId)

from SkuMaster sm LEFT JOIN .......... ?   sm.tally.skuId?

Open in new window

0
Comment
Question by:TumacLumber
  • 3
  • 3
6 Comments
 
LVL 42

Expert Comment

by:pcelba
ID: 39783537
Try this:
SELECT sm.skuId as sku, sm.tlcDescription as item,
       SUM(CASE WHEN t.status='Transit' THEN 1 ELSE 0 END) AS shp,
       SUM(CASE WHEN t.status='Stock'   THEN 1 ELSE 0 END) AS stk,
       SUM(CASE WHEN t.status='Invoice' THEN 1 ELSE 0 END) AS dlv
  FROM SkuMaster sm
 INNER JOIN Tally t ON t.skuId = sm.skuId
 GROUP BY sm.skuId, sm.tlcDescription

It will ignore items having no records in Tally but you may add them in additional UNION query.
0
 

Author Comment

by:TumacLumber
ID: 39783614
HQL does not like the ON in the join
0
 
LVL 42

Accepted Solution

by:
pcelba earned 500 total points
ID: 39783652
OK, above query should work on MySQL server...

You may replace the ON  by WITH

or better:
SELECT sm.skuId as sku, sm.tlcDescription as item,
       SUM(CASE WHEN t.status='Transit' THEN 1 ELSE 0 END) AS shp,
       SUM(CASE WHEN t.status='Stock'   THEN 1 ELSE 0 END) AS stk,
       SUM(CASE WHEN t.status='Invoice' THEN 1 ELSE 0 END) AS dlv
  FROM SkuMaster sm, Tally t
 WHERE t.skuId = sm.skuId
 GROUP BY sm.skuId, sm.tlcDescription

BUT I am not sure about other construsts like GROUP BY and CASE WHEN...

According to this manual both GROUP BY and CASE WHEN should work: http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/queryhql.html#queryhql-expressions
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:TumacLumber
ID: 39783665
This looked like it worked.  Since I already have the FK relationship in HQL - your CASE statements were what I was looking for.   I will try the above to see if it worked as well.

SELECT t.skuMaster.skuId as sku, t.skuMaster.tlcDescription as item,
       SUM(CASE WHEN t.status='Transit' THEN 1 ELSE 0 END) AS shp,
       SUM(CASE WHEN t.status='Stock'   THEN 1 ELSE 0 END) AS stk,
       SUM(CASE WHEN t.status='Invoice' THEN 1 ELSE 0 END) AS dlv
  FROM Tally t

 GROUP BY t.skuMaster.skuId, t.skuMaster.tlcDescription


The above also worked with the small change

WHERE t.skuMaster.skuID - sm.skuId   (FK relationship which you had no real knowledge on )

Thanks for the help.   This will be re-used throughout my app!
0
 
LVL 42

Expert Comment

by:pcelba
ID: 39783725
You are welcome.  Interesting language this HQL...
0
 

Author Comment

by:TumacLumber
ID: 39783733
Yeah - it's a real beetch for intermediate level programmers like myself.   I am using Wavemaker to develop a couple apps and it is great except having to learn HQL.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question