My SQL query not giving result

Question

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 17-Jul-19 1:52am

Member 11337367

Updated 17-Jul-19 1:59am

phil.o

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Anuj Vohra · Answer 1 · 2019-07-17T05:58:00

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'