NEWBIE-NEED HELP WITH THIS SQL QUERY

[LOgle0723]

SELECT OI_CHARGES.RESP_GUAR_NBR,OI_CHARGES.APPL_SOURCE,OI_CHARGES.POST_DT,OI_CHARGES.PERF_PHYS_NBR,
OI_CHARGES.PERF_DEPT_CD,OI_CHARGES.SERV_LOCATION,OI_CHARGES.SERV_SITE,OI_CHG_ERROR.AGC_CD,OI_CHG_ERROR.DT_OF_SERVICE,
OI_STATEMENT.FIN_CLASS
FROM OI_CHARGES, OI_CHG_ERROR, OI_STATEMENT,
WHERE OI_CHARGES.RESP_GUAR_NBR=OI_CHG_ERROR.RESP_GUAR_NBR=OI_STATEMENT.RESP_GUAR_NBR,AND
OI_CHARGES.APPL_SOURCE='EC', AND OI_CHARGES.POST_DT>='20070101'
ORDER BY OI_CHARGES.POST_DT DESC;

WHY WILL THIS NOT WORK? I AM TRYING TO USE 3 TABLES TO PULL INFORMATION WITH THE DATE >= 01/01/2007 (THE FORMAT IN THE DATABASE IS AS I HAVE IN THE QUERY AND WITH THE APPL SOURCE EQUL TO EC. I AM NEW TO THIS CAN YOU HELP? THANKS! LISA

 

[SQLBill]

What error are you getting? It helps us to know what is wrong and what you want fixed.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

Something kinda noticable:

WHERE OI_CHARGES.RESP_GUAR_NBR=OI_CHG_ERROR.RESP_GUAR_NBR=OI_STATEMENT.RESP_GUAR_NBR

What are you trying to do there?

Maybe it should be:

WHERE (OI_CHARGES.RESP_GUAR_NBR=OI_CHG_ERROR.RESP_GUAR_NBR
AND OI_CHG_ERROR.RESP_GUAR_NBR=OI_STATEMENT.RESP_GUAR_NBR)
Also, get rid of the commas before the AND in the WHERE clause.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

There are a couple problems here.

First. You have a comma at the end of your list of tables (in the FROM clause)

Second. Your where clause is a mess. Do NOT use commas to separate conditions in the where clause. AND is sufficient.

Third. You apparently want to join 3 tables. While your syntax is a little 'old school', it would work IF you change the way you link them together.

This does not work:
OI_CHARGES.RESP_GUAR_NBR[!]=[/!]OI_CHG_ERROR.RESP_GUAR_NBR[!]=[/!]OI_STATEMENT.RESP_GUAR_NBR

Instead, you need to separate it in to multiple conditions, like this...

OI_CHARGES.RESP_GUAR_NBR=OI_CHG_ERROR.RESP_GUAR_NBR
[!]And[/!] OI_CHARGES.RESP_GUAR_NBR=OI_STATEMENT.RESP_GUAR_NBR


After correcting these problems, your query will look stiking similar to this...

SELECT OI_CHARGES.RESP_GUAR_NBR,
       OI_CHARGES.APPL_SOURCE,
       OI_CHARGES.POST_DT,
       OI_CHARGES.PERF_PHYS_NBR,
       OI_CHARGES.PERF_DEPT_CD,
       OI_CHARGES.SERV_LOCATION,
       OI_CHARGES.SERV_SITE,
       OI_CHG_ERROR.AGC_CD,
       OI_CHG_ERROR.DT_OF_SERVICE,
       OI_STATEMENT.FIN_CLASS
FROM   OI_CHARGES, OI_CHG_ERROR, OI_STATEMENT
WHERE  OI_CHARGES.RESP_GUAR_NBR=OI_CHG_ERROR.RESP_GUAR_NBR
       And OI_CHARGES.RESP_GUAR_NBR=OI_STATEMENT.RESP_GUAR_NBR 
       AND OI_CHARGES.APPL_SOURCE='EC' 
       AND OI_CHARGES.POST_DT>='20070101'
ORDER BY OI_CHARGES.POST_DT DESC;

Notice how I used plenty of white space in the code. While it is not necessary to format your code like this, I do recommend it because it will make your queries easier to read and easier to debug.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

Besides what George said I would recommend forgetting that join syntax and using this one. Also all caps is yelling please don't use that in the future when you post.

SELECT OI_CHARGES.RESP_GUAR_NBR,
       OI_CHARGES.APPL_SOURCE,
       OI_CHARGES.POST_DT,
       OI_CHARGES.PERF_PHYS_NBR,
       OI_CHARGES.PERF_DEPT_CD,
       OI_CHARGES.SERV_LOCATION,
       OI_CHARGES.SERV_SITE,
       OI_CHG_ERROR.AGC_CD,
       OI_CHG_ERROR.DT_OF_SERVICE,
       OI_STATEMENT.FIN_CLASS
FROM   OI_CHARGES
inner join  OI_CHG_ERROR on OI_CHARGES.RESP_GUAR_NBR=OI_CHG_ERROR.RESP_GUAR_NBR
inner join  OI_STATEMENT on OI_CHARGES.RESP_GUAR_NBR=OI_STATEMENT.RESP_GUAR_NBR 
WHERE  OI_CHARGES.APPL_SOURCE='EC' 
       AND OI_CHARGES.POST_DT>='20070101'
ORDER BY OI_CHARGES.POST_DT DESC

This does several things, it gets your actual where conditions away from your joins making the query easier to understand, Also, once you get into using Oute joins, the synta you use does not work correctly; it will return bad results much of the time. So it is better to learn now to use joins correctly.

"NOTHING is more important in a database than integrity." ESquared

 

[LOgle0723]

Thanks for the suggestions and I will try those when I get back to work on Monday. Can you guys recommend some good instructional books on SQL Syntax so that I can reference for this information? Sorry about the caps. Thanks again for the responses!

Take Care!

 

[SQLBill]

The very best and least expensive place to learn SQL Server syntax is the Books OnLine. Go to Start>Programs>Microsoft SQL Server>Books OnLine (With 2005, go to Documentation and Tutorials).

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLSister]

For good SQl books please look over this list:

http://www.tek-tips.com/faqs.cfm?fid=3324

"NOTHING is more important in a database than integrity." ESquared