sql statment 1

[false420]

SELECT DISTINCTROW " & service & ".Carrier, " & service & ".Account, " & service & ".Cost_Center, " & service & ".Long_Date, Sum(" & service & ".Total) AS Total, Sum(" & service & ".Actual_Past_Due) AS Actual_Past_Due, Sum(" & service & ".Credit) AS Credit FROM " & service & ", UID_CostCenter WHERE (" & service & ".Long_Date BETWEEN #" & currYear & "# AND #" & nextYear & "#) AND " & service & ".Cost_Center = UID_CostCenter.Cost_Center AND UID_CostCenter.UID = '" & strSession_UID & "' Group By " & service & ".Cost_Center, " & service & ".Carrier, " & service & ".Account, " & service & ".Long_Date"


can anyone see whats wrong with this?

 

[tc3596]

This is obviously not in SQL Server. Is this Access? If this is a query being called and sent to SQL Server, distinctrow will not be recognized. Try Distinct instead.

 

[false420]

Hmm stil same error. see the thing is this was alot of asp for ms access. im trying to move everything over to sql server 2000 and im having trouble with this recordset stuff. im slightly new to sql , ive used the language for this asp i wrote but it was for access, now im kinda stuck

 

[tc3596]

Do you need dynamic tables for some reason?

 

[false420]

YES, im trying to pull data from the sql server to display. the user can select different periods for invoices and such. i need the functionality the same but i just cant get it to work with sql. any ideas or workaroundsw?

 

[false420]

Set objRS_DetailedInfo = Server.CreateObject("ADODB.Recordset")
objRS_DetailedInfpen strDetailedInfo, objConn, adOpenKeyset

this also may have to do with my cursor or the way i try to creat recordset

 

[false420]

i get the error on the line that tries to open the RS

 

[JamesLean]

2 things:

1) After you've built the SQL string on the ASP page can you Response.Write it to the screen and copy/paste it here so we can see exactly what you are trying to execute.

2) Can you post the actual error message you are getting.

--James

 

[false420]

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
ODBC driver does not support the requested properties.



here is the error staement, and im doing a response.wrirte now

 

[false420]

SELECT DISTINCTMobile.Carrier, Mobile.Account, Mobile.Cost_Center, Mobile.Long_Date, Sum(Mobile.Total) AS Total, Sum(Mobile.Actual_Past_Due) AS Actual_Past_Due, Sum(Mobile.Credit) AS Credit FROM Mobile, UID_CostCenter WHERE (Mobile.Long_Date BETWEEN #1/1/2005# AND #1/1/2006#) AND Mobile.Cost_Center = UID_CostCenter.Cost_Center AND UID_CostCenter.UID = 'admincii' Group By Mobile.Cost_Center, Mobile.Carrier, Mobile.Account, Mobile.Long_Date


here is what it gives me before trying to put it into a RS and open it. everything works fine until i try to open the recordset.

 

[cheerio]

a) if that is a true paste of the SQL you are missing a space between DISTINCT and Mobile.Carrier

b) # surrounding dates is an Access construct not used by SQL Server. It just wants single quotes and will convert the resulting strings into dates

c) although it should not break the query, it is preferable to use a proper join syntax rather than relying on the WHERE clause eg

FROM Mobile
JOIN UID_CostCenter
ON Mobile.Cost_Center = UID_CostCenter.Cost_Center
WHERE ...

 

[JamesLean]

OK, here's what you need to aim for. Note the use of single quotes to delimit dates. Also note the use of table aliases - this is particularly useful in your case as you only have to concatenate the variable table name once (in the FROM clause).

SELECT DISTINCT 
	t1.Carrier,
	t1.Account,
	t1.Cost_Center,
	t1.Long_Date,
	SUM(t1.Total) AS Total,
	SUM(t1.Actual_Past_Due) AS Actual_Past_Due,
	SUM(t1.Credit) AS Credit
FROM Mobile t1
	JOIN UID_CostCenter t2 ON t1.Cost_Center = t2.Cost_Center
WHERE t1.Long_Date BETWEEN '01/01/2005' AND '01/01/2006'
	AND t2.UID = 'admincii'
GROUP BY t1.Cost_Center,
	t1.Carrier,
	t1.Account,
	t1.Long_Date

--James

 

[JamesLean]

Also, just noticed, you don't need to use DISTINCT here as you are using GROUP BY with aggregates so the rows will always be distinct anyway.

--James

 

[false420]

Your right, now i dont get that error after taking out the #. im gonna try and mess with some of that code you made real quick. now it will just display a table with no info. 1 step closer. and this is what i get after a respoanse.wrtie after the rs is opened:

SELECT Mobile.Carrier, Mobile.Account, Mobile.Cost_Center, Mobile.Long_Date, Sum(Mobile.Total) AS Total, Sum(Mobile.Actual_Past_Due) AS Actual_Past_Due, Sum(Mobile.Credit) AS Credit FROM Mobile, UID_CostCenter WHERE (Mobile.Long_Date BETWEEN 1/1/2005 AND 1/1/2006) AND Mobile.Cost_Center = UID_CostCenter.Cost_Center AND UID_CostCenter.UID = 'admincii' Group By Mobile.Cost_Center, Mobile.Carrier, Mobile.Account, Mobile.Long_DateSELECT Local.Carrier, Local.Account, Local.Cost_Center, Local.Long_Date, Sum(Local.Total) AS Total, Sum(Local.Actual_Past_Due) AS Actual_Past_Due, Sum(Local.Credit) AS Credit FROM Local, UID_CostCenter WHERE (Local.Long_Date BETWEEN 1/1/2005 AND 1/1/2006) AND Local.Cost_Center = UID_CostCenter.Cost_Center AND UID_CostCenter.UID = 'admincii' Group By Local.Cost_Center, Local.Carrier, Local.Account, Local.Long_DateSELECT LD.Carrier, LD.Account, LD.Cost_Center, LD.Long_Date, Sum(LD.Total) AS Total, Sum(LD.Actual_Past_Due) AS Actual_Past_Due, Sum(LD.Credit) AS Credit FROM LD, UID_CostCenter WHERE (LD.Long_Date BETWEEN 1/1/2005 AND 1/1/2006) AND LD.Cost_Center = UID_CostCenter.Cost_Center AND UID_CostCenter.UID = 'admincii' Group By LD.Cost_Center, LD.Carrier, LD.Account, LD.Long_DateSELECT Misc.Carrier, Misc.Account, Misc.Cost_Center, Misc.Long_Date, Sum(Misc.Total) AS Total, Sum(Misc.Actual_Past_Due) AS Actual_Past_Due, Sum(Misc.Credit) AS Credit FROM Misc, UID_CostCenter WHERE (Misc.Long_Date BETWEEN 1/1/2005 AND 1/1/2006) AND Misc.Cost_Center = UID_CostCenter.Cost_Center AND UID_CostCenter.UID = 'admincii' Group By Misc.Cost_Center, Misc.Carrier, Misc.Account, Misc.Long_DateSELECT Pagers.Carrier, Pagers.Account, Pagers.Cost_Center, Pagers.Long_Date, Sum(Pagers.Total) AS Total, Sum(Pagers.Actual_Past_Due) AS Actual_Past_Due, Sum(Pagers.Credit) AS Credit FROM Pagers, UID_CostCenter WHERE (Pagers.Long_Date BETWEEN 1/1/2005 AND 1/1/2006) AND Pagers.Cost_Center = UID_CostCenter.Cost_Center AND UID_CostCenter.UID = 'admincii' Group By Pagers.Cost_Center, Pagers.Carrier, Pagers.Account, Pagers.Long_DateSELECT WAN.Carrier, WAN.Account, WAN.Cost_Center, WAN.Long_Date, Sum(WAN.Total) AS Total, Sum(WAN.Actual_Past_Due) AS Actual_Past_Due, Sum(WAN.Credit) AS Credit FROM WAN, UID_CostCenter WHERE (WAN.Long_Date BETWEEN 1/1/2005 AND 1/1/2006) AND WAN.Cost_Center = UID_CostCenter.Cost_Center AND UID_CostCenter.UID = 'admincii' Group By WAN.Cost_Center, WAN.Carrier, WAN.Account, WAN.Long_Date

 

[false420]

But i have to try and keep the service as variables.
since it is a dynamic table. this seemed so much easier with access but the co is growing too much for it anymore

strDetailedInfo = "SELECT " & service

& ".Carrier, " & service & ".Account, " & service

& ".Cost_Center, " & service & ".Long_Date, Sum(" &

service & ".Total) AS Total, Sum(" & service

& ".Actual_Past_Due) AS Actual_Past_Due, Sum(" & service

& ".Credit) AS Credit FROM " & service & ", UID_CostCenter

WHERE (" & service & ".Long_Date BETWEEN " & currYear & "

AND " & nextYear & ") AND " & service & ".Cost_Center =

UID_CostCenter.Cost_Center AND UID_CostCenter.UID = '" &

strSession_UID & "' Group By " & service

& ".Cost_Center, " & service & ".Carrier, " & service

& ".Account, " & service & ".Long_Date"

 

[JamesLean]

Using aliases you can do this:

sSQL = "SELECT t1.Carrier, t1.Account, t1.Cost_Center, t1.Long_Date, SUM(t1.Total) AS Total, SUM(t1.Actual_Past_Due) AS Actual_Past_Due, SUM(t1.Credit) AS Credit " &_
  "FROM " & [red]service[/red] & " t1 JOIN UID_CostCenter t2 ON t1.Cost_Center = t2.Cost_Center " &_
  "WHERE t1.Long_Date BETWEEN [red]'[/red]" & curryear & "[red]'[/red] AND [red]'[/red]" & nextyear & "[red]'[/red] AND t2.UID = 'admincii' " &_
  "GROUP BY t1.Cost_Center, t1.Carrier, t1.Account, t1.Long_Date"

Note the inclusion of single quotes which you were missing.

--James

 

[false420]

Wow you must be master at sql. that worked. howd you do that so fast? hehe. i kinda see where i went wrong on the date parts. i used "''" instead of '""'. like i said im new to sql. after i get everything moved over im going to sit down with a book.

 

[false420]

Im gonna study your statement cuz i got few more to redo