Basic problem (calculating nulls)

Hi Everyone,

I have a basic problem, am calculating two fields, which works fine when both have values (0's included), however when there is nothing populated in the field I don't get a result.

Therefore how do I default a null to a zero so this can be calculated?

Thanks all...Hi

if isnull(myfiel) then
myvar:=0|||Thank you very much will give that a go now.

Basic evaluations in SELECT statement

Is there a way to determine several fields under one CASE or IF in a SELECT statement? For example, I don't think I can do this (though I'd like to):

@.Type INT

AS

SELECT

CASE @.Type

WHEN 1 THEN

Field1 = <some calculation>

Field2 = <Some calculation>

WHEN 2 THEN

Field1 = <some calculation>

Field2 = <some calculation>

END

The alternative, of course, is to evaluate @.Type twice, once for Field1 and again for Field2. But this seems like such a waste. Is there a better way?

Thanks,

One alternative might be to use an inline table -- maybe something like this:

declare @.type integer
set @.type = 2

select type,
field1,
field2
from ( select 1 as type, 1+3+5 as field1, 1*3*5 as field2 union all
select 2, 2+4+6, 2*4*6
) a
where type = @.type

/*
type field1 field2
-- -- --
2 12 48
*/

|||

Jararaca,

You are correct that a CASE can only return a single value.

So in YOUR case, you must use two CASE statements: one for Field1, and then another for Field2.

I think the speed of evaluation of the CASE usually exceeds the speed of retrieving the rows of data and/or formatting the result set.

Dan

|||

If only one condition value, How about this.

Code Snippet

If @.Type = 1

begin

select
Field1 = <some calculation>
Field2 = <some calculation>

end

if @.Type = 2

begin

select
Field1 = <some calculation>
Field2 = <some calculation>

end

|||

Another method of doing this if you are wanting to apply it to an entire table is something like this:

declare @.tString char(20)
set @.tString = convert(char(10), 2*4*6)
+ convert(char(10), 1*3*5)

declare @.demo table
( rid integer,
type tinyint
)
insert into @.demo
select 1, 1 union all
select 2, 2 union all
select 3, 1 union all
select 4, 1 union all
select 5, 2

select rid,
rtrim(substring (@.tString, 10*type-9, 10))
computedField
from @.demo

/*
rid computedField
-- -
1 48
2 15
3 48
4 48
5 15
*/

basic design question

i am a beginner and any response will help me a lot.

i have 20 types of products,
each product has about 40 fields, most of them are common fileds for all products, but some of them are unique.

the number of the unique fields of all the product types is 75.

the question:
Should i build one products table with 75 fields and leave nulls where a field is not property of a product type?
Or should i build a products table for the 30 common fields and a 20 different tables for each product type extra fields?
Or just build 20 different tables each product type?

other ideas are welcome

you can use Named-pair design technique that is very comon in Software as Service application where we have to maintained the list of customer and some customer can have attribtues that are uncomon to other.

create a table called ProductAttributs that can have column like

1- ProductAttributeID,Attribute Name , Data Type ,IsNull

then Create another table called Product Attribute Value that has column

1-ProductID

2-ProductAttribute ID

3- Value

and you have a base table Product which contains the common fields only

so by this first you can create a set of advance product attributes in Product Attribute Table

then bind your product table with Product Advance attributes in product attribute value so by this you can manage dynamic Product attributes that are not comon in ever Product.

|||

hi., please help me, i am a student, i have basic idea on webprogramming specially ASP.NET, actually im using Visual Web Developer.,

i have a new "complicated" project. i really need help.

1st. the data base, this is the diagram of the data, it is not the actual form but its more like this:

if i will do it in the form of access, it will look like this.

LinkFromLinkToexisting1depleted1fots_brae1cdma_fots1 1111AngelesArayat16800AngelesBalanga27000AngelesBalibago63000AngelesClark16000AngelesDinalupihan0000AngelesHermosa0000AngelesMacabebe64000AngelesMagalang16800AngelesOrani0000AngelesSFP7312600AntipoloAntipolo RSU16000AntipoloTanay42000BacoorBacoor RSU166300BacoorCavite City16000BacoorDasmarinas152100BacoorImus63000BacoorKawit16000BaguioBaguio RLU/ELU1111BaguioLa Trinidad1111BalagtasAngat1111BalagtasAngeles1111BalagtasBocaue1111BalagtasCabanatuan1111BalagtasGuiguinto1111BalagtasIba1111BalagtasMalolos1111BalagtasMarilao1111BalagtasMeycauayan1111BalagtasNorzagaray1111BalagtasPandi1111BalagtasSan Jose DM1111BalagtasSta. Maria1111BalagtasTarlac1111BatangasLemery1111BatangasLipa1111BatangasMamburao1111BatangasSan Jose1111BatangasSto. Tomas1111BinalonanAsingan1111BinalonanBaguio1111BinalonanBalagtas1111BinalonanBalungao1111BinalonanDagupan1111BinalonanLaoag1111BinalonanLingayen1111BinalonanMt. Kabuyao RS1111BinalonanPozzorubio1111BinalonanRosales1111BinalonanSan Carlos1111BinalonanSan Isidro RS1111BinalonanSan Manuel1111BinalonanSan Nicolas1111BinalonanSan Quintin1111BinalonanSantiago1111BinalonanSFU1111BinalonanSison1111BinalonanSta. Maria1111BinalonanTayug1111BinalonanTuguegarao1111BinalonanUmingan1111BinalonanUrdaneta1111BinalonanVigan1111BinalonanVillasis1111

if you would look on the table and the diagram, there are just 2 lavel in the table which is in the diagram has 3 level.

thisis the story about that. the table also has 3 level, look on the Boldwords, if you would look on the many "angeles" there is also not so farbeneath a single "angles" but this time, he is under "balagtas", sothis is the levels, "Balagtas --> Angeles--> (bold names underangeles)" how to know the other names with same levels,

@. lookfor names that are inputed in many times in the "LinkFrom" header thenlook for the same name in the "LinkTo" header. so just look closely inthe table so you can under stand the format.

so this is the question, how can i design my database from access?? what will it look like?

after the database, this is the story for how should the web page operate.

@. assumption: that there will be 3 dropdown menu, each menu correspond to each level.

sofor example, if i choose an item in the level 3 dropdown menu, it willshow a table or info (if you would look in the table above, it will bethe numbers' "1's") for:

1. link from level 2 to level 3.
2. link from level 1 to level 2

so to make it short, will should detect he's original link were ever he was link to.

so if i select a value from the level 2 dropdown, it will only show the link from level 1 to level 2

if select a value from level 3 it will show link from level 2 to level 3 and level 1 tot level 2.

it should trace were he is connected so i think the real key here is the design of the database.

actuallythe table i show to you is the real table but i cut it to make itshort. so there are still some 3 level layer out there in the sameformation as the "Balagtas --> Angeles --> under angeles" intwo columns style.

i hope someone can help me tothis,...please. this day is friday here in the philippines, i willhave no class tomorow, only here in the school were i can work and openthis forum. i hope in monday there will be an answer. thanks in advanceto any person.

please feel free to ask it you want to know any clarification to my post.

SALAMAT PO...

Baffled

I have a table called lab_results_import, and one of the fields is called
qualifier_id. The table was empty, I imported 1 Excel spreadsheet with 150
0
records. 134 of these records have an empty cell in the Excel spreadsheet i
n
the qualifier_id column. The ss imports fine, but I am baffled by this quer
y
that I am running on the table. The query is
SELECT *, qualifier_id
FROM lab_results_import
WHERE (qualifier_id = '')
which returns 134 records, BUT
SELECT *, qualifier_id
FROM lab_results_import
WHERE (qualifier_id = ' ')
also returns 134 records, and regardless of how many spaces are in my
(qualifier_id = '') where clause, the query returns 134 records.
Anybody know what is going on, and/or how to search for a field containing a
certain number of spaces.
Thanks a lot.
ArcherWhat data type is this column (qualifier_id)? Is it nullable?
ML|||>> Anybody know what is going on, and/or how to search for a field
It is a known behavior where the trailing blanks of are being trimmed off
for comparison. The workaround is to use a character which is not in your
column like '~' or '*' like:
SELECT *
FROM tbl
WHERE '~' + col + '~' = '~' + SPACE( 10 ) + '~' ;
Note that such padding in general, will avoid the usage of any existing
indexes in the column, though.
Anith|||Try,
select
c1, datalength(c1)
from
(
select cast(space(0) as varchar(25))
union all
select space(1)
union all
select space(2)
union all
select space(3)
union all
select space(4)
) as t1(c1)
where
'.' + c1 + '.' = '.' + space(3) + '.'
-- or
select
c1, datalength(c1)
from
(
select cast(space(0) as varchar(25))
union all
select space(1)
union all
select space(2)
union all
select space(3)
union all
select space(4)
) as t1(c1)
where
c1 = ' ' and datalength(c1) = 3
go
Joe Celko explained one time why sql server seems to ignore trailing spaces
when comparing varchar data type, but I could not find it.
AMB
"bagman3rd" wrote:

> I have a table called lab_results_import, and one of the fields is called
> qualifier_id. The table was empty, I imported 1 Excel spreadsheet with 1
500
> records. 134 of these records have an empty cell in the Excel spreadsheet
in
> the qualifier_id column. The ss imports fine, but I am baffled by this qu
ery
> that I am running on the table. The query is
> SELECT *, qualifier_id
> FROM lab_results_import
> WHERE (qualifier_id = '')
>
> which returns 134 records, BUT
> SELECT *, qualifier_id
> FROM lab_results_import
> WHERE (qualifier_id = ' ')
> also returns 134 records, and regardless of how many spaces are in my
> (qualifier_id = '') where clause, the query returns 134 records.
> Anybody know what is going on, and/or how to search for a field containing
a
> certain number of spaces.
> Thanks a lot.
> Archer|||varchar(10)
and nulls are allowed. I actually want the cells to be null and I am trying
to avoid having cells with emtpy spaces.
A
"ML" wrote:

> What data type is this column (qualifier_id)? Is it nullable?
>
> ML