none
MSQuery from Excel 2010 Get contents of array colomn RRS feed

  • Question

  • I am developing an Appliaction part of which uses Excel 2010, MSQuery  & postgres

    The application has a  table defined as:


     CREATE TABLE myschema.classes
    (  clas integer NOT NULL,
      nam character varying,
      typs character(3)[],
      CONSTRAINT classes_pkey PRIMARY KEY (clas ))
    WITH (  OIDS=FALSE);


    The array is the issue

    When I use MSQuery to build by Query the query I Get an SQL statement:


    SELECT classes.clas, classes.nam, classes.typs
    FROM myschema.classes classes
    ORDER BY classes.nam


    the following data

    2; Comms Install; {J0
    1; Comms MRC; {I0
    3; Hosting Services; {I2
    7; Infrastructure; {}
    4 Software; {D0
    6; Staffing; {S0


    Note only the 1st 3 characters of typ

    when I execute the same query in SQL I get:

    2;"Comms Install";"{J01,J02,J03,J04,J05,J06,J07,J08,J09,J10,J11,J12,J13,J14,J15,J16,J17,J18}"
    1;"Comms MRC";"{I01,I02,I03,I04,I05,I06,I07,I08,I09,I10,I11,I12,I13,I14,I15,I16,I17,I18}"
    3;"Hosting Services";"{I20,I21,I22}"
    7;"Infrastructure";"{}"
    4;"Software";"{D01,E01,M01,P01}"
    6;"Staffing";"{S01,S02,S03,S04,S05}"

    Usually when I have problems defining a query in MSQuery (is complicated) I can write the query inb SQL .. insert it into the MSQuery SQl which is not displayable graphicly but reurns what I need..


    in this case the query is simple and even when I make it more complex so that it is not graphically displayable I still only get the 1st 3 characters..

    How do I get this data into Excel?

    I hope Modeling an tools is the correct forum... It seemed the best fit for the oprtions provided.

    Wednesday, March 6, 2013 5:06 PM