Using DISTINCT and COALESCE together?


Below is a query which gives me the Usernames of the Last-10 Visitors to a Member's Profile...
Code SQL: // FORMER QUERY $q8 = "SELECT COALESCE(m.username,'Non-Member') AS username FROM visitor_log AS v LEFT OUTER JOIN member AS m ON m.id = v.visitor_id WHERE v.member_viewed_id= ORDER BY v.created_on DESC LIMIT 10";
It produces output like this...
The last 10 Visitors on this page were...
username4
username4
JohnDoe
JohnDoe
Non-Member
Non-Member
Non-Member
Non-Member
Non-Member
Non-Member
Is there a way to use DISTINCT so that I would instead get a list like this...
The last 10 Visitors on this page were...
username4
JohnDoe
Non-Member
Posted On: Monday 31st of December 2012 01:44:53 AM Total Views:  360
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Query challenge: time intervals and grouping

I'm close, but cant quite figure out how to write a query for the following situation: I have a table called "store_sales" that records sales at several stores. There are two columns: a datetime called "dt" and a varchar called "name". When a store opens or closes, the date and time is recorded in "dt" and the store's name is recorded in "name". I want to create a query that results in a table that has one column for each store and a row for every date that sales took place. Each cell of the table should contain the duration of time (time of last sale - time of first sale) that the store was selling items for that date. The reason that I want this table is so I can then use it to figure out the average lenght of time that stores were selling items for a particular day. Perhaps there's another way to do this without the table I'm suggesting. So, alternatively, can anyone tell me how to write a query to give, for every day that stores were selling, the average length of time that a store was selling items for that day. Any ideas I'm close, but I was trying to solve with grouping, but if I group store, I aggregate dates and vice versa, so that doesn't work.
VIEWS ON THIS POST

62

Posted on:

Monday 29th October 2012
View Replies!

Adding hours to Date_Format()

The web server my site is hosted on is on the East Coast, and I'm located here on the West Coast. How can I subtract 3 hours from the string below, which uses the server's timezone PHP Code: $Adate="Date_Format(Current_Timestamp,'%a%b%e,%Y%h:%i%p')"; I've read that INTERVAL has something to do with it.
VIEWS ON THIS POST

67

Posted on:

Monday 29th October 2012
View Replies!

INNODB Speed Issue w/Inserts vs Load Data

Here are some thoughts about it. LOAD DATA INFILE is generally 20 times faster than INSERT statements, according to the manual. Each index takes roughly the same time as inserting the data alone. => One index = half the speed. (But this is really only true for MyISAM tables, read more explanation below.) InnoDB tables are actually stored as index tree's with the data as the leafs. Using the Primary Key as the tree structure so you can't really get away with turning of the primary key since it is needed for the storage structure (this is my theory based on how it stores the data, so somebody more knowing about InnoDB internals correct me if I'm wrong). But any additional indexes should be able to disable and gain speed. But I'm guessing that one of your biggest probles is that you have AUTOCOMMIT on. Which means that each statement is commited separately. This in turns means writing it to the log and since the default setting for innodb_flush_log_at_trx_commit is 1 it must also flush the log to disk before continuing with the next statement. Is your tablespace big enough so that it can store all data or are you using autoextend I know that on Oracle you got a big slow down when it started to autoextend the tablespace. Suggestions to improve: Run the inserts within a transaction with a reasonable bunch of commands in the same transaction. This way you will get less log writes/flushes to disk which should speed up things a lot. Something like (I haven't tried it myself, so you will be the first to know ). Code: BEGIN; INSERT ... COMMIT; If you can live with it. Set the innodb_flush_log_at_trx_commit to 0 which means that it will only flush it to disk about once a second. But read about the drawbacks in case you server goes down etc.
VIEWS ON THIS POST

48

Posted on:

Monday 29th October 2012
View Replies!

MySql to Postgres

I am a recent graduate and have recently joined my job. I have been assigned a task of introducing a layer between the c++ program and its connectivity with database which would translate a query to any given database which in this case is postgres. We are using mysql++ and I have to write the code such that it works with any database tool. Could anyone please guide me
VIEWS ON THIS POST

38

Posted on:

Wednesday 7th November 2012
View Replies!

Is this query is correct

is this query is correct for retriving that value which is checked "select * from latestNews where active=1"
VIEWS ON THIS POST

62

Posted on:

Wednesday 7th November 2012
View Replies!

Select query..(Quite urgent)..

Hi Friends, in my application i need to do the following.. i have a one tbl(named as tbl1) and have the records like the following tbl1 id lotno qty type serialno 0 43243 8.0 test 123456 0 54236 8.7 result 123456 0 34535 8.8 return 123456 in the above table i have 3 type of record for the same serial no..so in that i need to insert into these 3 types records into one row at another table like the following.. tbl2 qty test(type of tbl1) result return 8.0 42243(lotno of tbl 1) 54236 34535 the above is output i need.. actully in this table(tbl2)all the coumns (based on tbl1 type)..so i need to select lotno on the first table(tbl1) based on the type and insert into one row like the above output table(tb2) and the qty is based type test. and in my first table(tbl1) , here i showed only one serialno , but actually i have many serialno like that.. each serial no have 3 type os records and some have 2 types of records what query will make this things.. i am using mysql datbase and really quite urgent.. waiting for your valuable reply.. with regards, krish
VIEWS ON THIS POST

67

Posted on:

Wednesday 7th November 2012
View Replies!

Maximum date for each autor

Can someone help me out on this: I have a table 'articles' ID --- date --- authorID 1 --- 20060901 --- 1 2 --- 20060902 --- 1 3 --- 20060903 --- 3 4 --- 20060904 --- 3 5 --- 20060905 --- 2 I need a query (ID, date, authorID) that will produce the records that have maximum date for each author (distinct authors), ordered by date descending. For example, output should be in this case: 5 --- 20060905 --- 2 4 --- 20060904 --- 3 2 --- 20060902 --- 1 Hope that you understand what I mean... Please help! P.S. Don't worry about using the reserved word in the field name 'date', it has another original name...
VIEWS ON THIS POST

50

Posted on:

Wednesday 7th November 2012
View Replies!

Sql Help

Hi Everyone, I have a simple question regarding outer join. Please see the attached word file. It has screen shots of the query I am running. My first query shows the result where i have M.ReservationID = MA.MeetingID and it counts NoofRSVP (# of times the query runs). I have to modify first query in such a way that it returns records from eCDReservations table even if there is no matching MeetingID in MeetingAttendees table (means Null, see the result of 2nd query in attached file). So in my result for that case NoofRSVP column should show either Null or 0.
VIEWS ON THIS POST

294

Posted on:

Wednesday 7th November 2012
View Replies!

how i can update only certain records within a table

Originally posted by : Simon Parry (simon@cbox.co.uk)Can somebody tell me how i can update only certain records within a table. For examplei have a table Customerid, product, reservedthe reserved field is either (yes/no)a customer has the option of reserving a batch of products, i.e 7 products, i need to find a way of just updating 7 records instead of them all for a particular customer.this is the statement i am using at the momentUPDATE numbersSET reserved ='Yes' , Customerid =custnoWHERE type = 'bannana', reserved ='No'this updates all of the records
VIEWS ON THIS POST

82

Posted on:

Wednesday 7th November 2012
View Replies!

SQLserver

Originally posted by : zidane2 (hokalun@sinaman.com) if i use the SQL server to store data to run ASP, what SQL connectionstatement is within the code of XXX.asp.that mean set conn........ set as..........any other statementplease specif
VIEWS ON THIS POST

92

Posted on:

Wednesday 7th November 2012
View Replies!

Row Count in Results

Here is some sample Data: Code: Area G_C Asia 3 EMEA 10 Americas 19 What I would like to do is to add a third column that contains the total number of rows. For instance: Code: Area G_C Col_Count Asia 3 3 EMEA 10 3 Americas 19 3 I thought I'd add I am using an Oracle Database.
VIEWS ON THIS POST

55

Posted on:

Saturday 10th November 2012
View Replies!

MySQL help

I have two tables in a database, one table is used to store bulletin information (called 'bulletins'), and the other table is used to keep a record of who the bulletins have been sent to and when (called 'history'). To display a history of who the bulletins have been sent to I simply select the history data from the history table and the bulletin title and description from the bulletins table, I join the two together through an ID. The problem is though if I delete a bulletin from the bulletins table, the SQL to display the history won't return data based on any bulletins which have since been deleted. Is there a way to put something in the SQL that effectivly says "if a matching ID isn't found in the bulletins table, still execute the SQL but return the bulletin title and description values as 'DELTED'" I hope this makes sense
VIEWS ON THIS POST

52

Posted on:

Saturday 10th November 2012
View Replies!

is it possible to query 2 database, one server?

is it possible to query 2 database, one server If yes, pls. help
VIEWS ON THIS POST

86

Posted on:

Saturday 10th November 2012
View Replies!

SQL: Quering the results of a subquery.

Hi Folks, Hope someone can help with this, I'm a newbie to SQL. I have a list of movie names and where and when these movies are showing. What I want to do is return a distinct list of movies by name. Users can then drill down for a full list of where and when that movie is showing. Here's the code I'm currently using: Code: SELECT movieGenre.mgKey, movieLineUp.mluDateStart, movieLineUp.mluEventStart, movieEventName.menMovieEventName, movieLineUp.mluChanName, movieLineUp.mluPremier, movieGenre.mgGenreName, movieLineUp.mluKey FROM movieLineUp INNER JOIN (movieEventName INNER JOIN movieGenre ON movieEventName.men_snKey = movieGenre.mgKey) ON movieLineUp.mluMovieTitle = movieEventName.menRefMovieEventName WHERE (((movieLineUp.mluDateStart)>#10/12/2004#) AND ((movieEventName.menMovieEventName) Like "%the%")) ORDER BY movieLineUp.mluDateStart, movieLineUp.mluEventStart; This code works ok, but returns multiple instances of the same movie, with the various start times. What I'd like to do is use the above as a subquery, and then use SELECT FIRST(movieGenre.mgKey)AS...etc to return the first entry for each movie name. Problem is, I can't seem to put this into practice. Hope someone can help, G
VIEWS ON THIS POST

60

Posted on:

Saturday 10th November 2012
View Replies!

Recordset Check

I am attempting to create an SQL statement that checks my database to see if a soccer game has already been entered into the database. This might seem as simple as doing a Team name match with a date check, however this is not the case. Since a tournament can happen multiple games can be played on one day, therefore requiring me to check both the home team and the opposing team and the amount of goals scored for each team. Currently I have the table in the db set up like this: T1= home team T2= opposing team T1Goals = Home goals T2Goals = opposing goals When a coach submits scores I need the database to first sort out all games between the two teams submitted T1 and T2. From there, using those sorted I need it to see if the goals match up; T1Goals and T2Goals. If those match, then there will be no record added. Currently this is what I have: strSQL = "SELECT ID FROM Results WHERE T1='" & request("T1") & "' AND T2='" & request("T2") & "';" THEN '"SELECT ID FROM Results Where T2Goals='" & request("T2Goals") & "' AND T1Goals='" & request("T1Goals") & "';" Please assist me with my SQL statement!
VIEWS ON THIS POST

46

Posted on:

Saturday 10th November 2012
View Replies!

Dynamic Button Label

im trying to dynamically label a submit button, later on i will want to enable/disable the button depending on the value of hasVoted.. Im not a programmer (can you tell lol) and i know theres alwas a better way of doing things . . . . . .
VIEWS ON THIS POST

50

Posted on:

Saturday 10th November 2012
View Replies!

Only able to use numbers in a form post text field ??

Originally posted by : Mal (mal@go-planit.co.uk)Can only use numbers in a text form field\t\t\tOn the posted page I have:rdev=request.form("rdev")I wish to put the results in the database so I'm using:db.execute("update orders set rdevref= " & rdev & " where orderid=" & valid_sql(orderid))but it will only allow me to use numbers in the form text field, I wish to use any character. if I get rid of the db.execute I can put anything in. It's definitely passing it through because I checked with a response.write. I get Http error 500 page returned if I try to use anything other than numbers. The database feild can definitely take any character as if I change the db.execute bit to rdevref='test///***' (or anything similar) it puts it in. So I'm suspecting rdevref= " & rdev & " is coded incorrectly.
VIEWS ON THIS POST

63

Posted on:

Saturday 10th November 2012
View Replies!

SQL Query

I hope that this is in the right location. My problem I have a MS Access database with a table Called Clients. In that table there is miscellanous details about the client including their e-mail address, username and password address, requirements etc. Im trying to get the user to sign in with their username and password and when they do it displays the rest of their details from the database onto the webpage. I would be extremely greatful if anyone could give me some examples or point me in the right direction.
VIEWS ON THIS POST

37

Posted on:

Sunday 11th November 2012
View Replies!

Insert into using Domino front end

I'm creating a front end via Domino 6.0.3 to access an AS400 and insert values captured from a form into an existing table When the agent executes I gett he following error: [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0104 - Token was not valid. Valid tokens: ( : WITH SELECT VALUES ., Connector 'odbc2', Method -Execute- (-104) The agent is as follows: Sub Initialize On Error Goto Handler Dim sess As NotesSession Dim FieldOut As New LCFieldList Dim count As Long Dim Session As New LCSession Dim server As String session.ClearStatus Dim ZConType As String Dim ZConSvr As String Dim ZConUid As String Dim ZConPwd As String Set src = New LCConnection ("odbc2") src.server = "Live400" src.UserID = "LOTUS" src.Password = "SUTOL" src.Connect Set sess = New NotesSession Set doc = sess.DocumentContext Dim xs As String Dim y As String xs= sess.CommonUserName If xs = "xxxxx" Then y="xxxxx.domain name.org" Else y="yyyyyy.domain name.com" End If Server =y &"/"& doc.DbName(0) TxtDta = doc.xusr(0) NumPdln = doc.xprdln(0) NumPo = doc.xpo(0) MxtDta = doc.xso(0) CnCde= doc.xcnfcd(0) SF = "Insert Into TEST2.TSTPOCON (XUSR,XPRDLN,XPO,XSO#, XNCFCD)" SF = SF & " values ('" & TxtDta & "', " & NumPdln & ", " & NumPo & ", " & MxtDta &","" & CnCde &"')" doc.MsgFld = SF (thisis where the error message is shown) success = src.Execute(SF, FieldOut) doc.MsgFld = "Record Added" Exit Sub End End Sub The initial SF= call seems to be the casue of the token error.
VIEWS ON THIS POST

62

Posted on:

Sunday 11th November 2012
View Replies!

Multiple-step error message

Originally posted by : Robert Ramos (Robert.Ramos@DRMCI.ORG)I am just learning ASP and I've come across an SQL server error message that I cannot find to determine how to fix my program. The error message is 'Microsoft OLE DB Provider for SQL Server error 80040e21. Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.' I'm trying to do is add a new record using ADDNEW to a file.
VIEWS ON THIS POST

107

Posted on:

Sunday 11th November 2012
View Replies!

inserting pictures into a table

Originally posted by : venkat (venkat_karnati@hotmail.com)I want to insert n number of pictures(photos) into a database table field.Next i want to retrieve the same and display on the browser.can some body help me!
VIEWS ON THIS POST

131

Posted on:

Sunday 11th November 2012
View Replies!

Beginner Query Problem

I am trying to write a query to return the number of times a datetime variable is between two datetime values in a table...in my head it should look something like this... declare @dateget datetime, @datestart datetime, @datestop datetime; set @datestart = '20090101 00:00:00' set @datestop = '20090131 23:59:59' set @dateget = @datestart while @dateget = TABLE_VALUE1 and @dateget
VIEWS ON THIS POST

56

Posted on:

Monday 12th November 2012
View Replies!

Unknown Syntax Error - Probably a simple fix

I am constantly getting an error 80040e14 Syntax Error in INSERT INTO Statement in the following SQL query, but I am currently unable to locate where it is. Can somebody help me out SQL = "INSERT INTO comments (entryID,timestamp,authname,email,comment) VALUES (" & comEntryID & ",#" & comTimestamp & "#,'" & comAuthname & "','" & comEmail & "','" & comComment & "');" fields in table "comments": entryID=number field timestamp=date/time field authname=text field email=text field comment=extended text field (aka "memo" in MS Access 2000)
VIEWS ON THIS POST

67

Posted on:

Monday 12th November 2012
View Replies!

mySQL saving help

, Im using mySQL 4.0.17. I have a piece of JDBC code that sends querys to the mySQL server to create a table add values to the tables etc. My problem is saving this information to a file so that when the sql server is disconnected or computer is restarted a txt file or something can be loaded to get me back to where i was when the restart happened. There are a few sites i have found that talk about saving, but most of them are talking about mySQL and PHP. Im just using mySQL not PHP or access. can anyone help
VIEWS ON THIS POST

153

Posted on:

Thursday 15th November 2012
View Replies!

How to control multiple users?

Originally posted by : Venkatesh (bsvenki@hotmail.com)dear friend.I have to lock the record for other userwhen some one is editing the same records.I included adovbs.inc file and alsospecified lock type as adlockoptimisticand cursor type as adopendynamic butnothing seems to be working so if youhave any idea, help me out
VIEWS ON THIS POST

97

Posted on:

Thursday 15th November 2012
View Replies!

Parameters Question

Should I change the parameters from SqlDbType = SqlDbType.Int to SqlDbType.NVarChar Code: protected void Page_Load(object sender, EventArgs e) { SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlapplicationConnectionString"].ToString()); try { SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "INSERT INTO Application (" + " First, Last, City, State" + ") VALUES (" + " @First, @Last, @City, @State"; cmd.Parameters.Add(new SqlParameter() { ParameterName = "@First", SqlDbType = SqlDbType.Int, Value = Convert.ToInt32(Request["First"]) }); cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Last", SqlDbType = SqlDbType.Int, Value = Convert.ToInt32(Request["Last"]) }); cmd.Parameters.Add(new SqlParameter() { ParameterName = "@City", SqlDbType = SqlDbType.Int, Value = Convert.ToInt32(Request["City"]) }); cmd.Parameters.Add(new SqlParameter() { ParameterName = "@State", SqlDbType = SqlDbType.Int, Value = Convert.ToInt32(Request["State"]) }); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } catch (Exception ex) { } finally { if (conn.State == ConnectionState.Open) conn.Close(); conn.Dispose(); } }
VIEWS ON THIS POST

58

Posted on:

Monday 19th November 2012
View Replies!

Failure comverting datetime

I have a SQL 2005 DB and using ASP VB. I have a form that a user selects 'Month' and 'year' to run a 6-month forecast report. I'm trying to make the results page load up the next 6 months'-worth of month names in a table. So if a user selects 'June', the results page will load up 'June', 'July' etc. But whatever I try, when I run the code in the browser it just gives me the error: Conversion failed when converting datetime from character string. PLease can anybody help with this, I've not found anything through searching that's helping as yet. Code: Untitled Document                                    
VIEWS ON THIS POST

77

Posted on:

Monday 19th November 2012
View Replies!

Delete Script Not Working

Hi I have a script which creates a link from the select records to pass across the id variable. My backend script will then collect the variable and delete the record in the database where it is = to that id. My script looks as though it works, i get no errors but it doesnt actually delete out of the database Below are my two scripts any help is great. Administrator.ASP Code: DELETE RECORD var StayAlive = 0; // Number of seconds to keep window open function KillMe(){ setTimeout("self.close()",StayAlive * 1000); }
VIEWS ON THIS POST

46

Posted on:

Monday 19th November 2012
View Replies!

Is my case really an ordinary crosstable?

First post, ! I'm having trouble with a query to extract rows having a certain common field into columns. Well, at least that's a try to explain it, but I guess the practical example will be more expressive ... I have a table containing terms in different languages, all linked to a main table containing the terms id (and all common data). Basic table layout: Terms: id(pk) TermLang: term_id(pk,fk), lang(pk), term Example data in table TermLang: Code: term_id lang term 1 sv hej 1 en hello 1 de hallo 2 sv mjlk 2 sv milk 3 sv sill Now I want this output: Code: id sv.term en.term de.term 1 hej hello hallo 2 mjlk milk 3 sill I've managed to get it right for the case when every term has a translation in each of the languages: Code: SELECT sv.term_id, sv.term, en.term, de.term FROM (( TermLang AS sv LEFT JOIN TermLang AS en ON sv.term_id=en.term_id ) LEFT JOIN TermLang AS de ON sv.term_id=de.term_id ) WHERE sv.lang='sv' AND en.lang='en' AND de.lang='de' ORDER BY sv.term But since my query uses this WHERE clause the LEFT JOIN doesn't help me to get records even if the translation is missing in any of the languages. Since it's a matter of rows as columns I guess it could be expressed as a crosstable query, but I can't figure out how to do it. It doesn't look analogous to the examples shown in all crosstable tutorials. --- Sorry for the bad alignments. --- Edit: I use Access 2000 by the way.
VIEWS ON THIS POST

60

Posted on:

Monday 19th November 2012
View Replies!

COUNT occurence between 2 tables

Say I have 2 tables: Fruits: id name 1 apple 2 orange 3 banana 4 grape Barrel: fruit apple apple orange banana banana banana How would I write a single MS SQL query to retrieve how often apple, orange, banana, and grape occur in the table Barrel. The result should return: apple 2 orange 1 banana 3 grape 0 I have trouble writing this query, since grape does not occur in Barrel, when I try to do a join of the 2 tables.
VIEWS ON THIS POST

71

Posted on:

Monday 19th November 2012
View Replies!

SQL Code 'group by problem'

, have a bit of code that needs to do a select into and group by but also needs to do a where clouse. Can anyone please help me out with the syntax. Insert into referal_URL (Page_URL, Hits, AdvertiserID) select ReferalID, count(ReferealID) as Total, AdvertiserID, Affstats from cookie_01 where Affstats group by ReferealID,AdvertiserID
VIEWS ON THIS POST

84

Posted on:

Monday 19th November 2012
View Replies!

Updating datetime value with NULL

I'm trying to figure out how to insert a NULL value into a datetime column that already contains a date. The column contains NULL values by default before a date is inserted into it, but how can I insert NULL back into it
VIEWS ON THIS POST

116

Posted on:

Monday 19th November 2012
View Replies!

db populate listbox

Originally posted by : Tim (timothy.farrell@compuware.com)I am having a hard time locating a tutorial on how to dynamically populate a listbox from a database. It used to be pretty simple with old ASP (or VS6.0) but now things have changed.Can someone pooint me in the right directionHere are the specifics:SQL db1 Tblcolumn = SecurityI want this form field on the web form to be populated by a seperate tbl named UserAccess. UserAccess contains the id and the desc of the the security values available for admin selection.Help please!Thank you for your thoughts.Sincerely,Tim
VIEWS ON THIS POST

59

Posted on:

Tuesday 20th November 2012
View Replies!

Date Format Problems

Originally posted by : David (davo__@hotmail.com)I have been having various problems with formating dates to the australian format (dd/mm/yyyy). I am using ACCESS, the query I am sending to the database is:UPDATE tailback SET tailback_status='In Progress', tailback_processdate=#01/10/2001#, tailback_processtime=#15:23:13#, tailback_processps='dr' WHERE tailback_refnumber=12313 In the field tailback_processdate (in table "tailback") I have set the custom format to dd/mm/yyyy.However in the database the field tailback_processdate is displayed as 10/01/2001.Why is this! The custom format should overide any regional settings, shouldn't it!
VIEWS ON THIS POST

86

Posted on:

Tuesday 20th November 2012
View Replies!

how to put the images on database and retreive them using the search

Originally posted by : VIMAL BISHT (vimal_bisht@yahoo.com)I am developing a web site and I want to put a search engine. In my web site I haveto make a database where I have to store the 5000 images and then retreive them usingthe search engine. Please tell me from the begning how to do it. I want to use the Sql and ASP.
VIEWS ON THIS POST

165

Posted on:

Tuesday 20th November 2012
View Replies!

Problem with BIG Query - Running slow

I am new to SQL Querys and I have the following Query that takes a while to run, is there any way I can change this code in places to speed it up i.e. Tips, tricks .. Select * From ( SELECT L2OCUST.CUSTACOM, L2OCUST.CUSTCUNO, L2ODISC.DISCPRDC, Trim([L2ODISC.DISCCGRP]) AS CUSTCGRD, L2OTC08.CUSTDIGD, Trim(L2OTP08.PRODGLDD) AS PRODGLDD, L2OTP22.PRODPDGD, L2OTP03.PRODAGRD, L2OTP02.PRODPGRD, L2ODISC.DISCDISP, L2ODISC.DISCCGRP, Trim([L2ODISC].[DISCDISG]) AS DISCDISG, L2ODISC.DISCGLDG, L2ODISC.DISCPDGR, L2ODISC.DISCAGRP, L2ODISC.DISCPGRP FROM (((((L2ODISC LEFT JOIN L2OTC08 ON L2ODISC.DISCDISG = L2OTC08.CUSTDISG) LEFT JOIN L2OTP08 ON L2ODISC.DISCGLDG = L2OTP08.PRODGLDG) LEFT JOIN L2OTP22 ON L2ODISC.DISCPDGR = L2OTP22.PRODPDGR) LEFT JOIN L2OTP03 ON L2ODISC.DISCAGRP = L2OTP03.PRODAGRP) LEFT JOIN L2OTP02 ON L2ODISC.DISCPGRP = L2OTP02.PRODPGRP) INNER JOIN L2OCUST ON L2ODISC.DISCCOCU = L2OCUST.CUSTCOCU WHERE (((L2OCUST.CUSTACOM)='BR') AND ((Trim([DISCCGRP]))='') AND ((Trim([DISCDISG]))='')) UNION SELECT L2ODISC.DISCACOM AS CUSTACOM, L2OCUST.CUSTCUNO, L2ODISC.DISCPRDC, Trim([L2ODISC.DISCCGRP]) AS CUSTCGRD, L2OTC08.CUSTDIGD, Trim(L2OTP08.PRODGLDD) AS PRODGLDD, L2OTP22.PRODPDGD, L2OTP03.PRODAGRD, L2OTP02.PRODPGRD, L2ODISC.DISCDISP, L2ODISC.DISCCGRP, Trim([L2ODISC].[DISCDISG]) AS DISCDISG, L2ODISC.DISCGLDG, L2ODISC.DISCPDGR, L2ODISC.DISCAGRP, L2ODISC.DISCPGRP FROM L2OCUST INNER JOIN (((((L2ODISC LEFT JOIN L2OTC08 ON L2ODISC.DISCDISG = L2OTC08.CUSTDISG) LEFT JOIN L2OTP08 ON L2ODISC.DISCGLDG = L2OTP08.PRODGLDG) LEFT JOIN L2OTP22 ON L2ODISC.DISCPDGR = L2OTP22.PRODPDGR) LEFT JOIN L2OTP03 ON L2ODISC.DISCAGRP = L2OTP03.PRODAGRP) LEFT JOIN L2OTP02 ON L2ODISC.DISCPGRP = L2OTP02.PRODPGRP) ON L2OCUST.CUSTCGRP = L2ODISC.DISCCGRP WHERE (((Trim([DISCCGRP]))'') AND ((L2OCUST.CUSTACOM) ='BR')) UNION SELECT L2ODISC.DISCACOM AS CUSTACOM, L2OCUST.CUSTCUNO, L2ODISC.DISCPRDC, Trim([L2ODISC.DISCCGRP]) AS CUSTCGRD, L2OTC08.CUSTDIGD, Trim(L2OTP08.PRODGLDD) AS PRODGLDD, L2OTP22.PRODPDGD, L2OTP03.PRODAGRD, L2OTP02.PRODPGRD, L2ODISC.DISCDISP, L2ODISC.DISCCGRP, Trim([L2ODISC].[DISCDISG]) AS DISCDISG, L2ODISC.DISCGLDG, L2ODISC.DISCPDGR, L2ODISC.DISCAGRP, L2ODISC.DISCPGRP FROM L2OCUST INNER JOIN (((((L2ODISC LEFT JOIN L2OTC08 ON L2ODISC.DISCDISG = L2OTC08.CUSTDISG) LEFT JOIN L2OTP08 ON L2ODISC.DISCGLDG = L2OTP08.PRODGLDG) LEFT JOIN L2OTP22 ON L2ODISC.DISCPDGR = L2OTP22.PRODPDGR) LEFT JOIN L2OTP03 ON L2ODISC.DISCAGRP = L2OTP03.PRODAGRP) LEFT JOIN L2OTP02 ON L2ODISC.DISCPGRP = L2OTP02.PRODPGRP) ON L2OCUST.CUSTDISG = L2ODISC.DISCDISG WHERE (((Trim([L2ODISC].[DISCDISG]))'') AND ((L2OCUST.CUSTACOM) ='BR')) ) as inLineView Order By inLineView.CUSTACOM, inLineView.CUSTCUNO, inLineView.CUSTCGRD, inLineView.CUSTDIGD, inLineView.PRODGLDD, inLineView.PRODPDGD
VIEWS ON THIS POST

44

Posted on:

Tuesday 20th November 2012
View Replies!

Doubling Insert

Hi For some reason the following SQL INSERT statement is adding two records and I can't work out why. It's a simple form with 3 dropdowns, created from database entries and another text field. The action returns to the same script, but filters based on content_length being 0 and goes to the following script: .....Creates the form. The connection include just sets conn as ADODB connection and the database DSN. Can anyone shed any light on this problem I can't see how it's calling this INSERT statement twice.
VIEWS ON THIS POST

46

Posted on:

Tuesday 20th November 2012
View Replies!

Altering Column Names

I have table Employee. It contains the columns : name, phone, addr1 I would like to change the addr1 column to Address. How can I do this
VIEWS ON THIS POST

97

Posted on:

Tuesday 20th November 2012
View Replies!

WHERE Member_Num = & XMreadCookie("memberCookie") ???

.
VIEWS ON THIS POST

61

Posted on:

Tuesday 20th November 2012
View Replies!

Database connection and forms

Originally posted by : Jen (jennifer.jacobs@inet.com)I hope this isn't a completely ignorant question, but I am having a problem with database connections and forms. On my first asp page I have a form that has two dropdown lists from the same database - the responses to which feed the form on the next page, and so on. I am getting an "unspecified error" where the drop down lists should appear. If I am accessing the database only once, but creating two different drop down lists then I am assuming I don't need to create an ADODB connection each time. I can create it once and use it all throughout the page. Also, I was assuming that I could create the same connection on each subsequent form. Do I have to create a separate ADODB connection each time I hit the database and give each connection a different name, e.g. conna for the first drop down and connb for the second drop down and connc for the 3rd drop down on the 2nd asp page. Also, where is the correct place to close a connection when using a form Sorry if this is a stupid question.
VIEWS ON THIS POST

77

Posted on:

Tuesday 20th November 2012
View Replies!

date problems

Originally posted by : Johan Veldkamp (johan@so-studios.nl)Hai Everybody,I have a hughe problem with a date in ASP and Access.I am making an event database which is accessible from our Intranet. First you can make events, but how can they choose the right date and time The date and time must have the same value as the access default value for date and time.Then I put the event in the database, and after that someone goes to the eventintranetpage and looks for the events in the future, how can i sort them on the dateThat are my problems in the first, maybe next time there are more.
VIEWS ON THIS POST

54

Posted on:

Tuesday 20th November 2012
View Replies!

Insert one table into another

I have one table which i want to insert into another table. How can i do this. This is what i have so far. Code: INSERT INTO dbo_tbl_note (dbo_tbl_note.Note, dbo_tbl_note.Propref) SELECT (note.NOTE_TYPE, note.PROP_REF) FROM note;
VIEWS ON THIS POST

49

Posted on:

Tuesday 20th November 2012
View Replies!

Forcing line breaks?

Is there any way to force a line break within a SQL statement I want to combine name and address columns into one field, but force a line break in the appropriate places. Any thoughts
VIEWS ON THIS POST

40

Posted on:

Tuesday 20th November 2012
View Replies!

Classic asp ado constants nvarchar(MAX)

Does anyone know the correct method to accept nvarchar(MAX) as output in classic asp I tried Code: set parTemp = .CreateParameter ("outparam", adLongVarChar, adParamReturnValue, 30000) But error report suggests this datatype is depreciated and I have to use something else... but what\t maybe a nub question but im new to sql 2005....
VIEWS ON THIS POST

85

Posted on:

Monday 26th November 2012
View Replies!

70% of volume are made by X% of recordsets

I spent 3 days trying to create a querry to count stats which seeam to be easy, but are not so. I collect statistical data (clicks, sales etc) about thousands hotels in hundreeds cities. I need to know how many hotels receive 70% of visits (clicks) in every city. For example 70% of all clicks in Paris are received by 45 hotels of all 67 hotels in Paris 70% of all clicks in London are received by 55 hotels of all 98 hotels in Paris etc for every city. Are there any chances to count above
VIEWS ON THIS POST

114

Posted on:

Monday 26th November 2012
View Replies!

SQL image field

Would someone be able to explain the sql image field to me. I was reading about it a little but dont really understand. I would like to have word documents that can be indexed and searched and i heard the image field with an accompanying char field with the file extension would be the trick. how would i actually get binary files into the image field
VIEWS ON THIS POST

40

Posted on:

Monday 26th November 2012
View Replies!

SQL Data Types - Please Help

I have a search page that has several options to search a SQL Server 2000 database. The text box is the string, radio buttons to decide which field to search by.. Code: 'Request querystring search field first If srcBy = "" Then 'Determine what field to search by If Request.Form("rSel") = "S1" Then strSearchBy = "SSNUM" End If If Request.Form("rSel") = "S2" Then strSearchBy = "PATACNTNUM" End If If Request.Form("rSel") = "S3" Then strSearchBy = "NSRPLCYNUM" End If If Request.Form("rSel") = "S4" Then strSearchBy = "PATIENTNAM" End If Else strSearchBy = Request.QueryString("srcBy") End If And then the query to the database: Code: If strID = "" Then 'Initialize the strSQL variable with a SQL statement to query the database. strSQL = "SELECT * FROM tblVitalData WHERE " & strSearchBy & " = " & strSearch Else strSQL = "SELECT * FROM tblVitalData WHERE " & strSearchBy & " = " & strSearch & " AND VITALID=" & strID & "" End If 'Query the database. rsInfo.Open strSQL, strCon, 1, 3 'This is line 158 If SSNUM or PATACNTNUM are selected (which are type float(8)), I have no problem searching the database. If NSRPLCYNUM (a policy number type nvarchar(255)) is selected, and I search for 1234A I get this error: Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'A'. /vitaldata/search.asp, line 158 The same thing happens if I select PATIENTNAM (also nvarchar(255)) and search for John Smith. It would say error near Smith. Can someone please help here\t
VIEWS ON THIS POST

62

Posted on:

Monday 26th November 2012
View Replies!

PostgreSQL - what are the best resources on the net?

PostgreSQL -what are the best resources on the net Can anybody submit good links, tutorials, links to references, and recommended books
VIEWS ON THIS POST

114

Posted on:

Sunday 30th December 2012
View Replies!

Applying Mutliple Aggregate Funcs to the Same Columns

I'm trying to retrieve 2 Averages from records stored in a table of "Business Reviews". The businesses are reviewed in several categories such as "service," "cleanliness," etc... The scores are based on a scale between 1 - 5. The reviews are then compiled based on category into a table with columns structured as: business_id, question_category, R1, R2, R3, R4, R5 R1-R5 of course being how many people gave the business a score of 1, or 2, or 3... I first need to total and average the business's scores per category. I then need to see what percentage of that category score was made up by people rating them at the highest possible level of "5" My query works, but I'm sure there's a cleaner more efficient way. Perhaps a derived table or a self-join would be of use. Here's my code: Code: select business_id, category_id, avg(r1 + r2 + r3 + r4 + r5) as total_avg, (sum(r5) / (sum(r1) + sum(r2) + sum(r3) + sum(r4) + sum(r5)) * 100) as top_score_avg from results group by business_id, category_id Sorry for the long winded, convoluted question.
VIEWS ON THIS POST

81

Posted on:

Sunday 30th December 2012
View Replies!

"commands Out Of Sync . . " Mysqli Error

Below is the code and details of what is happening is below. PHP Code: $stmt=$mysqli->prepare("SELECTquestion,uniqueidFROMpollquestionWHEREstatus=1"); $stmt->execute(); $stmt->bind_result($question,$uniqueid); while($stmt->fetch()) { echo"$question"; if(!$mysqli->query("SELECToptionnameFROMpollchoicesWHEREpollid=$uniqueid")){echo"Multi-INSERTfailed:(".$mysqli->errno.")".$mysqli->error;}else{ $stmt2=$mysqli->prepare("SELECToptionnameFROMpollchoicesWHEREpollid=\t"); $stmt2->bind_param('s',$uniqueid); mysqli_free_result(); $stmt2->execute(); $stmt2->bind_result($option); while($stmt2->fetch()){echo"$option";} $stmt2->close(); } echo""; } $stmt->close(); With this I get the error "Multi-INSERT failed: (2014) Commands out of sync; you can't run this command now." I looked it up on Google and it was saying I should use multi_query. I replaced the query with multi_query and still no luck and no change in error. So I changed it back. I do stuff like this a lot in my coding and never came across this error. Should I be using multi_query I never used it before, if so - how should I be using it This is what I am trying to get to happen: I get the question for the poll and the unique id. With the unique id I can find out what poll options goes with it. That is all I want to do and I cannot get the poll options to show.
VIEWS ON THIS POST

102

Posted on:

Sunday 30th December 2012
View Replies!