In this query
SELECT E1.id, E2.id
FROM productHeadings AS B1, productHeadings AS E1,
productHeadings AS E2
WHERE B1.id = E1.parent_id
AND E1.id = E2.parent_id
and B1.id = 1
the result is 2 id columns... is there a way to return all id in 1 column?!
How do I create a select query which returns multiple columns from one actual DB column?
DB structure
ID (int), photo (nvarchar(50)), name (nvarchar(50))
Sample data
1, 'photo1.jpg', 'john smith'
2, 'photo2.jpg', 'jane doe'
3, 'photo3.jpg', 'bob brown'
4, 'photo4.jpg', 'mary brown'
5, 'photo5.jpg', 'sue smith'
6, 'photo6.jpg', 'bob rogers'
...
Required output
pic_col_1, name_col_1, pic_col_2, name_col_2, pic_col_3, name_col_3
photo1.jpg, john smith, photo2.jpg, jane doe, photo3.jpg, bob brown
photo4.jpg, mary brown, photo5.jpg, sue smith, photo6.jpg, bob rogers
Normally, I would just query the data and have the client data loop over the dataset to create the required output, however in this application it is not an option...
Thanks,
Steve
I have the following 2 Sql queries. They both are rowcounts of the same column but based on different criteria. What I want to do is return the two results side by side in separate columns:
-- Subscriptions since Sept. 24th
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate is not null
and c.clientunsubscribe = 0
and c.validemailaddr = 1
and s.unsubscribe = 0
-- Subscriptions in February
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate BETWEEN '2006-02-01 00:00:00.000' AND '2006-03-01 00:00:00.000'
AND c.clientunsubscribe = 0
AND c.validemailaddr = 1
AND s.unsubscribe = 0
It seems like a UNION ALL should work but it just returns the results in one column. I tried changing the count by specifying a different column for each but that doesn't work either. I also tried writing it as one query and using alias to differentiate the two tables but that just gives me syntax errors. I suspect there is a more elegant way to do this but I'm at a loss. Any help would be greatly appreciated!
Caeanis
Using SQL2000. I want to return the # of columns with non-nullvalues. Here's my query so far:selectcase when Dx1 is not null then 0 else 1 end +case when Dx2 is not null then 0 else 1 end +case when Dx3 is not null then 0 else 1 end +case when Dx4 is not null then 0 else 1 end as DxCountfrom tblClerkshipDataCleanwhere PalmName = @PalmNameThere are 7 rows for the particular PalmName I'm using. The queryreturns a result of 7. However, there are 25 values in Dx1 thru Dx4so the query should be returning 25.What am I doing wrong here? Thanks in advance.
View 4 Replies View Related#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):
View 6 Replies View RelatedI have a UDF with a select * that works fine in one region (DEV) but not another (QC). It's not returning the last 2 columns from the table in QC.I looked at the UDF and it does a fairly simple select:
select a.*
from myTable A
The table is the same in both regions and I did a sp_help on the table to ensure these 2 columns are listed. They are. Also, executing a select * in a query windows does return the final 2 columns from the table in QC. The issue resides in the QC version of the UDF only.
The UDF has already been updated to retrieve all columns by name but I'm curious why this would happen. For some reason I'd just like to know and in case it happens again.
We are facing an issue while executing a stored procedure which uses a table of current database with INNER JOIN a table of another database in same instance.
Per our requirement, we are inserting select statement output in table variable. Then applying business logic and finally showing the data from table variable.
This scenario is working exactly fine in Dev environment. But when we deployed the code in quality environment. Stored procedure does not returning OUTPUT/ (No column names) from table variable.
During initial investigation, we found that collation of these two databases are different but we added DATABASE_DEFAULT collation in the JOIN.
say i have some columns and a person can vote either once, twice or 3 times ( checkbox style control ) and in the database it looks something like this: eg: ID.... vote1..... vote2.... vote3 1 0 0 1 2 1 0 1for ID = 2, what type of query would i have to write to return the column names vote1 & vote3 ( vote2 is not null ) Cheers!!!
View 1 Replies View RelatedI am currently using IDENT_CURRENT to return the Id of a new row in SQL 2000, but I am looking for a similar way to do this in SQL 7. Ihave no experience with SQL 7
Anyone remember how they did this ?
Hello, { Merry xmas to all few hours early but hey !! }
I'm trynig to create an application that will connect to any database that i specify and return the colums names within any table that i specify.
I have done the code that establishes the connection but im a bit stumped on how to get the colum names from a tabel and was wondering if anybody had any sourse code for this in VB.
Thanks
Rob
ps: have a good new year !!
My table has a column called Period i want to get a list of different periods example:
Period
1
2
31
1
2
4
12
31
2
then run an sql statement and should return you the following
Period
1
2
4
12
31
Hi ,,
How to write the Sql Query to return the next generated Identity from the Sql server database.
I am trying to get the schema of database using the getschema method. However, the schema that is being returned does not include the description. I have added in table and column descriptions for some of my tables and columns but the dataset returned does not include the description column.
Any idea on how to get the description to be output?
Dim testCn As System.Data.OleDb.OleDbConnection
testCn = New System.Data.OleDb.OleDbConnection(step2.GetRevEngConn.ConnectionString)
Dim testDS As DataTable
testCn.Open()
testDS = testCn.GetSchema("TABLES")
testds.writexml("c:schematables.xml")
testDS = testCn.GetSchema("COLUMNS")
testDS.writexml("C:schemacol.xml")
testCn.Close()
- <Columns>
<TABLE_CATALOG>XDMDB_LAPTOP</TABLE_CATALOG>
<TABLE_SCHEMA>dbo</TABLE_SCHEMA>
<TABLE_NAME>PropertyValue</TABLE_NAME>
<COLUMN_NAME>property_value</COLUMN_NAME>
<ORDINAL_POSITION>6</ORDINAL_POSITION>
<COLUMN_HASDEFAULT>false</COLUMN_HASDEFAULT>
<COLUMN_FLAGS>230</COLUMN_FLAGS>
<IS_NULLABLE>true</IS_NULLABLE>
<DATA_TYPE>130</DATA_TYPE>
<CHARACTER_MAXIMUM_LENGTH>0</CHARACTER_MAXIMUM_LENGTH>
<CHARACTER_OCTET_LENGTH>0</CHARACTER_OCTET_LENGTH>
<CHARACTER_SET_CATALOG>master</CHARACTER_SET_CATALOG>
<CHARACTER_SET_SCHEMA>dbo</CHARACTER_SET_SCHEMA>
<CHARACTER_SET_NAME>iso_1</CHARACTER_SET_NAME>
<COLLATION_CATALOG>master</COLLATION_CATALOG>
<COLLATION_SCHEMA>dbo</COLLATION_SCHEMA>
<COLLATION_NAME>SQL_Latin1_General_CP1_CI_AS</COLLATION_NAME>
<COLUMN_LCID>1033</COLUMN_LCID>
<COLUMN_COMPFLAGS>196609</COLUMN_COMPFLAGS>
<COLUMN_SORTID>52</COLUMN_SORTID>
<COLUMN_TDSCOLLATION>CQTQADQ=</COLUMN_TDSCOLLATION>
<IS_COMPUTED>false</IS_COMPUTED>
</Columns>
Hi All,
I am working on a column chart type (stacked column sub-type) report.
Our customer requires us that the space(padding) between the columns should be a constant(including the space between the Y-axis and the first column). I know how to set the width of the columns, but I really don't know how to set the width of the space between them. The columns just varies the space between them automatically according to the number of the columns (the number of the columns is not certain).
Thanks a lot in advance!
Danny
SQL 2005 9.00.3402.00 (x64) As Above really when I select * OR select a single column from the view the wrong column data is returned. in SQL Management Studio when I expand the Columns of the view it reflects the old table structure not the new table structure. I can easily fix by compiling the view again but this would mean I would have to recompile all referencing views when I make a change to table structures. I've tried various DBCC Clean Buffers & drop cache with no effect. Is there a command to recompile all views & poss stored procs in a database. Any help or explanation would be appreciated GW
View 12 Replies View RelatedHi all--I've got a derived column transformation where I am adding a field called Import_Date. I'm telling it to add as a new column and use the function "GetDate()" to populate the field. When I run the package, it returns NULL as the data value for all rows. Any idea why this might be happening?
View 5 Replies View RelatedI cannot manage to fetch the new timestamp value inside a TSQL Transaction. I have tried to Select "@LastChanged" before committing the transaction and after committing the transaction. A TimestampCheck variable is used to get the timestamp value of the Custom Business Object. It is checked against the row updating to see if they match. If they do, the Update begins as a Transaction. I send @LastChanged (timestamp) and an InputOutput param, But I also have the same problem sending in a dedicated timestamp param ("@NewLastChanged"): 1 select @TimestampCheck = LastChanged from ADD_Address where AddressId=@AddressId
2
3 if @TimestampCheck is null
4 begin
5 RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1) -- AddressId does not exist.
6 return -1
7 end
8 else if @TimestampCheck <> @LastChanged
9 begin
10 RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1)
11 return -1
12 end
13
14
15 Begin Tran Address
16
17 Update ADD_Address
18 set StreetNumber= @StreetNumber, AddressLine1=@AddressLine1, StreetTypeId=@StreetTypeId, AddressLine2=@AddressLine2, AddressLine3=@AddressLine3, CityId=@CityId, StateProvidenceId=@StateProvidenceId, ZipCode=@ZipCode, CreateId=@CreateId, CreateDate=@CreateDate
19 where AddressId= @AddressId
20
21 select @error_code = @@ERROR, @AddressId= scope_identity()
22
23 if @error_code = 0
24 begin
25 commit tran Address
26
27 select @LastChanged = LastChanged
28 from ADD_Address
29 where AddressId = @AddressId
30
31 if @LastChanged is null
32 begin
33 RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1)
34 return -1
35 end
36 if @LastChanged = @TimestampCheck
37 begin
38 RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1)
39 return -1
40 end
41 return 0I do not have this problem if I do not use a TSQL Transaction. Is there a way to capture the new timestamp inside a Transaction, or have I missed something?Thank you,jspurlin
I am writing a query and have the bulk of it already written.
I am looking at a table that contains customer orders. There is a column named Customer_Order.Status Available values for this column is R, F, H, and C.
I'd like for my query to return all lines that have the value R, F, H.
My where clause is written like this
WHERE CUSTOMER_ORDER.SITE_ID = 'XXX' AND CUSTOMER_ORDER.STATUS = ('R','H','F')
I know I'm missing something....
I am doing a distinct count on a related table's column, but get an out of memory error if I run it for the entire table (works great for just a few rows when filtered down).The error I get is: "We couldn't get data from the external source.The operation has been cancelled because there is not enough memory available for the application. If using 32-bit version of the product consider upgrading.
I know I can add a related column and that works fine...but that seems to me like I've defeated the purpose, I have a good and proper lookup table, and should be able to run my query against its relationship.Here is the query and details below *Note I supplied a scaled down sample, on my actual model I receive these errors, not in the sample as it has only a few rows
List Workers Distinct Project Customers:=CALCULATE(DISTINCTCOUNT(Projects[CustomerID]),'WorkersToProjects')
Other measure which returns no errors, but included for completeness:
List Workers Projects:=CALCULATE(DISTINCTCOUNT([ProjectID]),ISBLANK(WorkersToProjects[ProjectID])=FALSE())
My goal here is to allow the user to view the workers assigned to a project, but also get counts of the workers assigned to the CUSTOMER of a project. For example, suppose we lose a customer, we want to see how many workers would be impacted by that, so a count of projects per worker is not useful there, we need to see a count of workers per project's customer (owner of project whom project work is being done for)The question being: How can I accomplish this:
1. WITHOUT adding a calculated column to WorkersToProjects (of Projects.CustomerID)
2. WITH better performance?
There must be a better way to write this DAX to still get the correct answer?*Pic of pivot table, again, the numbers are accurate but the formula used to List Workers Distinct Project Customers measure does NOT scale :( 3 count for red , the number of Projects John has and 2 count for blue, the unique customers/owners of those projects "Veridian Dynamics" and "Massive Dynamic". URL....
Hi, I have the following script segment which is failing:
CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
Any ideas?
LeaveEntitlementID PeriodID LeaveType EmployeeID NumberOfDays
1 1 Annual 1 10
2 1 Annual 1 10
3 1 Sick 2 10
4 2 Sick 2 10
5 2 Sick 2 10
I have the above table (LeaveEntitlement) which has the above columns.
What I want to sum the column NumberOfDays based on EmployeeID, LeaveType and PeriodID columns as of LeaveTypeNumberOfDays.
For example sum(NumberOfDays) where PeriodID=1 and EmployeeID=1 and LeaveType=Annual
The result should be shown in new column name AnnualLeave (20)
sum(NumberOfDays) where PeriodID=1 and EmployeeID=1 and LeaveType=Sick
The result should be shown in new column name SickLeave (10)
Same all leave Types
The table should be shown as the below after executing the query
LeaveEntitlementID PeriodID EmployeeID AnnualLeave SickLeave
1 1 1 20 0
2 1 2 0 10
3 2 2 0 20
is it possible in sql server
If a Select is done on a column whose data type is nvarchar(16) and contains only numerals (UPC numbers) the select does not return the record.
1. Query with numerals in nvarchar column works as long as multiple records are returned (LIKE '012%')
2. Numeric (INT only one tested) columns works as expected
3. String columns with alpha data works as expected
4. Problem only exist when running in Device Emulator and/or actual device.
5. Same test on desktop app runs as expected.
6. Windows Mobile 6, Vista Ultimate
7. Same results when when connection to device from SSMS
8. SQL Servers comes on
Previous thread discussion of this problem (I thought that Parameters corrected problem, but not in all cases???)
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.ce&mid=0cd9cd3a-f9b0-477f-b1e7-c27eb76158ae
Here is the complete code:
SqlCeConnection _conn = null;
_conn = new SqlCeConnection(@"Data Source=program FilesTestResultSetevsoft.sdf;");
_conn.Open();
// DOES NOT WORK *** This statement does not return the record (it exist)
string _sql = "SELECT * FROM Product where RegDescr='0123456' ";
// works correctly
string _sql = "SELECT * FROM PRODUCT where ProdNum = 6523 ";
// works correctly *** as long as multiple records are returned
string _sql = "SELECT * FROM PRODUCT where RegDescr LIKE '01%' ";
// works correctly
string _sql = "SELECT * FROM PRODUCT where RegDescr='BACARDI SILVER RAZZ'";
SqlCeCommand _cmd = _conn.CreateCommand();
SqlCeDataReader _rdr;
_cmd.CommandText = _sql;
_cmd.CommandType = CommandType.Text;
// Same results using ExecuteResultSet or ExecuteReader
//_rdr = _cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
_rdr = _cmd.ExecuteReader();
listBox1.Items.Add("In the while loop");
while (_rdr.Read())
{
listBox1.Items.Add(_rdr.GetValue(1) + " / " + _rdr.GetValue(3));
}
listBox1.Items.Add("Done");
hi friends, i've a table with (columns) username, content,data,........... i need to delete all column names(i.e.,content,data,........) except username for the specified username. eg: consider username=mahendran. i've to delete the values in the content,data,...............for the username=mahendran. but username should exist.how to do that?pls help me......
View 3 Replies View RelatedI have a SSIS package that reads data from a dump table, runs a custom script that takes date data and converts it to the correct format or nulls and formats amt fields to currency, then inserts it to a new table. The new table redirects insert errors. This process worked fine until about 3 weeks ago. I am processing just under 6 million rows, with 460,000 or so insert errors that did give error column and code.
Now, I am getting 1.5 million errors. and nothing has changed, to my knowledge. I receive the following information.
Error Code -1071607685 Error Column 0 Error Desc No status is available.
The only thing I can find for the above error code is
DTS_E_OLEDBDESTINATIONADAPTERSTATIC_UNAVAILABLE
To add to the confusion, I can not see any errors in the data written to the error table. It appears that after a certain point is reached in the processing, everything, or most records, error out.
Any help is appreciated.
Thanks
Derrick
Hi all
can we add a new column in a table inbetween other columns.
Sachin
I have a problem with my coding. Here is my code.
Code Block
WITH AwaitingApprovalCTE AS
(
SELECT type = 'Members Awaiting Approval Yesterday'
, borrowers = (select count(*)
from LoanApplication
where SubmittedOn >= GETDATE()-1)
, depositors = (select count(*)
from CDOrder
where SubmittedOn >= GETDATE()-1)
, MemberStatus = CuStatus
from Member
where Custatus = 'Pending'
),
AcceptedCTE AS
(
SELECT type = 'Members Accepted Yesterday'
, borrowers = (select count(*)
from LoanApplication
where SubmittedOn >= GETDATE()-1)
,depositors = (select count(*)
from CDOrder
where SubmittedOn >= GETDATE()-1)
, MemberStatus = CuStatus
from Member
where Custatus = 'Accepted'
),
RejectedCTE AS
(
SELECT type = 'Members Rejected Yesterday'
, borrowers = (select count(*)
from LoanApplication
where SubmittedOn >= GETDATE()-1)
, depositors = (select count(*)
from CDOrder
where SubmittedOn >= GETDATE()-1)
, MemberStatus = CuStatus
from Member
where Custatus = 'Denied'
),
combinedCTE AS
(
SELECT * FROM AwaitingApprovalCTE
UNION ALL
SELECT * FROM AcceptedCTE
UNION ALL
SELECT * FROM RejectedCTE
)
SELECT type,
NoOfMembersJoined = borrowers + depositors
FROM combinedCTE
Problem is I get only one field. I get only the first one.i.e. "Members Awaiting Apporval Yesterday"
I dont get other two types.."Members Accepted Yesterday" and "Members Rejected Yesterday".
Final report should look like this:
Type NoOfMembersJoined
Members Awaiting Approval yesterday 69
Members Accepted Yesterday 15
Members Rejected Yesterday 31
But I get only the "Members Awaiting Approval yesterday" for all the rows.
Type NoOfMembersJoined
Members Awaiting Approval yesterday 0
Members Awaiting Approval yesterday 0
Members Awaiting Approval yesterday 0
Members Awaiting Approval yesterday 0
Members Awaiting Approval yesterday 0
NoOfMembersJoined = 0 is ok because there may not be any data yet.
I can not figure it out why it is. Pease can anyone tell me where I went wrong?
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this:
SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89
The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1.
Why?
Hi Guys,
I have twotables(Employee and Borrower). In Employee table have EMPID and Borrower table have BorrowerID. I want to comebine these two columns into one column as EMPID in Employee table. Can any one help?
Thanks
Hi,
I am developing a search engine for my application and I need to grab the entire row from the table if I found the search field in any of the columns and the search is not defined to one table. Please let me know how to search all columns in table without using column names indivdually.
I would like to insert several columns into one column.
eg. Coloumn1, Column2 & Coloum3 insert into Column00
thanks, Vic
I have three records, with one column that is an identity/PK. I want the last record inserted and the whole record's information from the query, using a where clause to limit the record returned from the table.
I get an error if I do not have the group by added. However, with this query, I still get three records returned.
SELECT max([tablePK])
,[orderGUID]
,[tblPeopleFK]
,[tbl_shippingAddressFK]
,[tblBillingMethodFK]
,[shippingMethod]
[Code] ....
Hi,
I have a tabel called "Daily" which has 5 columns, "TesterID", "Activity", "Hours Given", "Hours Used", "Delta".
The data for "TesterID" and "Hours Given" columns are taken from a table called "Tester".
Data for columns "Activity" and "Hours Used" taken from table called ALD.
"Delta" column is the difference between Hours Given and Hours Used.
For "Hours Given" in table "Daily", the data source should change every 12 hours.
For Monday-Friday Mornings, "Hours Given" should read data from "Tester.Weekdays_day" and for Monday-Friday Nights it should read data from "Tester.Weekdays_Night" and for Saturday-Sunday Mornings it should read from "Tester.Weekend_Day" and for Saturday-Sunday Nights it should read from "Tester.Weekdays_Night" .
How to do that? Please Help. Thank you.
Can anyone help me how to add a new column between a column in sql server 2005
i have did this one in SQL Server 2000 like below.
1. Add a new column using ALTER statement
2. EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
EXEC sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
3.Change the colid in syscolumns table using UPDATE Statement
- But its not working in SQL Server 2005...it throws an adhoc error for system catalog table