August 12, 2011 at 12:57 pm
See explaining the problem can lead to much better code. 🙂
try this in your first query instead:
select isnull(b.User_Name, 'Not Available') as UserName, ISNULL(P2_Date_Acknowledged, 'N/A') as Result1
from Investment_Advisor ia
left join Investment_Advisor_Part2 iap on iap.P2_Year_ID = ia.Year_ID and iap.P2_userid = b.User_Name
where Year_id = '2010'
completely eliminates the need to go back to the database again. Just get what you want the first pass and you are all set. Does that get you closer to what you are looking for?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2011 at 1:02 pm
I will see if that works. When you put..
from Investment_Advisor ia
What does the ia stand for/mean ?
August 12, 2011 at 1:05 pm
Just a table alias so you don't have to type out the whole name again. You can make the alias anything you want, or not even use it. I find that shortened aliases make the query easier to read (and WAY less keystrokes to type).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2011 at 1:19 pm
sqlz = " select isnull(User_id, 'Not Available') as UserName, ISNULL(P2_Date_Acknowledged, 'N/A') as Result1 "
sqlz = sqlz & "from Investment_Advisor ia"
sqlz = sqlz & "left join Investment_Advisor_Part2 iap on iap.P2_Year_ID = ia.Year_id and iap.P2_userid = User_id"
sqlz = sqlz & "where iap.Year_id = '2010'"
and im getting this error...
Microsoft OLE DB Provider for SQL Server error '80040e14'
The column prefix 'ia' does not match with a table name or alias name used in the query.
August 12, 2011 at 1:24 pm
That is because you don't have proper spacing in your string. You can always "response.write sqlz" prior to executing it so you can debug your sql. Also, you could use the line continuation syntax to make things a lot more "eye friendly".
sqlz = " select isnull(User_id, 'Not Available') as UserName, ISNULL(P2_Date_Acknowledged, 'N/A') as Result1 " & _
"from Investment_Advisor ia " & _
"left join Investment_Advisor_Part2 iap on iap.P2_Year_ID = ia.Year_id and iap.P2_userid = User_id " & _
"where iap.Year_id = '2010'"
You were missing spaces at the end of a couple lines.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2011 at 1:32 pm
i really do appreciate all your feed back just to let you know you have been very helpfull......but as i was just looking at that query. i keep forgetting that selecting user_id is pulling in over 1000 user_id's from that table. I need to do processing on each user_id one at a time that is why i wanted to have a query inside of my while rs.eof loop..... take a look at this again if you could.....thanks
sql = " select b.User_id "
sql = sql & " from Investment_Advisor b "
sql = sql & " where "
sql = sql & " ("
sql = sql & " b.Year_id = '2010' )"
sql = sql & "order by user_name ASC"
set rs = server.CreateObject("adodb.recordset")
rs.open sql, conn
while not rs.EOF
dim name
name=rs("User_id")
sql = "DECLARE @result VARCHAR(12)"
sql = sql & "SET @result = (select P2_Date_Acknowledged FROM Investment_Advisor_Part2 WHERE P2_Year_ID = '2010' and P2_userid = '" & name & "')"
sql = sql & "SELECT ISNULL( @result, 'N/A') as theRESULT1"
missingis = "Annual Disclosure Reporting"
Response.Write("<tr>")
Response.Write("<td class=fielddHeader valign=top nowrap><b> "&rs("theRESULT1")&"</b></td>")
Response.Write("</tr>")
rs.movenext
wend
end if
August 12, 2011 at 1:45 pm
But you aren't doing anything inside the loop except writing a line to the screen...
...use the query with the left join here...
set rs = server.CreateObject("adodb.recordset")
rs.open sql, conn
while not rs.EOF
Response.Write("<tr><td class='fielddHeader' style='vertical-align:top; white-space:nowrap; font-weight:bold;'>" & rs("theRESULT1") & "</td>")
rs.movenext
wend
end if
And I know I discussed with you yesterday to use style for visual display. And for heaven's sake do not use <b> tags. They have been deprecated for more than a decade (and they will stop working at some point). See if this not only does what you need but is a lot easier to read because there are mountains of code lines removed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2011 at 2:12 pm
sql = "DECLARE @result1 VARCHAR(50)" & _
"select isnull(User_id, 'Not Available') as UserName, ISNULL(P2_Date_Acknowledged, 'N/A') as Result1 " & _
"from Investment_Advisor ia " & _
"left join Investment_Advisor_Part2 iap on iap.P2_Year_ID = ia.Year_id and iap.P2_userid = User_id " & _
"where ia.Year_id = '2010'"
i know P2_Date_Acknowledged is a datetime but how do i set it go into that VARCHAR
give me this error:
Disclosures test Microsoft OLE DB Provider for SQL Server error '80040e07'
Syntax error converting datetime from character string.
....... dont i have to..... SET @result1 = (my select statement) or something like this...
I am not sure how to do because we now have two variables (UserName and Result1)
August 12, 2011 at 2:17 pm
You do not need a variable to hold Result1. You do however need to convert your date to a varchar of some type if you are going to put 'N/A' in that column when it is null.
Take a look at bol convert. It will depend on how you want it formatted. Basically something like this:
select isnull(User_id, 'Not Available') as UserName, ISNULL(convert(varchar(20), P2_Date_Acknowledged, 101), 'N/A') as Result1 " & _
"from Investment_Advisor ia " & _
"left join Investment_Advisor_Part2 iap on iap.P2_Year_ID = ia.Year_id and iap.P2_userid = User_id " & _
"where ia.Year_id = '2010'"
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2011 at 2:18 pm
Just a suggestion but you should build you sql in ssms and then move you query to your code. You are fighting too many battles at the same time. Get the query right, then have your code use the results.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2011 at 2:26 pm
I know. I am brand new employee at this company. Its only my 2nd week. I havent been given SQL Server 2005 access yet. So for the past week I have been writing these queries blind. That worked. Thanks a million.
August 12, 2011 at 2:45 pm
tomperson349 (8/12/2011)
I know. I am brand new employee at this company. Its only my 2nd week. I havent been given SQL Server 2005 access yet. So for the past week I have been writing these queries blind. That worked. Thanks a million.
You should ask them how they expect somebody who is new to the company, it would seem somewhat new to development too, has any kind of a chance of succeeding if you can't even have a place to test your sql. Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply