Split Fields And Display Query Result
Hi,
I'm having a problem in spliting the fields
I need to ru the following query to join two tables and getting the output as shown.
Query:
select cusl.user_name,
pmts.bill_ref_info, pmts.payee_acid, pmts.cust_acid, pmts.txn_amt,pmts.pmt_id
from cusl, pmts
where cusl.ubp_user_id = pmts.ubp_user_id and pmts.ubp_user_id= 'testinglive'
Output:
user_name bills_ref_info payee_acid cust_acid txn_amt
SAMEER ALLA0210181#123456#Amita 378902010021095 383702070051411 1.000 16318
SAMEER BARB0GNFCOM#6788990#Vikram Kalsan 378902010021095 383702070051411 1.000 16327
SAMEER BKID0000200#378902010099678#Vikram 378902010021095 383702070051411 1.000 14031
SAMEER undefined#123456789123456#Vikram 378902010021095 383702070051411 1.000 13918
Now I need to display the second field which is a #-separated field as individual fields alongwith tghe other fields that are shown on execution of the query.
Can this be done? Please guide me on this...
View Complete Forum Thread with Replies
Related Forum Messages:
Is There A Way To Display This Query In 1 Result Set?
I am trying to count the number of Part that is repaired and those that is not repaired, is there a way to combine the following into one result set instead of returning 2? The bold line is the only condition that's different between this 2 query. I want to display these fields: date_complete, part_categoryid, part_model, repaired, not_repaired /* parts being repaired */select DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) as date_complete, part_categoryid, part_model, count(DISTINCT part_id) as repaired from tblPtSingapore INNER JOIN tblAuditPartStatus ON tblPtSingapore.part_Id = tblAuditPartStatus.auditpartstatus_partidwhere (tblAuditPartStatus.auditpartstatus_status = N'COMPLETE')and part_replaced = 0and (part_flag_nff = 0 and part_flag_ntf = 0 and part_flag_beyondrepair = 0)group by DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0), part_categoryid,part_modelorder by part_model, DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) /* parts completed but not being repaired */select DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) as date_complete, part_categoryid, part_model, count(DISTINCT part_id) as not_repaired from tblPtSingapore INNER JOIN tblAuditPartStatus ON tblPtSingapore.part_Id = tblAuditPartStatus.auditpartstatus_partidwhere (tblAuditPartStatus.auditpartstatus_status = N'COMPLETE')and part_replaced = 0and (part_flag_nff = 1 or part_flag_ntf = 1 or part_flag_beyondrepair = 1)group by DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0), part_categoryid, part_modelorder by part_model, DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0)
View Replies !
Display Query Result Vertically
Hi, I have a query to run, but the data in the tables are stored horizontally. I want the query to output the result vertically. e.g. if row 1 contains the following data: custA,3-april2008,mango's,123,456,78,10 Then i want it to output as follows: custA,3-april2008,mango's,123 custA,3-april2008,mango's,456 custA,3-april2008,mango's,78 custA,3-april2008,mango's,10 hope I'm clear, and would appreciate if someone could help me. Thanks
View Replies !
Re-display Result Set Without Re-running Query In Query Analyzer?
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
View Replies !
Connect To MSSQL2005 Using ASP2 And Display Query Result
Question: I have searched here and on Microsofts site already but it seems that all solutions require already either some ASP2 knowledge or MSSQL knowledge .. I am quite new in both but need to realise this for a project. I have installed a MSSQL 2005 server running MSSQL2005 Standard in mixed authentication mode. Services running using a domain account created for this purpose. I have then created a simple database called test with a table called testtable All I need to achieve now is a simple ASP2 page, which is located on the DC IIS, which connects to the SQL server (in same domain but different server) using WINDOWS authentication and returns the result of SELECT * FROM TESTTABLE I try now for a couple of days so I hope someone could help me with this .. I really need a step by step guide what I need to do on the SQL server side (so a specific user can connect to this particular database) and on the IIS side .. I know it is always painful to help someone with little knowledge but I am getting desperate. Thanks a lot guys
View Replies !
How To Split A Field Into Two Fields
I have the following fields in table A: GL_ID| Date |GL_Name_VC | Amount Period_TI|Year_SI =============================================== 1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005 =============================================== 1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006 =============================================== 1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006 =============================================== 1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006 The above database is running on Microsoft SQL Server 2000 and i would like to query for a report that looks something as below: Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance =============================================== 01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000 01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0 01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0 01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |6,000 The formula for the above calculated fields are as below: Opening Balance = carried forward balance from Year 2005 Debit = All positive amount Credit = All negative amount Net Change = Total Credit - Total Debit in Period 01 Balance = Total of Net Change + Opening Bal Guys, hope someone out there can help me with the sql command for the above report?
View Replies !
How To Split A Field Into Two Fields
I have the following fields in table A: GL_ID| Date |GL_Name_VC | Amount |Period_TI|Year_SI =============================================================== 1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005 =============================================================== 1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006 =============================================================== 1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006 =============================================================== 1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006 The above database is running on Microsoft SQL Server 2000 and i would like to query for a report that looks something as below: Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance ===================================================================== 01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000 01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0 01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0 01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |16,000 The formula for the above calculated fields are as below: Opening Balance = carried forward balance from Year 2005 Debit = All positive amount Credit = All negative amount Net Change = Total Credit - Total Debit in Period 01 Balance = Total of Net Change + Opening Bal Guys, hope someone out there can help me with the sql command for the above report?
View Replies !
How Do I Split A Column Result By A Nonalphanumeric Character?
I have a column that returns client numbers. The client numbers are 4-6 characters in length. A period (.) is added to the end of the client number, and then one last digit (1-4) is affixed at the end to denote a categorization. In SQL, I need to figure out how to divide these results into two columns, one for the client number and one for the categorization number. EG: client #4334.1 would become 4334 for client # and 1 for categorization number or Client #424561.3 would become 424561 for Client # and 3 for categorization number. I have to strip out the period in the process and leave myself with just the numeric characters divided into two columns. Ive been researching my brains out on string queries and substring queries and I can't figure out how to parse out the period and/or to have SQL understand that I need everything BEFORE the period for one column and everything AFTER the period for the second. Is it possible to do this? I really need help on this one. Thank you :)
View Replies !
UNION With A Max On Split Date/Time Fields
I have 2 tables, each with one ID field, a separate Date and Time fields and a number of other fields. The tables contain duplicates on the ID field. I want to do a UNION keeping only the record with the latest Date and Time. This would work: SELECT MyTab.myKeyField, Max(MyTab.myDate) AS myDate FROM (SELECT myKeyField, myDate from Table1 union SELECT myKeyField, myDate from Table2) AS MyTab GROUP BY MyTab.myKeyField But is only taking care of Date, not Time (some records have the same date but different times) The other problem is, when I add more fields, I have to include them in the GROUP BY clause, and this way I end up with duplicates (because some other fields have different values) Is there a way to do this?
View Replies !
How Can I Split Fields And Depending One Column Decide The Foreing Key
I´m wondering how to solve the following scenario with SSIS I have a CITY table and a STATE table, I have to load a file with the information regarding to the CITY: the state table is like this: StateCode(PK) stateLegalCode stateName ============= ============== ========= 1 01 Florida the city table is like this: citycode(PK) cityLegalCode cityname StateCode(FK) ============ ============= ======== ============= 1 1001 Quakertown 1 the file has the following information cityLegalCode cityName ============= ======== 01-1001 Quakertown ... how can I load the file into CITY table: 1-) with the file's cityLegalCode I have to split the string and if the two initial digits are 01 the registry must have 1 in the StateCode(FK). how can I do something like that using SSIS??? thanks
View Replies !
How To Display Result Of Different Year In Different Column Instead Of Different Row?
I am trying to display component usage in January for the past year, if I want to display the year in different column, what should I do? eg. component_id component_description qty_used_on_2005_Jan qty_used_on_2006_Jan C58B0BDD tape drive 2 3 Currently I am using this sql: select cast(year(date_complete) as varchar(10)) + ' Jan' as Year, component_id, component_description,sum(component_qty) as total_qty_used from view_jobComponent where month(date_complete) = 1group by component_id, component_description, cast(year(date_complete) as varchar(10)) + ' Jan'order by component_id, component_description which I will get something like this: Year component_id component_description total_qty_used 2005 Jan C58B0BDD tape drive 22006 Jan C58B0BDD tape drive 3
View Replies !
How Can I Display Different Fields In The Footer
Hi i have a report and i want 13 text boxes that i have in the body of the report in the Page footer. So should i specify each textbox that i want to have in page footer or is possible for me to just call the rectangle that its sitting on. and how can i reference a image which is been pulled by the dataset ..how should reference them.. Any help will be appreciated Regards Karen
View Replies !
Front End Display Of Two Fields
I am doing a report which needs to display a field like totals/percentages... the actual data values is something like this =(Sum(Fields!B.Value))& " / " & (Sum(Fields!B.Value))/(sum(Fields!A.Value) + sum(Fields!B.Value)+sum(Fields!C.Value)+sum(Fields!D.Value)+sum(Fields!E.Value)+sum(Fields!F.Value)) *100 the problem is sum (fields!B.value) is an integer (sum of students) like 32 and the aggregate value in the denominator is a percentage value (0.014343443%) I need to approximate the value to 0.014 something like this totals/percentages... .......32/0,014 instead of 32 / 0.014343443 in the front end of the report properties Please help Thanks
View Replies !
How To Use A Function To Format And Display Result From Data Reader
Hi guys n gals ! I am having a few problems manipulating the results of my data reader,To gather the data I need my code is: // database connection SqlConnection dbcon = new SqlConnection(ConfigurationManager.AppSettings["dbcon"]); // sql statement to select latest news item and get the posters name SqlCommand rs = new SqlCommand("select * from tblnews as news left join tblmembers as members ON news.news_posted_by = members.member_idno order by news.news_idno desc", dbcon); // open connection dbcon.Open(); // execute SqlDataReader dr = rs.ExecuteReader(); // send the data to the repeater repeater_LatestNews.DataSource = dr; repeater_LatestNews.DataBind(); Then I am using: <%#DataBinder.Eval(Container.DataItem, "news_comments")%> in my repeater.What I need to do is pass the "news_comments" item to a function I created which will then write the result. The code for my function is: // prevent html public string StripHtml(string data) { // grab the data string theData = data; // replace < with &alt; theData = Regex.Replace(theData, "<", "<"); // return result return theData; } But I am having problms in doing this,Can anyone point me in the right direction on what I should be doing ???
View Replies !
Display A Group In A Report That Has No Data Rows In The Result Set
Hello I have a report that retrieves its data from Analysis Services. The data includes a count and dollar value of projects against their current status: It looks something similar to (group1) status1 10 $200,000 (detail) p1 1 $5,000 p2 1 $10,000 . . p10 1 $20,000 (group1) status3 5 $90,000 (detail) . . (group1) status4 15 $150,000 (detail) . . In the report I hide the detail rows. I have a fixed/known number of statuses (in this case 4) and need to show all 4 in the report. eg (group1) status1 10 $200,000 (detail) p1 1 $5,000 p2 1 $10,000 . . p10 1 $20,000 (group1)status2 0 $0 (group1) status3 5 $90,000 (detail) . . (group1) status4 15 $150,000 (detail) . . ie in this case I need to show status 2 (that doesn't exist in the data set) with zero totals. Does anyone know if this is possible to get SSRS to display each of the status groups (in a known fixed list) and then match them to the records in the dataset. As an alternative, if I were using SQL Server I could add rows to the dataset using a union statement. Is there similar functionality using mdx? My mdx skills are very basic. Thanks Stewart
View Replies !
Display All The Fields In Sql Server 2005
I want to select all the fields from just one table This is what i have and i am having trouble selecting all the fields select * from ace_users where chdata EXISTS (select chdata, count(chserialnum) from ace_users group by chdata having count(chserialnum)> 1) Exists and IN does not work.
View Replies !
Display All Fields Including The Field Has Dup
I have one table where i want to use aggregate function for duplicate rows and also be able to select all fields to display. How would i do that? Here is my query: select Z, count(*)as num from Table group by Z having count(Z) > 1 ----- this returns 213 rows Select Z, A,B,C,D From Table Group By Z, A,B,C,D Having Count(Z)>1 ----This gives me nothing Hope I am explaining this correctly as to what I want. Thanks, Saru Brochu
View Replies !
Saving Query Result To A File , When View Result Got TLV Error
HI, I ran a select * from customers where state ='va', this is the result... (29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record Thanks for your help Ali
View Replies !
End Result Is Main Query Results Ordered By Nested Result
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows: SHOWS showID showTitle SHOWACCESS showID remoteID VIDEOS videoDate showID SQL is as follows: SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC; I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong? thanks
View Replies !
Do Not Display The Result Of My Long Text String, Approx About 400 Characters
I have the following problem. My SQL Query that i wrote works but the result that is displayed in Query analyzer cuts most of my long text that I want in my result. The long text string is approx about 400 characters and the type is varchar of the field. Any ideas?? SELECT '510', PRODCLASSID , '1', COMPONENTID,'ENG'+SPACE(2),'#'+SPACE(254),'#'+SPAC E(254),'#'+SPACE(254),'#'+SPACE(99),externalid, 'Desc1' = CASE WHEN SUBSTRING(externalid,1,2) = 'MF' THEN 'Full machine warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. ' WHEN SUBSTRING(externalid,1,2) = 'MP' THEN 'Full machine warranty, parts only : parts covered at warranty rates applicable at the time of repair. ' WHEN SUBSTRING(externalid,1,2) = 'PF' THEN 'Power line warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. ' WHEN SUBSTRING(externalid,1,2) = 'PP' THEN 'Power line warranty, parts only : parts are covered at warranty rates applicable at the time of repair. ' END + CASE WHEN SUBSTRING(externalid,LEN(externalid)- 3,4) = '2018' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 18 month or 2000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '3024' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 24 month or 3000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '4030' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 30 month or 4000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '5036' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 36 month or 5000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '6042' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 42 month or 6000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '8054' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 54 month or 8000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '1074' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 74 month or 10000 HRS, whichever comes first. ' END + 'Flexible warranty is handled according to the procedures described in ESPPM 3-10.' + CASE WHEN prodclassid IN ('P1','P11','P8','P9') THEN ' (mileage limited to 300 km)' WHEN prodclassid IN ('P7') THEN ' (mileage limited to 200 km)' ELSE NULL END + SPACE(5000 - LEN('Desc1')) .......
View Replies !
SELECT Statement To Display Combined Fields
Dear All I am trying to populate an OledbDatareader for binding to a ASP datagrid. For this I use select statement to display combined fields in a datagrid cell. Eg. Select (Field1+ '<br/>' + Field2 + '<br/>' + Field 3) As Address .. and so on. But the problem is if any of the three field is null the combined field 'Address' returns as Null. Please help me to overcome this problem. Regards kalanad ( beginner)
View Replies !
Display Fields In Controls Created At Runtime
I'm a vb beginner. How do you display the items counted from a table to a textbox which is created at runtime? Below is the code I started for counting. Dim RST As ADODB.Recordset Set RST = GetRecordset("select count(*) from lkpMeatTableFormula where intSKUTitle = '" & cmbSKU & "'") Count = RST(0) Set RST = Nothing Txs
View Replies !
Display Duration Between Two Datetime Fields In 00:00 Format
I have a requirement to display the elapsed time between two datetime fields. What i have been able to do is datediff the dates and get the elapsed time in minutes and then i am trying to format it as HH:MM. For example, an activity started on Nov 1 2007 08:00 and finished Nov 1 23:59, the elapsed time should be displayed as 15:59. The other example is if the activity spans over a couple of days, it should be say 49:03. I put the following code in the format of the textbox, but it only seems to work when the length of the minutes is greater than 1. =IIF(Len(CStr(Fix(ReportItems!textbox11.Value/60)))= 1, "0" + CStr(Fix(ReportItems!textbox11.Value/60)), CStr(Fix(ReportItems!textbox11.Value/60))) + ":" + IIF(Len(CStr(Fix(ReportItems!textbox11.Value Mod 60)))= 1, "0" + CStr(Fix(ReportItems!textbox11.Value Mod 60)), CStr(Fix(ReportItems!textbox11.Value Mod 60))) Have i missed something really simple or what? Any help would be much appreciated. Alternatively, if you have a better way of doing the whole thing would like to hear from you. Thanks in advance.
View Replies !
How Check Columns + Fields Add Result Row At END
hi need help from the wonderful people here i am working on project and convert a OLD DOS program to SQL server on the web this is the resalt i need to show napipot Fname new_unit day1 day11 day111 day2 day22 day222 day3 day33 day333 day4 day44 day444 1 aaaa 1_1 A C Q D E y 2 bbbb 2_2 - E A d 3 cccc 3_3 B F B z f 4 dddd 1_1 - * 5 eeee 4_4 C A C --------------------------------------------------------add space row------------------------- result row Ok OK OK ok B ok ok ok ok A B C i need check only the A B C if not show the missing A B C like this A on the left B on middleC on right
View Replies !
How To Display The Search Result Of A BookStore With Pictures Of Books, In A Proper Format.
Hi all, I am using ASP.NET 2003 with SQL Server as database. I have a database of a book store with BookPicture, Author, Title, and Description of the Book. Now when the user searchers for a book with a keyword, how can I display the results which should show: 1. The picture of the book, 2. The at it right, Title of Book, 3. The author,4. The descritionThen the image of "Add to cart" Each search result must be separated by a box like the cell of a table. Is it possible to be done? Thanking you in advance Tomy
View Replies !
How To Access Data From Different Database And Display Result Set In Managed Stored Procedure
Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Data.OleDb Imports System.Configuration Imports System.Text Imports System.Collections Partial Public Class StoredProcedures <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub StoredProcedureTest(ByVal strAS400ServerName As String, _ ByVal strCompany As String, _ ByVal decSerial As Decimal, _ ByVal strSerialCode As String, _ ByVal strSerialScan As String, _ ByVal decMasterSerialNumber As Decimal, _ ByVal strCustomerPart As String, _ ByVal strTakataPart As String, _ ByVal strCustomerRanNo As String, _ ByVal strCustomerAbv As String, _ ByVal strDestinationAbv As String, _ ByVal decQty As Decimal, _ ByVal strCreatDate As String, _ ByVal decVoidSerialNo As Decimal, _ ByVal strProductionLineNo As String, _ ByVal strProcType As String) Dim sp As SqlPipe = SqlContext.Pipe Dim strResult As Integer = 0 Dim strErrorText As String = String.Empty Dim dsData As New DataSet Dim parameter(15) As OleDbParameter If Not strAS400ServerName Is Nothing And strAS400ServerName <> String.Empty Then ' Populate parameter collection parameter(0) = (CreateParameter("PARM1", OleDbType.Char, 20, ParameterDirection.InputOutput, strAS400ServerName)) parameter(1) = (CreateParameter("PARM2", OleDbType.Char, 2, ParameterDirection.InputOutput, strCompany)) parameter(2) = (CreateParameter("PARM3", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decSerial)) parameter(3) = (CreateParameter("PARM4", OleDbType.Char, 2, ParameterDirection.InputOutput, strSerialCode)) parameter(4) = (CreateParameter("PARM5", OleDbType.Char, 25, ParameterDirection.InputOutput, strSerialScan)) parameter(5) = (CreateParameter("PARM6", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decMasterSerialNumber)) parameter(6) = (CreateParameter("PARM7", OleDbType.Char, 30, ParameterDirection.InputOutput, strCustomerPart)) parameter(7) = (CreateParameter("PARM8", OleDbType.Char, 15, ParameterDirection.InputOutput, strTakataPart)) parameter(8) = (CreateParameter("PARM9", OleDbType.Char, 15, ParameterDirection.InputOutput, strCustomerRanNo)) parameter(9) = (CreateParameter("PARM10", OleDbType.Char, 6, ParameterDirection.InputOutput, strCustomerAbv)) parameter(10) = (CreateParameter("PARM11", OleDbType.Char, 6, ParameterDirection.InputOutput, strDestinationAbv)) parameter(11) = (CreateParameter("PARM12", OleDbType.Decimal, 9, ParameterDirection.InputOutput, decQty)) parameter(12) = (CreateParameter("PARM13", OleDbType.Char, 10, ParameterDirection.InputOutput, strCreatDate)) parameter(13) = (CreateParameter("PARM14", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decVoidSerialNo)) parameter(14) = (CreateParameter("PARM15", OleDbType.Char, 3, ParameterDirection.InputOutput, strProductionLineNo)) parameter(15) = (CreateParameter("PARM16", OleDbType.Char, 2, ParameterDirection.InputOutput, strProcType)) RunDB2Sp("FABLE.MAP", parameter, dsData) If dsData.Tables.Count > 0 Then dsData.Tables(0).TableName = "Supreeth" Dim bitresult As String = dsData.Tables(0).Rows(0)(0).ToString() Dim errorstring As String = dsData.Tables(0).Rows(0)(1).ToString() ' I am not sure here SqlContext.Pipe.Send(bitresult) SqlContext.Pipe.Send("No errors") End If Else Throw New ArgumentException("AS400Db.GetAS400TraceabilityResult: AS400 server name is empty or invalid") End If End Sub Public Shared Sub RunDB2Sp(ByVal strProcedure As String, ByRef parms As OleDbParameter(), ByRef dsData As DataSet) '********************************************* ' Declare Variables '********************************************* Dim daAdaptor As OleDbDataAdapter Dim cmdAS400 As OleDbCommand 'Dim dstestMe As New DataSet Try cmdAS400 = CreateCommand(strProcedure, parms) daAdaptor = New OleDbDataAdapter(cmdAS400) ' Fill the Data Set daAdaptor.Fill(dsData) Catch expError As OleDbException daAdaptor = Nothing Finally daAdaptor = Nothing cmdAS400.Dispose() 'Me.Close() End Try End Sub Public Shared Function CreateParameter(ByVal name As String, _ ByVal type As OleDbType, _ ByVal size As Integer, _ ByVal direction As ParameterDirection, _ ByVal paramValue As Object) As OleDbParameter Dim param As OleDbParameter = New OleDbParameter param.ParameterName = name param.OleDbType = type param.Size = size param.Direction = direction param.Value = paramValue Return param End Function Private Shared Function CreateCommand(ByVal strProcedure As String, ByVal prams As OleDbParameter()) As OleDbCommand Dim CmdSAS400 As OleDbCommand Dim parameter As OleDbParameter Dim connAS400 As OleDbConnection connAS400 = New OleDbConnection("Provider=IBMDA400;Data Source=AHISERIESDEV1;User Id=****;Password=****;") connAS400.Open() CmdSAS400 = connAS400.CreateCommand() CmdSAS400.CommandText = strProcedure CmdSAS400.CommandType = CommandType.StoredProcedure CmdSAS400.Parameters.Clear() 'CmdAS400.CommandTimeout = intTimeOut If (prams Is Nothing) Then Else For Each parameter In prams CmdSAS400.Parameters.Add(parameter) Next End If Return CmdSAS400 End Function I have a UI which supplies 16 parameters to my stored procedure , which in turn call another sored procedure on as400 which returns result set. So far i am able to send 16 parms and get the values in dataset. My question here how would i send the result set to UI for display, please feel free to comment on any changes need to be made on code . I badly need to find a solution for this and i appreciate any feed backs Thanks
View Replies !
Is There A Way To Select What Fields To Display On A Report From A Multi-value Parameter?
I am trying to figure out a way to toggle the visibility of attribute data based on a parameter. Specifically, I have a report that has many columns that an end-user may not want to see, depending on what they are using the report for. I know you can toggle visibilities on individual columns easily enough, however I want the user to be able to select which fields (at the attribute level) they want visible on the report up in the parameters, via a multi-value prompt. Is this possible with reporting services 2005? Thanks.
View Replies !
How Can I Send Row Data To A SQL Database Table Via VB 2008, And Permanently Save And Display The Result?
This is related to: How can I make some graphics drawings stick while others disappear? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2905460&SiteID=1 Except that now I am trying to connect and update to an Microsoft SQL Server Database File (SqlClient) via VB 2008 Express; specifically a table called €œHexMap€? that contains some columns that I am ready to insert some row data into. Here is what my program should do: As I hover over a hexagon map of the US a red flickering hexagon follows the location of my mouse cursor. If I click on a given hexagon, the program draws a permanent blue hexagon, and sends a new set of row data into my database. Such information as the name of the state, row, column, center x, and center y, etc. Here is a quick snapshot of this program in action: http://farm4.static.flickr.com/3128/2325675990_4155edbdee_o.jpg -sorry, I didn't capture the mouse cursor inside the red hexagon I think I am missing something since I appear to be able to connect successfully to the database table. Unfortunately, I never see the changes in the database, when I try to Show Table Data (via Database Explorer). I am hoping someone will review my code snippet (below) and tell me what I am missing. What happens when I run this code is that it acts like it works just fine, except that I have no indication that any changes were actually affected. Code Snippet '====================================================================================== Dim CN As New SqlClient.SqlConnection() Dim da As New SqlClient.SqlDataAdapter 'Consider using Me._adapter that is used already CN.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Mapboard.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" CN.Open() 'Use the following code to verify that a connection to the database has achieved If CN.State = ConnectionState.Open Then MsgBox("Workstation " & CN.WorkstationId & "connected to database " & CN.Database & "on the " & CN.DataSource & " server") End If Try Catch ex As Exception MsgBox("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR" & vbCrLf & ex.Message) End Try 'use the Connection object to execute statements 'against the database and then close the connection da = New SqlClient.SqlDataAdapter("select * from HexMap order by Territory", CN) If CN.State = ConnectionState.Open Then CN.Close() '========================================================================== Dim rows As Integer rows = 0 Dim CMD As New SqlCommand("INSERT HexMap (Hexagon, HexRow, HexCol, HexX, HexY, Territory) VALUES(HexCounter, CaptureRow,CaptureCol,Hx,Hy,Territory_ComboBox1.Text)", CN) CN.Open() rows = CMD.ExecuteNonQuery If rows = 1 Then MsgBox("Table HexMap updated successfully") Else MsgBox("Failed to update the HexMap table") End If If CN.State = ConnectionState.Open Then CN.Close() '========================================================================== Thanks for reviewing my code. Technozoide
View Replies !
Change/Add Report Fields To Display Based On The Parameter Selected
I have a report parameter named "Schools" which display a list of schools. For example, Alo elementary school, Balo middle school and Calo high school. When "Alo elementary" is selected the report only display students from that school along with other assessments data fields. same goes for other schools too. But I want to display different data fields for "Calo high school" when it is selected. It is not currently possilbe becasue I am using the same template for all types of schools. There some fields only should be displayed for "Calo high school" but not for any other type of schools. I can accomplish this by creating two separate report, one for "Calo high school" and the other for other schools. But I want to accomplish this just by creating one report. So when "Alo elementary school" is selected it displays report with certain fields and when "Calo high school" is selected then it displays same fields as "Alo elementary school" but as well as some other fields too in the report. Is this possible? Need help.
View Replies !
How To Pull Data Into A Datagrid From The Same Table, 2 Fields, And Display Them As 1 Field In The SqlDataSource?
Hi, Im trying to pull data from 2 fields in the same table into a SqlDataSource that feeds into a GridView, and display them as 1 field in GridView? I have a database table that has entries of users and their friends. so this tblFriendUser has a column called UserName and another column called FriendUserName. I am trying to get a list of friends for that particular user. Note that if User1 initiated the friend request, he will be listed as UserName and his friend as FriendUserName, but if his friend initiated the friend request, it will be vice versa: him being the FriendUserName and his friend the UserName. So I want the following 2 queries run and merged into one query in order to return 2 columns only: UserFriendID & UserName, is that possible? Is my design bad? Any suggestions/advice would help! Thanks a lot! SELECT UserFriendID, UserName FROM tblUserFriends WHERE (UserName = @UserName); SELECT UserFriendID, FriendUserName AS UserName FROM tblUserFriends WHERE (FriendUserName= @UserName);
View Replies !
How To Split The Field In The Sql Query?
I have the field LocationID (string)which has values like "AZ001","AZ002","IN002","IN004" first 2 will be always alphabets and remaining 3 will be numbers, I want to split it like "AZ" ," 001" "AZ","002" "IN" "002" "IN" "004" now i will populate dropdownlist with unique values of "AZ" "IN" according to first dropdownlist how i will populate second dropdownlist? So how to write sql query for splitting? and then populating the dropdownlist ?
View Replies !
Mdx Query Dim?split?left?
:confused: Hello i have some problem with this MDX Statment : with SET [kunden] as 'Filter (descendants([Dim_x].[Alle Kunden], , LEAVES), left([Dim_x],1,1) = "(" ) ' select {descendants([Dim_Kx].[Alle Kunden], , LEAVES) } on rows, {([measures].[Standard])} on columns from z Result : Standard (test1) 44444 (test2) 54567 (test3) 3214 test5 4535 test6 11111 but i want to filter i need only result with () how can i do it please i need help many thanks, Prince
View Replies !
Conditional Split Query
Hi, I have the following table in MsAccess EmployeesA empId integer, empName varchar(60), empAge integer, empStatus char(1) - can be N,D or S - New, Deleted or Shifted and the following in Sql2005 EmployeesB Id smallint, Name varchar(60), Age int, Status char(1) - Bydefault 'N' I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now. If empStatus = N in EmployeesA, then insert a new record in EmployeesB If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D' If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row. How do I do it for each table each row in EmployeesA using a foreach file loop? Thanks, ron
View Replies !
One Table, Two Condition, Display Result As One Table
I got one table with 3 columns = Column1, Column2, Column3 Sample Table Column1 | Column2 | Column3 ------------------------------------ A | 12 | 0 A | 13 | 2 B | 12 | 5 C | 5 | 0 Select Column1, Column2, Column3 as New1 Where Column1 = A AND Column2 = 12 AND Column3 = 0 Select Column1, Column2, Column3 as New2 Where Column1 = A AND Column2 = 12 AND Column3 >0 The only difference is one condition Column3 = 0 and another one Column3 > 0. This two condition is not an "AND" condition... but just two separate information need to be display in one table. So how do i display the result in one table where the new Output will be in this manner Column1 | Column2 | New1 | New2| Thanks
View Replies !
Query Split Column In 2 Columns In SQL
I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that. I have a table like this: Select Name from Cars; Result: Col1 BMWMercedesFordAudi But i like to make a query so it is displayed like this: Col1 Col2 BMW FordMercedes Audi So i can bound a table directly to that column!Is this possible with SQL, and how can i build it.Thanks.
View Replies !
Query To Split A Database Column ?
How can i write a query to split a database column and shows 2 new columns. In my database column I have 2 mixing items and need to split out to 2 columns. Normally I have to write a query and change parameter and run another query. For example a database column with average number and range number. Thanks Daniel
View Replies !
Easy SQL Question. How To Display Query Results In Query Analyzer
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says. Command(s) completed successfully. What I really need it to do is to display the results of the query. Does anyone know how to do this? declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
View Replies !
Report Designer: Need To List Fields From Multiple Result Rows As Comma Seperated List (like A JOIN On Parameters)
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter. However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance). For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed). Something like: List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac How can I accomplish this? I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell. Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
View Replies !
SP To Perform Query Based On Multiple Rows From Another Query's Result Set
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
View Replies !
Using 3 TBLS. Want To Display Fields Frm Othr Tbls
I am using three tables. select * from ldap_users as PL where (emcidentitytype like '%P%') and(ctscUserKeywords NOT LIKE '%LockedOut%') AND(ctscUserKeywords NOT LIKE '%LockedOut%') and emclastdayworked = '' and (uid in(select fax from oracle_11i_user) or uid in (select fax from ORACLE_1103_USER)) This query only display all the fields from PL. I want to display end_date from both oracle11i and oracle1103 tables? How would I do that?
View Replies !
Return Result From One Query As A Column In Other Query
I'm having a bit of a trouble explaining what I'm trying to do here. I have 3 "source" tables and a "connecting" table that I'm going to use tblContacts - with contactID, ContactName etc tblGroups - with GroupID, GroupName tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups) They are related in a table called tblContactsGroupConnection - with ContactID, GroupID and SubGroupID One contact can be related to many subgroups. What I want is a list of all contacts, with their IDs, names and what groups they are related to: ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3] ContactID, ContactName, [SubGroupName1, SubGroupName3] ContactID, ContactName, [SubGroupName3] I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :) Kirikiri
View Replies !
Query Within Query - Checking For Multiple Fields
Hi, I remember seeing a fancy query that checked for multiple fields in a table (I think using a select statement in the where clause but not sure), but can't remember how to do it... here is what I want to do (and maybe there is a much easier way). Thanks! Table1 id item color 1 shoe red 2 shoe blue 3 coat green 4 coat black Table2 item color shoe red coat green I want everything in Table1 where item and color are not a match. So my results should be: 2 shoe blue 4 coat black I'm sorry if this is a dumb question... it's been that kind of a day! Thanks!
View Replies !
SQL Query - How To Display Row Two And Onwards?
Hi, I have this query: Dim strSQL As String = "SELECT Ticketreply.Status, TicketReply.ReplyID, TicketReply.AssignedTo, TicketReply.ReplyName, TicketReply.Reply, TicketReply.Attachment, TicketReply.LastUpdated, TicketReply.Priority FROM TicketReply INNER JOIN TicketThread ON TicketThread.TicketID = @TicketID WHERE (TicketReply.TicketID = @TicketID) ORDER BY TicketReply.LastUpdated" How in the gridview do I display all the results bar the first record? It does this at the moment: First Second Third I want it to display like this: Second Third So it misses the first row. Hopefully easy for you Pro's! Any help appreciated. Cheers, Ricky
View Replies !
|
TRACKER
