5 Replies Latest reply: Sep 20, 2016 4:42 AM by jagan mohan rao appala RSS

    Link Oracle and Excel

    g c

      I have the following queries and I cannot link them. Can someone show me how to do it?

       

      Regards.

       

      UploadStats:

      SQL Select

      (to_number(to_char(UPLOAD_DATE, 'DD')) || to_number(to_char(UPLOAD_DATE, 'MM')) || to_number(to_char(UPLOAD_DATE, 'YYYY'))) as Keyfield,

      INSTANCE_ID,

      ENVIRONNEMENT

      from My.OracleTable

       

      Climat:

      LOAD

          ([Day] & [Month] & [Year]) as Keyfield,

           [Temp (°C)], [Temp Indicator]

      FROM

      [C:\Users\me\Desktop\fre-hourly.xls](biff, embedded labels, table is [fre-hourly$]);

        • Re: Link Oracle and Excel
          Gysbert Wassenaar

          That's probably because the load from oracle will create a field called KEYFIELD instead of Keyfield. Try adding double quotes around the alias:

           

          UploadStats:

          SQL Select

          (to_number(to_char(UPLOAD_DATE, 'DD')) || to_number(to_char(UPLOAD_DATE, 'MM')) || to_number(to_char(UPLOAD_DATE, 'YYYY'))) as "Keyfield",

          INSTANCE_ID,

          ENVIRONNEMENT

          from My.OracleTable

          • Re: Link Oracle and Excel
            sasi k

            Also try to Store ORACLE table into a QVD and then load this QVD into your application.

            • Re: Link Oracle and Excel
              jagan mohan rao appala

              Hi,

               

              Oracle select statement will create the columns in Upper case, to bypass this you need to enclose column in double quotes or use preceding load.

               

              UploadStats:

              SQL Select

              (to_number(to_char(UPLOAD_DATE, 'DD')) || to_number(to_char(UPLOAD_DATE, 'MM')) || to_number(to_char(UPLOAD_DATE, 'YYYY'))) as "Keyfield",

              INSTANCE_ID,

              ENVIRONNEMENT

              from My.OracleTable

               

              Climat:

              LOAD

                  ([Day] & [Month] & [Year]) as Keyfield,

                   [Temp (°C)], [Temp Indicator]

              FROM

              [C:\Users\me\Desktop\fre-hourly.xls](biff, embedded labels, table is [fre-hourly$]);

               

               

              OR

               

              UploadStats:

              LOAD

              KEYFIELD AS KeyField,

              INSTANCE_ID,

              ENVIRONNEMENT

              SQL Select

              (to_number(to_char(UPLOAD_DATE, 'DD')) || to_number(to_char(UPLOAD_DATE, 'MM')) || to_number(to_char(UPLOAD_DATE, 'YYYY'))) as Keyfield,

              INSTANCE_ID,

              ENVIRONNEMENT

              from My.OracleTable

               

              Climat:

              LOAD

                  ([Day] & [Month] & [Year]) as Keyfield,

                   [Temp (°C)], [Temp Indicator]

              FROM

              [C:\Users\me\Desktop\fre-hourly.xls](biff, embedded labels, table is [fre-hourly$]);

               

              Hope this helps you.

               

              Regards,

              Jagan.

              • Re: Link Oracle and Excel
                g c

                Double quotes did work!

                 

                Thank you everyone.

                 

                Regard!