My problem is that my:hustyp)[1] only fetches the first occurace of this tag. In my xmlfile this field is a repeating table created in infopath 2007. How do I manage to get the rest dynamically.
------T-SQL for fetching data from XML datatype------
WITH xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T13:29:33' AS my)
SELECT FormData.value('(/my:xxx/my:Formular/my:group1/my:group2/my:hustyp)[1]', 'varchar(99)') AS IdFastBet
FROM MinaDekl
CROSS APPLY FormData.nodes('(/my:xxx/my:Formular/my:group1/my:group2/my:hustyp) as TempTab(testTab1)
-----------------
Hello.. i develope a web projects of horoscope or astrology(http://demo.reallianzbussimart.com/allzodiac.aspx), there is an 12 Zodiaz sign and all the data call on this page through the Database , in this page i there is an one sql query ---- (Select Top 1 col from colour order by newid(),Select Top 1 num from number order by newid() ,Select Top 1 days from day order by newid() ) then all data call one by one change .. when the refresh the page the value of all zodiac is change that is wrong ,,, so what is the quary that one time in day the value off all zodiac is same next day the value has been change............................................
so please help me /........... tell what is the process to not data change into the page refresh Ashwnai
I'm using a remote SQL Server Express database with a C# app, and to do so as most of you already know, there's no DataSource available, it's all around SQL. This poses a problem as when I want to browse (1 by 1 in my app, with search utility) the contents of a given table, I have to perform a Select command. Well the problem is when I do this it loads all records into a DataSet (or DataTable), which is fine by me, but one of my tables is expected to reach 400 or 500 records in a few months time. This will mean a lot of loading from the db once the app is launched. Is there a way to make this connection more efective? Thanks
And I specify that I want all child items below the Id 2, it must return: Id | Title | Url | ParentId | Depth 2 | Sub-Test | http://localhost/subtest | 1 | 0
hi,im just a newbie in asp.net.i have my sqldatasource and a label. i want to get the data from sqldatasource to the label. how can i do that.need codes
Hi, I have stored procedure with cursor I have to use following step 1 fetch RateValidDates into @Id_RateCode, @TheValidDate .... get data
step 2 fetch prior from RateValidDates ... condition
step 3 fetch NEXT FROM RateValidDates into @Id_RateCode, @TheValidDate ... go to next record
Sql query analyzer output result of fetch NEXT FROM on the screen What seeting should i use to avoid output on screen (The reason My cursor takes about 8000 - 10000 rows when it's open)
Hi,I have a table with two columns Task and Employee. It lists all thetasks and the assigned employees. The same task will have multipleroles with an employeename or 'manager' as the data.If I have the following data'sales', 'john''sales', 'manager''dev', 'manager''make_coffee', 'manager''browse', 'jane''browse', 'manager'I need to get the rows wherever an employee is named (sales and browsefor example) and get manager for the rest.I can make it in two queries. Look for not manager in one and then formanager. Is there anyway to get them in a single query?If I need to look for 'sales', I need to get 'john' and not 'manager'.How to do that in a single query?Another need is to list all tasks with assigned. So for the above, Ishould get the following list'sales', 'john''dev', 'manager''make_coffee', 'manager''browse', 'jane'with two queries, I will get'sales', 'john''browse', 'jane''dev', 'manager''make_coffee', 'manager'which is ok. Order/sequence is not important.TIA,Sreelatha
I am getting problem in fetching data from SQL using C++ program. How to Fetch SQL Unicode characters? I am using ODBC driver calls in C++ to fetch SQL data. It is working fine with character data. But for Unicode data it displays €œ????€? instead of data.
I am using SQL Server SQLSRV32.DLL for creating datasource.
I do an insert of the xml into the table and that works fine, but how do I split the tags to different tables. I have tried SSIS and a XML Source to an OLEDB Source, but since the xml file contains different groups that do not work.
The xml is created by Infopath and it seems like the groups are created if the components belongs to different sections.
Table1 id int, xmltag xml
I am starting to be desperate, I really need some help solving this one way or another.
I am using SQL Server 2005 Mobile Edition & Merge Replication
in this I want to insert a record into table, in that table I have taken UserID coloumn as auto incrementing Identity type.
In a book I read that, If you are using an Identity column, you must find the next available number and reseed before an insert can be successful. You will also have to set up ranged identity columns on the published database to prevent errors when the new data is merged.
Now I want to ask here that, how should I ressed that value before inserting?????
any help in a form of CODe will be appriciated.....
I was working on a logic which I am not able to code after many attempts. I have an Excel sheet(Base_Data.xlsx) with two sheets as "Mapping" & "Data" with the below data:
I like to get data from a signle table and arranged in hierarchical(hierarchy) order. What will be my sql script to be able to get the desired result shown below? Please include some explanation as too what script is doing..
Table Structure and Sample Data
Id ParentId Name Code DisplayOrder 1 null Group 1 G00001 1 2 null Group 2 G00002 2 3 1 Sub-Group 1 SG0001 1 4 2 Sub-Group 2 SG0002 1 5 3 Sub-Sub-Group 1 SSG001 1 6 null Group 3 G00003 3 7 3 Sub-Sub-Group 2 SSG002 2
Desired Result Id ParentId Level Name ExtendedName DisplayOrder 1 null 1 Group 1 Group 1 1 3 1 2 Sub-Group 1 Group 1 -> Sub-Group 1 1 5 3 3 Sub-Sub-Group 1 Group 1 -> Sub-Group 1 -> Sub-Sub-Group 1 1 7 3 3 Sub-Sub-Group 2 Group 1 -> Sub-Group 1 -> Sub-Sub-Group 2 2 4 2 2 Sub-Group 2 Group 1 -> Sub-Group 2 1 2 null 1 Group 2 Group 2 2 6 null 1 Group 3 Group 3 3
NOTE: I apologize to anyone (especially moderators) who may notice that I am basically repeating a question that was already posted by me in another recent thread. The reason why I am reposting is because I want to filter my question down to its crux because the other question may not have been asked in the most clear way.
The Question:
The sceanrio is this. (1) I have a sales person dimension with a hierarchy. (2) In this hierarchy, Bill and Ted roll up to John. (3) Bill sells 10 units, Ted sells 8 units, and John sells 5 units.
When you process this hierarchy, what would you expect the total to be for John? (A) 23, which is the sum for Bill, Ted, and John or (B) 18, which is the sum of Bill and Ted only and overwritting John's number
I say (A) and I think most will choose the same, and all the examples I've been reflects (A).
I have one table Emp_MAster with two column ID-Sup_Id. I need to create a table where i can store data link this
Id-Hreid
Only difference is that I need to store data in an hierarchy view so Emp 1 is reporting to Emp2 and Emp2 is reportign to Emp3 so in new table I should get
I need the rows updated today in catalogue table irrelevant of timing. I tried all the below queries.
select * from CATALOGUE where CAT_DATE=CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) select * from CATALOGUE where CAT_DATE= CONVERT(date, getdate()) select * from CATALOGUE where CAT_DATE= cast(GETDATE() as date)
I am getting output only if the information updated on 04/21/2015 00.00.00.000 but not for other timings, For example 04/21/2015 03.30.00.000, 04/21/2015 07.17.00.000 and all.
Out of nowhere my derived hierarchies starting showing the following message in the MDS UI
No Level Defined:Â This Derived Hierarchy is incomplete......
As you can see below the structure is defined and still in tact. This message shows up in by the Explorer & System Admin areas. I'm also able to query the subscription view setup without any issue.
This is with 2012 w/ no CUs. Same setup in running in another environment without issue.
I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .
I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.
select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC
System.DateTime.Now is a function for getting current date time in C#
The query is neither giving error nor giving desired result.
I have been trying to write a cursor to fetch required data from table but somehow its running forever and inserting duplicate records.
I have a temp table named getInvoice where I have five important columns
1. invoice number 2.group 3.invoice status 4. Invoice Expiration date 5. Creation date time
and some other columns.One invoice number can belong to one or more group and there can be one or more records for a particular invoice number and group.
An example is below :
InvoiceNumber Group InvoiceStatus InvoiceExpirationDate CreationDateTime
My query condition is complex and that is why Im facing problem retrieving the output.I need a cursor for getting distinct invoice number from the table and for each invoice number I need to get the latest record for each invoice number and suffix combination based on creationdateand time column and if that record has invoice status of 2 and also the invoice expiration date can be either null or greater than today's date, then I need to get that record and put it in a temp table.
The query I wrote is below
declare myData cursor for select distinct invoiceNumber from #getInvoice declare @invoiceNumber varchar(30) open myData fetch next from myData into @invoiceNumber while @@FETCH_STATUS = 0
I have a time dimension which has Date, Week, Month and Year. However, the hierarchy will have only Week, Month and Year. It works great for any Sales measure with AggregateFunction as SUM.
I have created a new measure with AggregateFunction = LastNonEmpty. Also in the backend, I have pushed all the inventory data to last date in every month as inventory is always looked on a monthly basis not on a weekly basis. This measure shows correct data for every last week of the month in the hierarchy. However, Months and Years are displayed as zeros.
I am currently working with C and SQL Server 2012. My requirement is to Bulk fetch the records and Insert/Update the same in the other table with some business logic? How do i do this?
I have an excel sheet containing one column (ID_NO) with 400K rows. I have a database from where I have to fetch some other columns from a Netezza database. Initially I tried hardcoding all the 400K rows in the query that I wrote using filter WHERE ID IN ('1212','2334'). But after pasting all the 400K rows the query is running indefinitely.
I have imported all the ID in a SQL table (MY_LIST table). I used a DFT, and selected ODBC source, and selected my netezza server. Then in the 'Data access mode' I selected the SQL command from the dropdown.I pasted the same query that I wrote in Netezza. Is there any way to pull only for those records that I have pulled in my SQL table (MY_LIST) ?
Is it possible to have fetch within a fetch? I am getting this error message "A cursor with the name 'crImgGrp' does not exist." So i separate the process into two stored procedures?
CREATE PROCEDURE TrigSendPreNewIMAlertP2 @REID int
AS
Declare @RRID int Declare @ITID int
Declare @intIMEmail varchar(300)
Declare crReqRec cursor for select RRID from RequestRecords where REID = @REID and RRSTatus = 'IA' and APID is not null open crReqRec fetch next from crReqRec into @RRID
Declare crImpGrp cursor for select ITID from RequestRecords where RRID = @RRID open crImpGrp fetch next from crImgGrp into @ITID while @@fetch_status = 0
HI! I am new in DB so I need some advices for finding the right solution. I need to be able to make automatically any join between tables which the user choose and deliver the result. is like making an hierarchy between all tables in DB (parent-child) and then making the select statement for the right join.
if u have any idea about how can I manage this, pls help me.
I have an Interesting situation that I'm hoping some of you experts can help me with basically I have the following hierarchy:
Net Profit .....Gross Profit ..........Revenue ..........Direct Costs .....Indirect Expense
Now, I have another hierarchy - separate from the above - that I need to assimilate to the hierarchy above - Example:
Expenses .....Node a ..........Child a (Indirect Expense Type) .....Child b (Direct Cost type) Net Sales Adjustments .....Child a (Revenue type) .....Child b (Revenue type)
Depending on the type, the top most node needs to be assigned to the appropriate node in the initial hierarcy, like this:
Net Profit ....Gross Profit ........Revenue ................Net Sales Adjustments ....................Child a (Revenue type) ....................Child b (Revenue type) ........Direct Costs ...........Expenses .................Child b (Direct Cost type) ........Indirect Expense ............Expenses .................Node a .....................Child a (Indirect Expense Type)
Now, I've been able to figure out how to assign the top most node and leafs if all the children have the same type (using the expan stored proc listed in the books online), but my question is on this portion:
Expenses .....Node a ...........Child a (Indirect Expense Type) .....Child b (Direct Cost type)
Basically, the answer is to work backwards - if a child has a type that is different than the other children, a copy of the hierarch (up to the child) needs to be made and assigned to the appropriate initial node.
I've tried modifying the expand stored proc to give me the lineage of the child and see if there's a way I can copy the node and place it appropriately - I'm brainfried at this point, and I'm hoping that someone outthere can point me in the right direction.
Hai everyone., i need to get solution for tree hierarchy in sql is there any solution or any keyword like 'connect by prior' in oracle .. plz help me on this..
for example: id | FName |parentid | 1 | sandy | 2 | robert| 1
if i give the parentid 1 in where condition of a query i need the details of 'sandy'
I want my cursor to loop at the same APID then assign one ITID then move to the next APID and so on...
Any help is highly appreciated....
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO
ALTER PROCEDURE InsNewEmployeeImpTaskP2 @REID int, @LOID int, @RetValintoutput
AS
Declare @RRID int Declare @APID int Declare @intREID varchar(20) Declare @intIMID varchar(20)
Declare crReqRec cursor for select RRID from RequestRecords where REID = @REID and RRSTatus = 'AC' and APID is not null open crReqRec fetch next from crReqRec into @RRID
set @APID = (select APID from RequestRecords where REID = @REID and RRID = @RRID)
set @intIMID = (SELECT ImplementationGroup.IMID FROM ImplementationGroup_Location INNER JOIN ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @APID and ImplementationGroup_Location.LOID = @LOID )
insert into ImplementationTasks ( IMID, ITStatus, ITStatusDate ) VALUES ( @intIMID, '2', GetDate() ) SET @RetVal = @@Identity while @@fetch_status = 0 Begin
Update RequestRecords set ITID = @RETVal, RRStatus = 'IA' where REID = @REID and RRID = @RRID
FETCH NEXT FROM crReqRec into @RRID end
close crReqRec deallocate crReqRec
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
