Getting Nulls When Pivoting

Aug 29, 2013

I'm getting nulls on execution of the below query. I have tried both isnull and coalesce, but to no avail.

select *
from
(
select
a.BusinessUnitCode,
a.AdmitCCYYMM As Date, a.[Count of Admits]*1.0 / m.MemberCount * 12000 AdmitsPer1000

[Code] .....

View 3 Replies


ADVERTISEMENT

Column Allows Nulls I Want To Change No Nulls Allowed

May 16, 2006

When i do a select on my emplee table for rows with null idCompany i dont get any records

I then try to modify the table to not allow a null idCompany and i get this error message:

'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.

This sux

View 4 Replies View Related

Testing Permutations Of Nulls And Not Nulls

Feb 17, 2008

is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?

View 4 Replies View Related

Pivoting

Sep 21, 2007

hello guys !!!

i have a table as

id date data
1 3/10/2007 "hello"
1 4/10/2007 "hi"
2 3/10/2007 "hello"
2 4/10/2007 "why"


i need the output like

id 3/10/2007 4/10/2007
1 "hello" "hi"
2 "hello" "why"


Any idea ???
any means to do it??

View 5 Replies View Related

Pivoting

May 7, 2008



Hi ,


My source file looks like this.








Month

Mar-07
Apr-07
May-07







Non-Accruals

$304,732,515
$307,051,978
$308,274,921

REO

$115,072,839
$123,957,394
$149,744,174

Home Equity Total NPA

$419,805,354
$431,009,372
$458,019,095


Destination table should look like this.










Month
HE Non-Accruals
HE REO
Home Equity Total NPA

Mar-07
$ 304,732,515
$ 115,072,839
$ 419,805,354

Apr-07
$ 307,051,978
$ 123,957,394
$ 431,009,372

May-07
$ 308,274,921
$ 149,744,174
$ 458,019,095



Can anyone help me to write t-sql code to transfer data into destination table as its shown above.
Thanks

View 1 Replies View Related

Pivoting A CTE

Apr 25, 2008



I've done both a CTE and a pivot, but never together. I did see a few examples out there and followed them, but mine isn't working. I have four 'tables' within the CTE, and then my final select statement joins all of them and attempts to pivot. My error is:


Msg 156, Level 15, State 1, Line 90

Incorrect syntax near the keyword 'PIVOT'.



WITH TwoYrsActual (LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,"06Actual")

AS (

SELECT DISTINCT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD,

ActualSalesAmtUSD AS '06Actual'

FROM dbo.FactInventoryHistory HIS

INNER JOIN DimLocation LOC ON

His.LocationType_Code = Loc.LocationType_Code

INNER JOIN DimDate Date ON

His.Week_idx = Date.Date_Idx

INNER JOIN DimScenario Scenario ON

His.Scenario_Idx = Scenario.Scenario_Idx

WHERE Scenario_Code = 'FY06A'

GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,

ActualSalesAmtUSD

),

OneYearActual (LocationType_Name, FiscalYear_Name,FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,

"07Actual")

AS (

SELECT DISTINCT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD,

ActualSalesAmtUSD AS '07Actual'

FROM dbo.FactInventoryHistory HIS

INNER JOIN DimLocation LOC ON

His.LocationType_Code = Loc.LocationType_Code

INNER JOIN DimDate Date ON

His.Week_idx = Date.Date_Idx

INNER JOIN DimScenario Scenario ON

His.Scenario_Idx = Scenario.Scenario_Idx

WHERE Scenario_Code = 'FY07A'

GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,

ActualSalesAmtUSD

),

PresentYrActual (LocationType_Name,FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,

"08Actual")

AS (

SELECT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD,

ActualSalesAmtUSD AS '08Actual'

FROM dbo.FactInventoryHistory HIS

INNER JOIN DimLocation LOC ON

His.LocationType_Code = Loc.LocationType_Code

INNER JOIN DimDate Date ON

His.Week_idx = Date.Date_Idx

INNER JOIN DimScenario Scenario ON

His.Scenario_Idx = Scenario.Scenario_Idx

WHERE Scenario_Code = 'FY08A'

GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,

ActualSalesAmtUSD

),





PresentYrPlanned (LocationType_Name,FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedTY,

ActualSalesAmtUSD)

AS (

SELECT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD AS PlannedTY,

ActualSalesAmtUSD

FROM dbo.FactInventoryHistory HIS

INNER JOIN DimLocation LOC ON

His.LocationType_Code = Loc.LocationType_Code

INNER JOIN DimDate Date ON

His.Week_idx = Date.Date_Idx

INNER JOIN DimScenario Scenario ON

His.Scenario_Idx = Scenario.Scenario_Idx

WHERE Scenario_Code = 'FY08P'

GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,

ActualSalesAmtUSD

)





select * from (

SELECT PresentYrActual.LocationType_Name, PresentYrActual.FiscalPeriodOfYear, PresentYrActual.FiscalWeekOfPeriod, PresentYrActual.FiscalWeekOfYear, PresentYrActual.LocationType_Code, PresentYrActual.Scenario_Idx, PresentYrActual.Scenario_Code, "08Actual", "07Actual", "06Actual",PlannedTY

FROM PresentYrActual

FULL OUTER JOIN OneYearActual ON

PresentYrActual.FiscalWeekOfYear = OneYearActual.FiscalWeekOfYear AND

PresentYrActual.LocationType_Code = OneYearActual.LocationType_Code

FULL OUTER JOIN TwoYrsActual ON

PresentYrActual.FiscalWeekOfYear = TwoYrsActual.FiscalWeekOfYear AND

PresentYrActual.LocationType_Code = TwoYrsActual.LocationType_Code

FULL OUTER JOIN PresentYrPlanned ON

PresentYrActual.FiscalWeekOfYear = PresentYrPlanned.FiscalWeekOfYear AND

PresentYrActual.LocationType_Code = PresentYrPlanned.LocationType_Code

)

PIVOT

(

SUM(PlannedTY)

FOR PresentYrActual.LocationType_Code IN (C, M))

AS pivottable

View 13 Replies View Related

Do Not Keep NULLS Using SSIS Bulk Insert Task - Insert Empty Strings Instead Of NULLS

May 15, 2008

I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.

The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.

The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.

So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).

I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.

Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?

From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.

View 2 Replies View Related

Pivoting A Recordset

Nov 12, 2004

I have a reference table that looks like this

id | value
==========
1,abc
1,def
1,ghi
2,def
2,jkl

I want these values to go horizontally into another table matched on id, to look like this:

id | value
========
1,abc def ghi
2, def jkl

I built a cursor to parse through it but was taking forever (there's 185,000 records in the reference table). Any idea's on the fastest way to perform this function?

View 1 Replies View Related

Related To Pivoting

Jan 4, 2006

there is a prblem with data in pivoting the table.
problem is like this--

there is some data 'xy' and some data 'xy '. when i m giving 'xy' as a pivot key value it doesent recognise 'xy ' and viseversa..

i can't reduce the size of the datatype coz there is some data of diffrent size as 'abcd'.

this data is loaded from excel sheet to sql sever table.

wht can i do for this problem.

is there any method to truncate the indivisual data, i m using nvarchar datatype for this.

View 2 Replies View Related

Pivoting Query On T-sql

Jul 20, 2005

gud day.please help me. im working right now on a case study that willretrieve/produce a simple report on sql. my problem is I dont know howto pivot queries like in access. please help me. thanks

View 2 Replies View Related

Pivoting Table

Jan 28, 2008



Hello there I have a table like so

Q1 Q2 Q3 Q4 Sales Rep
1 5 6 0 John

2 3 6 0 Rod
3 2 3 0 Gill
4 5 1 0 Guy



And I would like to rearrange it like

John Rod Gill Guy
Q1
Q2
Q3
Q4



Is this something I can accomplish with the PIVOT or UNPIVOT commands? Is there another way?

View 9 Replies View Related

Pivoting A Table

May 7, 2008



Hi ,

I want to pull data from XLs file and put them in a table.

Source file looks like this.








Month

Mar-07
Apr-07
May-07







Non-Accruals

$304,732,515
$307,051,978
$308,274,921

REO

$115,072,839
$123,957,394
$149,744,174

Home Equity Total NPA

$419,805,354
$431,009,372
$458,019,095


Destination table should look like this.
Date(Only the first row ) in XL file is in the following format.
DATE(YEAR($O1)-1,MONTH($O1)-1,DAY($O1)).
From second row onwords data format is Money type.
I hope I need to convert the date row into SQL datetime type too. Otherwise it comes as NULL.










Month
Non-Accruals
REO
Home Equity Total NPA

Mar-07
$ 304,732,515
$ 115,072,839
$ 419,805,354

Apr-07
$ 307,051,978
$ 123,957,394
$ 431,009,372

May-07
$ 308,274,921
$ 149,744,174
$ 458,019,095



Can i create a SSIS package to do this job? if so , How? I'm not sure which transformation should i used and how?
Hope some one can help me

Thanks

View 5 Replies View Related

Pivoting Data

Apr 28, 2008


Hi,

Can anybody help me with the following...I want to Pivot the following data






Pcode
Year
Month
Mcode
Value



2
2008
March
EN10A
56349.1



2
2008
March
EN10B
1061.6



2
2008
March
EN10C
2.67



2
2008
March
EN10D
8370



2
2008
April
EN10A
819.31



2
2008
April
EN10B
245.09



2
2008
April
EN10C
33.38



2
2008
April
EN10D
2.31



























After Pivot...the data should be like this






















Pcode
Year
Month
EN10A
EN10B
EN10C
EN10D

2
2008
March
56349.1
1061.6
2.67
8370

2
2008
April
819.31
245.09
33.38
2.31



Can we use Pivot function or is their a easier way for doing this...Also the MCodes are dynamic so now there are only 4 distinct MCodes but they may be more than four...

View 4 Replies View Related

Pivoting Row Values Into Colums

Oct 11, 2004

I’d like to get some data which includes month values bound to a data grid. The data is stored in a table like so:

Measure Month Value
A June 10.00
A July 9.00
A Aug 11.00
B Jun 100.00
B Jul 98.00
B Aug 99.00
C Jun 0.75
C Jul 0.8
C Aug 0.91

I need to report the data like this:
Measure Jun Jul August
A 10 9 11
B 100 98 99
C 75% 80% 91%

This was simple in classic ASP. Just use two recordsets, create a new table cell for each month using the first recordset then use the second recordset for each row.

But is there a way to “Pivot� or rotate the data so I can use the DataGrid? It only seems possible if each month has its own column field in table. Each month add a new column.

I can restructure the database, if needed.

I thought about creating a Cube, but that seems to have its own limitations. For example what if I want to add a Column for Quarter and year totals? I don’t think it’s possible to show multiple planes like that in an query of a cube.

It seems that this might be resolved in the presentation layer or the data layer. Any Suggestions?

View 1 Replies View Related

How To Set Null Values To Zero When Pivoting

Jun 4, 2008

I have a table with the following structure

[ID] [A] [B] [Cnt] [Qty]
1 s v1 4 40
2 g v1 2 23
3 p v2 7 22
4 l v3 1 6
5 v v4 7 5


Since I do not know before hand what [B] will be , I have created a dynamic script to pivot the data :


Select *
from ( select [ID],[A],[B],sum([Cnt]) AS Cnt
group by [ID],[A],[B]
) a
PIVOT ( sum(cnt) for [B] in (@list)) b


Now thequstion is :
1. How do I amend this qry to eliminate nulls in my output
[ID] [A] [v1] [v2] ......[vn]
1 s null 9
2 g 10 null


2. Is it possible to include both [cnt] and [Qty] traspose along
[B] = v1,v2 ...... vn


Any advice will be most welcome.

Thanks

View 4 Replies View Related

SQL Server 2000 - PIVOTING

Nov 29, 2007

I need some help here in Pivoting the table.
I have the table with the Following Columns.. and here is the sample data. Assume that below table will just have one weeks worth of data.

I can write a stored Proc using cursor, but I just want to learn how
to do it with using cursors

TD-------- Acct------ RouteID----Symbol---- Quantity----
---------- ---------- ---------- ---------- -----------
11/26/2007 40B91209-- CSFB------ GBL--------100
11/26/2007 40B91209-- SIGMA-X----TDY--------100
11/26/2007 4W3L1209-- CSFB------ BIDZ------ 1300
11/26/2007 4W3L1209-- CSFB------ SURW------ 100
11/27/2007 HFS10003-- SIGMA-X----URBN------ 500
11/27/2007 RAM10001-- ISE--------DSCP------ 300
11/27/2007 RAM10001-- SIGMA-X----HYC--------468
11/28/2007 HFS10003-- CSFB------ ARO--------5900
11/28/2007 HFS10003-- CSFB------ CAB--------1300
11/28/2007 HFS10003-- CSFB------ PNRA------ 4600
11/29/2007 RAM10001-- CSFB------ DSCP------ 175
11/29/2007 HFS10003-- CSFB------ CL-------- 220
11/29/2007 WIL10008-- SIGMA-X----CBM--------1400


The output should look some thing like this. If some can help me

AcctNum----RouteID--symbol--MON--TUE--Wed--THU---- FRI
WIL10008---SIGMA-X--CBM-----0----0----0----1400-- 0

Thanks for any help.

View 2 Replies View Related

Pivoting Data Works In One Db But Not Another

Mar 20, 2008

In the following code examples I got to learn PIVOT, I found an error for SUM. However when this is ran against the AdventureWorks db it works fine. Notice it is using a table variable and not an actual table. What do I need to do to my db to get this to work?

Thanks!




declare @sales table
(
[Year] int,
Quarter char(2),
Amount float
)

insert into @sales values(2001, 'Q1', 70)
insert into @sales values(2001, 'Q1', 150)
insert into @sales values(2002, 'Q1', 20)
insert into @sales values(2001, 'Q2', 15)
insert into @sales values(2002, 'Q2', 25)
insert into @sales values(2001, 'Q3', 50)
insert into @sales values(2002, 'Q3', 20)
insert into @sales values(2001, 'Q4', 90)
insert into @sales values(2001, 'Q4', 80)
insert into @sales values(2002, 'Q4', 35)

select * from @sales
PIVOT (
SUM(Amount)
for Quarter in (Q1, Q2, Q3, Q4)) as p

Yields...
Incorrect syntax near 'SUM'.

View 2 Replies View Related

Pivoting A Results Table MSSQL

Nov 12, 2007

I have a query in which I would like to pivot the resultsI presently have my results displaying something like this. OrderNumber    Product       OrderQuantity---------------        ---------------    ----------------------0608                Prod1          30608                Prod2          120608                Prod3          2 What I am after is for the results to display something like this.OrderNumber    Prod1   Prod2   Prod3 
---------------        ---------   ---------  ---------
0608                3           12       2 This is using SQL Server ver 8.0  

View 3 Replies View Related

T-SQL (SS2K8) :: GROUP BY CUBE Aggregation - Pivoting On 2 Totals

Aug 1, 2014

I'm trying using the GROUP BY CUBE aggregation. Currently I have this working as such:

SELECT
ISNULL(CONVERT(VARCHAR,Date), 'Grand Total') Date
,ISNULL([1 Attempt],0) [1 Attempt]
,ISNULL([2 Attempts],0) AS [2 Attempts]
,ISNULL([3 Attempts],0) AS [3 Attempts]
,ISNULL([4 Or More],0) AS [4 Or More]

[Code] .....

Basically this is used to work similar to a Pivot table in excel. My data will look as follows:

Date 1 Attempt2 Attempts3 Attempts4 Or MoreTotal
2012-09-04 239 68 2 8 317

The problem I'm having is the Total column. Although this is summing the line values correctly, the total should be based on the sum not count of attempts i.e. 1 x 239, 2 x 68, 3 x 2, 4 x 8

If I change the FROM select clause to use SUM instead of COUNT

SELECT
CONVERT(DATE,[Date]) Date
,ISNULL(AttemptsFlag,'Total') as Attempt
,SUM(NoOfTimes) AS Totals
FROM
XXXXX
GROUP BY
CUBE([Date],AttemptsFlag)

It will return the correct Total amount but not the right numbers for the Attempt groupings...

View 1 Replies View Related

SSRS -- Data Driven Subscription And Pivoting For Dynamic Parameter Mapping

Feb 12, 2007

Hi,

For the Data Driven Subscription in SSRS we are using the following stored procedure

In Step 3 - Create a data-driven subscription



create procedure spRSGetReportSettings

(

@ReportID as integer

) as

begin

set nocount on

declare @t as table(y int not null primary key)

declare

@cols as nvarchar(max),

@y as int,

@sql as nvarchar(max)

set @cols=stuff(

(select N',' + quotename(y) as [text()]

from (select ParameterName as y from Reportsettings where reportid=1) as Y

order by y

For XML Path('')),1,1,N'');

set @sql=N'select * from

(select reportid,parametername, parametervalue from ReportSettings where reportid= ' + Cast(@ReportID as varchar(5)) +' ) as D

pivot(min(parametervalue) for parametername in(' + @cols +N')) as p'

exec sp_executesql @sql

end



Basically the idea is to maintain a single report parameter setting table for multiple reports.

Structure of the table is as given below

ReportID, ParameterName, ParameterValue.

Using Pivot we can generate the ParameterName/ParameterValue combinations for each report. This stored procedure is working fine in query editors(Management Studio)

But, in SSRS it is giving any results.

In Step 4 - Create a data-driven subscription,

Get the value from the database drop down, I am not getting any database columns.

Please help.

Kumar

View 3 Replies View Related

NULLs

Sep 16, 2004

Do NULLs take up space on the Database? I mean is room allocated for them?

View 9 Replies View Related

Nulls

Apr 5, 2004

Can someone tell me what to do if several NULLS are showing up within tables. Is there an easy step to locate all NULLS and delete them within the table?

Can someone provide detailed infromation on how to delete NULLS within tables. I'm new o the SQL side so I hope I have explained what it is that I'm looking to do. Our in house programmer said that the NULLS maybe causing a problem with the software communicating with the SQL Server software.

View 12 Replies View Related

Nulls

Apr 12, 2008

I have a page with 100 numeric fields. SQL insert crashes lest there is a number in every field. Any way around this?

View 1 Replies View Related

Two Nulls

Feb 9, 2007

Hi

i have a table t1 and wish to have to fk's now i want to be able to set it up so that only one of them can be null example:

t1
id___________fk1________fk2

1____________6__________NULL
2____________NULL_______3
3____________7__________NULL
4____________NULL_______NULL <<<<<<< This should not be allowed

thanks
james

View 2 Replies View Related

Nulls

May 21, 2007

Hi i am new to SQL, and i have a data base thats got NULLS in it
is there a query i can run, which can take the NULL value out ?
anything will be greatly appreciated.

View 14 Replies View Related

NULLS And ''

Feb 6, 2007

Hi,I don't have any specific details as I do not really understand whythis is happening but we have a customer database that we have beenusing and queries on text fields have begun returning empty stringvalues instead of NULL.So for example:SELECT TAB1.DESCRIPTIONFROM TABLE1 TAB1WHERE TAB1.DESCRIPTION IS NOT NULLORDER BY 1may have returned 50 records in the past (purely an example). It isnow returning an additional record first that appears just to be ''.Now I am not too sure where to begin looking into this. ODBCconnections, collation settings? I just am not sure where to begin.The column will not have had '' inserted into it; therefore it shouldbe NULL.I have posted this mainly so I can see if anybody else has seen thissort of behaviour - I cannot see why this has suddenly happened.The collation setting on our server is different to that of thecustomer DB, but this shouldn't make a difference should it?If anybody has any ideas then I can post some more information.Thanks,Paul

View 12 Replies View Related

Nulls

Mar 20, 2007

create table t1(c1 int, c2 varchar(10))insert t1 values(1,'Hello')insert t1 values(2,'')insert t1 values(3,NULL)select *from t1c1c21Hello23NULLselect *from t1where c2 = ' 'c1c22select *from t1where ltrim(rtrim(c2)) is nullc1c23NULLThe last query should have result as following. However sql server2000 does no list row c1 = 2.c1c223NULL

View 6 Replies View Related

Are Nulls Bad

Jul 20, 2005

HiI am probably going to regret asking this because I'm sure you are going totell me my design is bad 8-) ah well we all have to learn....anywayI often use Nulls as a marker to see if certain tasks have been completed.A typical example would be a column say invoice_valuewhen new work is entered and a new record is appended, I leave theinvoice_value column as NULL, so if I want a View for uninvoiced work Icheck for the invoice_value being equal to NULL, and alternatively if I wantinvoiced work, I check for not null. I did it this way to save putting inanother column that needed to be set to TRUE or FALSE. (I do similar thingselsewhere aswell)so far everything seems to work OK but reading some old stuff on Google Iget the feeling that NULLS should be left alone, have I done wrong?many thanksAndy

View 3 Replies View Related

NULLS

Nov 26, 2007

Hello all

I have a colunm - Datatype : nvarchar - Contains: Numeric, characters, strings, date and NULLS

I am getting a the value of each row of this colunm in a variable say @var1

Now I am trying to do some functionalities depending on this variable.

The requirement is if this variable is not null I need to do some stuff.

So I write simple query:

IF (@var1 IS NOT NULL)
BEGIN
......
END

when i am doing this I get an Error:


Error converting data type nvarchar to numeric.

realizing this I change the condition to
IF ( LEN (@Var1) > 1)

then
IF (LEN (@Var1) <> 0)

but no use. I get the same error.

I even use cast
I get the error

Conversion failed when converting the nvarchar value 'null' to data type int.


IF(CAST (@OldValue AS INT)IS NOT NULL)
OR

IF(LEN (CAST (@OldValue AS INT)) <> 0)


Can somebody help in guiding the best practice to do this?





View 8 Replies View Related

Column Does Not Allow Nulls

Nov 4, 2007

 Hi,This should be straight forward, but I've searched high and low on the net.I have a FORM which allows me to INSERT data, SQL Server 2005 backend. I populate the all the mandatory fields. But when I click on the Insert button, it won't let me save and says:Column 'PERSONAL_ID' does not allow nullsI'm
using tableadapters, business logic layers etc. and pausing clearly
shows that the values from the form are being passed to the procedure
that I have created "AddNewRecord". And PERSONAL_ID is definitely not
NULL! It fails on Line 971: Me.Rows.Add(row)PERSONAL_ID
is a primary key which I generate. The pause also shows that for
PERSONAL_ID it says " {"Conversion from type 'DBNull' to type 'String'
is not valid."}Function AddNewRecord looks like this:<System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, True)> _
Public Function AddNewRecord(ByVal PERSONAL_ID As String, ByVal SURNAME
As String, ByVal CHRISTIAN_NAME As String, ByVal SEX As String, _
ByVal FAMILY_POSITION As String, ByVal FAMILY_ID As String, ByVal
ADDRESS_1 As String, ByVal ADDRESS_2 As String, _ ByVal ADDRESS_3
As String, ByVal ADDRESS_4 As String, ByVal ADDRESS_5 As String, ByVal
ADDRESS_6 As String, ByVal POSTCODE As String, ByVal COUNTRY As String,
ByVal ORG_ID As String) As Boolean ' create a new details row instance Dim details As New smDetails.smTbl_DetailsIDDataTable Dim detail As smDetails.smTbl_DetailsIDRow = details.NewsmTbl_DetailsIDRow details.AddsmTbl_DetailsIDRow(detail)...my formview code is this: <InsertItemTemplate> PERSONAL_ID: <asp:TextBox ID="PERSONAL_IDTextBox" runat="server" Text='<%# Bind("PERSONAL_ID") %>' AutoPostBack="True" /> <br /> SURNAME: <asp:TextBox ID="SURNAMETextBox" runat="server" Text='<%# Bind("SURNAME") %>' AutoPostBack="True" /> <br /> CHRISTIAN_NAME: <asp:TextBox ID="CHRISTIAN_NAMETextBox" runat="server" Text='<%# Bind("CHRISTIAN_NAME") %>' /> <br /> SEX: <asp:TextBox ID="SEXTextBox" runat="server" Text='<%# Bind("SEX") %>' /> <br /> FAMILY_POSITION: <asp:TextBox ID="FAMILY_POSITIONTextBox" runat="server" Text='<%# Bind("FAMILY_POSITION") %>' /> <br /> FAMILY_ID: <asp:TextBox ID="FAMILY_IDTextBox" runat="server" Text='<%# Bind("FAMILY_ID") %>' /> <br /> ADDRESS_1: <asp:TextBox ID="ADDRESS_1TextBox" runat="server" Text='<%# Bind("ADDRESS_1") %>' /> <br /> ADDRESS_2: <asp:TextBox ID="ADDRESS_2TextBox" runat="server" Text='<%# Bind("ADDRESS_2") %>' /> <br /> ADDRESS_3: <asp:TextBox ID="ADDRESS_3TextBox" runat="server" Text='<%# Bind("ADDRESS_3") %>' /> <br /> ADDRESS_4: <asp:TextBox ID="ADDRESS_4TextBox" runat="server" Text='<%# Bind("ADDRESS_4") %>' /> <br /> ADDRESS_5: <asp:TextBox ID="ADDRESS_5TextBox" runat="server" Text='<%# Bind("ADDRESS_5") %>' /> <br /> ADDRESS_6: <asp:TextBox ID="ADDRESS_6TextBox" runat="server" Text='<%# Bind("ADDRESS_6") %>' /> <br /> POSTCODE: <asp:TextBox ID="POSTCODETextBox" runat="server" Text='<%# Bind("POSTCODE") %>' /> <br /> COUNTRY: <asp:TextBox ID="COUNTRYTextBox" runat="server" Text='<%# Bind("COUNTRY") %>' /> <br /> ORG_ID: <asp:TextBox ID="ORG_IDTextBox" runat="server" Text='<%# Bind("ORG_ID") %>' /> <br /> <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert" /> <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" /> </InsertItemTemplate> Any pointers in the right direction would be appreciated. Thanks in advanceTushar

View 3 Replies View Related

Need To Get NULLs Into The Action..

Mar 11, 2008

I have the following query:
 SELECT tblArticleCategory.ACategoryID, IsNull(ParentCategory, '') + IsNull( ' > ' + ACategoryName, '') AS Category from tblArticlecategory
RIGHT OUTER JOIN (SELECT ACategoryName as ParentCategory, ACategoryID from tblArticleCategory WHERE AParentID is null AND aActive=1) AS Parent
on parent.ACategoryID = tblArticleCategory.AParentID
WHERE tblArticleCategory.AActive=1ORDER BY Category
This produces an ordered list of the parent categories and subcategories from the table as long as there is a subcateogry directly below one of the parent categories. If there isn't one directly below the parent category, the parent category is dropped from the list. None of the parents with the NULLS back to back are in the list.

6 Arts & Entertainment > Dance2 Arts & Entertainment > Movies13 Computers > E-Learning4 Computers > Hardware14 Computers > Java16 Computers > Link Popularity17 Computers > Microsoft.net15 Computers > RSS5 Computers > Software8 Real Estate > Finance
How do I fix the query so that Automotive, Business, Cancer and Communications are included in the list?

View 9 Replies View Related

Allow Nulls Cannot Be Changed

Apr 9, 2003

I have a table as part of a replicated database where i need to change the allow nulls value (set it to not allow nulls) but when i try to save the table i get this error.

'CONTACTS' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.CONTACTS' because it is being used for replication.

will i have to stop replication to make the change or is it worse and i have to delete the publication and start all over again?

View 7 Replies View Related

How To Sort NULLs?

Jul 13, 2004

Hi All,

I have a table with 3 columns. Product, Location and Value. The data looks like this:

NULL NULL 100
Atlanta NULL 50
Atlanta Cookie1 30
Atlanta Cookie2 20
Dallas NULL 120
Dallas Cookie1 80
Dallas Cookie2 40

This table gets filled with a Groupby with Rollup option. The NULLS show subtotals/total. Is there a way to build a query that returns the results with NULLs at the bottom of each section like:


Atlanta Cookie1 30
Atlanta Cookie2 20
Atlanta NULL 50
Dallas Cookie1 80
Dallas Cookie2 40
Dallas NULL 120
NULL NULL 100

Thanks,

Shab

View 4 Replies View Related







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