T-SQL (SS2K8) :: Change A Selected Value To Null?
Dec 1, 2014
I am emailing data using this:
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
USE R4W_001
SET @xml = CAST(( SELECT ITEM_ID AS 'td','',"td/@align" = 'right', replace(convert(varchar,convert(Money, AVAILABLE),1),'.00','') AS 'td','',
[Code] ....
The result displays:
ITEM_ID Available DESC EXPCT DELV
AC 16,015Apples 01/18/2015
ORG12 2,908 Oranges 12/30/2014
AHA 3,355Apricots 01/01/1753
Our systems stores 01/01/1753 when the date field is blank. In the above TSQL, how can I replace 01/01/1753 with null or blank in teh results?
View 5 Replies
ADVERTISEMENT
Apr 1, 2008
Hi all,
I have the situation below. I can't use dual. Thank you for your time.
Example Query:
select name
from employee
where id=1;
Result:
0 rows selected
Wanted Result:
1 row selected
View 5 Replies
View Related
Nov 19, 2007
Hello,
I start with this table,
Tagname
Timestamp
Value
A
12/12/2007 12:00:01
1
A
12/12/2007 12:00:02
1,1
A
12/12/2007 12:00:03
30
B
12/12/2007 12:00:01
12
B
12/12/2007 12:00:02
12
B
12/12/2007 12:00:03
12
C
12/12/2007 12:00:01
13,4
C
12/12/2007 12:00:02
14
C
12/12/2007 12:00:03
14,5
I would like the table to be as following
Timestamp
A
B
C
12/12/2007 12:00:01
1
12
13,4
12/12/2007 12:00:02
1,1
12
14
12/12/2007 12:00:03
30
12
14,5
Does anyone knows how to do this within a query?
Thx,
Nic
View 9 Replies
View Related
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
Apr 10, 2008
Hello everyone,
I have a little problem here. I need to select data from multiple columns and multiple tables, some of those columns can have NULL value. I have tried few things, and havent find the right way so far. Problem that i am incountering is that when i run SELECT statment as it is right now, i dont get back results which match search parameters but have NULL value in one or more column in select statment. How can i fix this problem?
SELECT Person2role.person2roleID,Person.firstname, Person.lastname, Person.sex
FROM Person LEFT OUTER JOIN
Person2Role ON Person.personID = Person2Role.personID
WHERE (Person.firstname LIKE '%' + ISNULL(@firstname + '%', ''))
AND (Person.lastname LIKE '%' + ISNULL(@lastname + '%', ''))
AND (Person.sex = ISNULL(@sex, Person.sex))
This is a part of SELECT statment..just so you can get the idea of what i am doing..i have few more parameters
I am working with SQL Server 2005 Express Version
Thank you,
Alex
View 10 Replies
View Related
Apr 9, 2014
I have the following query
SELECT [KPI].*
FROM
OPENQUERY(LINKED_OLAP,'SELECT
HEAD(TAIL(DESCENDANTS
(TAIL([Time].[CalendarMonth].[Year],1), [Time].[CalendarMonth].[Month]),4),3) ON COLUMNS,
([Game].[Game Code].&[1] ,
[Code] ...
The last three columns are dynamically generated because they change during time. Next month they will be different.
I like to introduce aliases for them and to have them in the select as 'TWO_MONTHS_AGO','ONE_MONTH_AGO', 'CURRENT_MONTH'
I wonder if exists something like [KPI].(0), [KPI].(1), and etc.. of the OPENQUERY to get the selected columns by their ordering number...
View 1 Replies
View Related
Dec 18, 2007
I have a report parameter named "Schools" which display a list of schools. For example, Alo elementary school, Balo middle school and Calo high school.
When "Alo elementary" is selected the report only display students from that school along with other assessments data fields. same goes for other schools too. But I want to display different data fields for "Calo high school" when it is selected. It is not currently possilbe becasue I am using the same template for all types of schools. There some fields only should be displayed for "Calo high school" but not for any other type of schools.
I can accomplish this by creating two separate report, one for "Calo high school" and the other for other schools. But I want to accomplish this just by creating one report. So when "Alo elementary school" is selected it displays report with certain fields and when "Calo high school" is selected then it displays same fields as "Alo elementary school" but as well as some other fields too in the report. Is this possible? Need help.
View 4 Replies
View Related
Aug 3, 2005
hi,my structure table in database:Amount float(53) not null default 0when i try to run his script:alter table ABC alter column Amount float(53) nullit can only set the Amount to allow null, but can't set the defaultvalue to empty.anyone know how to set the field to allow null and default set toempty, no value.thanks
View 5 Replies
View Related
Feb 29, 2008
Hi all,
One of my columns is the table has some Null values, and I Would like to stop having NULL values into that column any more.
I know, If I alter the column to NOT NULL will throw me an error, since it does a batch update.
Is there any way to achieve this...
Thanks...
View 3 Replies
View Related
May 6, 2015
Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.
Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.
View 3 Replies
View Related
Feb 21, 2014
I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"
The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...
View 0 Replies
View Related
Jan 26, 2015
I would like to force a column to be null all the time. I cannot alter the table structure or alter the code that inserts data.
create table dbo.tblCustomer
(
CID int IDENTITY(1,1) not null,
Fnamevarchar(20) not null,
Lnamevarchar(20) not null,
Extravarchar(20) null
[Code] ....
So when this is executed the field Extra is always NULL
INSERT INTO tblCustomer (Fname, Lname, Extra)
VALUES ('bob', 'smith', 'ignore'), ('jane', 'doe', 'empty')
update dbo.tblCustomer set Extra = 'something'
If I've understood After triggers correctly the data will be written and the trigger will fire and overwrite. To avoid 2 writes
I could create an INSTEAD OF trigger
CREATE TRIGGER TR_I_Customer
ON tblCustomer
INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON
INSERT INTO tblCustomer
(Fname, Lname, Extra)
SELECT Fname, Lname, NULL
FROM Inserted
END
This will not write the "extra" field twice. However if a new Nullable column were added; it would be v.easy to forget to update the Instead Of trigger. Everything would still work OK but I would be effectively ignoring the new column as well.
What I would like in the instead of trigger is do something like this...
UPDATE INSERTED SET Extra=NULL
Continue with insert without supplying column /value list.
What would be the best way of achieving this, trigger is the only way I could think of?
View 6 Replies
View Related
Apr 14, 2015
I have to populate a table of exchange rates which is easy enough however because rates are held on Fridays but I need to make calculations on weekends or holidays I need to populate the Friday rate on non weekends and holidays. I have a sample table below with null values on the weekends for the last 90 days but need a script that will show the Friday exchange rate on Saturday and Sunday
Here was my latest attempt
;with cte as
(
select currxdate, [from], [TO], CurrXRate
from dbo.CurrXchange
)
select a.CurrXDate, a.[From],a.[To]
, isnull(a.CurrXRate, b.currxrate) as 'CurrXRate'
[Code] ....
View 8 Replies
View Related
Oct 6, 1999
Is there any way in SQL Server 6.5 to change a column that was originally set up as nullable to not accept nulls? None of the values in this column contain null values. I need to add a primary key constraint on the column for replication purposes and SQL won't let me because the column is nullable.
Thanks!
Paul
View 1 Replies
View Related
May 10, 1999
Hi,
In my SQL Server database one of the tables column has to be changed from NOT NULL to NULL. The table has got records.
Thanks in advance,
Srini
View 3 Replies
View Related
May 6, 2014
I am working on some data that is JOINing to another table. Not a big thing. In the child table, there are different values for a single ID. I want to be able to select the Max ColorID that is Not Null, for each distinct CarID. The CarID is what I am joining the other table with. I need selecting the distinct row with the Max ColorID that is not Null. All this data is made up, so nothing looks logical in the design.
DECLARE @ColorList TABLE
(
CarID float
, ColorID int
)
INSERT INTO @ColorList
SELECT 1.55948815793043E+15, 9 UNION ALL
SELECT 1.55948815793043E+15, 27 UNION ALL
[code]....
So this would be the resultset:
1.55948815793043E+15, 27
1.62851796905743E+15, 27
1.51964586107807E+15, 9
1.55948815793043E+15, 27
1.47514023011517E+15, 5
1.64967408641916E+15, 27
1.51964586107807E+15, 9
1.56103326128036E+15, 27
1.49856249351719E+15, 9
1.5736407022847E+15, 6
1.64664602022073E+15, 27
1.51964244007807E+15, 27
View 3 Replies
View Related
May 14, 2014
I'd like to be able to offer the option of selecting a project by the ProjectID number, and if a projectID is not supplied then the default result set would be select * from tbl_Projects.
ALTER PROCEDURE [USP_SelectProject]
-- Add the parameters for the stored procedure here
@ProjectNumber as int
AS
BEGIN
[code]....
View 9 Replies
View Related
Jun 24, 2015
I have an instance where a process is trying to insert a Customer into the Customers table and CustomerLastName is a non nullable field. Customer's last name is Null. Should this be the reason this Customer is never in the end table?
View 5 Replies
View Related
Aug 13, 2015
I added a new field to an existing ETL process which uses SSIS to ingest a CSV file. The new field in the file, Call_Transaction_ID, will not always be populated for every record and so can be NULL or empty for certain records.
Here's the problem:After the file is extracted into a staging table, the Call_Transaction_ID field is showing blank or empty when it has no ID for that particular record. The problem is when I try to then ETL this data into a fact table - I'm trying to set the Call_Transaction_ID field to -1 if it is NULL or empty, however SQL Server doesn't see the field as empty even though there is no value in the field so -1 will NEVER return.
Using a WHERE DATALENGTH(Call_Transaction_ID) = 0 returns 0 records, again because SQL Server doesn't see the field as empty or NULL.
What do I do now to get around this? How do I fix it?
View 5 Replies
View Related
Mar 5, 2015
I've a SSRS report with the details in regards to shipping information. Currently in my report the 'Scheduled Ship Date' is set to following Monday, if we miss the 'Requested Delivery Date'. However, I need to change this report to set shipment date('Scheduled Ship Date') to Tuesday and Friday for the shipment having 'Requested Delivery Date' for this week or next week(current week and next week).
For example- Jobs that have shipment date between 3/1/2015 - 3/7/2015 will have 'Scheduled Ship Date' as Tuesday and Friday of current week. If we miss the shipment for this week it should be scheduled for next Tuesday(3/9/2015) and Friday (3/13/2015). I need to follow this for current week(3/1/2015-3/8/2015) and following week(3/9/2015-3/15/2015) and after that all the following weeks will have 'Scheduled Ship Date' as Tuesdays.
Below is the SQL:
USE tempdb;
GO
DECLARE @Date datetime;
SET @Date = GETDATE();
--SET @Date = '2014-07-25';
DECLARE @TEST_DATA TABLE
[code]....
View 8 Replies
View Related
Mar 16, 2015
Why does this code (below) cause a change in the transaction state?
DECLARE @date DATE,
@string VARCHAR(5)= 'test'
BEGIN TRAN
PRINT XACT_STATE()
[Code] ....
View 8 Replies
View Related
Apr 14, 2015
i have a query i.e.
select project, description from table
with results
chocolate | white
chocolate | black
chocolate | brown
sugar | black
vinegar | yellow
i would like to get a result like:
chocolate | white, blakc, brown
sugar | black
vinegar | yellow
so I would like to get all values of one project in one records included with separators/a space in it
View 8 Replies
View Related
Apr 30, 2014
I am creating web application for state and dist wise map. I've the tables like
create table ind_state
(
ind_stat_id int,
ind_state_name varchar(50)
)
insert into ind_state values ('1','Pondi')
[Code] .....
My output is depends on the dist selection so made procedure
alter procedure LAT_TEST_DYNAM0
@dist_id int
as
begin
create table #temp (pincode varchar(10) ,latitude numeric(18,10),longitude numeric(18,10) ,descp varchar(5000))
if @dist_id!=0
[Code] ....
Myself doubt is when @dist_id is null or "0" how to show default value ?
Otherwise above my code is correct?
View 4 Replies
View Related
Sep 9, 2014
declare @user varchar(30) = ''
if(@user is not null or @user <> '')
BEGIN
print 'I am not empty'
END
ELSE
BEGIN
print 'I am empty'
ENd
The output should be 'i am empty' but when i execute this i am getting ' i am not empty'. even i did browse through but i don't find the difference in my logic.
View 9 Replies
View Related
Feb 24, 2015
I have the following tables:
tbl_Person (personID, first_name, lastname)
tbl_student (studentID)
tbl_stupar (personID, StudentID, relationship)
tbl_person_Phone (personID, phone)
I need to list all students who have both parents phone number is null. The parent relationship values 1 and 2 indicates the person is either Mom (value 2) or dad (value 1) of the student. Note: I am using student parent as an example to write my query.
View 4 Replies
View Related
May 2, 2009
I believe query optimizer can leverage the predicate used to define the filtered index. But I came to a situation that's not true:
USE tempdb;
GO
IF OBJECT_ID('Employees') IS NOT NULL
DROP TABLE Employees;
GO
CREATE TABLE Employees
[Code] ....
If we take a look at the query plan. we'll find an unnecessary Nested Loop and Key Lookup operator there with the IS NULL predicate.
Ironically, If I change the IS NULL to IS NOT NULL in both the index and Query, everything goes fine.
View 9 Replies
View Related
May 14, 2014
I have a table with this info:
NrCard numberPersonAuto123456789101112
11111111111111111111User1VW Jetta6,46,46,46,45,825,825,825,825,825,825,826,4
22222222222222222222User2Honda CR-V 13,2113,2113,2112,0112,0112,0112,0112,0112,0112,0113,2113,21
How I can get this result:
NrCard numberPersonAutomonthvalue
11111111111111111111User1VW Jetta16,4
11111111111111111111User1VW Jetta26,4
11111111111111111111User1VW Jetta36,4
11111111111111111111User1VW Jetta45,82
11111111111111111111User1VW Jetta55,82
11111111111111111111User1VW Jetta65,82
[code]....
Should I use unpivot or pivot?
View 2 Replies
View Related
Sep 17, 2014
I am creating dynamic pivot and my column order is as below
[2015-02],[2015-04] [Prior] ,[2014-08],[2014-11]
but i want to display as below:
[Prior],[2014-08],[2014-11],[2015-02],[2015-04]
View 1 Replies
View Related
Oct 15, 2014
This statement adds two additional months to which is fine :
DATENAME(MM,dd.date)+ ' ' + DATENAME(D,dd.date) + ', ' + DATENAME(YY,dd.date)
but if my month is November and two months is added, the year does not change, it stays the same. how do I make the year change when two months are added toward the end of the year.
View 7 Replies
View Related
Mar 17, 2015
If I skip those objects that were deleted, is there a query that I can run off Master/MSDB that will show all database objects and when they were last modified, or create date if it has not been modified?
View 7 Replies
View Related
Aug 22, 2012
I use the merge statement in a sproc to insert, update and delete records from a staging table to a production table.
In the long sql, here is a part of it,
When Matched and
((Student.SchoolID <> esis.SchoolID
OR
Student.GradeLevel <> esis.GradeLevel
OR
Student.LegalName <> esis.LegalName
OR
Student.WithdrawDate <> esis.WithdrawDate
Student.SPEDFlag <> esis.SPEDFlag
OR
Student.MailingAddress <> esis.MailingAddress)
Then update
Set Student.Schoolid=esis.schoolid,
.....
My question is how about if the column has null values in it.for example
if schoolID is null in production table is null, but in staging table is not null, will the <> return true.or if either side of <> has a null value, will it return true.
I don't want it to omit some records and causing the students records not get updated.If not return true, how to fix this?
View 9 Replies
View Related
Mar 21, 2014
I have a table with data in two columns, item and system. I am trying to accomplish is we want to compare if an item exists in 1, 2 or all systems. If it exists, show item under that system's column, and display NULL in the other columns.
I have aSQL Fiddle that will allow you to visualize the schema.
The closest I've come to solving this is a SQL pivot, however, this is dependent on me knowing the name of the items, which I won't in a real life scenario.
select [system 1], [system 2], [system 3]
from
(
SELECT distinct system, item FROM test
where item = 'item 1'
) x
pivot
(
max(item)
[Code]...
View 2 Replies
View Related
Apr 11, 2014
I'm trying to insert multiple records, each containing a null value for one of the fields, but not having much luck. This is the code I'm using:
Use InternationalTrade
Go
CREATE TABLE dbo.ACCTING_ADJUST
(
stlmnt_instr_id varchar(20) null,
instr_id varchar(20) not null,
[Code] ....
View 5 Replies
View Related
