I have a query on a peson application that poduces a record. Within the application there is a question that allows the applicant to choose several answers: -
Applic_no Question_ID Question Answer
12345 40 Medical 2,5,12
There is a lookup table that tells me what each answer is: -
However, how do I query the lookup table if my answer is 2,5,12? I need to somehow split it or query the string array to pick out the values seperated by commas.
Hello to all, I have a problem with ms sql query. I hope that somebody can help me. i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships . 3387 (2345, 2388,4567,....) 4567 (8990, 7865, 3387...) i wirte a query to check if there is Relationship between two members. Query: Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567; select * from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in (select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query. Can Someone tell me where is the problem? Thanks
Link ( GroupID int , MemberID int ) Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID, then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
End Sub /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// I executed the code successfully and I got a box which asked for "Enter the query string". I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
I have got a grid view which i want to query i have added a WHERE for it WHERE (([carinfo] = @carinfo) AND ([carmake] = @carmake) AND ([postcode] LIKE '%' + @postcode + '%') AND ([carprice] <= @carprice)) I have made a search page with 4 textboxes and a search button but what i cant same to get working is the code to take the infor from my text boxes and run the query on the grid view page.If i just had a query with (([carinfo] = @carinfo) i can get that to work by doing this Protected Sub SearchButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles searchButton.Click '~/Default2.aspx Response.Redirect("search.aspx?man=" + Carmake.Text) End Sub After that i just dont know what to do, my asp code for the Data Source is <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:addcar %>" SelectCommand="SELECT [AdId], [carinfo], [carmake], [cartype], [carprice], [other1], [enginesize], [fuel], [listdate], [adtitle] FROM [classifieds_ex] WHERE (([carinfo] = @carinfo) AND ([carmake] = @carmake) AND ([postcode] LIKE '%' + @postcode + '%') AND ([carprice] <= @carprice))"> <SelectParameters> <asp:QueryStringParameter Name="carinfo" QueryStringField="man" Type="String" /> <asp:QueryStringParameter Name="carmake" QueryStringField="make" Type="String" /> <asp:QueryStringParameter Name="postcode" QueryStringField="postcode" Type="String" /> <asp:QueryStringParameter Name="carprice" QueryStringField="pricerange" Type="Decimal" /> </SelectParameters> if anyone could help me with this would be great i been trying to work this out for two days now. Keep safeNick
Hi, I'm self learning asp.net and have a little experience in vb.net. What i'm trying to do is run an SQL query and write the results into variables. The SQL query's im running will only ever return 1 row (specifing primary key / uniqueID). This is becasue i dont want to output the results as a table, rather to other objects. (using VS Web Developer express 2008) Currently my code is: ------------------------------------------------------------------------------------------------------------------------------------------------------ Imports System Imports System.Data.SqlClientPartial Class details Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'Dim VariablesDim job_id As Integer Dim myConnection As SqlConnection Dim var_jobid As String = "" Dim var_2 As String = "" Dim var_3 As String = ""Dim SQL_read As New Object
'Write valus to Variables job_id = Request.QueryString(job_id)SQL_read = "SELECT * FROM TABLE_NAME WHERE job_id = " & job_id 'Write Variables and Text to labelsLbl_title.Text = "Details for Job " & job_id Lbl_jobid.Text = job_id 'Try to Establish link to SQL Server '=================================== TrymyConnection = New SqlConnection("server= [my server here] ; database=ServerLog ; User Id = sa; Password= [my password here]") myConnection.Open() -------------------------------------------------------------------------------------------------------------------------------- I realise the quality of the code may be pretty abismal, due to pretty much guessing my way through. I dont know how to basically 'run' my sql query for starters, and then to output the results into variables. -IE- coloumn 1 (row1) of my results being put into var_1, column 2(row1) being put into var_2 etc etc.
I inherited a project at work in which I have to diagnose a bad query string that should be passing a value. Below are what I hope are relevant pieces of code. Your help ASAP will help insure job protection for yours truly. Thanks. Dim strSQL as String = "SP2 "'strSQL = strSQL & Request.Cookies("ODM")("User_ID_NO") & ", "strSQL = strSQL & Request.Querystring("queue_id")strSQL = strSQL & ",'" & Request.Querystring("subtype")+ "'" ''xx no subtype is being passed herePart of SP2:SELECT '<A target="new" href="../document-ds.aspx?dcn=' + CAST(A.DCN AS VARCHAR(25)) + '">' + CAST(A.DCN AS VARCHAR(25)) + '</A>' AS 'DCN', QUEUE_NAME AS 'Queue Name', DOC_SUBTYPEDESC as 'Department' , DOC_CLASSDESC as 'WorkGroup', DOC_TYPEDESC as 'Doc Type' , WKF_SUBMIT as 'Received Date', QI.QUEUE_DATE as 'Queue Date', dbo.MIN2PARTS(DATEDIFF(mi,A.WKF_SUBMIT, GETDATE())) as 'Doc Age', CASE WHEN (DBO.SLA_HOURSDIFF_DCN(A.WKF_SUBMIT, GETDATE(), A.DCN) - SLA_HOURS) > 0 THEN '<font color = red> ' + CAST(DBO.SLA_HOURSDIFF_FORMAT( DBO.SLA_HOURSDIFF_DCN (A.WKF_SUBMIT, GETDATE(),A.DCN)) AS VARCHAR(25)) + '</font>' WHEN (DBO.SLA_HOURSDIFF_DCN(A.WKF_SUBMIT, GETDATE(), A.DCN) - SLA_HOURS) < = 0 THEN '<font size = 2 color = green><b> 0 </b></font>' WHEN (DBO.SLA_HOURSDIFF_DCN(A.WKF_SUBMIT, GETDATE(), A.DCN) - SLA_HOURS) IS NULL THEN 'No SLA Configured' END AS 'Out of SLA', (DBO.SLA_HOURSDIFF(A.WKF_SUBMIT, GETDATE()) -SLA_HOURS) as 'SLA_HOURS' FROM T_WF_DOC_TYPES DT INNER JOIN T_WF_AP_TRACKING A ON A.DOC_ID = DT.DOC_ID INNER JOIN T_WF_QUEUE_INV QI ON QI.DCN = A.DCN INNER JOIN T_WF_QUEUES Q ON Q.QUEUE_ID = QI.QUEUE_ID WHERE Q.QUEUE_ID = @QUEUE_ID AND DT.DOC_SUBTYPE = @DOC_SUBTYPE
My main task is to discover where the @DOC_SUBTYPE comes from and why it's not passing the value to the stored proc or beyond. Let me know if you have any other questions since I'm unsure if I gave you the right info or not.
Hi there, im trying to create a string from a query, i got a table like this one id name -- ----------- 1 Robert DeNiro 2 Will Smith 3 Bruce Willis 4 Al Pacino
Now, i want to get this output
Robert Deniro; Will Smith; Bruce Willis; Al Pacino
I'm wondering if there is a way to acomplish this.
Hi folks, I have two tables in one database. One table has an automatic numbering primary key named ID and is named rfi. The other table has a field named rfinumber and is named discussion. Both ID and rfinumber have a datatype of number. Upon using the querybuilder with Web Matrix, I issue a select command to get all records from the discussion table that have a rfinumber field equal to the ID field in the rfi table. Problem is that I am getting the entire discussion table when I use the following query: "SELECT [discussion].* FROM [discussion], [rfi] WHERE ([discussion].[rfinumber] = [rfi].[ID])" For example DISCUSSION TABLErfinumber1112 RFI TABLErfi12 Given the query, I should get a discussion table only listing the rfinumber = 1. When I run the query from my database package it runs fine?? Any clues? thanks,glenn
Hello,I would like to keep some values as session variables while the user is loged in, but i am missing some part of how to implement it.This is what I have:<script runat="server">
Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs)
Dim conn As SqlConnection Dim cmd As SqlCommand Dim cmdString As String = "SELECT users.username, users.password, users.FirstName, users.LastName, users.CompanyId, Company.CompanyName, users.SecurityLvl FROM users LEFT OUTER JOIN Company ON users.CompanyId = Company.CompanyId WHERE (users.password = @Password) AND (users.username = @Username)"
I am sorry for my basic question I am sure. I have this SQL Query that attaches to 3 tables and pulls data in: Select OffNormalInfo.OffNormalID, OffNormalInfo.CompanyID, OffNormalInfo.SiteID, OffNormalInfo.CaskID, DATEPART(mm,EventDate)as Month, DATEPART(dd,EventDate)as Day, DATEPART(yyyy,EventDate)as Year, EventName, Damage, Action, UnitIDVCC, UnitIDTSC FROM OffNormalInfo, DamageInfo, CaskInfo WHERE OffNormalInfo.OffNormalID = DamageInfo.OffNormalID and CaskInfo.CaskID = OffNormalInfo.CaskID Now, the main table that has the main document that everything else rides off of is the OffNormalInfo table. The CaskInfo table will also always have data in it, but there could be an instance where the DamageInfo table doesn't have a corrisponding record with the same OffNormalID as the OffNormalInfo table. What is happening right now with this string is that nothing is being returned at all for that OffNormalInfo record if there is nothing in the DamageInfo table. There are also many times that the DamageInfo table has multiple records with the OffNormalID and so it returns the entire row multiple times with the same info from the OffNormalInfo table - this is how it is designed and is working correctly. So - the question. How do I change my SQL to get it to still return everything in the OffNormalInfo table and CaskInfo table even when there is not a corresponding record in the DamageInfo table? In this event I would like it to show the two columns (Damage and Action) with empty strings in the GridView. Any thoughts would be appreciated. Thanks! Tim
Hi, I have hit a brick wall with this. My code is as below
public void fillCustomer() { string connectionString = "server='local'; trusted_connection= true; integrated security=sspi; database='Mrbob'"; System.Data.SqlClient.SqlConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString); string queryString = "SELECT * FROM [Customer] WHERE ([CustomerID] = @CustomerID)"; System.Data.SqlClient.SqlCommand dbCommand= new System.Data.SqlClient.SqlCommand(); dbCommand.CommandText = queryString; dbCommand.Connection = dbConnection; System.Data.IDataParameter param_CustomerID = new System.Data.SqlClient.SqlParameter(); param_CustomerID.ParameterName ="@CustomerID"; param_CustomerID.Value = customerID;dbCommand.Parameters.Add("@CustomerID", SqlDbType.Int); dbCommand.Connection.Open(); System.Data.IDataReader dataReader = dbCommand.ExecuteReader(); dbCommand.Connection.Close(); while(dataReader.Read()) { customerID = dataReader.GetInt32(0); date = dataReader.GetDateTime(1); eposCode = dataReader.GetInt32(2); } dataReader.Close();
}
The error I am getting is
Prepared statement '(@CustomerID int)SELECT * FROM [Customer] WHERE ([CustomerID] = ' expects parameter @CustomerID, which was not supplied.
As you can see from my queryString the @CustomerID parameter is passed in. It seems as if the string is being truncated at 64 characters long. If I remove the paramter to pass the relevant infomration and pass in a customerID I know exists it works.
I am really stumped on this and would really appreciate any pointers
Hi! When I run a select statement, it would retrieve a product description. In some rows, it is long. Consequently, the product description was truncated. Did anybody have resulotion for this issue?
Now I should issue a sql which should extract the CustomerID where UserID in (‘U2’,’U4’) or (U2 or U4) in OtherUsers. i.e I should get the CustomerIDs C2,C4,C6,C8
I have trouble in coming out with the sql. Does anyone have idea how to achieve this?
declare @sql char(500) Select @sql = '"Insert Into T74ACCO Select * From tempdb..##Temp tp Where tp.' + @Key + ' not In (Select ' + @Key + ' From T74ACCO)"'
As I build a record set in an SP I need to add in a string containing a list derived from a second query. I need the results of the sub query to be presented as a single string in the first query, separated by a single space.
I have no idea how to do this in t-sql, and am doing it on the web server at the moment, but becase the dataset is quite large, I'm getting 20 - 40 second processing times which is far too long.
I have found reference to the xp_sprintf function but this is not supported by my host, so not a solution.
I'm no expert in t-sql, so I imagine there's a way somewhere, and would be grateful for any advice available.
Hi I wanna put string in query "where" part.For example,$sql="select VEHICLEFROM databaseWHERE MECHANIC =BRIAN";like above, "mechanic" column is filled with strings. Then how can Iwrite "where" part?Above query does not work.Thanks.
Hi,could you tell me please what are the ways of the query stringencryption in T-SQL ? I would like to have a storage procedure thatencrypts e.g. http://123.23.43.1/pagegen.asp?param1=23¶m2=124 intohttp://pagegen.asp?code=fdgfehiqrzvhe and convert it back to theorginal url.Thanks in advanceOmi
So I've run into another problem. I've figured out how to concatenate multiple rows into a single string my only problem is using that on another query with multiple rows...Basically what I'm trying to do is pull up information for each class a student has in his/her profile and while at it pull up any prerequisite classes that are associated with a certain class. So the final query would look something like this...
StudClassID Completed Class ID Name Description Credits Prereq... rest are insignificant... 0 0 CSC200 Cool prog... blah.... 3 CSC160, CSC180
I get the concept of the coalesce and cast just i'm not understanding how to get it to work with each return on the main select...anyways below are the tables and my current query call...
Code Snippet
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Student_Classes] Script Date: 03/31/2008 01:32:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Student_Classes]( [StudClassID] [int] IDENTITY(0,1) NOT NULL, [StudentID] [int] NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CreditID] [int] NULL, [Days] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Time] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Classroom] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Grade] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Semester] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Notes] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Completed] [tinyint] NULL CONSTRAINT [DF_Student_Classes_Completed] DEFAULT ((0)), CONSTRAINT [PK_Student_Classes] PRIMARY KEY CLUSTERED ( [StudClassID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_ClassID] GO ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_CreditID] FOREIGN KEY([CreditID]) REFERENCES [dbo].[Credits] ([CreditID]) GO ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_CreditID] GO ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_StudentsID] FOREIGN KEY([StudentID]) REFERENCES [dbo].[Students] ([StudentID]) GO ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_StudentsID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Prerequisites] Script Date: 03/31/2008 01:32:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Prerequisites]( [PrerequisiteID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_Prerequisite] PRIMARY KEY CLUSTERED ( [PrerequisiteID] ASC, [ClassID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Prerequisites] WITH CHECK ADD CONSTRAINT [FK_Prerequisite_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Prerequisites] CHECK CONSTRAINT [FK_Prerequisite_ClassID] GO ALTER TABLE [dbo].[Prerequisites] WITH CHECK ADD CONSTRAINT [FK_Prerequisite_Prereq] FOREIGN KEY([PrerequisiteID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Prerequisites] CHECK CONSTRAINT [FK_Prerequisite_Prereq]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Credits] Script Date: 03/31/2008 01:32:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Credits]( [CreditID] [int] IDENTITY(0,1) NOT NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Credits] [tinyint] NULL, CONSTRAINT [PK_Credits] PRIMARY KEY CLUSTERED ( [CreditID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Credits] WITH CHECK ADD CONSTRAINT [FK_Credits_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Credits] CHECK CONSTRAINT [FK_Credits_ClassID]
SELECT sClass.StudClassID ,sClass.Completed ,sClass.ClassID AS 'Class ID' ,c.LongName AS 'Name' ,c.Description ,cred.Credits ,(SELECT COALESCE(@prerequisites + ', ', '') + CAST(PrerequisiteID AS varchar(7))) AS 'Prerequisites' ,sClass.Grade ,sClass.Days ,sClass.Time ,sClass.Classroom ,sClass.Semester ,sClass.Notes FROM Student_Classes sClass INNER JOIN Prerequisites preq ON preq.ClassID = sClass.ClassID INNER JOIN Classes c ON c.ClassID = sClass.ClassID INNER JOIN Credits cred ON cred.CreditID = sClass.CreditID WHERE sClass.StudentID = 0 ORDER BY sClass.ClassID ASC
As I'm searching for solutions to my problem (see my most recent post before this one if you're interested), I keep coming upon "command-based expressions." I keep seeing examples such as:
= "select a,b from table1 where c=0"
I can't put that in a textbox can I? I tried it and it just spits out the string on report execution, it doesn't actually execute the command.
So what's up? Should that be able to be put right in a textbox? That would solve a world of problems for me if it could be.
I have been attempting to build a search engine that searches a database full of invoices. The user will enter in a invoice number and then the results will be returned if the exact invoice exists and if the record belongs to the user. My first attempt has involved the use of a SQL Datasource to pass a stored procedure based on what is in the search textbox and the information in the cookie about the user. I was hoping that on the btnsearch.click event that I could somehow return the stored procedure in the SQL DataSource. My next step was to make the result (if exists) part of the query string like this: Response.Redirect("~/Invoice.aspx?Invoice=?"+ SQLRETURNEDVALUE)
I'm looking for any help to a possible way to use my idea or any other ideas to get a invoice number and place it into a query string so that the result can be fetched on the next page. Thanks
I can not get this query to work can some on help me? SELECT count(JOBID) AS 'transcount', count(distinct patientid) AS 'patient count', sum(TRANSPORTATION_TCOST) AS 'tcost,sum(TRANSPORTATION_DISC_COST) as ' dtcost ',avg(TRANSPORTATION_DISC) as "avgTDisc",(sum(transportation_tcost) + sum(TRANSPORTATION_DISC_COST)) as "TGrossAMTBilled', (sum(transportation_tcost) / count(DISTINCT patientid)) AS 'PatAvgT', sum(TRANSPORTATION_DISC) AS 'avgPercentDiscT' job.JURSDICTIONFROM job LEFT JOIN payer ON payer.payerid = job.payerid LEFT JOIN states ON job.jurisdiction = states.initialsWHERE (job.transportation = '1') AND ((job.datedcreated = @startdate) AND (job.datecreated = @enddate)) AND states.region = 'GA'GROUP BY job.JURISDICTIONORDER BY PatAvgT DESC
Hi, I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers. Thanks
I have a column name URL in Table1 with data like <a href="/Folder1/view_media_news.cfm?news_media_i=1">August 2002 Factsheet</a> <a href="/Folder1/view_media_news.cfm?news_media_i=149">March 2002 Newsletter </a> i need to grab the news_media_i value by sql query Please any one can help me to get that particular value from string using substring or any other suggestion Thank you in advance
Hi all, In a sql query I need to replace 0 (zero) with "Not rated" ...Can some one help me to do this.In short: how to replace a integer value to a string in a query? is it possible?Thanks for the HelpRamesh
Hi Everyone,My SQL Query is :"select Field1 from table1"The Field1 in Database is of Type "nvarchar" . I need to convert this Field into "integer" in the Query itself.Please Help!!I have already tried "Select convert(int, Field1 ) from table1"The field gives zero output ThanksRegardsNavdeep
I'm trying to look up customer records by e-mail domain by using a text box on a Web form. So if I want to look for all my customers that have an aol e-mail domain, I would type aol.com in the text box and the sub routine would know to count 7 characters from the right and through those characters into maybe a parameter query. I'm having problems passing this in. I can count the characters properly by using: dim strText = MyTextBox.Textdim intLength = strText.Length but having problems starting here...... MyCommand.SelectCommand.Parameters("@email").Value = MyTextBox.Text .............. but how would I ultimately feed this into my sql satement? Select * from Customers Where email = right(@email,intLength) Help appreciated. Frank
Sorry, I realize that this is probably a pretty simple question for you, but I am in a quick time bind and would really appreciate it if someone could help me with this update that I'm trying to run.
Let's say you have a table (call it testtable) that contains the column "text".
Now in column text you want to replace all instantiations of the phrase "in the US" with "to the United States", how would you structure your update query to perform this change (Please note that this phrase could be embedded in the middle of a larger sentence like 'Send a package in the US'. You'd want to change this instance to 'Send a package to the United States')
I am trying to insert values into a table, but i have an Employee ID field, which needs incrementing. How can i do this through my SQL query string? Is this possible? As it can't accept a NULL value.
I need to append text to the end of my sql query results.For instance, my query returns a list of user names infirstname.lastname. I need to add @yahoo.com to each record. So theend result should be Join Bytes!. What should byselect statement look like?Any help?
