We help IT Professionals succeed at work.

SQL syntax for joins

dhinkle
dhinkle asked
on
I am trying to access a database via a Mini-Soft HP3000 odbc driver through SQL 7.0 and I cannot get the syntax right for the joins. Can anybody help? This is what I am using and I am getting an error in table list msg. The table names are all correct and verified. It has something to do with the syntax of that {oj }

SELECT parcel_no, acct_no, num_cards
FROM {oj (TAXSUP.REALPROP left outer join taxsup.taxname
           on taxsup.realprop.acct_no = taxsup.taxname.acct_no)
                          LEFT outer JOIN APPRHDR.APPRMAST
           ON TAXSUP.REALPROP.PIN_ID = APPRHDR.APPRMAST.APPR_PIN_NUMBER}
Comment
Watch Question

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
I don't think you should use the parentheses, more like this maybe:

SELECT parcel_no, acct_no, num_cards
FROM TAXSUP.REALPROP
LEFT outer join taxsup.taxname
    on taxsup.realprop.acct_no = taxsup.taxname.acct_no
LEFT outer JOIN APPRHDR.APPRMAST
    ON TAXSUP.REALPROP.PIN_ID =  
    APPRHDR.APPRMAST.APPR_PIN_NUMBER

Author

Commented:
It doesn't work with or without them and this driver seems to require the {oj } around the statement. It is different than I'm used to, for instance to use the Left function rather than typing Left(fieldname,5) I have to say {fn Left(fieldname,5)}
Try the old school outer join syntax

SELECT parcel_no, acct_no, num_cards
FROM {oj TAXSUP.REALPROP, taxsup.taxname, APPRHDR.APPRMAST
WHERE taxsup.realprop.acct_no *= taxsup.taxname.acct_no
   AND TAXSUP.REALPROP.PIN_ID *= APPRHDR.APPRMAST.APPR_PIN_NUMBER}  

if the * doesn't work try + in its place.  Just a thought  . . .
           

Author

Commented:
I just tried it and got the same error msg as I get with every other try -

In:                    hstmt = 0x00BF06EC, szSqlStr = "SELECT parcel_no, acct_no, num_cards
FROM {oj TAXSUP....", cbSqlStr = -3
                    Return:     SQL_ERROR=-1
                    stmt:          szSqlState = "42000", *pfNativeError = 0, *pcbErrorMsg = 51, *ColumnNumber = 0, *RowNumber = 0
                                                  MessageText = "[MiniSoft] [3kodbc.dll] Syntax error in table list."
are all the tables within the brakets on the remote server, i,e, are TAXSUP, and APPRHDR both databases on th HP machine?

Author

Commented:
yes and I verified that with the ODBC Test prg I am using. I am able to display a list of tables and column names for all of them
HOw abou this?


SELECT parcel_no, RP.acct_no, num_cards
FROM {oj TAXSUP.REALPROP} RP left outer join {oj taxsup.taxname} TN
    ON RP.acct_no = TN.acct_no
LEFT outer JOIN {oj APPRHDR.APPRMAST} AM
    ON RP.PIN_ID = AM.APPR_PIN_NUMBER


Also I noticed that the items in your select list aren't fully qualified, at least the aact_no was amboguous . . .

Author

Commented:
same error
dhinkle:

You have several open questions:

http://www.experts-exchange.com/jsp/qShow.jsp?qid=20189640
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20185039
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20143701

To assist you in your cleanup, I'm providing the following guidelines:

1.  Stay active in your questions and provide feedback whenever possible. Likewise, when feedback has not been provided by the experts, commenting again makes them receive an email notification, and they may provide you with further information. Experts have no other method of searching for questions in which they have commented, except manually.

2.  Award points by hitting the Accept Comment As Answer button located above and to the left of that expert's comment.

3.  When grading, be sure to read:
http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3
to ensure that you understand the grading system here at EE. If you grade less than an A, you must explain why.

4.  Questions that were not helpful to you should be PAQ'd (stored in the database for their valuable content?even if not valuable to you) or deleted. To PAQ or delete a question, you must first post your intent in that question to make the experts aware. Then, if no experts object after three full days, you can post a zero-point question at community support to request deletion or PAQ. Please include the link(s) to the question(s).
CS:  http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
At that point, a moderator can refund your points and PAQ or delete the question for you. The delete button does not work.

5.  If you fail to respond to this cleanup request, I must report you to the Community Support Administrator for further action.

Our intent is to get the questions cleaned up, and not to embarrass or shame anyone. If you have any questions or need further assistance at all, feel free to ask me in this question or post a zero-point question at CS. We are very happy to help you in this task!


thanks!
amp
community support moderator

1/28
dhinkle:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.

Author

Commented:
Thanks much for all the help!
Not sure how much help I actally was, but thanks for the nod. =)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.