default value

i want a defauld vaule in my query to come for each row(50 row)

say i have address table with column name,street1, state,pin but no column for city--

How do i return a query for each name(50 row) which has a default value of city as 'Miami' for all 50 rows
the city name is not there in databse at all

any help will be appreciatedSELECT street1, state,pin,'Miami' as city
FROM yourTable|||thanks how stupid of Meeeeeeeeeeee op|||futher to that can i put city value as Miami for say record 1,5,10,15,40
and the rest as 'Davie' ??|||Are 1,5,10,15,40 field values in some kinf of primary key?

If so try something like this...

SELECT (CASE WHEN fieldname in (1,5,10,15,40) THEN 'Miami' ELSE 'Davie' END) as City
FROM yourTable

This is not really scalable. What happens when you have 50K or 500K records you want marked as Miami. You might just want to add a city column.|||and what if you want to include miramar, plantation, coral gables, and dania? :D
best to create a city column and populate accordingly
you can add a column after the table is created

Books Online{Adding and Deleting Columns}

then you could add a default constraint to the column to specify miami
Books Online{Default Constraints}

Sample

create database helpme
go
use helpme
go
create table t1
(
c1 int
,c2 int
)
go
alter table t1 add city varchar(20) null
go
alter table t1 add Constraint dCity Default('Miami') for city
go
insert t1(c1,c2) values (1,2)
go
select * from t1
go
--drop table t1|||GOOd but some of it was greeek to me since i came over from oracle

Can you give example from say pubs here is the query
and i want to change the city value from this query to say 'miami,davie, ftlauderdale

how do i put in a table and update the table pull out the output and drop the table ?

And can i do it in the same database?

select e.au_ord,a.city,a.state, a.au_lname,t. price, t.ytd_sales,t.pub_id
from authors a, titles t, titleauthor e
where a.au_id = e.au_id
AND t.title_id = e.title_id

thanks|||GOOd but some of it was greeek to me since i came over from oracle

Can you give example from say pubs here is the query
and i want to change the city value from this query to say 'miami,davie, ftlauderdale

how do i put in a table and update the table pull out the output and drop the table ?

And can i do it in the same database?

select e.au_ord,a.city,a.state, a.au_lname,t. price
from authors a, titles t, titleauthor e
where a.au_id = e.au_id
AND t.title_id = e.title_id

thanks HElp those whom you can ?|||how come no body could do this ?|||Do what?

-PatP|||if you could help me understand what it is that you want, i could help you better.
help me to help you
Help Me.
To help You...|||Multi table query --> put in dummy table ( so create and insert)

Update --> dummy table with some condition
(Say change city name from a to anew
b to bnew etc)
I have a complex calculation to do

Return the updated output from this dummy table

drop the dummy table

So here is the query from pubs database

select e.au_ord,a.city,a.state, a.au_lname,t. price
from authors a, titles t, titleauthor e
where a.au_id = e.au_id
AND t.title_id = e.title_id

thanks|||I'm going WAY out on a limb here, because I still am not sure I understand your question, but does this help:CREATE TABLE city_renames (
city VARCHAR(20) NOT NULL
CONSTRAINT XPKcity_renames
PRIMARY KEY (city)
, newname VARCHAR(20) NOT NULL
)

INSERT INTO city_renames (city, newname) VALUES ('Oakland', 'Otherside')
INSERT INTO city_renames (city, newname) VALUES ('Palo Alto', 'Andy''s Place')
INSERT INTO city_renames (city, newname) VALUES ('Corvallis', 'HP-41c')

SELECT
e.au_ord
, Coalesce(r.newname, a.city)
, a.state
, a.au_lname
, t. price
FROM authors AS a
JOIN titleauthor AS e
ON (e.au_id = a.au_id)
JOIN titles AS t
ON (t.title_id = e.title_id)
LEFT JOIN city_renames AS r
ON (r.city = a.city)

DROP TABLE city_renames-PatP|||how come no body could do this ?

Dude, have YOU even TRIED looking in Books Online to figure out how SELECT statements, UPDATEs, and say Temporary tables/table variable work?

Taunting won't get you far around here. Your problem is probably easy to solve, but you have yet to explain it clearly.|||Sorry i didnot mean't to taunt just asking
I dont know how Temporary tables/table variable work

i am trying this example since everyone has pubs with them mine is more complecated,for i have to write many conditionaly statements inbetween

So let me try again

first start with a query that i gave ie (mine is more complex)

select e.au_ord,a.city,a.state, a.au_lname,t. price
from authors a, titles t, titleauthor e
where a.au_id = e.au_id
AND t.title_id = e.title_id

Store this whole result of above query into a dummy table <-- this is what i
dont know ?

Do changes to the dummy table --(here i have a complex change with an if
then else statement city
name is just an example )

do a select from this dummy table only !!!!!

return the value from dummy table and drop the dummy table|||I like my previous idea better, but how about:SELECT e.au_ord,a.city,a.state, a.au_lname,t. price
INTO #foo
FROM authors a, titles t, titleauthor e
WHERE a.au_id = e.au_id
AND t.title_id = e.title_id-PatP|||SELECT e.au_ord,a.city,a.state, a.au_lname,t. price
INTO #foo
FROM authors a, titles t, titleauthor e
WHERE a.au_id = e.au_id
AND t.title_id = e.title_id

Does this also create a dummytable FOO that can be updated, inserted and select statement ?

Any other way would also be helpfulll|||yes but the table is known as a Temporary table and is queried explicitly as #foo
as in select * from #foo

a temporary table exists only as long as your session.

you should consider using microsoft books online which is in the sql server start menu.

look up temporary tables , variables, insert, update, and delete.|||What you are calling a "dummy" table can be implemented in TSQL as eigther a temporary table or a table variable.

Briefly, a temporary table is created in a stored procedure using the same syntax as for a permanent table, except that the name it preceded with a # character. A temporary table exists for the duration of the stored procedure or, if running a script in Query Analyzer, until dropped or the session ends. A temporary table can be indexed and joined like any other table.

A table variable is a variable that holds a dataset. Like other variables, a table variable is declared and the name is preceded with a @. symbol. A table variable exists until it goes out of scope. Table variables are generally faster than temporary tables, but they cannot be indexed to the extent of temporary tables. They can be queried and joined like any other table, but you will need to alias them.

Please look up Temporary Tables and Table Variables in Books Online for more information.|||Thanks you'll have being great Help

Wish if you'll were girls or i was ; would have Kissed you'all

But a Special Thanks|||?
I have no response to that.|||?
I have no response to that.That's ok... Its better that way.

-PatP|||After my query is put in table say #foo
I have to add 2 columns say year and month

One column (small int) in #foo has date in the form 104,204...1204
I want to update this column depending on system date

if > than system date then "F" for forcast
and if < system date then "B" for budget

so # foo has among other column

Bill column2 column3 column4
104 entry1 entry2 entry3
106 value1 value2 value 3

First row has Jan04 (past) and second row has Jan06(future)

replace that Jan04 with "B"
and Jan06 with "F"

also i want to add column5(year) and column6 (month)
and insert Month and year with relevant value|||I'd suggest you post the code you've got so far, and I'll see if I can mangle it into something useful (if somebody else doesn't beat me to it).

-PatP|||Select p.Billcycle,'Corp1' as RecordSourceCode, '5407' as MarketID, 'Miami' as MarketName,
sum(p.grossrev) BudgetGross,
(sum(p.grossrev)-sum(p.agyrev))BNetDollars,(sum(p.grossreve)- sum(p.agyrev)-sum(p.reprev)) BNetNetDollars
into #mytable
from conproj p, contract n
where p.conid=n.conid
and billcycle IN (105,205,305,405,505,605,705,805,905,1005,1105,120 5)
and n.status not in ('H')
group by p.billcycle

select * from #mytable

How do i add 2 columns ( say year and Month) to #mytable ???

One column Billcycle (small int) in #mytable has date in the form 104,204...1204
I want to update this column depending on system date

if > than system date then "F" for forcast
and if < system date then "B" for budget

so # mytable has among other column

Bill column2 column3 column4
104 entry1 entry2 entry3
106 value1 value2 value 3

First row has Jan04 (past) and second row has Jan06(future)

replace that Jan04 with "B"
and Jan06 with "F"

the result should look like this

Bill Newcolumn1 Newcolumn2 column2 column3 column4
B Jan 2004 entry1 entry2 entry3
F Jan 2006 value1 value2 value 3|||What's the data type for p.Billcycle? If character, exactly how is the value formatted? Please show examples for Jan-Dec of some year.

-PatP|||p.Billcycle smallint
sum(p.grossrev) Money
p.agyrev Money
p.reprev Money

How do i add 2 columns ( say Myyear and MYMonth) to #mytable ???
of type Varchar to store Month and year

One column Billcycle (small int) in #mytable has date in the form 104,204...1204
I want to update this column depending on system date

if > than system date then "F" for forcast
and if < system date then "B" for budget

so # mytable has among other column

Billcycle(smallint--size 2) col2 ( money size 8) col3(money size8), col4

104 entry1 entry2 entry3
204 entry1 entry2 entry3
304 entry1 entry2 entry3......
1204 entry1 entry2 entry3 ...... (each month may have one row)
106 value1 value2 value 3
206 value
306
106 value1 value2 value 3

First row has Jan04 (past) and second row has Jan06(future)

replace that Jan04 with "B"
and Jan06 with "F"

the result should look like this

Bill Newcolumn1 Newcolumn2 column2 column3 column4
B Jan 2004 entry1 entry2 entry3
F Jan 2006 value1 value2 value 3|||Here is the excel sheet(see zippped attachment) which first has #Foo table

i want in the form of new table if possible
or second choice if thats not possible|||can i do this

Select p.Billcycle,'Corp1' as RecordSourceCode, '5407' as MarketID, 'Miami' as MarketName,
sum(p.grossrev) BudgetGross,
(sum(p.grossrev)-sum(p.agyrev))BNetDollars,(sum(p.grossreve)- sum(p.agyrev)-sum(p.reprev)) BNetNetDollars
into #mytable
from conproj p, contract n
where p.conid=n.conid
and billcycle IN (105,205,305,405,505,605,705,805,905,1005,1105,120 5)
and n.status not in ('H')
group by p.billcycle

exec( create table newtable as select * from #mytable)

go

alter table newtable
add column newcolumn1 varchar(20);

go

alter table new table
add column newcolumn2 varchar(20)

go

insert in to new column

something

will this work ?
I had included the change in zip file that i want how do i proceed ?|||How about:SELECT
p.Billcycle, 'Corp1' as RecordSourceCode, '5407' as MarketID
, 'Miami' as MarketName, sum(p.grossrev) BudgetGross
, (sum(p.grossrev) - sum(p.agyrev)) AS BNetDollars
, (sum(p.grossreve) - sum(p.agyrev) - sum(p.reprev)) AS BNetNetDollars
, b.BillCycle % 100 AS MyYear
, b.BillCycle / 100 AS MyMonth
, CASE WHEN GetDate() < Convert(DATETIME, Str(CASE WHEN b.BillCycle % 100 < 50
THEN 2000 ELSE 1900 END + b.BillCycle % 100, 4) + '-'
+ Str(b.BillCycle / 100, 2) + '-01') THEN 'F' ELSE 'B' END AS thingy
INTO #mytable
FROM conproj AS p
JOIN contract AS n
ON (p.conid = n.conid)
WHERE billcycle IN (105, 205, 305, 405, 505
, 605, 705, 805, 905, 1005, 1105, 1205)
AND n.status not in ('H')
GROUP BY p.billcycle-PatP