Page 1 of 2 12 LastLast
Results 1 to 15 of 30

Thread: default value

  1. #1
    Join Date
    Oct 2003
    Posts
    232

    Arrow Unanswered: 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 appreciated
    Last edited by sjumma; 01-04-05 at 11:32.
    bigfoots

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    SELECT street1, state,pin,'Miami' as city
    FROM yourTable
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Oct 2003
    Posts
    232
    thanks how stupid of Meeeeeeeeeeee op
    bigfoots

  4. #4
    Join Date
    Oct 2003
    Posts
    232
    futher to that can i put city value as Miami for say record 1,5,10,15,40
    and the rest as 'Davie' ??
    bigfoots

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    and what if you want to include miramar, plantation, coral gables, and dania?
    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

    Code:
    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
    Last edited by Ruprect; 01-04-05 at 16:09.

  7. #7
    Join Date
    Oct 2003
    Posts
    232

    Arrow

    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
    Last edited by sjumma; 01-06-05 at 17:03.
    bigfoots

  8. #8
    Join Date
    Oct 2003
    Posts
    232

    Arrow PUBS dAtabase eg zzzzzzzzzzzzzzzzzzzzz

    Quote Originally Posted by sjumma
    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 ???
    bigfoots

  9. #9
    Join Date
    Oct 2003
    Posts
    232

    no body can accept this challenge

    how come no body could do this ???
    bigfoots

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do what?

    -PatP

  11. #11
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    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...

  12. #12
    Join Date
    Oct 2003
    Posts
    232

    Lightbulb here its more detail

    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
    bigfoots

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm going WAY out on a limb here, because I still am not sure I understand your question, but does this help:
    Code:
    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

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by sjumma
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Oct 2003
    Posts
    232

    Talking

    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
    Last edited by sjumma; 01-08-05 at 14:36.
    bigfoots

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •