[SELECT] question

Hi all,
I've got a table structured as follow where the code field identify a particular parameter. Is it possible with a single query to retrieve more than just one parameter at a time? I mean can I get a recordset with the first field representing the date, the second field holding the values of parameter code [01], the third field the values of parameter code [02] and so forth??

Table structure
CREATE TABLE `test` (
`date` datetime NOT NULL ,
`code` varchar(3) NOT NULL ,
`value` float NOT NULL
) TYPE=MyISAM;

Sample data
+-------+--+---+
| date | code | value |
+-------+--+---+
| 2004-06-01 00:00:00 | 001 | 89 |
| 2004-06-01 00:00:00 | 002 | 1 |
| 2004-06-01 01:00:00 | 001 | 76 |
| 2004-06-01 01:00:00 | 002 | 5 |
| 2004-06-01 02:00:00 | 001 | 67 |
| 2004-06-01 02:00:00 | 002 | 7 |
| 2004-06-01 03:00:00 | 001 | 46 |
| 2004-06-01 03:00:00 | 002 | 4 |
| 2004-06-01 04:00:00 | 001 | 43 |
| 2004-06-01 04:00:00 | 002 | 3 |
+-------+--+---+

Recordset
+-------+----+----+
| date | value_cod1 | value_cod2 |
+-------+----+----+
| 2004-06-01 00:00:00 | 89 | 1 |
| 2004-06-01 01:00:00 | 76 | 5 |
| 2004-06-01 02:00:00 | 67 | 7 |
|..................................... ..............|
+-------+----+----+

Thank you very much!
Paolo SaudinTo select all of the columns in an arbitrary order, you can use:SELECT *
FROM testTo select the columns in a specific order, you can use:SELECT date, code, value
FROM test-PatP|||ah, the good old denormalization question

mysql doesn't support full outer join, but this is an equivalent --

select t1.date
, t1.value as value_cod1
, t2.value as value_cod2
from `test` as t1
left outer
join `test` as t2
on t1.date
= t2.date
and t2.code = '002'
where t1.code = '001'

union all

select t2.date
, null
, t2.value
from `test` as t1
right outer
join `test` as t2
on t1.date
= t2.date
and t1.code = '001'
where t2.code = '002'
and t1.date is null

you'll need mysql 4.0 to support union, and if you don't have any code 002 rows without a code 001 row, then you can get away with just the first of the two queries|||ah, the good old denormalization questionI wondered what on earth you were babbling about as I read your response, but when I went back and re-read the question carefully I discovered that you were correct! I've always preferred the structure:SELECT date
, Sum(CASE WHEN '001' = code THEN value END) AS '001'
, Sum(CASE WHEN '002' = code THEN value END) AS '002'
, Sum(CASE WHEN '003' = code THEN value END) AS '003'
FROM test
GROUP BY dateThis only has to make one pass through the table, so it can be more efficeient if the optimiser does its job.

-PatP|||i babble often

i'm wrong occasionally

:cool:|||Thank you all very much !! :)
Paolo Saudin

[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error.

Hi,

I have a classic ASP application that has been running on Win2003,IIS6 and SQL2000 for 3 years without a single error.

We've just moved to SQL2005 enterprise and we're getting the following intermittent error.

Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.

Any ideas where to start?

this is a common connection problem and without details like your current sql server configuration and connection string, we just can write our guesses,

first, if you have not installed latest service pack for sql serer 2005, please install it before trying the following suggestions,

second, you can check if you have enabled remote connections on sql server, to check this setting go to sql server surface area configuration -> surface area configuration for services and connections -> remote connections and enable remote connections by selection "local and remote connections" and using tcp/ip only.

if the previous step did not solve your problem, you can check your connection string, please examine the following connection strings,
for odbc connections:
- Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
- Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

for ole db connections
- Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
- Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

for .net connections
- Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
or
- Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;
- Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
or
- Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

also if you are connecting with an sql server account, please check if you have set authentication mode of sql server to mixed mode.

and finally if your sql server is on a seperate machine, please be sure that there is no firewall or router is locking the communication of iis and sql server.

if these won't help you to solve the problem, please give us some details like
- your connectionstring
- is your sql server is on another computer
- configuration details of your sql server (like active net libraries, authentication details)

|||hi kadir,

thank-you for the quick reply. i will check all your suggestions and report back.

in the meantime, sql and webserver are all on the same server and the connection string is:-

databaseServer = "PROVIDER=SQLOLEDB;DATA SOURCE=pretty.ucc.usyd.edu.au;UID=xxx;PWD=xxx;DATABASE=xxx"|||hi kadir,

i must confess i'm not an sql server expert.

1. latest service pack and mdac were already installed.

2. allow remote connections is enabled. i couldn't see where (see point 3) using tcp/ip was. i'm using the ms sql management studioto find this information.

3. using sql server configuration manager i could find the following.

sql server 2005 network config - protocols for msqlserver
shared memory enabled
named pipes disabled
tcp/ip enabled
via disabled

sql native client config - client protocols
shared memory enabled
tcp/ip enabled
names pipes enabled
via disabled

4. we are connecting using an sql account. however, i couldn't see the option to allow mixed. i could only choose between windows authentication and sql server authentication.

thanks|||ok, just found the sql server surface area configuration. will check and report back.
|||remote connections is set to "local and remote connections" and using tcp/ip only.|||sql server and windows authentication mode were selected. so it looks like everything was configured they way you wanted.|||

Hi peter, sorry for the delay,

sql server and iis is on same machine so can you please test the following connectionstrings:
Provider=SQLNCLI;Server=(local);Database=xxx;Uid=xxx;Pwd=xxx
or
Provider=SQLNCLI;Server=127.0.0.1;Database=xxx;Uid=xxx;Pwd=xxx

and please inform me about the result.