Returning # Of Columns With Non-null Value

Jul 20, 2005

Using SQL2000. I want to return the # of columns with non-null
values. Here's my query so far:

select
case 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 DxCount
from tblClerkshipDataClean
where PalmName = @PalmName

There are 7 rows for the particular PalmName I'm using. The query
returns a result of 7. However, there are 25 values in Dx1 thru Dx4
so the query should be returning 25.

What am I doing wrong here? Thanks in advance.

View 4 Replies


ADVERTISEMENT

Returning N/A Instead Of Null

May 1, 2005

I have null fields in one of the column and want to return "N/A" when the column is null. How can I do that ?

View 2 Replies View Related

SQL Returning Null

Feb 13, 2007

Thanks to everyone in advance

The code below returns the members who have accounts that are inactive, and works correctly.
It returns the memberid, but returns null values for the barcode values.
I am explicitly looking for unmatched rows, i.e. the LEFT OUTER JOIN will return joined member-address
rows which do not have a matching account, according to the ON conditions, and these are the ones I want
because of that WHERE condition.
Therefore account.barcode will always be NULL too.

Is there to perform the same query and return account details?
I have tried running a query to return just the memberid values and then perform a second query to match the account
details, but this is very ineffecient and slow.


select member.memberid, account.barcode
FROM member
INNER
JOIN address
ON address.MemberID = member.Memberid
LEFT OUTER
JOIN account
ON account.Memberid = member.MemberID
AND account.enddate >= current_date
AND account.closed = 0
WHERE account.Memberid is null AND member.isDeleted = 0

Special thanks to r937 for help.
James

View 4 Replies View Related

Need Help Returning A Null Value, Please

Jul 23, 2005

I've got the following query in SQL 2000:select a.SSN, a.MonthName, a.IMClinicDay,b.IMClinicDay as SecondDayfrom tblResidentRotations ainner join view7 bon a.SSN = b.SSNwhere a.AcademicYear = '2004-2005' and a.SSN = '999999999' anddatename(month, a.IMClinicDateFirst) = b.MonthNameThis query returns a resultset like this:<SSN> <Month> <a.IMClinicDay> <SecondDay>999999999 July Friday PM Tuesday PM999999999 September Tuesday PM Friday PM999999999 October None Friday PM999999999 November Friday PM Tuesday PM999999999 January Tuesday PM Friday PM999999999 April Friday PM Monday PM....and so onFor some of the months, there is a null value for "b.IMClinicDay". Forexample, it's null for August, December, and February. I want myresultset to look like this:<SSN> <Month> <a.IMClinicDay> <SecondDay>999999999 July Friday PM Tuesday PM999999999 August Tuesday PM null999999999 September Tuesday PM Friday PM999999999 October None Friday PM999999999 November Friday PM Tuesday PM999999999 December Tuesday PM null999999999 January Tuesday PM Friday PM999999999 February Friday PM null999999999 April Friday PM Monday PM....and so onHow can I return a null for these days? Thanks for any help oradvice.

View 4 Replies View Related

RowCount Is Returning Null

Apr 4, 2007

i have 2 stored procedures: a delete and a select.  the delete sp returns the rowcount properly.  the select returns null.  the code for both sp's is extremely simple and extremely similar.  when i execute the select sp in server management studio the rowcount shows a 1 as expected.  but the calling method gets null.
SP Code 
ALTER PROCEDURE [dbo].[RetrieveEmployeeKeyFromAssignmentTable]
@assignmentPrimaryKey int,
@rowCount int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT employeePrimaryKey FROM assignmentTable WHERE primaryKey = @assignmentPrimaryKey;
SET @rowCount = @@RowCount;
END
c# code
SqlConnection conn = GetOpenSqlConnection();
if (conn == null)  return true;
SqlDataReader reader = null;
SqlParameter p1 = new SqlParameter();  SqlParameter p2 = new SqlParameter();
try {
SqlCommand command = new SqlCommand();
command.CommandText = "RetrieveEmployeeKeyFromAssignmentTable";
command.CommandType = CommandType.StoredProcedure;
command.Connection = conn;
p1.ParameterName = "@assignmentPrimaryKey";
p1.Value = assignmentPrimaryKey;
p2.ParameterName = "@rowCount";
p2.Direction = ParameterDirection.Output;
p2.Value = 0;
command.Parameters.Add(p1); command.Parameters.Add(p2);
reader = command.ExecuteReader();
if (p2.Value == null) //always true
any suggestions would be appreciated.
thanks.  matt

View 3 Replies View Related

Returning Null Values

Jun 4, 2007

I have a simple table on my webpage which shows data from my database.
It all worked fine apart from one part. I wanted the select statement to select only NULL values from a column, as these are classed as open queries. Once I have closed the query a 0 or 1 will automatically be posted and will no longer have a NULL value.
I have a simple select statement (which I will post on here when I log in with my work computer) and at the end I have a WHERE Column = NULL. I have also tried WHERE column <> 0.0 AND column <>1.0 and that did not work.
If I only did WHERE column <> 1.0, i only get the 0.0 results and none of the NULL results, but if I have no WHERE statement I get all the results including the NULL values.
 
Oliver

View 6 Replies View Related

@@Identity Is Returning NULL

Feb 22, 1999

Hi,

I have upgraded database from sql6.5 to sql 7.o

I have a table tblnetwork on which identity property
is defined on column networkid.

When I issue the following command new record is getting inserted into
tblnetwork and identity column is getting incremented properly.
But, I am not able to get the @@identity value
It is returned as NULL.

insert tblnetwork(formalname,networktype)
values ("name2",11897)
select @@identity

I need this value to insert into some other table.
Pls suggest me why @@identity is returning NULL
thanks,
MMS

View 5 Replies View Related

@@servername Returning NULL

Mar 9, 2006

I have a SQL 2005 clustered server which is returning a Null value for @@servername. I find the server entry in sysservers. I have replication configured on this so i am not able to do a Sp_dropserver & sp_addserver as this acts as a publisher. The configured merge repication stopped working because of this issue and I am not able to delete replication as the the delete option uses @@servername which returns a null value. So I am struck in a loop.

Any advice is appreciated.



thanks

View 13 Replies View Related

Insert GUID Returning Null

Jun 5, 2006

I am trying to insert a record to based on the source below, however the GUID of GiftOcc_ID is being returned as zero's so the first record can be added but as it is defined as the primary Key and uniqueidentifier the next record fails with a duplicate entry. Basically how do I ensure that the GUID is created and not nulls. As you can see I am trying to use Newid() which I have inserted as a default value but that does not work. Also as it is a unique identifier the "is identity" is not available
 
Protected Sub CreateGiftOccasion(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
Dim null As New Nullable(Of Integer)
Dim da As New DataSet2TableAdapters.Gift_OccasionTableAdapter
Dim GiftOcc_ID As Guid
da.Insert(newid(), Occ_Type_Text.Text, Occ_Desc_Text.Text, Calendar1.SelectedDate, Calendar2.SelectedDate, 1)
Catch ex As Exception

Response.Write("Ooops")
End Try
End Sub
 
The code from the table adapter regarding inserts is:
<InsertCommand>
<DbCommand CommandType="Text" ModifiedByUser="False">
<CommandText>INSERT INTO [dbo].[Gift_Occasion] ([GO_ID], [Go_Type], [GO_Description], [GO_DateOn], [GO_DateOff], [GO_Active]) VALUES (@GO_ID, @Go_Type, @GO_Description, @GO_DateOn, @GO_DateOff, @GO_Active);
SELECT GO_ID, Go_Type, GO_Description, GO_DateOn, GO_DateOff, GO_Active FROM Gift_Occasion WHERE (GO_ID = @GO_ID)</CommandText>
<Parameters>
<Parameter AllowDbNull="False" AutogeneratedName="" DataSourceName="" DbType="Guid" Direction="Input" ParameterName="@GO_ID" Precision="0" ProviderType="UniqueIdentifier" Scale="0" Size="0" SourceColumn="GO_ID" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@Go_Type" Precision="0" ProviderType="Text" Scale="0" Size="0" SourceColumn="Go_Type" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@GO_Description" Precision="0" ProviderType="Text" Scale="0" Size="0" SourceColumn="GO_Description" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="DateTime" Direction="Input" ParameterName="@GO_DateOn" Precision="0" ProviderType="DateTime" Scale="0" Size="0" SourceColumn="GO_DateOn" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="DateTime" Direction="Input" ParameterName="@GO_DateOff" Precision="0" ProviderType="DateTime" Scale="0" Size="0" SourceColumn="GO_DateOff" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@GO_Active" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="GO_Active" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
</Parameters>
</DbCommand>
</InsertCommand>
 
Many thanks in advance for any assistance
 
 
 

View 2 Replies View Related

Decrypt Sproc Returning NULL To Non DBO.

Nov 28, 2006

I'm still having issues with this despite my attempts to resolve. I even
have "with exec as dbo" in my sproc, and and "exec as dbo" in my execution,
but still the encrypted data returns nulls when I exec as a user other than
DBO. Below is precisely what I have done. All ideas are welcomed.

TIA, ChrisR


--If there is no master key, create one now

IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117
r$$#1946kcj$n44ncjhdlj'
GO

CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [AdventureWorks];
GO

-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO

-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.

alter procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO

/*works for me, shows the decrypted data*/

exec getDecryptedIDNumber

USE [master]
GO

CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [AdventureWorks]
GO

CREATE USER [test] FOR LOGIN [test]
GO

use [AdventureWorks]
GO

GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO

GRANT IMPERSONATE ON USER:: dbo TO test;
GO

/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber

/*This returns NULL values where it should show the decrypted data*/

View 1 Replies View Related

Returning CASE As Null Problem

May 6, 2008

Hello again! I'm having yet another problem with my code.


CASE Kit WHEN 'Y' THEN '(Kit)' WHEN 'N' THEN '' END + ' ' +
CASE cream WHEN 'Y' THEN '(cream)' WHEN ' N' THEN '' END + ' ' +
CASE Phone WHEN 'Y' THEN '(Phone)' WHEN 'N' THEN '' END

The problem that I am running into is that if one of the values comes up as NULL the whole field is NULL. am i doing something wrong?

View 11 Replies View Related

Count - Returning 0 For Rows With Null

Oct 8, 2014

Is there a way to allow the count () to return a 0 for the rows with a NULL value instead of not returning that row?

View 15 Replies View Related

EncryptByKey Function Always Returning Null

Aug 8, 2006

When I use EncryptByKey function to encrypt text using AES 128 bit key, I get always null result. this is how I do the encryption:

ALTER PROCEDURE [dbo].[ProcMyProc](@ClearText nvarchar(50))
AS
BEGIN

OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD= 'MY_Password_128';

Declare @Temp varbinary(8000);

Set @Temp =EncryptByKey(Key_GUID('MyKey'),@ClearText);

close symmetric key MyKey;

select @Temp as temp;
END


The result I get for this procedure is null. Is there something wrong with this code?

View 5 Replies View Related

Returning A NULL Value In An SSIS Expression

Dec 5, 2006

Greetings SSIS friends,

I have the following expression but it doesn't seem to evaluate



findstring(eventstatus, "O", 1) > 0 ? "OFF" : NULL(DT_STR, 30, 1252)

All I want to do is something like :



All I want to do is return a NULL value if my condition is false.



Your help would be appreciated.

View 3 Replies View Related

Excel Source Returning NULL

Feb 27, 2007

I've a package that has a excel source. But i'm having a strange problem with it. One of the columns in the source file have a lot of null values but not all of them. But when i run the package a put a data viewer right after the source and i can see that it's showing that the few fields that should have values are also null. I've tried a lot of things but they didn't work. I need some help and fast if possible.
Example: Source file.xls
Name Grade OtherGrade
John 30 30.23
In the DataViewer
Name Grade OtherGrade
John 30 NULL

thanks

Adriano Coura

View 10 Replies View Related

Sql SELECT Returning NULL On DateTime Field

Aug 8, 2006

Hi there ;
This Problem is goin to make me crazy!
I've got a table with couple of fields, 2 of them are DateTime, in Sql Express 05 .
I used asp.net 2.0 to insert datetime values, directly,  using sth like DateTime.Now.ToString() .
i use 2 selects as follows :
1)select * from X where  Date1='8/9/2006 11:17:41 AM'
2)select * from X where  Date2='8/9/2006 11:17:41 AM'
#1 is OK, but the second one returns a row with all columns set to NULL .
(X is the name of the table)
Thanks in advance

View 6 Replies View Related

Datediff Formula On Insert Returning Null Value

Sep 28, 2006

I have a form with two date fields that the user will submit their requested vacation time off with.  When they insert it, I am trying to say find the difference between the request_start_date and request_end_date in days MINUS any of the days they would already have off like weekends or holidays that are included in another table.  Everything inserts okay, but I am getting null for the request_duration.  If I put dates in quotes and run the query it comes back with the right results.  If I put the dates in the form and submit it, I get Null for the request_duration.  Thank you in advnace for any help on this!  INSERTrequest
(
emp_id,
request_submit_date,
request_start_date,
request_end_date,
request_duration,
request_notes,
time_off_id
)
Select@emp_id,
GETDATE(),
@request_start_date,
@request_end_date,
1 + DATEDIFF(day, @request_start_date, @request_end_date) - (select count(*) from WeekEndsAndHolidays where DayOfWeekDate between @request_start_date and @request_end_date),
@request_notes,
@time_off_id   

View 2 Replies View Related

Java Code Returning Null Resultset

Oct 26, 2007

Hi,

Here's a snippet of my java code :

Code:


boolean b = statementSQLServer.execute("select ROW_ID from S_INDUST where NAME='InWire'");

resultSetSQLServer = statementSQLServer.getResultSet();

System.out.println("b = " + b);
System.out.println("Are there any rows ?? " + resultSetSQLServer.next());



This code is returning null resultset.

But when I fire the same query directly into MS SQL, it is returning the corresponding rows.

I dont know where i'm going wrong. But just to make things more clearer, here's my connectivity code :

Code:


Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
connectionSQLServer = DriverManager.getConnection("jdbc:odbc:dm", "user", "pass");

statementSQLServer = connectionSQLServer.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);




Please help me out if possible....

View 4 Replies View Related

Returning Values When Null Parameter Selected

Apr 3, 2008

i have a stored procedure that has parameters. this dataset is used in a crystal report. the parameters are in the sp and will return records if a value is selected. i would like to return records if one parameter or all parameters are selected.
there are 3 parameters, date range, receipt, po #
if i select a distinct value and leave the others null, or 2 values 1 null etc, i would like to return the records, i am having trouble with the syntax. thank you

View 1 Replies View Related

ASP Returning Null Sized Field From MS SQL Database

Jul 20, 2005

We currently have an SQL db running on a web server.One of these fields is a large(ish) amount of text data – up to 400characters – and has been cast variously as varchar, nchar and texttype to overcome a problem. The problem appears to be in retrievingthe data via ASP. I understand that ASP can handle string data of thissize so I am okay there.When the records are retrieved from the db, the data string length =0.I know the data is there because I have written a Delphi data managerwhich interrogates the db and shows all records and their contents.So if ASP can handle strings this size and the data is there, why do Iget a data length of zero bytes returned when I interrogate the recordset?Whichever way I cast this field I get the same result.I know the code is sound as it works locally through a MS SQL serveron my PS.Anyone have this problem or know what's causing it? I have logged asupport call with my hosting company, but they haven't replied as yetand I am stuck on an urgent project.Any suggestions?CheersGrant

View 1 Replies View Related

Parameter Issues When Returning Null Records

May 14, 2008

Hi,

I'm running into an issue where if a report retrieves 0 records, I run into an error. My report has 2 queries-- the main query to retrieve Account information and the other to return Contact information based on the contact id(s) returned by the Account query. The reason needing two separate queries is that the contact and account tables are on different databases.

Now here is my account query: select contact_id, username, password from account.
My contact query looks like this: select name, address, .... from contact where contact_id = @contact_id.

In my report parameters, I define contact_id like this:
*******************************************
name = contact_id
data type = integer
prompt = [blanked out]
hidden = checked
internal = checked
multi-value = unchecked
allow null value = checked
allow blank value = unchecked

available values:
from query: dataset = account
value field = contact_id

label field = contact_id

default values:
from query: dataset = account
value field = contact_id
**********************************************

Now, when I run my report and knowing there are no records, I get the following error: "The 'contact_id' parameter is missing a value". Any ideas on how to solve this issue? Thanks.

larry

View 4 Replies View Related

Call To Stored Proc Returning Null Datatable

Jun 6, 2007

I have a stored proc which should be returning a datatable.  When I execute it manually it returns all requested results. However, when I call it via code (C#) it is returning a null table which leads me to believe the problem is in my code.  I'm not getting any errors during runtime.  Any help at all would be a BIG help!
private void PopulateControls()    {        DataTable table = CartAccess.getCart();    }
public static DataTable getCart() {        DbCommand comm = GenericDataAccess.CreateCommand();        comm.CommandText = "sp_cartGetCart";
        DbParameter param = comm.CreateParameter();        param.ParameterName = "@CartID";        param.Value = cartID;        param.DbType = DbType.String;        param.Size = 36;        comm.Parameters.Add(param);
        DataTable table = (GenericDataAccess.ExecuteSelectCommand(comm));        return table; }
public static DataTable ExecuteSelectCommand(DbCommand command)    {        // The DataTable to be returned         DataTable table;        // Execute the command making sure the connection gets closed in the end        try        {            // Open the data connection             command.Connection.Open();            // Execute the command and save the results in a DataTable            DbDataReader reader = command.ExecuteReader();            table = new DataTable();            table.Load(reader);            // Close the reader             reader.Close();        }        catch (Exception ex)        {            Utilities.SendErrorLogEmail(ex);            throw ex;        }        finally        {            // Close the connection            command.Connection.Close();        }        return table;    }

View 1 Replies View Related

Left Outer Join Not Returning All/null Rows

Dec 31, 2007

Need some join help...

Table 1 - Modules:
ID | Name
1 | A
2 | B
3 | C

Table 2 - CompanyModules
ModuleID | CompanyID
1 | 1
2 | 1
3 | 1
1 | 2


I'd like to return the following result set:
CompanyModules.CompanyID | Modules.Name | Present
1 | A | True
1 | B | True
1 | C | True
2 | A | True
2 | B | False
2 | C | False

What would be the query for this? Thanks.


Edit: This is the query I have tried:


select CompanyModules.CompanyID, Modules.Name, count(Modules.ID) as Present from

CompanyModules RIGHT outer Join Modules on CompanyModules.ModuleID = Modules.ID

group By CompanyModules.CompanyID, Modules.Name

Order by CompanyID


However, it only returns a partial result set:

CompanyModules.CompanyID | Modules.Name | Present
1 | A | 1
1 | B | 1
1 | C | 1
2 | A | 1

View 1 Replies View Related

SQL XML :: Importing Data From XML Into Database And NULL Returning Results

Sep 16, 2015

I have a xml file downloaded from webAPI which i want to import into my database.There are several sub childnodes and need to combine data between nodes.

<?xml version="1.0" encoding="UTF-8"?>
<classes type="array">
<class>
<class_id>3S</class_id>
<class_pk type="integer">3900</class_pk>

[code]....

View 4 Replies View Related

Adding Null And Non-null Columns Together ----??

Nov 27, 2000

I have two columns A (which allows nulls) and B( which does not allow nulls).
How can I add the contents of columns A and B SO THAT I DO NOT GET A NULL RESULT WHEN A IS NULL.

The result of A+B concatanation will be stored in a column, C.


Appreciate your help
Ziggy

View 10 Replies View Related

Returning 2 Columns As 1 Column

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

Transact SQL :: CASE With Subselect And DATEADD Function Returning NULL Values

Jun 15, 2015

I'm running the following test query on a single table:

SELECT sph.datestamp, sph.stocksymbol, sph.closing, DATENAME(dw, sph.datestamp),
CASE DATENAME(dw, sph.datestamp)    
WHEN 'Monday' then 'Monday'  
ELSE (SELECT CAST(sph2.datestamp AS nvarchar) FROM BI_Test.dbo.StockDB AS sph2 WHERE sph2.DateStamp = DATEADD(d, -1, sph.datestamp) AND sph2.StockSymbol = 'NYA') 
END AS TestCase,

[Code] ....

And here's an example of the output I'm getting:

Why the exact same subquery in the THEN of the second CASE statement is returning NULL when the first one completes as expected?

View 7 Replies View Related

Returning Multiple Columns From One Db Column

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

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 View Related

Returning Subsets Of The Same Column In Separate Columns...

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

Transact SQL :: Stored Procedure Not Returning Dataset (No Columns Are Coming)

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

Null Columns

Jun 14, 2001

ColumnA has existing data in it some of the data are Nulls
what would be the best way to have new as well as existing
data change to 0, using a constraint?

View 1 Replies View Related

Columns [not Null]

Oct 15, 2006

hi, I have a problem with a flat file and a table

I have a flat file with 5 columns and in the table I have 30 columns and they all are (not null)
I cannot leave other columns in target for which it throws mistake, have to insert something, since as I insert information in the demas columns???

Since I can solve this problem???




helps please

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved