I know this is an easy one and I know I've read it somewhere, but I can't seem to write the correct format to run correctly. I am trying to build a SELECT statement base on the selected values of a dropdown list on a webform. The selected values will be part of the Table name.. ("client_info" & location_option.selecteditem.value) Can someone show me the correct syntax for adding a form variable into a SELECT statement? Thanks
ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle]
AS BEGIN
DECLARE @article_id INT SELECT @article_id = ( SELECT TOP 1 article_id FROM article ORDER BY article_id DESC )
DECLARE @comment_count INT SELECT @comment_count = ( SELECT COUNT(comment_id) FROM comment JOIN article ON article_id = comment_article_id GROUP BY article_id HAVING article_id = @article_id )
SELECT TOP 1 article_id, article_author_id, article_title, article_body, article_post_date, article_edit_date, article_status, article_author_id article_author_ip, author_display_name, category_id, category_name--, comment_count AS @comment_count
FROM article
JOIN author ON author_id = article_author_id JOIN category ON category_id = article_category_id
GROUP BY article_id, article_title, article_body, article_post_date, article_edit_date, article_status, article_author_ip,article_author_id, author_display_name, category_id, category_name
HAVING article_id = @article_id
END GO
as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable.
I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id.
But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id.
And i cant add a join, because it would return the amount of rows of the comment...
unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement?
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table. Here is my select statement: SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate - (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name, department.department_name, location.location_name FROM employee INNER JOIN request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN department ON employee.emp_department = department.department_id INNER JOIN location ON department.department_location = location.location_id GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_name ORDER BY location.location_name, department.department_name, employee.emp_lname
The section below is the part that may or may not contain information: SELECT (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft
So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!! TIA
Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.
ie.
exec sp_SomeFunc 'select * from table1 where id=1'
It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure?
I am using the lookup transform with the following settings:
Reference table: Use results of an SQL query. The query retrieves the surrogate key and four business key columns from a dimension table which contains a few thousand rows.
Columns: business keys in the incoming data are mapped to the business keys in the reference table, and the surrogate key is looked up from the reference table.
Advanced: Enable memory restriction is OFF (and the other items on the Advanced tab are greyed out).
I assumed that this means that the lookup transform would cache all of the rows in the SQL query, and then perform the lookups against this cache. This is the behaviour that I saw when I was running the package in my local environment in the BIDS debugger.
However, a colleague was doing some profiling on our production database server, and noticed that the lookup transform is instead issuing a single SQL query for each row of input. Our production ETL server has many GBs of free RAM available (way more than enough to cache the contents of the lookup table in memory), and given that memory restriction is disabled, I don't understand why the lookup transform is behaving in this fashion. Does anyone have an explanation for this? I'm probably misunderstanding a key concept here.
I have to produce a report to calculate no of days based on user input start date and end date. I have tried to explain below.
say for eg: in the tables I have emp name user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days Total days 53 Days
For this :
I calculated datediff + 1 and got sub jobs days BUT
say financial director wants to see Title of 'Sub Jobs' with 'Days' from 1st Dec to 31st Dec
so on paper I calulated as :
1-31 Dec 2014 PhaniMarketing NULL (Do not fall in Req Dt) PhaniAdmin 20 (Deduct 2 days of Nov & calculated 20 days of Dec) PhaniCRM 11 (Deduct 20 days of Nov and deduct 11 days of Jan so for Dec , we got 11 days) Total days 31
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
Below. I have also pasted the current result of this query and the desired result.
Query can be updated to get the desired result as given below?
Query: Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'
Current Result which I am getting with this query
OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT 5501 07/13/2015 CO 11 23.87 5501 07/13/2015 PR 12 3.76 5501 07/13/2015 OT 32 33.45 2032 07/14/2015 CO 12 23.87 2032 07/14/2015 OT 14 43.01
Desired/Expected Result for which I need updated query
I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN*** Example Record 2. Theale, Nr Reading, Berkshire Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0 THEN LEN(Address ) ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0 THEN LEN(Address) ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
I want to use an input parameter as my filename, but I get a synataxerror message. Howerve, when I hard code the filename the proc compilessuccessfully.Thanks for any help. I'm using SQL Server 2005LTR_90,LTI_ELIG_pct,LTI_REC_pct,LOW_SALARY,HIGH_SALARY FROM OPENDATASOURCE("Microsoft.Jet.OLEDB.4.0","Data Source=C:inetpubwwwrootORC_Beta_Companies"' + @infilename +'"Extended Properties=Excel 8.0")...[summary_data$]Syntax message:Msg 102, Level 15, State 1, ProcedureimportExcelSpreadSheetIntoeNavigator_DataORC, Line 244Incorrect syntax near 'Microsoft.Jet.OLEDB.4.0'.
I have a user table for an organization that can best be described as a pyaramid. We are using the terms parent and child to define the relationship between users. Each user is a child under a specific parent; accept for the top person who is not a child under anyone. Each user can be a parent over many children.
User A has a parent - User B User B has a parent - User C User C has a parent - User D User D has not parent
I want to do is create a select statement that would return all the parents for a particular user. For instance, if I ran the select statement for User A, it would return:
User A User B User C User D
If I ran it for User C, it would return: User C User D
Hi, I have created a login page (webform1) that enables me to enter my user number and password and if correct it re-directs to webform 2 where all data related to the usernumber that was entered in the login page, appears. Now I want to be able to set different re-direct pages dependant upon different users as some may require more privileges then others. As for my query I have 2 types of users: staff and managers. I want to set the login page so that if staff logins in it goes webform 3 and if manager logins in it goes webform 4. My table in the Sql database is called Users & the fields are: unumber(pk), pwd, userRole, forename, surname. I have been advised to use CASE statements, yet I do not know how to use them. Below have left my code of a simple login without the userRole validation. Please help! Sub cmdLogin_ServerClick If ValidateUser(txtUserNumber.Value, txtUserPass.Value) Then Dim tkt As FormsAuthenticationTicket Dim cookiestr As String Dim ck As HttpCookie tkt = New FormsAuthenticationTicket(1, txtUserNumber.Value, DateTime.Now(), _ DateTime.Now.AddMinutes(30), chkPersistCookie.Checked, "your custom data") cookiestr = FormsAuthentication.Encrypt(tkt) ck = New HttpCookie(FormsAuthentication.FormsCookieName(), cookiestr) If (chkPersistCookie.Checked) Then ck.Expires = tkt.Expiration ck.Path = FormsAuthentication.FormsCookiePath() Response.Cookies.Add(ck) Dim strRedirect As String strRedirect = Request("ReturnURL") If strRedirect <> "" Then Response.Redirect(strRedirect, True) Else strRedirect = "webform1.aspx" Response.Redirect(strRedirect, True) End If Else Response.Redirect("webform3.aspx", True) End If End Sub
Hello everyone, I have a view, NAS_vPosition that has a coloumn vLogin_Acting and I want to use the user.identity.name to select the row from this table that matches. So far i have tried: SelectCommand = "Select * FROM NAS_vPosition WHERE vLogin_Acting = ' <%=User.Identity.Name %> ' " with no success. Any help is appreciated
I am currently trying to write a query that pulls a summation of item specific data from sales orders. For simplicity's sake, the column structure can be something like the following...
Item#, PoundsDuringWeekNumber (this would be the current week number out of the 52 weeks in a year and the pounds of the item during that week)
However, those are not going to be the only 2 columns. The idea of the query is that the user would be able to provide the query a date range (say 2 months) and the columns would then morph into the following...
Item#, PoundsDuringWeekNumber (current), PoundsDuringWeekNumber(current - 1), PoundsDuringWeekNumber (current - 2), etc. etc. PoundsDuringWeekNumber(current - 8)
Initial ideas where to create a function to execute the summation of the pounds during the date range of the week in question and execute it across the columns, but with the indeterminable number of columns, the query would not know how many times to execute the function.
SELECT DATEDIFF(n , LAG(CAST(Date AS DATETIME) + CAST(Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ), CAST(Date AS DATETIME) + CAST(Time AS DATETIME)) FROM [DataGapTest]
Gives the right output:
NULL 1 1 3548 0
However, when I put the statement in a function, I get only zeros as the output. It's as if the lag and current value are always the same (but they are not of course).
CREATE FUNCTION dbo.GetTimeInterval(@DATE date, @TIME time) RETURNS INT AS BEGIN DECLARE @timeInterval INT SELECT @timeInterval = DATEDIFF(n , LAG(CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ), CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME)) FROM dbo.[DataGapTest] RETURN @timeInterval END
Is it possible to allow user input via a Reporting Services Report? What I mean is could a report be created that would allow someone using the report to enter a number that would be written to the SQL database?
I am trying to help an individual with a MS Query.
In Excel 97 MS Query the script was written:
TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS YEAR
In Excel XP I can run an existing Query and it returns the data as expected, but when I view the query it is:
TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS 'YEAR'
However if I try to create a new query using XP MS Query then the AS YEAR or AS 'YEAR' will be removed by MS Query and my column header will now be:
TO_CHAR(LABTRANS.STARTDATE, 'YYYY')
Here are my examples:
MS Excel 97 Query
SELECT LABTRANS.LABORCODE, LABTRANS.REGULARHRS, LABTRANS.OTHRS, LABTRANS.OTSCALE, LABTRANS.STARTDATE, LABTRANS.TRANSTYPE, VALUELIST.VALDESC, TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS 'YEAR' FROM MAXIMO.LABTRANS LABTRANS, MAXIMO.VALUELIST VALUELIST WHERE LABTRANS.TRANSTYPE = VALUELIST.VALUE AND (LABTRANS.TRANSTYPE IN (SELECT VALUE FROM VALUELIST WHERE LISTNAME = 'LTTYPE' AND MAXVALUE = 'NON-WORK')) AND LABTRANS.LABORCODE IN (SELECT LABORCODE FROM LABOR WHERE LABOR.TYPE = 'CUPE' AND LABOR.CREWID IS NOT NULL AND LABOR.LA9 = 'N' AND LABOR.LA16 = 'WW' OR LABORCODE = '210308') AND VALUELIST.MAXVALUE = 'NON-WORK' ORDER BY LABTRANS.STARTDATE
MS Excel XP Query
SELECT LABTRANS.LABORCODE, LABTRANS.REGULARHRS, LABTRANS.OTHRS, LABTRANS.OTSCALE, LABTRANS.STARTDATE, LABTRANS.TRANSTYPE, VALUELIST.VALDESC, TO_CHAR(LABTRANS.STARTDATE, 'YYYY') FROM MAXIMO.LABTRANS LABTRANS, MAXIMO.VALUELIST VALUELIST WHERE LABTRANS.TRANSTYPE = VALUELIST.VALUE AND (LABTRANS.TRANSTYPE IN (SELECT VALUE FROM VALUELIST WHERE LISTNAME = 'LTTYPE' AND MAXVALUE = 'NON-WORK')) AND LABTRANS.LABORCODE IN (SELECT LABORCODE FROM LABOR WHERE LABOR.TYPE = 'CUPE' AND LABOR.CREWID IS NOT NULL AND LABOR.LA9 = 'N' AND LABOR.LA16 = 'WW' OR LABORCODE = '210308') AND VALUELIST.MAXVALUE = 'NON-WORK' ORDER BY LABTRANS.STARTDATE
In Excel XP, no matter what I put in the AS part of the statement when I execute the script it runs and removes the AS part from the statement. This only happens when I run the script from MS Query, if I stay in Excel and just refresh the data, then it works fine.
I have a database with a field which contains a load of dates in this format 01/03/1998
What I want to do is select all the records from the database where the month of the date is what ever, this month or the month the customer selected etc.
I suspected it may be something like the following (which doesnt work)
sql = "SELECT * FROM avail_lowermill WHERE CH_Arrival.month = " & showmonth & " ORDER by CH_Arrival"
Hi, Just wondering if I could make sure that "hackers" don't tamper with my querystring - which is a parameter for a SQL query. i.e... Dim ListOfValues as string = request.querystring("listOfValues")
'Output would be this: 324234,5445,554654,45632,SQL command : Delete From table where product_id IN (@ListOfValues)
How can I validate it so that hackers can't add any characters other than 'numbers' and ' , ' to the sql parameter? I have tried to tryparse the 'ListOfValues ' as an integer - by replacing "," with "" but an integer overflow occured. Any questions or ideas? Thanks
I am new to ASP.NET so pordon me if my questions seems to be stupid.
I was given an assign to develop a ASP that would return results from a SQL server.
The problem is that part of my query to the SQL server comes from the webform
select a.invnum, a.invdate, a.duedate, a.invamt,a.payamt from vpshead a inner join vendors b on (a.vendnum = b.vendnum) where (a.vendnum = " & user.text & " ) and (b.vendpass = " & Pass.text & " ) and (a.payflag <> 'V') and ( (a.invamt <> 0) or (a.payamt <> 0) )and (a.chkno = '') order by a.invdate"
As your can tell I am using the user.text and Pass.text in my query which will come from the web form.
well here's a problem that I encountered, i got a textbox that is used to store the Date of Birth of a user. So when user keys in something, it is store as a string. However i wish to convert it into a datetime so that i can store it into my database. Anybody know of a way to help? i've tried countless methods but doesn't seem to work. Please give me a hand guys.... Thanks
Hello. I'm using SQL query analyzer to run some queries against anSQL2000 DB. Unfortunately, my previous SQL experience is some OralceSQL I took in school.To put it simply, I'm trying to write a query to pull records thatmatch a variable input. In Oracle, I'd use a substitution variablelike this...SELECT * FROM jobs WHERE jobid = &job_id_to_query;Is this possible under SQL2000? Is this only a feature of SQL*Plus?TIARich
I want to pull dates from my database that are between to set dates i have written a query that does this which looks like this: "SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >= CONVERT (datetime, LEFT ('12/02/2007', 2) + '/' + SUBSTRING('12/02/2007', 4, 2) + '/' + RIGHT ('12/02/2007', 4))) AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) < CONVERT (datetime, LEFT ('14/02/2007', 2) + '/' + SUBSTRING('14/02/2007', 4, 2) + '/' + RIGHT ('14/02/2007', 4)))" This works when the dates are included in the statement but when i try and use parameters to pull them in like this: "SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >= CONVERT (datetime, LEFT ('@Choice1', 2) + '/' + SUBSTRING('@Choice1', 4, 2) + '/' + RIGHT ('@Choice1', 4))) AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) < CONVERT (datetime, LEFT ('@Choice2', 2) + '/' + SUBSTRING('@Choice2', 4, 2) + '/' + RIGHT ('@Choice2', 4)))" I then recieve the following error message why is this? ERROR -------------------> Syntax error converting datetime from character string. Any Help would be greatly appreciated thanks
I have a search page that allows users to type/select values contined within the entry they're looking for. My SELECT statement returns columns in a table that get compared to the user input. So if someone selected Status (Open) then all of the 'Open' Request entries should populate the search page. How do I phrase the SELECT statement to compare values if the user gives them, but ignore the fields where no data was input when it's searching? So a search where no values were entered would return every Request Entry instead of an error; no entry. Thanks!
Hi folks, i am lookin for an option in the QA to take user input while executing a query. just like we do in SQLPLUS: SELECT * FROM dept WHERE deptno=$abc here it would ask me for the deptno. I guess it's a very handy feature and we could test our variables and calculations withing the procedures etc.
