Hi All,
I would like to send a query via Table adapter using parameter that is not the whole field. (Its only a part of the filed).
I will try to illustrate my needs:
We have a list of items in a data grid.
I would like to get the list of items who are answering my item name search query.
(I need to change the list every time the user enter another key letter for the search).
I would like to do so in the data adapter, but I'm not able to use the LIKE statement.
Please please try to help me
P.S.
Do I need to use stored procedure, if so, can anyone give me an idea how to do so ?
Now, I don't know if what I want to do is possible, but here goes. In the table I want to query, there is an "approval status" column, of type Int32. There are four approval levels, 1, 2, 3 and 4. What I want to set up is a query in the table adapter that can return all entries of one or more approval levels. In "raw" sql, I would do something like: SELECT * FROM facility_table WHERE (approved IN (1,2,3));What I want to do though, is to have the list of approval codes to be a parameter that I can pass to the table adapter query, so the where clause becomes "WHERE (approved IN (@approval))", and I pass a string with the list of approval codes. But the query designer doesn't want to cooperate with me, as it insists that "@approval" should be an int32.Any suggestions?
I'm looking for a way to pass an array of values as a parameter to a query in a table adapter. For example I want to run a query something like:SELECT * FROM menu WHERE menu_role IN (@roles)And I could pass something like 'RegisteredUser, SuperUser, OtherUser' to the @roles parameter.For some reason I can't figure out a way to do this. Any help would be greatly appericated.Thanks,Ryan.
I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database. I have a very simple dilemma. In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields. It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table. In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters. The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%') If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null> My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>. For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null> (accounting for about 95% of the records in the database). Thanks in advance Simon
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
Can someone tell me why I an getting the following error message for my table adapter: "incorrect syntax near '?'" The selecte statement is below. SELECT Questions.QuestionID, Questions.QuestionNumber, Questions.QuestionText, Questions.SampleAnswer, SAFSection.SectionNumber + ' - ' + SAFSection.SectionName as [Section], SAFSubSection.SubSectionNumber + ' - ' + SAFSubSection.SubSectionName as [SubSection], SAFSubSection.SubSectionDefinition, SAFSubSection.SubSectionQuestion, SAFSubSection.SubSectionInstruction, Answers.AnswerFROM (SAFSubSection INNER JOIN (SAFSection INNER JOIN Questions ON SAFSection.[SectionID] = Questions.[QuestionSectionID]) ON SAFSubSection.[SubSectionID] = Questions.[QuestionSubSectionID]) LEFT JOIN Answers ON Questions.QuestionID = Answers.QuestionIDWHERE Answers.SystemID = ?Order BY SAFSection.SectionID, SAFSubSection.SubSectionID,Questions.QuestionID
Hello, I have a table adapter which uses a Query string to get the select view and a stored procedure for its delete portion. Not all of the columns in the select statement are required for the stored procedure. When I try to do the delete, it attempts at passing in more variables than it needs and ends up resulting in an error saying it can't find a procedure that is valid. Is there any way to restrict which columns are being passed? Thanks, Chris
I have an application (ASP.NET 2.0/SQL Server 2005) which makes heavy use of table adapters for pulling records from SQL. Under heavy load, we get a lot of SQL Server Timeout errors. We have run a trace on SQL Server and it shows that several of the SQL statements being passed into SQL Server, from the Table Adapters, have bad SQL.
For example, here is the SQL in one of the table adapters
SELECT HomeMsgID, messageName, messageHTML, messageText, populationID FROM MyUCR_HomeMessages WHERE (populationID IN (SELECT populationID FROM MyUCR_Population_CPID AS MyUCR_Population_CPID_1 WHERE (CPID = @CPID))) AND (isVisible = 1) AND (showDate <= @showDate) AND (removeDate >= @removeDate)
myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessagesTableAdapter ta = new myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessagesTableAdapter(); myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable dt = new myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable();
ta.FillByCPID(dt, showDate, removeDate, CPID);
What the SQL trace shows, when it fails, is this (notice the extra single quotes around the showDate, removeDate parameters): E000 exec sp_executesql N'SELECT HomeMsgID, messageName, messageHTML, messageText, populationID FROM MyUCR_HomeMessages WHERE (populationID IN (SELECT populationID FROM MyUCR_Population_CPID AS MyUCR_Population_CPID_1 WHERE (CPID = @CPID))) AND (isVisible = 1) AND (showDate <= @showDate) AND (removeDate >= @removeDate)',N'@showDate datetime,@removeDate datetime,@CPID int',@showDate=''2007-02-05 00:00:00:000'',@removeDate=''2007-02-05 00:00:00:000'',@CPID=3071225 1[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2007'.
I recreated the SQL to use a stored procedure, and got a similar error:
I have an SP that I call via table adapter. If I do not pass a value in the string field ('eventRef'), the SP never gets called - it appears that the table adapter is filtering it...? I need to be able to count records that have an 'eventRef' = to the one I pass, including null. This works fine if I pass an 'eventRef', but never runs if that parm is empty.
Hi, Quick question on how SSIS handles queries from Data Source in a Data Flow. I noticed that when I run a particular query from Query Analyzer it takes forever. But, when I run the same query in SSIS data source in a data flow. The query results are immediate.
The query plan is already cached in SQL.
Is this just something which I am seeing incorrect or is there some bit of optimization in there in SSIS. As per my understanding SSIS does not optimize the source query.
Hi All, I'm trying to use a very simple SQL String that check if a specific string exist in the DB. When I do the SQL and use a regular Connection I get the result. When I use a data set and on the table adapter I need to use the "LIKE %" method I get an error.
What should I write on the table adapter in order to make this search?
ItemID is a type string.
This is my SQL: SELECT itemID, itemName FROM Items WHERE (itemID LIKE @ItemIDTemp)
This sample give no result or
SELECT itemID, itemName FROM Items WHERE (itemID LIKE '%' + @ItemIDTemp + '%')
This sample return an error message "Data conversion failed. [OLEDB status value (if known)=2]"
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows. SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches FROM dbo.MAKES INNER JOIN dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC "> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" /> <asp:Parameter Name="ATT_ID2" /> <asp:Parameter Name="VAL2" /> <asp:Parameter Name="ATT_ID3" /> <asp:Parameter Name="VAL3" /> <asp:Parameter Name="ATT_ID4" /> <asp:Parameter Name="VAL4" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource> <br /> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID"> </asp:DropDownList> <asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br /> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False" ReadOnly="True" SortExpression="DERIVATIVE_ID" /> <asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" /> </Columns> </asp:GridView> </asp:Content> AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback). Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage. Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
Hi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code: create proc insertLocalizationASdeclare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalizationif (@idAp <> null)beginInsert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)endGO This only inserts the last line in the "oltpLocalization" table. O the other hand, like this:create proc aaaaasdeclare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)while exists (select distinct(idAp) from OLTPLocalization)begin select @idAp =idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization) select @macAp = macAp from OLTPLocalization where idAp = @idAp select @building = building from OLTPLocalization where idAp = @idAp select @department = department from OLTPLocalization where idAP = @idApif (@idAp <> null)begin insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)endendgo this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table? I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table. Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!
hi I have this asp sql statement which works fine but I want to use an spl adapter instead as I am enabling paging in details view. I am stuck in one place. How do I declare @listing as I have done in the select parameter in the asp sql statement? Where in ths sql adapter do i declare it? listing value is derived from a dropdown box when the user makes a selection. This is the sql statement i want to declare in the adapter.<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>" SelectCommand="SELECT UserDetails.UserId, UserDetails.FirstName, UserDetails.LastName, UserDetails.City, aspnet_Membership.Email FROM UserDetails INNER JOIN aspnet_Membership ON UserDetails.UserId = aspnet_Membership.UserId WHERE Category = @Listing"> <selectparameters> <asp:controlparameter name="Listing" controlid="SearchListingDropDownList" propertyname="SelectedValue"/></selectparameters> This is the adapter so far which gives me an error to declare scalable value @listing. How do i incoporate the select parameters below? Function getTheData() As DataTable Dim DS As New DataSet() Dim strConnect As New SqlConnection(ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString1").ConnectionString)Dim objSQLAdapter As New SqlDataAdapter("SELECT aspnet_Membership.UserId,UserDetails.FirstName + ' ' + UserDetails.LastName AS 'Customer',UserDetails.City, aspnet_Membership.Email FROM aspnet_Membership INNER JOIN UserDetails ON aspnet_Membership.UserId = UserDetails.UserId WHERE Category = @Listing", strConnect)objSQLAdapter.Fill(DS, "aspnet_Membership,UserDetails")
Return DS.Tables("aspnet_Membership,UserDetails").Copy End Function
I am running Windows XP and have SQL Server 2005 Enterprise Edition that I use for testing on my notebook. I want to install Oralce 11g on it as well and use VS 2005 as a front end programming. For me to install Oracle I need to install a loopback adapter. If I do this will it mess up SQL Server 2005 or will it not affect SQL Server 2005? Please advise
In VS2003 I have a user defaults table that I would call in the OnLoad - No Postback section to get the user defaults and then used some of the fields from that recordset as Parameters for other recordsets that would also load in that same section and might even issue a different Sql statement based on the field. In VS2005 (VB.NET), I can no longer figure out how to accomplish this. I can use the SQLDatasource Control which seems much improved but I cannot even find the generated code which I would use as a quick way to create my own datasources in the OnLoad event. Where is this code now stored and how can I accomplish my objectives in VB.NET 2005 with the current framework or programatically create data sources as necessary as I did before?
I am currently writing a custom source adapter that extracts data from a JD Edwards OneWorld system. In the custom user interface of the source component I need to allow the user to set a query (a custom property), and then refresh a list of output columns that will be extracted based upon that query (similar to the list shown in the advanced editor).
My question is, can I apply the custom property change to the component and build my output column list without closing and restarting my custom UI form? I understand that the IDTSComponentUI interface being implemented allows for transactional editing of the component, in that the changes to the component are not applied until I have returned a result in the implemented Edit() method. However is there a way to apply changes without returning this result (and closing my UI)?
Essentially I am looking to have similar behaviour to that of the 'Refresh' button in the advanced component editor form.
I have built a custom flat file destination adapter but it appears that the code is not working. When I debug the process I notice that the ProcessInput section is called multiple times. The first time it looks like everything is working, then it call it again and there is no inpout from the DTSInput90.
I have been struggling with the Adapter Fill issue for days. I got an eeror "Invalid object name 'Weather'." Why? How to modify it?
Thanks
Code Snippet using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using My.Name.Space; public partial class GetStrings : System.Web.UI.Page { getStrings g = new getStrings(); protected void Page_Load(object sender, EventArgs e) { string strConnectionString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString; SqlConnection myConnection = new SqlConnection(strConnectionString); DataSet myDataSet = new DataSet(); try { myConnection.Open(); // create a new DataSet
// create the data GenerateDataSet(myDataSet, myConnection); } finally { myConnection.Close(); // bind each to table to a grid GridView1.DataSource = myDataSet.Tables["Weather"];
// fill the DataTable WeatherAdapter.Fill(dset,2,0,"Weather");//wrong here, //WeatherAdapter.Fill(dset.Tables["Weather"]); } //assuming the first dimention is collumn and the second is row public void GetDataRow(DataTable datatable, string[][] source) { DataRow dr; int rows = source.GetLength(0); int collumns = source[0].Length;
for (int i = 0; i < rows; i++) { dr = datatable.NewRow(); if (source[i] != null) { dr.ItemArray = (object[])source[i]; datatable.Rows.Add(dr); } } datatable.AcceptChanges(); } }
I downloaded and installed the Informix Client SDK 3.0 and was able to extract data from an Informix 10 database. All of a sudden, it stopped working. The error message I received is "... error in initializing provider. (-25580) System error occurred in network function.". Has anyone encountered this error before and know of a solution?
I am using an "XML Source Adapter" in an SSIS Package to try and load an XML file into a database table. But having trouble with a date field. The XSD defines the field as:-
So in the Data Flow I have a "Data Conversion Transformation" converting [DT_WSTR] to [DT_DBTIMESTAMP]. However, when I run the package I get:-
Error: 0xC02020C5 at Load XXX File, Data Conversion 1 [13550]: Data conversion failed while converting column "DateTime" (15100) to column "DateTimeDT" (15422). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
So what how do I fix it? I don't mind losing some miliseconds of precision, so I have tried configuring the error output of the Data Conversion for this field to "ignore failure" for "Truncation". But that seems to have no effect.
Will someone please tell me how to use "Destination adapter in store procedure" .............
I'm using Sql server 2005, instead of using BCP i have to use "Sql server destination adapter" in my store procedure, Is it possible to implement destination adapter in store procs, if it is yes please let me know how
Will someone please tell me how to use "Destination adapter in store procedure" .............
I'm using Sql server 2005, instead of using BCP i have to use "Sql server destination adapter" in my store procedure, Is it possible to implement destination adapter in store procs, if it is yes please let me know how
Will someone please tell me how to use "Destination adapter in store procedure" .............
I'm using Sql server 2005, instead of using BCP i have to use "Sql server destination adapter" in my store procedure, Is it possible to implement destination adapter in store procs, if it is yes please let me know how
I have conditional split on a boolean variable, when the value is true, case1 output goes to Oledb Destination, defaukt Output goes to Sql Server Destination.
When I run the package with value "true" the package runs, but when i run the package with bool value "false", the data is inserted through oledb but Sql Server destination fails.
Can any one tell me Why.
[SQL Server Destination [292]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".
I am looking for a query to solve this. I had two table's.
1) Employee Table
Employee_ID Designation ---------- ------------- Savin Database Administrator Ray Software Engineer Adam Software Engineer Scott Software Engineer
2). EmployeeCred Table
Employee_ID SkillSet ----------- --------- Savin Oracle8i Savin SQL Server 7.0. Savin SQL2000 Ray VB 6.0 Ray Java2.0 Ray C++ Adam Share point Adam VB 6.0 Adam Java2.0 Scott ASP Scott VB 6.0
Output I requried. -----------------
Employee_ID Designation SkillSet ------------ ------------ -------- Savin Database Administrator Oracle8i, Sql Server 7.0, SQL2000 Ray Software Engineer VB 6.0, Java2.0, C++ Adam Software Engineer Share point, VB 6.0, Java2.0 Scott Software Engineer ASP, VB 6.0
I need a query to solve this problem. Kindly please help me. Its urgent!!!
There are 2 tables: 1. ItemMaster Table: This table contains all the available Items (around 500,000). Fields are: (a) ItemId Integer (PK) (b) ItemDetails Varchar(100)
2. ItemImage Table: This item contains the location of picture of few items out of above table (around 10,000). Fields are: (a) ItemId Integer (PK) (b) ImageURL Varchar(50)
Requirement: I want to create a query or stored procedure, which would return me: ItemId, ItemDetails and a flag to indicate if image is available for that item or not.
Can anybody help me out in getting this flag which has to be build dynamically in the query as its not there in the tables. And what would be the query for acheiving the desired result.
Hi, I am a new SQL programmer. I need your help for writing a query. I am able to get the desired result set by looping through but it takes lots of time and resourses to run and crashes my machine. Is there any way to write a sql statement which performs batchwise? I really appreciate your help.
Following is a data from a view called ind_results.
last_name first_name course_code course_title score ------------------------------------------------------------------------- Walker Jay 1 Mid-Term Exam 71 Walker Jay 2 Final Part I Online Exam 64 Walker Jay 3 Final Exam Part II Online Exam NULL
Reed Steve 1 Mid-Term Exam 70 Reed Steve 2 Final Part I Online Exam 78 Reed Steve 3 Final Exam Part II Online Exam 80
I need help in writing a query, where
1) If Score is null for either course_code 2 or 3, display score = 'incomplete' for course_code 2 and 3 (for Jay Walker) 2) If Score is not null for either course_code 2 or 3, display score = average value of score for course_code 2 and 3 (For steve Reed)
i.e. the resultset should look like this.
last_name first_name course_code course_title score ------------------------------------------------------------------------- Walker Jay 1 Mid-Term Exam '71' Walker Jay 2 Final Part I Online Exam 'incomplete' Walker Jay 3 Final Part II Online Exam 'incomplete'
Reed Steve 1 Mid-Term Exam '70' Reed Steve 2 Final Part I Online Exam '79' Reed Steve 3 Final Exam Part II Online Exam '79'
I am trying to write a query where I get the following result. Basically I have to display latest part_number in the chain with status as 'current' for any part number that is superceded by another part_number.
I have written a code using cursor. But I just learnt that cursor should not be used. So I am looking for different syntax so that I can learn programming without cursor.
