URGENT! Need To Extract Default Values And Update Similar Tables
May 19, 2008
I am using SQL 2005 merge replication with a publisher managing about 45 articles(tables) with about 10 subscribers (remote servers). The problem is that we had to re-start replication from scratch and noticed that, although the publisher's tables have the default values, the subscribers did not get the default values with the initial snap shot, schema building..?!?
I now have to go over 450 tables (10 remotes SQl servers at 45 tables each) and 'reset or set' over 1,000 default values. Meanwhile, the system is down...omg...so not good.
Is there a script out there that automatically extracts the default values from a table and set it to another exact table with the same structure? any ideas?
Thanks..CF
btw, i'm no scripting wizard....help!.
View 1 Replies
ADVERTISEMENT
Nov 8, 2013
I have a grid like this and when I change Designation value of Name X from PA to PAT, this change should reflect to Sathish Designation too. Changing similar values should reflect all over the grid. Like update database on change of value of similar kind.
By far I use this query to update the grid. So a where clause should be used now to update the grid to fit this scenario.
("UPDATE AppInvent_Test SET Name = @Name, Designation = @Designation, City = @City WHERE EmpID = @EmpID");
EmpID Name Designation City
21 X PA Chn
2 Sathish PA Chn
3 Shiva A Cbe
17 Venkat M Hyd
22 Y SM Cbe
18 Vignesh SA Hyd
Table:
CREATE TABLE [dbo].[AppInvent_Test](
[EmpID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
[Designation] [varchar](100) NULL,
[Code] ....
View 4 Replies
View Related
May 26, 2007
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View 4 Replies
View Related
Oct 26, 2000
Does anyone know if or how I can insert a default value of the (+) character into a column? when I try to save it I get an error message saying it has a problem with ')'.
View 1 Replies
View Related
Aug 20, 2007
Hi,
I am trying to replicate some of my production tables into development tables in an automated way. Decided to use the Select INTO command because it seemed to be the easiest way. This command does copy all the col sizes, but does not copy over the "default value or binding" fields. How do I do this? I saw that in create table you can use "INCLUDING COLUMN DEFAULTS "
But this doesn't seem to work for the select into command. Can you tell me how I can do this? Currently the command I am using is:
SELECT * INTO " + DevTableName + " From " + ProdTableName
However, this won't copy over the default column values that I have specified inside the table ProdTableName. Can you tell me how I can do this?
Thanks,
-Ankur
View 6 Replies
View Related
Feb 8, 2007
Ok, so I must have screwed something up.
I have several databases set up for transactional replication to another instance of SQL Server 2005 for fail over purposes. Today, I restored one of those replicated databases to my development machine and discovered two surprising problems:
1) The Default Values settings in the replicated tables are missing. They are there in the publishing tables, just as they were before I set up replication. However, they are not in the subscribing tables. Now, this is not such a big issue, since I tend to send all default values in insert queries as necessary.
2) The second problem is a more of an issue, since I use auto-numbered Identity columns in my tables (yes, I know that's just plain lazy...). Anyway, in the replicated tables, €œIs Identity€? is indeed set to yes, but despite that fact that there are thousands of records with incrementally unique IDs, SQL server is trying to insert a record starting with 1. This, of course, throws a PK constraint error.
Obviously, if I am use them for failover purposes, these replicated databases need to be identical in every way.
So, what did I do to cause this situation, and how to I fix it?
Thanks a bunch!
md
View 9 Replies
View Related
Jul 23, 2005
How can i enter Default Values of " " to all the columns of type characterof all the tables (excluding system tables) and Default Values of 0of all columns of type numbers. Excluding all primary key columns.Thank you
View 1 Replies
View Related
May 28, 2007
I am really stuck here,
I hope to get some helpful answers on this forum.
Ok, I have three four tables in my db,
1- Stages
2- Activities
3- Tasks
4- Subtasks
Structure is like:
Stages
1- SrNo (Unique)
2- Stage
3- StartDate
4- FinishDate
Activities
1- SrNo (Unique)
2- Activity
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Stages Table)
Tasks
1- SrNo (Unique)
2- Task
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Activities Table)
Subtasks
1- SrNo (Unique)
2- Subtasks
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Task Table)
Now what i want is to update Tasks, StartDate and FinishDate according the Min(StartDate) and Max(FinishDate) of related Subtasks and same for Activities and Stages.
I have tried following query to Update tasks, StartDate and FinishDate
Update Tasks Set Tasks.StartDate=(Select Min(Subtasks.StartDate) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1
But this query updates all Tasks with Min and Max date from Subtasks regardless of their relation.
Any help, would be appreciated.
Thanks
View 3 Replies
View Related
Feb 21, 2012
I have two tables A and b
A
projectID Setid value
301 1 abc
301 2 xyz
302 4 def
..... .... ..
B
SettingsId Setvalue
1 ter
2 yet
4 44
... ....
I want to update all the Setid of table A with that of values from table b. How can i do this?
View 7 Replies
View Related
Apr 19, 2014
I have two tables and I need to update values in them via a stored procedure. Tried too much to update but some times it update the first table only, others the second or even fail due to cannot allow duplicates. Also when it updates the WHOLE data in the table becomes the same as the new updated ones. I've now reached to this error after all these lines of codes
Cannot insert the value NULL into column 'Emp_ID',table 'DatePics'; column does not allow nulls. UPDATE fails.The statement has been terminated
Here is the SQL code :
ALTER procedure [dbo].[UpdateEmp]
@EmpName nvarchar(100),
@Nationality nvarchar(30),
@Passport nvarchar(20),
@ContractDate date,
@HealthDate date
[Code] ......
View 4 Replies
View Related
May 1, 2006
I created one stored proc, then copied it to create another similar stored proc with just some filtering changes in the second. Now I want to obtain the results from both like this:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount
---------------------------------------------------------------------------------------------------------------------------
Row# 1 from current stored proc
Row # 2 from called stored proc
so something like this I should get in the end for example when both results are combined:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount
---------------------------------------------------------------------------------------------------------------------------
100000 33455 200 300 345555 4455555 5666666 Arizona 56000
103400 22455 900 700 777555 3333555 5444666 Illinois 660000
The first stored proc (CurrentMonthCollections_AZ) attempts to include the results of the second stored proc at the end (CurrentMonthCollections_IL) by calling it and inserting the fields into a temp table.
I was told by Angel to instead convert the second stored proc into a UDF...but having trouble with this.
What is the best approach to get the desired 2 rows back that I need in that fasion?
Here is the code for the 2 stored procs (very long so I will post as as links):
http://www.webfound.net/CurrentMonthCollections_AZ.txt
http://www.webfound.net/CurrentMonthCollections_IL.txt
Look at the end of CurrentMonthCollections_AZ.txt to see where I'm stuck in trying to select the results (ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Var1 PostedAmount
) from both stored procs.
I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows
View 7 Replies
View Related
Oct 7, 2015
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
View 1 Replies
View Related
Dec 13, 2004
Folks, i have to create four fields in every user table within my database:
CREATED_BY VARCHAR(25), CREATED_DATE [DATETIME], MODIFIED_BY VARCHAR(25), MODIFIED_DATE [DATETIME]
There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!
declare @name VARCHAR (50)
declare cur cursor
fast_forward
for select name from sysobjects where type='u' and status not like '-%'
open cur
WHILE (1=1)
BEGIN
FETCH NEXT
FROM cur
INTO @name
IF @@fetch_status = 0
BEGIN
ALTER TABLE @name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD created_date [DATETIME]
GO
ALTER TABLE @name
ADD modified_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD modified_date [DATETIME]
END
ELSE
BREAK
END
DEALLOCATE cur
I also want that if one column for a table exists; the other columns should be created rather than it quits.
Howdy!
View 4 Replies
View Related
May 26, 2007
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.
Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.
I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.
I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.
How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?
Thanks in advance.
View 5 Replies
View Related
Jan 3, 2014
For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.
There is only one column extra in _dup tables i.e.,idn column.
Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.
View 6 Replies
View Related
Mar 5, 2014
I am trying to write an SQL command for my crystal report. I need to compare the same column in 3different tables & get the data from each table for only the matching data.. I understand I need to create a temporary table, get the data into it & then work around.. I am quite new to SQL.
Eg: Considering one customer account
Table 1
Cust.No Name Amt_Counter AmtPaid
123.456 sam 0 0
123.456 sam 1 50
Table 2
Cust.No Name Freq_Counter Frequency
123.456 sam 1 0
123.456 sam 2 15
[code]....
View 3 Replies
View Related
Dec 10, 2013
I am using this query to search checkbox values.
SqlCommand cmd = new SqlCommand("Select * from Details where Emp_Code in (" + selectedValues + ")", con);
I want to join a table called Materials to this now. Material table also has an Emp_Code column. How can I write a select sql query to fetch Emp_Code from both Details and Materials table.
SqlCommand cmd = new SqlCommand("select D.Emp_Code, M.Emp_Code from Details D, Materials M where D.Emp_Code = M.Emp_Code in (" + selectedValues + ")", con);
View 2 Replies
View Related
Jan 3, 2014
For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.
There is only one column extra in _dup tables i.e.,idn column.
Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.
View 5 Replies
View Related
Jul 23, 2005
I have two tables in the same SQL database. Both have a similar numericfield. One field has the same number as the other field but is prefixedwith various letters. I know how to use LIKE but won't know the partialstring I am looking for. I am trying to use LIKE '%' + Field A orsomething that will do this.Eg.Table 1 Field A is 'A12345"Table 2 Field B is '12345"I want to find every record in Table 1 Field A that contains the exactField B data.*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies
View Related
Sep 28, 2015
What I am working with unfortunately is a very poorly designed and non-normalized database. Please don't criticize the design. I didn't design it, but I have to write queries against it.I have 2 tables. 1 is called EnterVolume. The other is ExitVolume. Similar columns exist in each.
CREATE TABLE [EventPortion].[EventEnterVolume](
[SequenceNumber] [int] NULL,
[TrialID] [nvarchar](255) NULL,
[TimeOfEvent] [int] NULL,
[UniversalTime] [nvarchar](255) NULL,
[SBOINumber] [int] NULL,
[SEntityPosUpdateIndex] [int] NULL,
[VolumeIndex] [int] NULL,
[code]....
The rules of the database state that for every EnterVolume row (for a given TrialID, SBOINumber, and OwnerBOI) there must be a corresponding ExitVolume row in the ExitVolume table.What I need to do is to capture the paired TimeOfEvent entries from each table for each paired row. Nothing says that an SBOI cannot enter and exit a OwnerBOI's volume several times during the same Trial.Every time a SBOI Enters an OwnerBOI's volume during a certain trial, a row is created in the EnterVolume table. And Likewise when Exiting a OwnerBOI's volume during a certain trial, a row is created in the ExitVolume table.
So here is a query that I attempted, but gave undesirable results:
SELECT EV.TimeOfEvent AS [Enter Time], XV.TimeOfEvent AS ExitTime
FROM IntegratedTest1.EventPortion.EventEnterVolume AS EV INNER JOIN
IntegratedTest1.EventPortion.EventExitVolume AS XV ON EV.TrialID = XV.TrialID AND EV.SBOINumber = XV.SBOINumber AND EV.OwnerBOI = XV.OwnerBOI
Here is some sample data:
EnterVolume
TrialIDÂ Â Â Â Â Â Â Â Â Â Â Â SBOIÂ Â Â Â Â Â Â Â Â Â OwnerBOIÂ Â Â Â Â Â Â Â TimeOfEvent
1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â DÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 5
1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â DÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2000
[code]....
View 8 Replies
View Related
Apr 11, 2008
I have my stored procedure set to
Territory_code IN (@Territory)
, now , how do i enter in more then one value. When i select the multi value check box, it gives me more spaces. But then doesnt recognize the values when i put in more then one. am i doing something wrong?
The field is a Varchar 20
View 1 Replies
View Related
May 30, 2014
I am working with SP. How can we find out values of parameters when the SP is executed with the default values?
View 9 Replies
View Related
Jul 23, 2005
Hi,I m searching for some Script / Function ... to find difference in datab/w 2 similar tables (exactly same fields structure) in sql 2000.plz update me asap !thanks in advance !
View 2 Replies
View Related
Mar 18, 2007
A have a multi-valued parameter (B) which is dependent on a single-valued parameter (A) on my report. When a value is selected in A, I want all matching values in B to be selected by default and the "Select All" option checked. To do this I have set the Default Values section in B to point to the same dataset as the "Available Values" section. Both A and B have default values so the report runs automatically.
One of the values in parameter A (say Value1) yields more values in parameter B than the other (say Value2).
If I run the report the first time with Value1 selected as the default for parameter A, all values in B are checked correctly. If I run the report with Value2 selected the first time and then change the selected value to Value2 and run my report, all values in B are displayed but only the values that were previously checked (when Value1 was selected), are now checked, leaving the "Select All" unchecked.
What am I doing wrong? Why are all the values in B not checked? The dataset is the same in "Available Values" section and "Default Values" section.
View 8 Replies
View Related
Jul 20, 2005
I have two tables in my database called CartItems and OrderItems. Istore all of a session's shopping cart items in the CartItems tableusing the sessionID as the identifier (called cartID in my DB). Afteran order is placed and is approved, I would like to copy all of theitems in the CartItems table for that given cartID to the OrderItemstable given a new orderID.I will know the cartID and orderID ahead of time and would like tosend them both into a stored procedure and have the transfer takeplace.Example:take this data...CartItems (table)--------------------------------------cartID | itemID | quantity | price--------------------------------------12345 2 1 12.9512345 7 2 17.95and make it this data...OrderItems (table)--------------------------------------orderID | itemID | quantity | price--------------------------------------00001 2 1 12.9500001 7 2 17.95via some stored procedure that I send (@cartID,@orderID)Any help would be greatly appreciated!!
View 5 Replies
View Related
Sep 30, 2007
It seems that there should be a solution for my situation, but for the life of me I can't seem to figure it out.
I need to compare two "like" tables, containing similar data. Tbl 1 is "BOOKED" (which is a snapshot of inventory) and tbl 2 is "CURRENT" (the live - working inventory table). If I write my query as follows the the subsequent result is "duplicate" data.
Code Block
SELECT booked.item, booked.bin, booked.quantity, current.bin, current.quantity
FROM BOOKED
LEFT JOIN
CURRENT
ON booked.item = current.item
No matter what type of join I use, there is duplicate data displayed for each table. For example, if there are more bins in the BOOKED table that contain a certain product then the CURRENT table will repeat data and vica versa.
As follows:
Item
Bin
Quantity
Bin
Quantity
12345
A01
500
A01
7680
12345
B01
6
A01
7680
12345
C01
20
A01
7680
54321
G10
1032
E15
1163
54321
G10
1032
F20
523
54321
G10
1032
H30
750
98765
Z20
7000
Z20
8500
98765
Y15
2500
Y15
3000
98765
X10
1200
Y15
3000
What I would like to do is display Bin and Quantity only once and the repeating values as NULL or [BLANK]. Or, to display all of the bins from both tables and only the quantities from each table in relation to the bin found in that table, returning a "0" if no quantity exists.
This is what I'm after:
Item
Bin
Quantity
Bin
Quantity
12345
A01
500
A01
7680
12345
B01
6
B01
0
12345
C01
20
C01
0
54321
G10
1032
E15
1163
54321
F20
0
F20
523
54321
H30
0
H30
750
98765
Z20
7000
Z20
8500
98765
Y15
2500
Y15
3000
98765
X10
1200
X10
0
Is this possible? If so, how?
I also might add that it is ok for each table to contain multiple entries for any given item. This is basically being requested as an inventory variance report - inventory before physical count and immediatly after physical count - and will only be run once a year.
-----------------------------------------------
Just thinking out loud here:
What if I created three subqueries, the first containing only BOOKED information, the second containing only CURRENT information and the third being a UNION of both tables? Something like this:
Code Block
SELECT q3.bin, q1.item, ISNULL(q1.quantity, 0) as QTY_BEFORE, ISNULL(q2.quantity, 0) as QTY_AFTER
FROM
(select item, bin, quantity
from BOOKED)q1
Left Join
(select item, bin, quantity
from CURRENT)q2
on q1.item = q2.item
Left Join
(select bin, item
from BOOKED
UNION
CURRENT)q3
on q1.item = q3.item
Order By q1.item
I don't know if I wrote the UNION statement correctly, but I will have to try this when I get back to work...
Any suggestions?
View 7 Replies
View Related
Mar 5, 2008
Hi, I have a table with one XML type column. This column holds custom field information. Its used as a way of storing ad hoc fields and data that don't fit the DB design. <?xml version="1.0"?><contact><Reference>A39390TFH</Reference><Misc>all kinds of stuff go in here</Misc></contact>I want to provide a way of displaying the data stored in this column in the same DataTable as normal relational data from the same table. I have been able to achieve this goal BUT I want to know if the community had any ideas on how I could speed the process. I am using the XML value() function. It allows me to extract the data I need. SELECT Name, Number, Reference FROM
(
SELECT Name, Number, xmlvalues.value( ' (contact/Reference)[1] ', ' varchar(40) ' ) as Reference
From MyTable
WHERE Name = 'Some Dude'
AND xmlvalues is not null
) T
GROUP BY Name, Number, Reference anyone know if there are better xml functions to get this data out of the XMLColumn?????? There is no schema, because each xml fragment has different tags and different values. Regards Niall
View 1 Replies
View Related
Feb 11, 2008
Using SQL2K.
I'm extracting columns from a CSV file.
Once column, in the CSV file, has a format of general and is only populated by numbers (0 - 126); no decimal places.
During the extract, the column is copied into my staging table as an NVARCHAR value. However, after the extract, the values have all been appended with a ".0". What could be causing this?
Thanks!
View 11 Replies
View Related
Apr 16, 2008
Hi!
I am creating a scheduling web application. I have managed to insert data into the database. The code is as follow:
Dim insertSQLDatasource As New SqlDataSource()
insertSQLDatasource.ConnectionString = ConfigurationManager.ConnectionStrings("ScheduleConnectionString").ToString
insertSQLDatasource.InsertCommandType = SqlDataSourceCommandType.Text
insertSQLDatasource.InsertCommand = "INSERT INTO Demo_Theatre_DB (StartTime, EndTime, CompanyName, Purpose, AccountManager, Presenter, ColorCode, Status, Comments) VALUES (@StartTime, @EndTime, @CompanyName, @Purpose, @AccountManager, @Presenter, @ColorCode, @Status, @Comments)"
insertSQLDatasource.InsertParameters.Add("StartTime", StartTimeTextBox.Text)
insertSQLDatasource.InsertParameters.Add("EndTime", EndTimeTextBox.Text)
insertSQLDatasource.InsertParameters.Add("CompanyName", CompanyNameTextBox.Text)
insertSQLDatasource.InsertParameters.Add("Purpose", PurposeTextBox.Text)
insertSQLDatasource.InsertParameters.Add("AccountManager", AccountManagerTextBox.Text)
insertSQLDatasource.InsertParameters.Add("Presenter", PresenterTextBox.Text)
insertSQLDatasource.InsertParameters.Add("ColorCode", ColorDDL.SelectedValue.ToString)
insertSQLDatasource.InsertParameters.Add("Status", StatusRadioButtonList.SelectedValue)
insertSQLDatasource.InsertParameters.Add("Comments", CommentTextBox.Text)
Try
insertSQLDatasource.Insert()
Catch ex As Exception
Panel1.Visible = True
InsertMsgLabel.Text = ex.Message.ToString
Finally
insertSQLDatasource = Nothing
End Try
Now I am trying to select the values for a particular event. For example, if the user selects an event with id 40, the values that corresponds to that row will be read and filled into the respective controls. I tried the following code, but doesn't work.
Dim sqlConn As SqlConnection = New SqlConnection("ScheduleConnectionString")
Dim com As SqlCommand = New SqlCommand("SELECT * FROM Demo_Theatre_DB WHERE ID=@ID", sqlConn)
sqlConn.Open()
Dim r As SqlDataReader = com.ExecuteReader()
While r.Read()
StartTimeTextBox.Text = r("StartTime")
EndTimeTextBox.Text = r("EndTime")
CompanyNameTextBox.Text = r("CompanyName")
PurposeTextBox.Text = r("Purpose")
AccountManagerTextBox.Text = r("AccountManager")
PresenterTextBox.Text = r("Presenter")
ColorDDL.SelectedValue = r("ColorCode")
StatusRadioButtonList.SelectedValue = r("Status")
CommentTextBox.Text = r("Comments")
End While
I hope someone can help me with this.
Your help will be appreciated.
Thanks!
View 8 Replies
View Related
May 13, 2015
I've the following data:
<Hour>11</Hour><Hour>12</Hour><Hour>15</Hour><Hour>18</Hour>
And my goals is to the extract the following example:
11,12, 15, 18
How Can I do That?
View 6 Replies
View Related
Jul 20, 2005
From the table i want everything highlighted with a *I wanted an SQl expression to look at values in Column 1 (ID), look atthe corresponding values in the second column (F1) and select the rowwith the highest value, and then if there was more than one row forthat ID with the same value in F1, look at column 3 (F2) and selectthe row with the highest value in this column.SQL> select * from test;ID F1 F2---------- ---------- ----------1 12 4* 1 12 61 11 11 9 122 3 5* 2 9 132 9 93 1 23 1 1* 3 7 5I try the followingSQL> select * from test t12 where f1 = (select max(f1) from test t2 where t2.id = t1.id)3 ;And get the followingID F1 F2---------- ---------- ----------1 12 4* 1 12 6* 2 9 132 9 9* 3 7 5If I add another line with an AND statement after line 2 I either getnothing or rubbish as the output.Ideas?Cheers
View 2 Replies
View Related
May 29, 2015
I have a report which is redirecting to a subreport. The main report is having multi value parameter. I need to pass these  multi values to sub report. Passing parameters from MDX report to T-sql report. So, I'm using the below exp.
=SPLIT(REPLACE(TRIM(Join(Parameters!Grade.Label,",")),",  ",","),",")
The value will look like this
01-Manger
02-Senior Mange
21-Associate
25-Associate Trainee
This is working for me in all the cases except one. In all other cases, the parameter's Label and Value field has same data in the sub report. But, in a specific parameter I'm getting Label and Value data are different. I'm getting an alpha numeric string value from MDX report , but I need to pass only the numeric values to the sub report since its value field contains only numeric value. The numeric value is coming at the starting of the string data. So I have used Mid()
=SPLIT(Mid(REPLACE(TRIM(Join(Parameters!Grade.Label,",")),", Â
",","),1,2),",")
Result will be  01
But, mid() will give only the first value. It is working for single value. But I need to extract multiple values.
View 5 Replies
View Related
May 15, 2006
Hi,
I have a task that requires me to pull a list of tables used in a select query and put them into a string array. For arguments sake, let's say this is my query:
select *
from table1 tb1, table2 tb2
where ...
I can easily extract the 'from 'clause, in this case: table1 tb1,table2 tb2, but I still have to split out the Alias before I can use it. Can anyone show me a regex that can do this instead?
Thanks,
--Yonah
View 2 Replies
View Related