T-SQL (SS2K8) :: Select Asterisk Not Returning All Columns In UDF
May 13, 2015
I 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.
View 5 Replies
ADVERTISEMENT
Aug 27, 2014
This query was run against a database on 2008 SP3 and 2012 SP2
Here is an example of what I ran
Declare @A_number varchar(5)
Select Top 1 Column_Is_an_int = @A_number
From Our_Main_Table
Where A_Database_Name = 'A database with many records in this table and multiple records in the Column_is_an_int some are more than 5 characters'
Select @A_number
Exec My_Stored_Proc @A_Nmuber
The top result for that database had an integer that was 8 characters including the - sign.
When the query ran I would normally expect it to throw the binary data cannot be truncated error. However in this case it returned a * in the variable which then tried to pass it in to the SP which of course threw a fit.
Once I changed the varchar(5) to varchar(50) it worked perfectly.
View 5 Replies
View Related
Aug 27, 2014
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
View 4 Replies
View Related
Dec 2, 2014
I am trying to find a way to lock rows of data used in a SELECT query from being read by another SELECT query.
I could do a "begin tran - select - update - rollback" sequence but was wondering if there is a cleaner way to do this??
Tried UPDLOCK, ROWLOCK, TABLOCK, HOLDLOCK in multiple variations but none seem to block the select.
View 9 Replies
View Related
Jun 1, 2015
I have event table that containing multiple events, and many of them are empty. I'd like to select only the columns that have values in them.
Here is an example:
IF OBJECT_ID('tempdb..#events') IS NOT NULL
DROP TABLE #events
create table #events (eventId int,
Category varchar(250),
events1 varchar(250),
[Code] .....
In this case, I'd like to run a query like this one(skip Column Event3):
Select eventId,Category,events1,events2,events4,events5 From #events
View 4 Replies
View Related
Apr 28, 2015
How to include row values as columns in my select query. I have a table that stores comments for different sections in a web application. In the table below, I would like display each comment as a new column. I only want one row for each record_ID.
Existing table layout
table name - tblcomments
Record_ID Comment_Section_ID Comment
1 5 Test 5 comment
1 7 Test 7 comment
2 5 New comment
2 7 Old comment
3 5 Stop
3 7 Go
Desired table layout
table name - #tempComment
Record_ID Comment_Section_5 Comment_Section_7
1 Test 5 comment Test 7 comment
2 New comment old comment
3 Stop Go
Once I figure out how to get the data in the layout above, I will need to join the table with my record table.
table name - tblRecord
Record_ID Record_Type_ID Record_Status
1 23 Closed
2 56 Open
3 67 Open
4 09 Closed
5 43 In progress
I would like to be able to join the tables in the query below for the final output.
Select r.Record_ID, r.Record_Type_ID, r.Record_Status,
c.Comment_Section_5, c.Comment_Section_7
from tblRecord r
left outer join #tempComment c
on r.record_ID = c.record_ID
How I can get the data in the desired #tempComment table layout mentioned above?
View 2 Replies
View Related
Jul 6, 2007
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?
View 2 Replies
View Related
Jul 29, 2014
I need to create a TSQL to return MAX(Value) removing the first part and last part.
Example
I have these Varchar Reference code:
1.00001-Q1
2.00100-Q2
3.00005-Q4
I need to cut the string to find the max number excluding (Part1):
1.
2.
3.
And also excluding (Part3):
-Q1
-Q2
-Q4
In this case converting varchar to INT, the correct value that i want is: 00101
The middle part excluding (Part1 and Part3)
Then my final reference could be:
1.00101-Q1
or
2.00101-Q2
or
3.00101-Q4
View 3 Replies
View Related
Jun 19, 2014
i need to cut my string on 3 portion, for exemple my string is :
a) 1.9999-Q1
b) 01.9999-Q11
I need to keep all values before "." (point), and all values after "-" , and also all values between "." and "-", like this:
a) 1
9999
Q1
b) 01
9999
Q11
View 9 Replies
View Related
Jul 10, 2014
Here is my setup: I have the following tables -
tblPerson - holds basic person data.
tblPersonHistorical - holds a dated snapshot of the fkPersonId, fkInstitutionId, and fkDepartmentId
tblWebUsers - holds login data specific to a web account, but not every person will have a web account
I want to allow my admins to search for users (persons) with web accounts. They need to be able to search by tblPerson.FirstName, tblPerson.LastName, tblInstitutions.Institution, and tblDepartments.Department. The only way a Person record is joined an Institution or Department record is through many -> many junction table tblPersonHistorical.
People place orders and make decisions in our system. Because people can change institutions and departments, we need an historical snapshot of where they worked at the time they placed an order or made a decision. Of course that means some folks will have multiple historical records. That all works fine.
So when an admin user wants to search for webusers, I only want to return data, if possible, from he most recent/current historical records. This is where I am getting bogged down. When I search for a specific webuser I simply do a TOP 1 and ORDER BY DateCreated DESC. That returns only the current historical record for that person/webuser.
But what if I want to return many different webusers, and only want the TOP 1 historical for each returned?
Straight TOP by itself won't do it.
GROUP BY by itself won't do it.
View 9 Replies
View Related
Aug 6, 2015
I need to build a query that can return only documents where the field "u_DIM4" for the same document have more than one different value..my script are this one just to having an example:
Select docnome [documentname], adoc [docnr], count(*) [countAlldifferentbyDoc], u_dim4
from fn
where u_dim4 <> '' and data between '2015-01-01' and '2015-07-31'
AND adoc = '02634'
Group by docnome,adoc,u_dim4
ORDER BY 2 asc
[code]...
View 4 Replies
View Related
Jun 11, 2015
Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.
Sample Data:
;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'
[Code] ....
The results from the above are as follows:
columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL
My desired results with desired headers are as follows:
PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30
View 3 Replies
View Related
Jul 3, 2014
Actually title should be returns true when should false.
I want to check a table to see if a record already exists, if it doesn't then insert it, else do nothing:
IF NOT EXISTS
(SELECT 1 FROM Table1 WHERE col1 = 'Test')
BEGIN
INSERT INTO Table1 (col1) VALUES ('Test')
END
The value 'Test' is already in the database yet, the code is saying it's not and trying to insert it, resulting in duplicate key errors.
View 9 Replies
View Related
Mar 11, 2015
Firstly may I say that the sproc I am having problems with and the service that calls it is inherited technical debt from an unsupervised contractor. We are not able to go through a rewriting process at the moment so need to live with this if possible.
Background
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data. In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
Observations
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe. It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing). e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
So the Sproc...
USE [xxxxxxxxxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
The only suggestions I can make have been...
change @pIdentifier nvarchar(25) to nvarchar(22)
Trim any potential blanks from either side of both parts of the identifier comparison
Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. Not sure why he did it that way or what the purpose is.
I have been unable to replicate this using a test app and our test databases. Has observed selects failing to find even though the data is there, like this before?
View 6 Replies
View Related
Jun 30, 2006
I'm trying to creat a search form and want to use the Like clause in my select statement so that a user can enter part of a word rather than the entire word. When I use this sql I get no results:SELECT DISTINCT Keyword.CodeID FROM Keyword INNER JOIN Code ON
Keyword.CodeID = Code.CodeID WHERE ((Keyword.Keyword) Like '*ARR*' AND
(Code.ProgLang)='VB.NET') ORDER BY Keyword.CodeIDThe problem is with the * . If I remove the * it works fine. If I use the code within Access rather than from my aspx code, it works fine. Is there a work around for this?
View 2 Replies
View Related
Aug 20, 2013
Is it possible to return the results of a stored procedure into either a CTE or temp table?
In other words, is it possible to do this:
with someCTE as (
exec someStoredProc
)
or this:
exec someStoredProc into #tempTable
???
View 9 Replies
View Related
Jun 3, 2014
I am using MSSQL Server 2008R2 and I am interested in returning rows from a 'financial' table that fall within the current year (each row contains a 'Entered Date'). I am located in Australia so my financial year consists of all entries between the date 01/07/xx to the 30/06/yy.
Perhaps using the datediff() function, or other functions as required to achieve what I need?
View 3 Replies
View Related
Nov 19, 2014
I've got a working query which returns all leads within a supplied proximity to a city. I followed a tutorial I googled a couple months ago (can't find it now). It works, but would love others to look the query over (provided DDL and sample data) and tell me if it's as it should be.
Two things I don't like about query:
1. I have to do a UNION to another query that retrieves everything that is in the same city in order to have complete results.
2. very slow to retrieve results (> 1 minute)
Sample DDL: 2 tables
create table dim_lead
(
date_created datetime,
[contact_first_name] varchar(20),
[contact_last_name] varchar(20),
lead_id int,
[Code] .....
View 9 Replies
View Related
Jul 20, 2005
I have a table which is returning inconsistent results when I queryit!In query analyzer:If I do "SELECT * FROM TABLE_NAME" I get no rows returned.If I do "SELECT COL1, COL2 FROM TABLE_NAME" I get 4 rows returned.In Enterprise manager:If I do "return all rows" I get 4 rows returned, and the SQL is listedas being "SELECT * FROM dbo.TABLE_NAME".I've tried adding the "dbo." before my table name in QA, but it seemsto make no difference.I'm using SQL Server 2000, which is apparently 8.00534.Can anyone help me, or give me ideas about what to check?Thanks,Rowland.
View 9 Replies
View Related
Jan 26, 2008
can I transfer sql characters (e.g. *,+,%,OR,AND etc.)
and other search keywords through a parameter (@example)?
View 5 Replies
View Related
Oct 30, 2006
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?!
View 3 Replies
View Related
Jul 20, 2005
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
Apr 1, 2014
Is it ok/possible to use and * in a column that is NVARCHAR type?
View 4 Replies
View Related
Feb 24, 2008
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
View 3 Replies
View Related
Mar 23, 2006
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
View 1 Replies
View Related
Jun 3, 2015
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.
View 14 Replies
View Related
Apr 29, 2015
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
View 14 Replies
View Related
Sep 10, 2007
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups
Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:
How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
Thanks for all your help!
View 1 Replies
View Related
Jul 6, 2007
Hi,
My Select query returing the one column:
Options
1
2
3
Form this Options column I have to check the Checkbox1, checkbox2, checkbox3 out of total 6 checkboxes in asp.net form. I am getting the Options column from database.
How to store that resultset.
Thanks in advance.
View 2 Replies
View Related
Apr 21, 2014
I have a report that looks like the following
NameCity Client NoAccount No Balance
SmithSydney 1234561258792 3.95
JonesMelbourne 2589641000657 9.54
BrownPerth 9876541000879 5.46
BrownPerth 9876541000880 7.51
WhiteSydney 6548521007562 10.65
HOWEVER I need it to look like the following:
NameCity Client NoAccount No BalanceAccount No Balance
SmithSydney 1234561258792 3.95
JonesMelbourne 2589641000657 9.54
BrownPerth 9876541000879 5.461000880 7.51
WhiteSydney 6548521007562 10.65
The requirement is that if the Client No is the same that the Account No and Balance appear on the same row but just additional columns. There is no restriction on how many extra columns there would be.
Once the query is working it needs to go into Visual Studio so that the report can be set up as a subscription.
View 1 Replies
View Related
Oct 17, 2014
I am currently working on extracting data from a text file and loading into sql server.I have a table with 1 column and the data in the column is in this format:
TableName1
A
1
10
TableName2
D
2
20
I want to convert this to 2 columns in this format:
ColumnA ColumnB
TableName1 A
TableName1 1
TableName1 10
TableName2 D
TableName2 2
TableName2 20
View 1 Replies
View Related
Dec 16, 2014
I've the table structure below. Example table of my original
create table fms
(
fs_locn char(100),
fs_account_no varchar(200),
fs_cost_center_no varchar(100),
fs_tran_type char(50),
fs_post_amt float,
fs_tran_date datetime
[Code] ....
Expecting Output :
Account 200Prod 201PROD ProdcutionCost
E002-SW100-2100 2500 1000 3500
How to do that?
I tried like
select
k.fs_acoounts,
k.200Prod,
(
my query
)k
its showing error '200prod'
View 1 Replies
View Related
Mar 30, 2015
I want to display full name column as FirstName and LastName.
Eg if Full Name is Abhas Jadhav then i want to convert it into FirstName- Abhas and LastName - Jadhav
i.e. Before Space is FirstName and After Space is LastName.
View 9 Replies
View Related
