Click here to Skip to main content
14,920,009 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
select  det.* ,uim.c_code from 
cust_inv_det det 
join u_stockiest_item usc on det.c_c2code=usc.c_stockiest_code and det.c_item_code=usc.c_stockiest_item_code
join u_item_mst uim on uim.c_code=usc.c_ucode
join u_item_mfac_mst mfac on mfac.c_code=uim.c_item_mfac_code
where det.d_date='2019-01-16'


This query will execute in less than a second and no problem.

If i add any condtion other than det.d_date into that query it wont execute still running the query.

To test i have added condition on usc,uim,mfac tables one at a time but did not execute fully

What I have tried:

This is the query with another condtion in where clause but it will not execute
SQL
select  det.* ,uim.c_code from 
cust_inv_det det 
join u_stockiest_item usc on det.c_c2code=usc.c_stockiest_code and det.c_item_code=usc.c_stockiest_item_code
join u_item_mst uim on uim.c_code=usc.c_ucode
join u_item_mfac_mst mfac on mfac.c_code=uim.c_item_mfac_code
where det.d_date='2019-01-16' and usc.c_stockiest_item_code='142193'
Posted
Updated 17-Jul-19 1:59am
v2
Comments
CHill60 17-Jul-19 7:56am
   
"will not execute" - then what is the error message?
"did not execute fully" - again, what is the error message?
Some sample data is always useful if you would like us to help you.
Member 11337367 17-Jul-19 7:58am
   
means query has not executed its in running mode
ZurdoDev 17-Jul-19 7:59am
   
1. You said it is still running. That is very different than it will not execute.
2. We can't see your data nor your database so I'm not sure what you want from us. You have a poor performing query. You need to fix that with indexes or changing your query but there's nothing we can do to help other than give generic suggestions.
Member 11337367 17-Jul-19 8:05am
   
yeah actually query is running only not giving result only after adding second condition in where clause.index has got created on usc.c_stockiest_item_code column so why its not giving result
CHill60 17-Jul-19 8:07am
   
As I said earlier and as ZurdoDev has also mentioned - there is nothing we can do without sample data
MadMyche 17-Jul-19 9:36am
   
Database schema and indexes would be helpful in troubleshooting

1 solution

You should try to format the date and then compare it with the value 

DATE_FORMAT("2019-01-16", "%Y-%m-%d")


You can try this:

select  det.* ,uim.c_code from 
cust_inv_det det 
join u_stockiest_item usc on det.c_c2code=usc.c_stockiest_code and det.c_item_code=usc.c_stockiest_item_code
join u_item_mst uim on uim.c_code=usc.c_ucode
join u_item_mfac_mst mfac on mfac.c_code=uim.c_item_mfac_code
where DATE_FORMAT(det.d_date, "%Y-%m-%d") ='2019-01-16' and usc.c_stockiest_item_code='142193'
   
Comments
Member 11337367 18-Jul-19 0:29am
   
If i add date_format query keeps running only.it will not give result before execution stops by mysql sever

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900