8 Replies Latest reply on Jul 26, 2017 11:59 AM by RHifum

    I want to add Member Type to my SQL Query. What is the name of the field for Member Type?

    RHifum Rookie

      I want to add Member Type to my SQL Query. What is the name of the field for Member Type?

        • 1. Re: I want to add Member Type to my SQL Query. What is the name of the field for Member Type?
          John Neighbors SSMMVPGroup

          Member Type does not sound like an "out of the box" field name in GoldMine.  Is this a custom field or even just a custom "label" that has been applied to an out of the box field?  If so, then we'd need to know more about your specific GoldMine setup.

           

          If it's simply a custom field or a re-labelled out of the box field, and presuming you have MASTER rights in your GoldMine, then simply right-click on the field and select Properties:

           

           

          The name of the field in the database will then be clearly identified:

           

           

          If this does not meet your need or help you find the desired answer, then can you possibly provide a simple screenshot of where this field is in your GoldMine, such that we can understand the context?

           

          Hope this feedback helps.

          • 2. Re: I want to add Member Type to my SQL Query. What is the name of the field for Member Type?
            RHifum Rookie

            I don't know if you can help me. I did what you said and found out the field name for Member Type is KEY2. I tried to add it to my SQL Query, but I can't seem to add it. When I try to add it to my query, it says, "Invalid column name 'key2'." Below is my original query that I want to add Member Type. Any suggestion would be appreciated.

             

            select distinct c1.company, cs1.contact, cs1.phone, cs1.address1, cs1.address2, cs1 .city, cs1.state, cs1.zip, cs2.contsupref + isnull (cs2.address1,'') as Email, c1.accountno from contact1 c1, contsupp as cs1 right outer join contsupp as cs2 on cs1.accountno = cs2.accountno where cs1.rectype = 'C' and (cs2.rectype = 'P' and cs2.contact = 'E-mail Address' and cs2.linkacct = cs1.recid) and c1.accountno=cs1.accountno and cs1.mergecodes like '%u%'

            union

            select contact1.company, contact1.contact, contact1.phone1, contact1.address1, contact1.address2, contact1 . city, contact1.state, contact1.zip, CONTACT2.upubemail as Email, contact1 .accountno from contact1, contact2 where contact1.mergecodes like '%u%' and contact1.accountno = contact2.accountno

            order by COMPANY, contact

            • 3. Re: I want to add Member Type to my SQL Query. What is the name of the field for Member Type?
              John Neighbors SSMMVPGroup

              My disclaimer:  I did NOT study these queries to determine if they could be designed better or not.  I purely added KEY2, as requested.

               

              Try this:

               

              select distinct c1.company, cs1.contact, c1.key2 as 'Member Type', cs1.phone, cs1.address1, cs1.address2, cs1 .city, cs1.state, cs1.zip, cs2.contsupref + isnull (cs2.address1,'') as Email, c1.accountno from contact1 c1, contsupp as cs1 right outer join contsupp as cs2 on cs1.accountno = cs2.accountno where cs1.rectype = 'C' and (cs2.rectype = 'P' and cs2.contact = 'E-mail Address' and cs2.linkacct = cs1.recid) and c1.accountno=cs1.accountno and cs1.mergecodes like '%u%'

              union

              select contact1.company, contact1.contact, contact1.key2 as 'Member Type', contact1.phone1, contact1.address1, contact1.address2, contact1 . city, contact1.state, contact1.zip, CONTACT2.upubemail as Email, contact1 .accountno from contact1, contact2 where contact1.mergecodes like '%u%' and contact1.accountno = contact2.accountno

              order by COMPANY, contact

               

              I suspect the problem with whatever you attempted was adding the wrong alias to the KEY2 reference.  It is in the CONTACT1 table, which used the C1 alias in the first query (before the union) and contact1 in the second query (after the union).

               

              Hope this feedback helps. Please let me know if I need to clarify anything.

              • 4. Re: I want to add Member Type to my SQL Query. What is the name of the field for Member Type?
                RHifum Rookie

                Thank you so++ much! I did have one small problem, but was able to fix. Instead of "CONTACT2.ugmver as Email", it had to say, "CONTACT2.upubemail as Email." Anyway, it added the column I needed. Thank you!

                • 5. Re: I want to add Member Type to my SQL Query. What is the name of the field for Member Type?
                  John Neighbors SSMMVPGroup

                  Doh!  Sorry about that Renee.  I changed custom fields just for testing on my local system and THOUGHT I had changed it back when copy-n-pasting to this thread.

                   

                  My apologies for the confusion.  Glad it ultimately worked out well.

                  • 6. Re: I want to add Member Type to my SQL Query. What is the name of the field for Member Type?
                    RHifum Rookie

                    No worries! I appreciate your help. I had another question that I think you may be able to help me with. In that SQL query, I am asking for records with the merge code "u." How would I make it search for merge code "u" OR "z"?

                    • 7. Re: I want to add Member Type to my SQL Query. What is the name of the field for Member Type?
                      John Neighbors SSMMVPGroup

                      Where you have this:

                       

                           and cs1.mergecodes like '%u%'

                       

                      ...change it to this:

                       

                           and (cs1.mergecodes like '%u%' OR cs1.mergecodes like '%z%')

                       

                      Where you have this:

                       

                           contact1.mergecodes like '%u%' and

                       

                      ...change it to this:

                       

                           (contact1.mergecodes like '%u%' OR contact1.mergecodes like '%z%') and

                       

                      Hope this feedback helps.