4 Replies Latest reply: Jan 14, 2011 6:40 PM by Hans kleijnen RSS

    check on coding

    Hans kleijnen

       

      Anyone sees a mistake in this code?

      it results in: Field not found - <CRM5.contact.name>

      Let vConnection = 'CRM5.contact Left Join SuperOffice.CRM5.udcontactsmall On CRM5.contact.userdef_id=CRM5.udcontactsmall.udcontactSmall_id Left Join CRM5.udlist On CRM5.udcontactsmall.long01=CRM5.udlist.UDList_id Left Join CRM5.associate On CRM5.contact.associate_id=CRM5.associate.associate_id Left Join CRM5.country On CRM5.contact.country_id=CRM5.country.country_id Left Join CRM5.business On CRM5.contact.business_idx=CRM5.business.business_id Left Join CRM5.category On CRM5.contact.category_idx=CRM5.category.Category_id';

      LOAD CRM5.contact.name as client,

      CRM5.country.name as country;

      SELECT

      CRM5.contact.name,

      CRM5.country.name

      From $(vConnection);

       

      appreciate the help

       

      kindrgrds

      Hans

       

       



       

        • check on coding

           

           

          LOAD

          CLIENT ,

          COUNTRY;

           

          SELECT



           

          CRM5.contact.name AS CLIENT ,

          CRM5.country.name AS COUNTRY

          From $(vConnection);

           

          Please check if this works .

           




          • check on coding

            Hi

            I think the problem is this line

             



            LOAD CRM5.contact.name as client,
            CRM5.country.name as country


            because QlikView is not making the difference between the name from the table contact and the name from the table country.

            In Sql you can have this because internally the name will be table.field, but qlikview doesn't work in this way

            Change your script like this

             



            LOAD
            contact_name as client,
            country_name as country;
            SELECT
            CRM5.contact.name as contact_name,

            CRM5.country.name as country_name

            From $(vConnection);


            Anyway, you can check this removing the load statement, and using the pure sql query

            Also, check how the driver retrieves the field names, for example in Oracle it doesn't matter if you put the aliases in lowercase, it convert them in upper case, so sometimes QlikView gives you an error because you are retrieving "field_name", but oracle returns "FIELD_NAME"

            Rgds