Can we retrieve data from table type variable as an array in oracle key values..
E.g.
ALTER PROCEDURE prn1( @p_prn as KeyValuePair readonly)
-- proc which having parameter table type as parameter
AS
declare @v_formatted_str varchar(50)
BEGIN
set @v_formatted_str = v_formatted_str + @p_prn(1)(1)
END
Hi Forum,I have a split function.CREATE FUNCTION dbo.Split2(@List nvarchar(2000),@SplitOn nvarchar(5)) RETURNS @RtnValue table (Id int identity(1,1),Value nvarchar(100)) AS BEGINWhile (Charindex(@SplitOn,@List)>0)Begin Insert Into @RtnValue (value)Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))End Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List)) ReturnEND Courtesy p2p.wrox.com It returns a TableHow to take the return into a temporary table?I want it inside an SP.Because i have a varchar field named occasionTime (varchar) in a tablea sample data inside it is "January,21,EveryWeek"In my SP i have to get either "January" and compare it to something or "21" or "EveryWeek" for comparison.How to do it?Regards,Naveen
View 1 Replies View RelatedI am using C# in  Visual Studio 2008 and remote database as sql server 2008 R2. I want to read remote database table's field value and i have to move that read value to string variable. how to do it.Â
And my code is :
string sql = "Select fldinput from tmessage_temp where fldTo=IDENT_CURRENT('tmessage_temp')";
SqlCommand exesql = new SqlCommand(sql, cn);
exesql.CommandType = CommandType.Text;
SqlDataReader rd1 = default(SqlDataReader);
rd1 = exesql.ExecuteReader();
Does abyone know how to compare data-type xml in a temp/variable/physical table in MSSQL 2000?
I tried this works in MSSQL 2005,
Code Snippet
create Table #t1 ([c1] int identity(1,1) not null, [c2] text)
create Table #t2 ([c1] int identity(1,1) not null, [c2] text)
Insert into #t1
Values('This is a test')
Insert into #t2
Values('This is a test')
Select * from #t1
Select * from #t2
Select * from #t1 where [c2] LIKE (Select [c2] from #t2)
drop table #t1
drop table #t2
but not MSSQL 2000.
Server: Msg 279, Level 16, State 3, Line 12
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
Is this true (from BOL)?
Code SnippetIn comparing these column values, if any of the columns to be compared are of type text, ntext, or image, FOR XML assumes that values are different (although they may be the same because Microsoft® SQL Server„¢ 2000 does not support comparing large objects); and elements are added to the result for each row selected.
Hi all,
I am trying to extract certain data from MySQL
(example sql statement is SELECT COLUMN1, COUNT(COLUMN2) FROM TABLE
GROUP BY COLUMN1) and stored COLUMN1 and COUNT(COLUMN2) values into
variables and then copy the values from the variables and insert them
into a new table in sql server 2005 and has two columns in it (COLUMN1
of type nvarchar and COLUMN2 of type int). I can map the first output
(COLUMN1) correctly to variable type String and store them in the new
table in sql server 2005 (store the value in a variable type String and
then use insert and store it into sql server using parameter in Execute
Sql Task set the data type to NVARCHAR, but I cannot map the second
output (COUNT(COLUMN2)) using the same method, I could not even get it
to store into a variable of any type. Anyone have any idea how to go
about it? Thanks in advance.
The output of the sql statement for MySQL should look like:
COLUMN1 COUNT(COLUMN2)
----------------------------------------
aaa 101
bbb 232
ccc 542
Daren
I€™ve got some tables with the year is part of the name, for example: TABLE2006, TABLE2007, etc.. . The year of the name of table I will read in the table INSERTED of my Trigger : I nead to create a trigger where I update those tables :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [TESTE]
ON [dbo].[TABTESTE]
FOR INSERT
AS
DECLARE
@YearTable nvarchar(4),
@IdClient INT,
@MyTable TABLE
(
IdClient INT,
Situ NVARCHAR(50)
)
BEGIN
SET NOCOUNT ON;
SELECT @YearTable = SITUACAO, @IdClient = IdClient FROM INSERTED
SET @MyTable = 'TABLE' & @YearTable
UPDATE @MyTable
SET
Situ = 'X'
WHERE IdClient = @IdClient
END
GO
Erros:
Msg 156, Level 15, State 1, Procedure TESTE, Line 9
Incorrect syntax near the keyword 'TABLE'.
Msg 137, Level 15, State 1, Procedure TESTE, Line 17
Must declare the scalar variable "@MyTable".
Msg 1087, Level 15, State 2, Procedure TESTE, Line 18
Must declare the table variable "@MyTable".
how to pass the numeric(12,0) data type to a variable in SSIS? what kind of variable data type should I choose?
I am trying to assign object_key column ( numeric(12,0)) to a variable in SSIS
If i select int32 , it keep giving me an error:
Error: 0xC001F009 at Row by Row process: The type of the value being assigned to variable "User::Object_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Is there anyway to change the data type of a variable while in an expression?
My problem is I am trying to compare a variable w/ a string data type to a variable w/ an object data type. I would change the data type of the variable from object to string but if I do that my sql task fails when it tries to write a value to that variable. The variable w/ the object data type is the result of an openquery sql stmnt. So I guess there are two ways around my problem.
1. Change data type of variable while in an expression..ie flow constriant
or
2. Change data type of vraiable from object to string and still get the openquery result to work.
Any help???
Thanks !!
HI Experts,
I have same table structures in two database and one master table which contains Table id, Table name,primary key, data type of primary key. i have to comapare
Tables in both tha database and as per result i have to do insert,update or delete.
for that i have written query :
DECLARE @rowcount_mastertable FLOAT
SET @rowcount_mastertable = (select count(*) from master_table)
DECLARE @TABLE_ID float,
@TABLE_NAME varchar (100),
@primary_key varchar (100),
@Primarykey_DATATYPE varchar (50),
DECLARE @COUNTER FLOAT
SET @COUNTER = 1
WHILE (@Counter <= @rowcount_mastertable)
Begin
SET @TABLE_NAME = (SELECT TABLE_NAME FROM MASTER_TABLE TABLE_ID = @COUNTER)
SET @primary_key = (SELECT primary_key FROM MASTER_TABLE WHERE TABLE_ID = @COUNTER)
SET @Primarykey_DATATYPE = (SELECT Primarykey_DATATYPE FROM MASTER_TABL WHERE TABLE_ID = @COUNTER)
--In below line i want to declare a variable and datatype should be same as what we got from master table so that i can use this @MAX_primary_key to fetch --max of primary key from table name where table id is 1
DECLARE @MAX_primary_key @Primarykey_DATATYPE
SELECT @MAX_primary_key = MAX(@primary_key) FROM @TABLE_NAME
WHERE TABLE_ID = @COUNTER
---But by running it i am getting error that "Incorrect syntax near '@Primarykey_DATATYPE'. and "Must declare the variable '@MAX_primary_key'.
Please suggest
Thanks in Advance
Greetings my SSIS friends
I am attempting to create an expression as follows:
"Select * from someTable where someColumn >= " + (dt_str, 25, 1252) @[User::DateTimeVariable]
The problem is that my variable is a Datetime field and when I convert it to string, the string will not execute correctly.
How to solve this problem?
In a stored proc, can you declare a local variable that is an existing column in a table & then based on other criteria, do an order by using the local variable?
View 1 Replies View RelatedI am trying to find what datatype I can use for variable values like below in a column
E.g. column which we get
10000.10
100
180.34
98203710231.34
From the above example, you can see some of the values contains no decimal and with decimal
Also we cant say whether the decimal comes after the 5th number or 10th number. Any other datatype to capture this values. If not last option is to give varchar2.
how to pass the column that has a numeric(12,0) data type to user variable in SSIS? what kind of variable data type should I choose?
if i select int64, it keep giving me an error:
Error: 0xC001F009 at Row by Row process: The type of the value being assigned to variable "User:bject_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
there is no numeric data type in variable..
if you click the drop down box in variable data type, you can only see the below data type:
Int32
int64
Object
Sbyte
single
string
uint32
uint64
boolean
byte
char
Hello Everyone,A have a Managed Stored Procedure ([Microsoft.SqlServer.SqlProcedure]). In it I would like to call a UserDefinedFunction:public static SqlInt32 IsGetSqlInt32Null(SqlDataReader dr, Int32 index) { if(dr.GetSqlValue(index) == null) return SqlInt32.Null; else return dr.GetSqlInt32(index) }I than allways get the following ErrorMessage:Column, parameter, or variable #1: Cannot find data type SqlDatareader.Is it not possibel to pass the SqlDatareader to a SqlFunction, do the reading there and return the result.My original Problem is, that datareader.GetSqlInt32(3) throws an error in case there is Null in the DB. I thought SqlInt32 would allow Null.Would appreciate any kind of help! Thanks
View 1 Replies View RelatedCannot use dynamic sql in current context. So need some help regarding this.I am developing a stored procedure to update a table. Sending Column names as parameters, but not able to use them as given below.INSERT INTO Books (@Column1, @Column2) values.. Any way to execute without using dynamic sql?..Thanx.
View 1 Replies View RelatedI am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
Hi all,
I am creating a customer data flow component for SSIS for use in a package. I've got some custom properties that I am exposing using the supplied advanced editor (no custom property editor here).
Some of my properties are enumerated types, and I have deciphered how to get those properties to show as dropdown lists of their respective enumerations. (For those of you who may be looking as hard as I did as to how to accomplish this, see the end of this post.)
I also have a few properties which request SSIS package variable names - such as an file name variable. However, I can't figure out how to tell the advanced editor that the property is looking for an SSIS variable, so that it can show a dropdown list of package variables, much like virtually any other Microsoft supplied Data Flow component can.
Is there a Type Converter I could specify for those custom properties? Is there another way to instruct SSIS that my custom property is expecting a variable? Or do I need to code a custom UI for editing my Data Flow Task?
To create a dropdown list of values for a custom property that represents an enum, do the following:
1. Create your enum definition, such as "public enum ThisIsMyEnum { one, two }"
2. Create a new class that inherits from TypeConverter, such as "public class MyEnumConverter : TypeConverter"
3. Override "CanConvertFrom", and return true if "sourceType == typeof(string)"
4. Override "CanConvertTo", and return true if "destinationType == typeof(string)"
5. Override "ConvertFrom", and return the enum value (such as "one" or "two" in my example) that corresponds to the string passed in the parameter "value"
6. Override "ConvertTo", and return a string that corresponds to the enum value passed in the parameter "value"
7. Override "GetStandardValuesSupported" and return true
8. Override "GetStandarValuesExclusive" and return true to indicate that ONLY the enum values should be accepted
9. Override "GetStandardValues", and return a new StandardValuesCollection constructed with Enum.GetValues() of your enum, such as "return new StandardValuesCollection(Enum.GetValues(typeof(ThisIsMyEnum)));"
10. Just above your "public enum" declaration, add a "TypeConverter" attribute to link your type converter to your enum, such as "[TypeConverter(typeof(MyEnumConverter))]"
11. In "ProvideComponentProperties", after you've created your custom property like this: "IDTSCustomProperty90 propEnum = ComponentMetaData.CustomPropertyCollection.New()", add another line to specify the TypeConverter property of the property to the full assembly name of your type converter, like so: "propEnum.TypeConverter = typeof(MyEnumConverter).AssemblyQualifiedName;"
Hi, everione,
I am having a big problem, trying to create datebase.
I have 3 tables: SUPERAVATAR, MASTERAVATAR, MEGAAVATAR.
- SuperAvatars are heroes in an online role playing gaming. They have an ID, ‘superavatarID’ which contain an UNIQUE NUMBER NOT NULL PRIMARY KEY to identify them.
A wisdom – ‘trickster’,’conjuror’,’magician’, etc..
A current owner… who is the user or player of the game and has that Super Avatar– we associate the link to USERID to know the person.
SuperAvatars can be fathers or mothers of Mega Avatars.
-MegaAvatars are the children of SuperAvatars…. They also have an ID UNIQUE NUMBER NOT NULL PRIMARY KEY to indentify them.
A magic power – it can be a ‘Leader’ or ‘Sheep’…
A ‘parent’ – parent is the number identifying to know to who Super Avatar belongs.
e.g.
SUPERAVAT WISD CURRENTOWNER FATHEROF MOTHEROF
1 Thick 3 1
2 Mentally Ch. 11 3
3 Smart 9 2
4 Genius 16 4
5 Thick 19
MEGAAVATARID MAGICPOWER PARENT
1 MAGICIAN 1
2 WIZARD 3
3 SORCERER 2
4 MAGICIAN 4
-We see that MEGAAVATAR 1 has a magic power as Magician and his father is ‘1’. ‘1’ identifies the SUPERAVATAR.
We see SUPERAVATARID…. who has the number ‘1’? the first in the row… who has wisdom – thick and he belongs to the USER with ID number 3.
-We see MEGAAVATARID… we choose the number 2…. His magic power is as WIZARD… and his father is the number 3.
We see SUPERAVATARID now… we look up the SuperavatarID 3…. We can see he has a wisdom – GENIUS… who belongs to the USERID 16 and he is father of MEGAAVATAR number 2.
The list can carry on and never stop.
I have this Problems:
We create in this example 3 tables: Users, SuperAvatar, MegaAvatar
SQL
CREATE TABLE users(userID NUMBER CONSTRAINT pk_user PRIMARY KEY,email VARCHAR2(50) NOT NULL UNIQUE,password VARCHAR2(15) NOT NULL UNIQUE,subscription CHAR(8) NOT NULL CHECK (subscription IN('ACTIVE' , 'INACTIVE' ) ) );
CREATE TABLE superavatar(superavatarID NUMBER CONSTRAINT pk_superavatar PRIMARY KEY, wisdom VARCHAR2(19) NOT NULL CHECK (wisdom IN ('THICK', 'MENTALLY CHALLENGED', 'AWAKE', 'SMART', 'GENIUS')), currentOwner NUMBER NOT NULL, fatherOf NUMBER,motherOf NUMBER);
CREATE TABLE megaavatar (megaavatarID NUMBER CONSTRAINT pk_megaavatar PRIMARY KEY, magicPower VARCHAR2(12) NOT NULL CHECK (magicPower IN('TRICKSTER','CONJUROR','MAGICIAN','WIZARD','SORCERER')), parent NUMBER);
Now, the 3 tables are created…..
What happen when we try to insert values to this table?
In this case we insert to User Table some examples:
INSERT INTO users VALUES('3','john@hotmail.com','great78','ACTIVE');
INSERT INTO users VALUES('9','chrisandsandra@gmail.com','chrisandra)','ACTIVE');
Now, we insert to SuperAvatar some examples;
INSERT INTO superavatar VALUES('1','THICK','3','1','');
INSERT INTO superavatar VALUES('3','SMART','9','3','');
|
|
‘9’ is the UserID that we already insert to the User table
What’s the problem?
We have to insert manually the data from USERID to CURRENTOWNER as we didn’t match or link CURRENTOWNER from SUPERAVATAR Table to USERID from USER Table with a SQL CODE.
What happen if we have thousands of USERS that they register to this game…? We will never know to how belongs that SUPERAVATAR but if someone do it manually can spend a year.
I am trying to fix this problem …. I add in SUPERAVATAR TABLE ‘CHECK’ in currentOwner..
SQL> CREATE TABLE superavatar
(superavatarID NUMBER CONSTRAINT pk_superavatar PRIMARY KEY, wisdom VARCHAR2(19) NOT NULL CHECK (wisdom IN ('THICK', 'MENTALLY CHALLENGED', 'AWAKE', 'SMART', 'GENIUS')),
currentOwner NUMBER NOT NULL CHECK (currentOwner IN(SELECT userID FROM users)),
fatherOf NUMBER,
motherOf NUMBER);
*
ERROR:
ORA-02251: subquery not allowed here
It doesn’t work.
Please HELP, I have exam tomorrow
thank you
Desy
Hi,
Can any one help me to retrieve an image data from a table.
I wanted to show the images on my web page using ASP.
I am trying with pub_info table of pubs database, in this table logo is a image data type colum. I opened a record set in ASP page and I am not able to retrieve the image from the record set.
Any help is appreciated!
Ravi.
Hi there, here is my current query:
sqlTemp = "SELECT id,code,description FROM tbl_content " &_
"WHERE EXISTS " &_
"(SELECT * FROM tbl_published_content " &_
"WHERE tbl_content.id = tbl_published_content.id ORDER BY tbl_published_content.nCorrectOrder)"
I don't get results sorted by tbl_published_content.nCorrectOrder, and have also no iea how to retrieve that values.
Please can any one tell me how can I have access to tbl_published_content.nCorrectOrder and get it sorted by this field?
So I can use objRS.("tbl_published_content.nCorrectOrder") or some alias?
thanks a million!
What would be the most efficient method of constructing a sql statement to retrieve certain record into (this gets tricky) rows of a table with 3 columns date.desc.
<table>
@foreach(var row in maytable){
<tr>
<td>@row.Most_Recent_Record1</td>
<td>@row.Most_Recent_Record2</</td>
<td>@row.Most_Recent_Record3</</td>
</tr>
}
</table>
I have 4 tables I am pulling data from. Table A and B can be joined and Table C and D can be joined via foreign keys but A and/or B can not be Joined to C and/or D via foreign Key.
My statement basically looks like this to start
SELECT s.HTENANT, s.HPROP, s.HUNIT, s.SHEADNAME3B, s.DTOT7I
FROM H8summ S join tenant t on s.htenant = t.hmyperson
And returns this :
29226 128 20577 Almukhtar, Khadijah M 4242.00
26574 128 17980 Archie, Mary 20218.00
The last number is the key it is the income.
In another table we have an income limit table setup that would have the low, medium and high settings for the range.
For instance it may have 11400 as the elimit attribute and say 22000 as the vlimit attribute.
Then in the fourth table we the description that can be linked back to the income limit table via the hinclimit attribute. In this case the desc attribute would be ELI for elimit and VLI for vlimit.
So I want to take the income compare it to the income limit table and then return the description
The output would look like this :
29226 128 20577 Almukhtar, Khadijah M 4242.00 eli
26574 128 17980 Archie, Mary 20218.00 vli
I just not sure how to reference the income limit table without the foreign key in the from statement. When I just add it it returns 6 rows for each person (number of rows on the income limit table)
View 1 Replies View Related
Hi,
I am working on inserting information into a DB and then retrieving the ID created for that Data to use elsewhere in my code. I have the code below but I do not know how to get toOutput parameter. Can anyone please help?
CREATE PROCEDURE dbo.InsertProduction
@DATEOUT datetime(8),
@DATEREQUIRED datetime(8),
@PREPAREDBY varchar,
@COMMENTID INteger,
@TOTALQUANTITY INteger,
@VENDORID INteger,
@WPO varchar,
@TCAPONUMBER INteger,
@APPROVEDBY varchar,
@Identity int OUT
AS
INSERT INTO PRODUCTION (DATEOUT,DATEREQUIRED, PREPAREDBY, COMMENTID, TOTALQUANTITY, VENDORID, WPO, TCAPONUMBER, APPROVEDBY) VALUES( @DATEOUT, @DATEREQUIRED, @PREPAREDBY, @COMMENTID, @TOTALQUANTITY, @VENDORID, @WPO, @TCAPONUMBER, @APPROVEDBY)
SET @Identity = SCOPE_IDENTITY()
'collect all the information from the form and then apply all and then update
'Get a reference to the Production table.
Dim dtProduction As DataTable = DS.Tables("Production")
Dim dtLineItem As DataTable = DS.Tables("LineItems")
' Create the SqlCommand to execute the stored procedure.
Production.InsertCommand = New SqlCommand("dbo.InsertProduction", connection)
Production.InsertCommand.CommandType = CommandType.StoredProcedure
' Add the parameter for the CategoryName. Specifying the
' ParameterDirection for an input parameter is not required.
'Production.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")
Production.InsertCommand.Parameters.Add("@DATEOUT", SqlDbType.DateTime, 8, "CategoryName")
Production.InsertCommand.Parameters.Add("@DATEREQUIRED", SqlDbType.DateTime, 8, "CategoryName")
Production.InsertCommand.Parameters.Add("@PREPAREDBY", SqlDbType.VarChar, 50, "CategoryName")
Production.InsertCommand.Parameters.Add("@COMMENTID", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@TOTALQUANTITY", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@VENDORID", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@WPO", SqlDbType.VarChar, 50, "CategoryName")
Production.InsertCommand.Parameters.Add("@TCAPONUMBER", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@APPROVEDBY", SqlDbType.VarChar, 50, "CategoryName")
' Add the SqlParameter to retrieve the new identity value.
' Specify the ParameterDirection as Output.
Dim parameter As SqlParameter = Production.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "ProductionID")
parameter.Direction = ParameterDirection.Output
' Create a new row with the same schema.
Dim dr As DataRow = dtProduction.NewRow()
'you need the ID from this to insert into the Production DB
' Set the value of all the columns.
dr("DateOut") = CDate(DateTimePicker1.Text)
dr("DateRequired") = CDate(DateTimePicker2.Text)
dr("VendorID") = CInt(vendorbox.SelectedValue)
dr("HomeAddress") = txtApproved.Text.ToString
dr("ApprovedBy") = txtPrepared.Text.ToString
dr("TCAPO") = CInt(txtTCAPO.Text.Trim)
dr("CommentID") = CommentID
dr("TotalCost") = CDec(txtTotals.Text)
dr("TotalQuantity") = CInt(txtQtyTotal.Text)
' Add to the Rows collection or table .
dtProduction.Rows.Add(dr)
'Update the Production Table and then retrieve the ID created in this case
Production.Update(dtProduction)
Dollarjunkie
Hi all,
I have 2 Tables "dbo.Samples" and "dbo.TestResults" listed below:
dbo.Samples:
SampleID SampleName Matrix SampleType ChemGroup ProjectID
1
Blueriver01
Water
Primary
VOCs
1
2
Blueriver02
Water
Duplicate
VOCs
1
3
Blueriver03
Water
QA
VOCs
2
4
Blueriver11
Soil
Primary
VOCs
1
5
Blueriver12
Soil
Duplicate
VOCs
1
6
Blueriver13
Soil
QA
VOCs
3
where SampleID is Primary key in this table.
dbo.TestResults:
AnalyteID AnalyteName Result Unit SampleID
1
Acetone
120.800
ug/L
1
2
Benzene
25.600
ug/L
1
3
Trichloroethene
13.000
ug/L
1
4
Xylenes
0.000
ug/L
1
5
Acetone
90.700
ug/L
2
6
Benzene
31.400
ug/L
2
7
Trichloroethene
19.200
ug/L
2
8
Xylenes
2.000
ug/L
2
9
Acetone
140.300
ug/L
3
10
Benzene
21.500
ug/L
3
11
Trichloroethene
22.200
ug/L
3
12
Xylenes
0.000
ug/L
3
13
Acetone
222.100
ug/Kg
4
14
Benzene
10.300
ug/Kg
4
15
Trichloroethene
30.200
ug/Kg
4
16
Xylenes
50.700
ug/Kg
4
17
Acetone
211.900
ug/Kg
5
18
Benzene
16.400
ug/Kg
5
19
Trichloroethene
34.700
ug/Kg
5
20
Xylenes
60.000
ug/Kg
5
21
Acetone
220.300
ug/Kg
6
22
Benzene
13.200
ug/Kg
6
23
Trichloroethene
32.000
ug/Kg
6
24
Xylenes
55.500
ug/Kg
6
NULL
NULL
NULL
NULL
NULL
where AnalyteID is Primary key and SampleID is Foreign key in this table.
Note: The relationship between "dbo.Samples" and "dbo.TestResults" has been established.
============================================
I want to get a new T-SQL output "dbo.Report" like:
SampleID SampleName Unit Acetone Benzene Trichloroethene Xylenes
1 Blueriver01 ug/L 120.800 25.600 13.000 0.000
2 Blueriver02 ug/L 90.700 31.400 19.200 2.000
3 Blueriver03 ug/L 140.300 21.500 22.200 0.000
4 Blueriver11 ug/Kg 222.100 10.300 30.200 50.700
5 Blueriver12 ug/Kg 211.900 16.400 32.000 60.000
6 Blueriver13 ug/Kg 220.300 13.200 32.000 55.500
I want to get the first 2 columnns from the "dbo.Samples" table and the 3rd, 4th, 5th, 6th, and 7th columnns from the "dbo.TestResults" table. I think I can create a new "dbo.Report" table with the 7 Columnn Names, and retrieve the data of SampleID and SampleName from the "dbo.Samples" table and put them into the "dbo.Report" table. But I do not know how to retrieve the data of Unit, Acetone, Benzene, Trichloroethene, and Xylenes from the "dbo.TestResults" table and put them into the "dob.Report" table formatted and presented in the above blue-colored table. The following is my T-SQL programming plan:
-- Create a new table called "Report"
CREATE TABLE Report
(
SampleID int,
SampleName nvarchar(25),
Unit nvarchar(25),
Acetone decimal(9,3),
Benzene decimal(9,3),
Trichloroethene(9,3),
Xylenes decimal(9,3),
);
-- Insert the data of SampleID and SampleName from dbo.Samples --
SELECT SampleID, SampleName
FROM dbo.Samples
-- Insert the data of Unit, Acetone, Benzene, Trichloroethene, and Xylenes from dbo.TestResults --
I guess I may be able to use the following code:
INSERT INTO dbo.Reort(Unit, Acetone, Benzene, Trichloroethene, Xylenes)
SELECT x????
FROM y????
WHERE z????
(SELECT xxx??
FROM yyy??
WHILE zzz='##??")
GO
I do not know how I can get and use the above planned "subquery" correctly to get the data I want!!?? Please help and give me a delaied instructions or direction to achieve this task.
Thanks in advance,
Scott Chang
I am trying to populate some controls on a web page with values retrieved from a sql server database recordset. The text type controls work fine. However I have a Check Box on my form. I get a runtime error when I try to write into it. So tried wrting the value into a text control and was suprised to see the value retrieved was "S00817". Heres the relevant line of code
Message.Innerhtml=MyDataset.tables(0).Rows(0)(12)
I would expect this to come up with a 1 or a 0. I get S00817 if the database record holds a 1 or a 0. So what is going on here?
I want retrieve data from properties field in prifile table in aspnetdb.mdf for multi users,what do i ?
View 1 Replies View RelatedI have an SSIS package that creates a csv file based on a execute sql task.
The sql is incredibly simple select singlecolumn from table.
In the properties of the execute sql task I specify the result set as "full result set" when I run it I get the error that: Error:
The type of the value being assigned to variable "User::CSVoutput" differs from the current variable type.
Variables may not change type during execution. Variable types are strict, except for variables of type Object.
If I change the resultset to single row then I only get the first row from the DB (same if I choose none), If I choose XML then I get the error that the result is not xml. What resultset type do I need to choose in order to get all the rows into the CSV? The variable I am populating is of type string and is User::CSVoutput
I'm moving data between identical tables and have to use a flat file as an intermediary. I thought: "No problem, SSIS can do a quick export to a file, then move the file to another server, then use SSIS to import the data to the new server."
Seems simple, right?
I'm hitting all sorts of surprising data conversion errors. I used the export wizard to create the export package. This works fine. However using the same flat file definition, the import package fails -- even when I have no destination. That is I have just one data flow task that contains only one control: the Flat File source. When I run the package the flat file definition fails with data type conversion and truncation errors. One of the obvious errors is for boolean types. The SQL field is a bit, SSIS defined the column as DT_BOOL, the output of the data are literal text values "TRUE" and "FALSE". So SSIS converts a sql datatype of bit to "TRUE" and "FALSE" on export, but can't make the reverse conversion on import?
Does anyone else find this surprising? I would expect that what SSIS exports, it can import given all the same table and flat file definitions. Is SSIS the wrong tool to do such simple bulk copies? I'd like to avoid using BCP because this process will need to run automatically within SQL Agent so we can leverage all the error tracking and system monitoring.
I'm trying to output the number of rows that were effected by my stored proc.
Here is the stored proc:
ALTER Proc Update_IndividualMoves_GTPhone_NCOAPhone_Differ
AS
Update Results
SET Results.home_phone = Results.NEWPhone,
Results.Address1 = Results.NCOAADDRESS1,
Results.CITY = Results.NCOACITY,
Results.ST = Results.NCOAST,
Results.ZIP_OUT = Results.NCOAZIP5,
Results.ZIP4_OUT = Results.NCOAZ4
Where AddressServiceStatus = 'I' AND home_phone IS NOT NULL AND NEWPhone IS NOT NULL AND home_phone <> NEWPhone
Return @@Rowcount
Here is the code from the DAL class that I'm calling the stored procedure from (I'm using the SQL Helper Class.)
Public Shared Function GetAddressIncorrect_HH_GTPhone_NCOAPhone_Differ()
Dim Rowcount As Integer
Dim GlobalConnString As String = AppSettings("ConnectionString")
''Put proc in that gets this data out for household moves that have both
''GTPro and NCOA update phone numbers however they differ. Does not apply
''to DRC donors / < 12 month donors. Update to latest and greatest phone number
''from NCOA listing.
Try
Return ExecuteDataset(GlobalConnString, CommandType.StoredProcedure, "Update_HouseholdMoves_GTPhone_NCOAPhone_Differ", New SqlParameter("@@Rowcount", Rowcount))
Catch ex As Exception
Throw New ApplicationException("An error occured when calling this stored proc out Update_HouseholdMoves_GTPhone_NCOAPhone_Differ")
End Try
End Function
I want to post how many rows were effected in a label that is located on my aspx page through referencing the function above:
What I'm doing is activating the function through an asp:button control and then I want to display the @@Rowcount result in the label next to it.
Here is what I have now:
<code>
Private Sub cmdHouseholdMove2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdHouseholdMove2.Click
'Dim rowcount As Integer
GetAddressIncorrect_HH_GTPhone_NCOAPhone_Differ(New SqlParameter("@@rowcount", lblHouseholdMoves2.Text))
End Sub
If anyone knows how to do this please let me know:
Thanks in advance everyone.
Regards,
RB
I am working on a program in VB 2005 in which i want to store and retrieve GPS coordinates. I am not sure which data type is the best to use to enter Latitude & Longitude numbers and maintain their proper integrity.
Like LAT ( N38 28.025' ) and LONG (W105 52.098' )
The numbers will be entered by the user and that format can be maintained, but how to re-enter & or insert them into the database using the same format is my real question.
I hope I have explained this right. The numbers in BOLD are what I need to maintain.
Thanks for any help in advance.
Steve
Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Relatedfor example
CREATE TABLE Table1 (
Email NVARCHAR(256)
)
CREATE TABLE Table2 (
Email <------
)
How can I use Table1's Email column's data type as for Email col in Table2. I remember it's possible in Oracle
Thank you