How to I use Native sql using creatSQLQuery

atewari · **Joined:** Thu Oct 13, 2005 3:40 pm **Posts:** 29

How to I use Native SQL for the following SQL:

Code:

SELECT gt.gs_team_id FROM GS_TEAM gt inner join TEAM_DETAIL td ON td.team_id = gt.team_id AND td.DRAFT_GS_TEAM_ID = gt.gs_team_id inner join TEAM t ON t.team_id = td.team_id inner join STORE s ON s.store_id = t.store_id WHERE (s.store_id = 9000 OR s.parent_store_id = 9000) AND td.fiscal_period_id = 70

Her is what I used in the code

Code:

sql += "SELECT "; 
sql += "{gt.gs_team_id} "; 
sql += "FROM GS_TEAM gt "; 
sql += "inner join TEAM_DETAIL td ON {td.team_id} = {gt.team_id} AND {td.DRAFT_GS_TEAM_ID} = {gt.gs_team_id} ";
sql += "inner join TEAM t ON {t.team_id} = {td.team_id} ";
sql += "inner join STORE s ON {s.store_id} = {t.store_id} ";
sql += "WHERE ";
sql += "({s.store_id} = " + storeId + " OR {s.parent_store_id} = " + storeId+ ") AND ";
sql += "{td.fiscal_period_id} = " + fp.getFiscalPeriodId();

I get the following error

Code:

ERROR [11-21-2005 17:40:51] (CalcIterator.java:String;:152) - org.hibernate.QueryException: No column name found for property [gs_team_id] [SELECT {gt.gs_team_id} FROM GS_TEAM gt inner join TEAM_DETAIL td ON {td.team_id} = {gt.team_id} AND {td.DRAFT_GS_TEAM_ID} = {gt.gs_team_id} inner join TEAM t ON {t.team_id} = {td.team_id} inner join STORE s ON {s.store_id} = {t.store_id} WHERE ({s.store_id} = 9000 OR {s.parent_store_id} = 9000) AND {td.fiscal_period_id} = 70]

Thanks!

Atul

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp
Hibernate version:

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

atewari · **Joined:** Thu Oct 13, 2005 3:40 pm **Posts:** 29

Here is something else I tried. Hibernate create the right SQL, but still gives me error. Code is

Code:

String sql = "";
         
sql += "SELECT "; 
sql += "{gt.*} "; 
sql += "FROM GS_TEAM gt "; 
sql += "inner join TEAM_DETAIL td ON td.team_id = gt.team_id AND td.DRAFT_GS_TEAM_ID = gt.gs_team_id ";
sql += "inner join TEAM t ON t.team_id = td.team_id ";
sql += "inner join STORE s ON s.store_id = t.store_id ";
sql += "WHERE ";
sql += "(s.store_id = " + storeId + " OR s.parent_store_id = " + storeId+ ") AND ";
sql += "td.fiscal_period_id = " + fp.getFiscalPeriodId();

SQLQuery storeQuery = session.createSQLQuery(sql);
storeQuery.addEntity("gt", GsTeam.class);         
storeQuery.addEntity("td", TeamDetail.class);
storeQuery.addEntity("t", Team.class);
storeQuery.addEntity("s", Store.class);
List storeList = storeQuery.list();

The generated SQL is below and executes successfully

Code:

SELECT gt.GS_TEAM_ID as GS1_0_, gt.GS_RUN_ID as GS2_35_0_, gt.TEAM_ID as TEAM3_35_0_, gt.INPUT_TEAM_SAVINGS_POOL as INPUT4_35_0_, gt.INPUT_DEEP_DEFICIT_FLAG as INPUT5_35_0_, gt.INPUT_OT_ADJ_FACTOR as INPUT6_35_0_, gt.INPUT_FP_OT_ADJ as INPUT7_35_0_, gt.INPUT_YR_OT_ADJ as INPUT8_35_0_, gt.INPUT_ALLOCATED_LABOR_PCT as INPUT9_35_0_, gt.INPUT_STORE_TEAM_PCT as INPUT10_35_0_, gt.INPUT_STORE_SAVINGS_POOL as INPUT11_35_0_, gt.INPUT_CONTRACT_LABOR as INPUT12_35_0_, gt.INPUT_VENDOR_CREDIT as INPUT13_35_0_, gt.INPUT_SUPPLEMENTAL_LABOR as INPUT14_35_0_, gt.CALC_OT_ADJ_TOTAL as CALC15_35_0_, gt.CALC_ADJ_SAVINGS_POOL as CALC16_35_0_, gt.CALC_TEAM_SALES as CALC17_35_0_, gt.CALC_ALLOCATED_LABOR_BUDGET as CALC18_35_0_, gt.CALC_ACTL_LABOR_COST_PP1 as CALC19_35_0_, gt.CALC_ACTL_LABOR_COST_PP2 as CALC20_35_0_, gt.CALC_ACTL_LABOR_COST_TOTAL as CALC21_35_0_, gt.CALC_LABOR_SURPLUS as CALC22_35_0_, gt.CALC_REG_LABOR_HOURS_PP1 as CALC23_35_0_, gt.CALC_REG_LABOR_HOURS_PP2 as CALC24_35_0_, gt.CALC_REG_LABOR_HOURS_TOTAL as CALC25_35_0_, gt.CALC_OT_LABOR_HOURS_PP1 as CALC26_35_0_, gt.CALC_OT_LABOR_HOURS_PP2 as CALC27_35_0_, gt.CALC_OT_LABOR_HOURS_TOTAL as CALC28_35_0_, gt.CALC_SURPLUS_TO_TEAM_PRELIM as CALC29_35_0_, gt.CALC_SURPLUS_TO_POOL_PRELIM as CALC30_35_0_, gt.CALC_SURPLUS_PER_REG_HOUR as CALC31_35_0_, gt.CALC_NEW_MEMBERS_ADJ as CALC32_35_0_, gt.CALC_TERMINATED_MEMBERS_ADJ as CALC33_35_0_, gt.CALC_ROUNDING_ADJ as CALC34_35_0_, gt.CALC_SURPLUS_TO_TEAM_FINAL as CALC35_35_0_, gt.OUTPUT_FP_SURPLUS_2_STORE_POOL as OUTPUT36_35_0_, gt.OUTPUT_FP_SURPLUS_TO_TEAM_POOL as OUTPUT37_35_0_, gt.OUTPUT_OT_ADJ as OUTPUT38_35_0_, gt.INPUT_NEEDS_OT_ADJ_FLAG as INPUT39_35_0_, gt.CALC_LABOR_SURPLUS_B4STORE as CALC40_35_0_, gt.MODIFIED_BY as MODIFIED41_35_0_, gt.MODIFIED_DT as MODIFIED42_35_0_, gt.CREATED_DT as CREATED43_35_0_, gt.CREATED_BY as CREATED44_35_0_, gt.INPUT_FRONT_END_FLAG as INPUT45_35_0_, gt.INPUT_ADMIN_FLAG as INPUT46_35_0_, gt.INPUT_SURPLUS_PER_HR_OVERRIDE as INPUT47_35_0_, gt.INPUT_STORE_TOTAL_SALES as INPUT48_35_0_, gt.INPUT_STORE_TOTAL_SURPLUS as INPUT49_35_0_, gt.INPUT_STORE_TOTAL_REG_HOURS as INPUT50_35_0_, gt.CALC_STORE_TOTAL_HRS_INC_ADMIN as CALC51_35_0_, gt.INPUT_SUPPLEMENTAL_FLAG as INPUT52_35_0_, gt.INPUT_REGION_ID as INPUT53_35_0_, gt.INPUT_REGION_ABBREV as INPUT54_35_0_, gt.INPUT_REGION_NAME as INPUT55_35_0_, gt.INPUT_STORE_ID as INPUT56_35_0_, gt.INPUT_STORE_ABBREV as INPUT57_35_0_, gt.INPUT_STORE_NAME as INPUT58_35_0_, gt.INPUT_PS_STORE_ID as INPUT59_35_0_, gt.INPUT_SUB_STORE_ABBREV as INPUT60_35_0_, gt.INPUT_SUB_STORE_NAME as INPUT61_35_0_, gt.INPUT_SUB_PS_STORE_ID as INPUT62_35_0_, gt.INPUT_STORE_LIVE_ON_GS_FLAG as INPUT63_35_0_, gt.OUTPUT_TEAM_SALES as OUTPUT64_35_0_, gt.OUTPUT_REG_LABOR_HOURS as OUTPUT65_35_0_, gt.OUTPUT_OT_LABOR_HOURS as OUTPUT66_35_0_, gt.OUTPUT_LABOR_COST as OUTPUT67_35_0_, gt.OUTPUT_LABOR_BUDGET as OUTPUT68_35_0_, gt.INPUT_COUNTRY_CODE as INPUT69_35_0_, gt.INPUT_STORE_TEAM_FLAG as INPUT70_35_0_, gt.INPUT_STORE_NON_GS_ADMIN_HRS as INPUT71_35_0_ FROM GS_TEAM gt inner join TEAM_DETAIL td ON td.team_id = gt.team_id AND td.DRAFT_GS_TEAM_ID = gt.gs_team_id inner join TEAM t ON t.team_id = td.team_id inner join STORE s ON s.store_id = t.store_id WHERE (s.store_id = 9000 OR s.parent_store_id = 9000) AND td.fiscal_period_id = 70

The ERROR is

Code:

 WARN [11-21-2005 18:07:52] (JDBCExceptionReporter.java:String;)V:71) - SQL Error: 17006, SQLState: null
ERROR [11-21-2005 18:07:52] (JDBCExceptionReporter.java:String;)V:72) - Invalid column name
ERROR [11-21-2005 18:07:52] (CalcIterator.java:String;:152) - org.hibernate.exception.SQLGrammarException: could not execute query

So what am I doing wrong??

Thanks!

Atul

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

are you sure that generated sql is correct - hibernate claim error from jdbc - invalid column name

try execute generated sql from any command line utility or pure jdbc

atewari · **Joined:** Thu Oct 13, 2005 3:40 pm **Posts:** 29

Yes, I tested the SQL in TOAD before posting and it worked just fine.

Do you think there is anything else that is evidently wrong?

Thanks for your reply!

Atul

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

i see 'invalid column name' and it is error from database (sql error, no hibernate)
check that you use same schema, database ... for TOAD and hibernate

atewari · **Joined:** Thu Oct 13, 2005 3:40 pm **Posts:** 29

Yes! The two schemas are exactly the same. I think it is something hibernate needs to execute that is missing.

Thanks!

Atul

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

try call generated sql with jdbc - it is hard without debuging if hibernate generate sql and sql is fine

atewari · **Joined:** Thu Oct 13, 2005 3:40 pm **Posts:** 29

Thanks for replying back. Hibernate does generate the SQL fine. I set the show_sql flag to true and ran the SQL created by Hibernate.

Thanks again! I noticed there was another article where another user had the same problem. The solution provided did not help.

So then I used HQL to get what I wanted. Although for an inner join you cannot use the "AND" logic in HQL to join on two columns within a single inner join unlike SQL that allows it. I could very easily add the join td.gsTeam_1.gsTeamId = gt.gsTeamId as part of the inner join rather than the WHERE clause. I tried adding it to the inner join and created an additional join on the "gt" table. Not sure if this a bug or HQL always adds the table again (by design) if the table appears in multiple inner join statements.

Code:

String hql = "select gt from GsTeam gt ";
hql += " inner join gt.team as t ";
hql += " inner join t.teamDetails as td ";
hql += " inner join t.store as s ";
hql += " where ";
hql += " td.gsTeam_1.gsTeamId = gt.gsTeamId ";
hql += " and (s.storeId = :storeId or s.store = :store) and td.fiscalPeriod = :fiscalPeriod ";
hql += " order by t.storeSortOrder";

I appreciate your replies!

Atul

max · **Posted:** Tue Nov 22, 2005 3:05 pm

SELECT {gt.gs_team_id} FROM ... does not make sense.

SELECT gt.gs_team_id as {gt.gs_team_id}, ...other colums FROM or
SELECT {gt.*} FROM also works.

Go read the native sql examples and make sure you write something similar.

max · **Posted:** Tue Nov 22, 2005 3:05 pm

SELECT {gt.gs_team_id} FROM ... does not make sense.

SELECT gt.gs_team_id as {gt.gs_team_id}, ...other colums FROM or
SELECT {gt.*} FROM also works.

Go read the native sql examples and make sure you write something similar.

atewari · **Joined:** Thu Oct 13, 2005 3:40 pm **Posts:** 29

Max,

I read the manual and did exactly what you and the manual state. I tried {gt.*} as well as gt.gs_team_id as {gt.gs_team_id}.

Here is the code

Code:

String sql = ""; 
          
sql += "SELECT "; 
sql += "{gt.*} "; 
sql += "FROM GS_TEAM gt "; 
sql += "inner join TEAM_DETAIL td ON td.team_id = gt.team_id AND td.DRAFT_GS_TEAM_ID = gt.gs_team_id "; 
sql += "inner join TEAM t ON t.team_id = td.team_id "; 
sql += "inner join STORE s ON s.store_id = t.store_id "; 
sql += "WHERE "; 
sql += "(s.store_id = " + storeId + " OR s.parent_store_id = " + storeId+ ") AND "; 
sql += "td.fiscal_period_id = " + fp.getFiscalPeriodId(); 

SQLQuery storeQuery = session.createSQLQuery(sql); 
storeQuery.addEntity("gt", GsTeam.class);          
storeQuery.addEntity("td", TeamDetail.class); 
storeQuery.addEntity("t", Team.class); 
storeQuery.addEntity("s", Store.class); 
List storeList = storeQuery.list(); 

The generated SQL is fine but errors out with

Code:

WARN [11-21-2005 18:07:52] (JDBCExceptionReporter.java:String;)V:71) - SQL Error: 17006, SQLState: null 
ERROR [11-21-2005 18:07:52] (JDBCExceptionReporter.java:String;)V:72) - Invalid column name 
ERROR [11-21-2005 18:07:52] (CalcIterator.java:String;:152) - org.hibernate.exception.SQLGrammarException: could not execute query 

max · **Posted:** Tue Nov 22, 2005 3:40 pm

you are definitly not doing what the doc states.

You do {gt.*} which states "generate sql aliases for columns for the entity class witht the alias "gt" and then you have 4 (!) calls of addEntity with 3 entity aliases (td,t,s) that is not in your SELECT clause.

the error you see is oracles bad error message that does not care about telling which column we are trying to look up; i'll assume its one of the columns for those 3 unselected entities you have aded.

atewari · **Joined:** Thu Oct 13, 2005 3:40 pm **Posts:** 29

Thank you Max. Looks like I misunderstood the docs. So to explicitly state what you say in your previous email, will I have to alias each table alias {}? I tried that with no luck! I also removed the other three addEntity calls and I still had the same problem. Is the rule to use {} across table aliases and columns?

thanks!

Code:

String sql = ""; 
          
sql += "SELECT "; 
sql += "{gt.*} "; 
sql += "FROM GS_TEAM gt "; 
sql += "inner join TEAM_DETAIL {td} ON td.team_id = gt.team_id AND td.DRAFT_GS_TEAM_ID = gt.gs_team_id "; 
sql += "inner join TEAM {t} ON t.team_id = td.team_id "; 
sql += "inner join STORE {s} ON s.store_id = t.store_id "; 
sql += "WHERE "; 
sql += "(s.store_id = " + storeId + " OR s.parent_store_id = " + storeId+ ") AND "; 
sql += "td.fiscal_period_id = " + fp.getFiscalPeriodId(); 

SQLQuery storeQuery = session.createSQLQuery(sql); 
storeQuery.addEntity("gt", GsTeam.class);          
storeQuery.addEntity("td", TeamDetail.class); 
storeQuery.addEntity("t", Team.class); 
storeQuery.addEntity("s", Store.class); 
List storeList = storeQuery.list(); 

max · **Posted:** Tue Nov 22, 2005 4:50 pm

you are *still* only select gt columns and adds all entities.

Look at the examples and just do the same thing.