Select a column that doesn't exist.... (hot project) 3

[Qmoto]

Hello everyone,

I've got a project that I need to create a view with several columns that I do not have in my database. I'm also supposed to set the columns that don't exist in our database to a default value.

I think this is a simple problem, but as I'm no SQL expert I'm at a loss.

What help/advice could you give me.

Thanks
Steve

 

[gmmastros]

You just select a constant, like this...

Select Field1, 
       Field2, 
       10 As ColumnName1, 
       'Yes' As AnotherColumn
From   Table

Each record returened by the query will have a column named 'ColumnName1' with a value of 10.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

I am not so sure what you mean with

set the columns that don't exist in our database to a default value

But try this in QA:

USE MyDataBase
GO
CREATE VIEW MyView
AS
SELECT ......,
       CAST('Default' as varchar(200)) AS ColumnThatNotExists
....
GO
SELECT * from MyView

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Catadmin]

And lastly, if you need to create columns that "don't exist" based off of data that actually does exist, you can use a CASE statement.

Select CASE When color = 'yellow' Then 'Go Tigers!'
       CASE When color = 'blue' Then 'Go Jets!'
       Else 'No team is selected'
       END
from MyTable
where color IS NOT NULL

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Qmoto]

@gmmastros

Perfect!!
That's exactly what I was looking for - I GREATLY appreciate the help. I believe this is the fastest method.
Have a star!

@bborissov
Thanks for you idea as well - I will actually be needing something like that as well. The CAST trick will come in handy very shortly.
Have a star!

Although, just for clarity and the other uninitiated among us a sligtly more complete (copy and paste version) of the code.

CREATE VIEW MyView
AS
SELECT 
  CAST('Default' as varchar(200)) AS ColumnThatNotExists,
  Column1 AS INVOICE_DATE,
  Column2 AS INVOICE_NUMBER,
  ...
  Column9 AS INVOICE_AMOUNT
FROM
  MyTable;

 

[Qmoto]

@Catadmin

Sorry, I must have been typing when your post came in.

Thanks for the tip, I'm not sure if I'll need that for this project or not, but I have not doubt it will come in handy.

I really do appreciate the help!
Have a star? (I think I just like handing out stars) :0)

Actually, I firmly believe in thanking those who help out, especially if there's good code involved.


To any and all:
This is a second question, so if you would prefer I start another thread I will.

Question:

What functions/statements are allowed between the SELECT and FROM ?

I now know two ( CAST & CASE ) are there others?

 

[gmmastros]

If you are using SQL Analyzer, take a look at the Object Browser. If you don't see it, press F8 on your keyboard. Near the bottom of the object broswer window, you will see Common Objects, with folders below that. Take a look at 'Date And Time Functions', 'Mathematical functions', etc...

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Qmoto]

@gmmastros

I'm using SQL 2K5, so I'll have to dig up a copy of 2000 and install it to see what you're talking about.

@Cadadmin
It appears your solution was needed far sooner than I thought - thanks for the input.


Steve

 

[gmmastros]

You can see a similar list in 'SQL Server Management Studio'

Expand Databases
Expand 'Your Database'
Expand Programmability
Expand Functions
Expand System Functions

Look at aggregate functions, configuration functions, etc...

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Qmoto]

Excellent!

There's quite a list of options to work with - looks like I have some serious reading/learning ahead.

 

[gmmastros]

It's important to know what's available, even if you don't think you need it right away. There are quite a few functions that many programmers never use simply because they didn't know those functions exist.

Happy learning.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom