I have created a report that uses 10 Command Add statements.  I did this because the 10 data fields that I needed were all in one memo data field and this was the only way I could extract what I needed.  This has slowed down processing of the report significantly.  To process about 5,000 records takes about 5 minutes. Any suggestions on how to speed up processing time.

 

Any help is greatly appreciated.

 

pellis

   There is a database table called Profile_value which contains three fields; uid(user id), fid(field ID) and value(a value depending on the fid)

I need to extract data for fname, lname, gender,birthday, state, zip, etc.

    Here is how that data is idenfified.

Profile_values.fid = 1  then profile_values.Value = fname

Profile_values.fid = 2  then profile_values.value = lname

Profile_values.fid = 3 then profile_values.value = gender 

 

This is joined with a table called User which is joined on the uid field.

 

Here is how I wrote the add command for each peice of data.

     select uid,fid, cast(value as char (20)) as 'Fname'

     from profile_values

     where profile_values.fid =1      ,   etc.

 

Not sure how to accomplish this using a stored procedure because I would be using the same select statement over and over and assigning a new name to value depending on the fid number.   Can you give me a couple of lines of sql statment that will accomplish this in a stored procedure?  All help is greatly appreciated and thanks so much in advance.

 

Pattyg   

 

 

Failed to retrieve data from database.

Details:42000:[ySQL][ODBC 3.51Driver][mysqld-5.0.51-community-nt] you have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near " at line 24

[Database Vendor Code:1064]

 

Here is the sql statement.

 

SELECT a.UID, a.fid, b.fname , c.lname, d.gender,e.city,f.state
FROM a as profile_value
LEFT OUTER JOIN
(SELECT uid,fid, cast(value as char (20)) as 'Fname'
     FROM profile_values
     WHERE profile_values.fid =1) as b
ON a.UID = b.UID
LEFT OUTER JOIN
(SELECT uid,fid, cast(value as char (20)) as 'lname'
    FROM profile_values
     WHERE profile_values.fid =2) as c
ON a.UID = c.UID
LEFT OUTER JOIN
(SELECT uid,fid, cast(value as char (20)) as 'gender'
    FROM profile_values
     WHERE profile_values.fid =3) as d
LEFT outer join
(SELECT uid,fid,cast(value as char(20)) as 'city'
   FROM profile_values
   WHERE profile_values.fid=16) as e
left outer join
(SELECT uid,fid, cast(value as char(20)) as  ' state'
  FROM profile_values
  WHERE profile_values.fid=17) as f

 

I am currently trying to figure out how to resolve this issue.  Have any of you guys run into this problem before?   Thanks.