Tips for writing queries needed

Question

Tips for writing queries needed

mashikoo

Ten Centuries

Points: 1188
More actions
September 7, 2010 at 12:15 pm

#148684

--==== daily query to pull data from database to flat file
SELECT
V_APPT_BASIC.PERSON_ID, V_APPT_BASIC.FIRST_NAME, V_APPT_BASIC.LAST_NAME,
V_BU_BASIC.BU_CODE, V_BU_BASIC.BU_NAME,
V_BU_BASIC.BU_PARENT, V_BU_BASIC.BU_PARENT_NAME,
V_EMP_ALL.BIRTHDATE, V_EMP_ALL.GENDER
FROM database(databasename)
--===== create column(Columnname) to reflect in flat file even if not in database(databasename)
CREATE Column (A), pos(posid)

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Instead of trying to explain what you're looking for it would be more efficient if you would provide table def and sample data in a ready to use format as described in the first link in my signature together with your expected result.

Based on your description I have no idea what you're looking for (except the need for a flat file...).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mashikoo Ten Centuries Points: 1188 More actions · Answer 2

Thanks for your prompt response. I updated my question with what I'm trying to do. Here it is:

--==== daily query to pull data from database to flat file

SELECT

V_APPT_BASIC.PERSON_ID, V_APPT_BASIC.FIRST_NAME, V_APPT_BASIC.LAST_NAME,

V_BU_BASIC.BU_CODE, V_BU_BASIC.BU_NAME,

V_BU_BASIC.BU_PARENT, V_BU_BASIC.BU_PARENT_NAME,

V_EMP_ALL.BIRTHDATE, V_EMP_ALL.GENDER

FROM database(databasename)

--===== create column(Columnname) to reflect in flat file even if not in database(databasename)

create Column (A), pos(posid)

Thanks for your help.

LutzM SSC Guru Points: 107049 More actions · Answer 3

What exactly do you want to achieve with your approach? It's still not clear.

Again, please read and follow the instructions given in the article referenced as the first link in my signature.

As a side note: If you alter your table and add a column but don't include it in your select statement you won't see that column in the flat file either. Especially, if you don't add the logic to populate the columns with some values...

Edit: typo corrected

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mashikoo Ten Centuries Points: 1188 More actions · Answer 4

Thanks for your response. I'm trying to make all the employees active in the output in flat file thereby writing a query to create a separate column with A as it's value so that beside every employee info there will be a column for A (Active).

mashikoo Ten Centuries Points: 1188 More actions · Answer 5

Also, I'm not allowed to update,alter any table in the master database from which the data is pulled. I hope you can help me.

LutzM SSC Guru Points: 107049 More actions · Answer 6

As stated before:

Please read and follow the instructions given in the article referenced as the first link in my signature ("How to get fast answers to your question").

You need to help us help you. well, you don't need to. But you should...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mashikoo Ten Centuries Points: 1188 More actions · Answer 7

Here is the an example of the end result:

Last name first name Status

Doe John A

LutzM SSC Guru Points: 107049 More actions · Answer 8

mashikoo (9/7/2010)
Here is the an example of the end result:
Last name first name Status
Doe John A

You really don't like to read the article, do you?

How about adding ,'A' AS Status to your query?

It won't give you the posid column as requested in an earlier post but your requirements may have changed in between...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Mashikoo,

First, please read the article that Lutz has indicated in his signature block (you'll also find it the first article I reference below in my signature block). Following the instructions in that article, plus providing the expected results will get you the help you seek.

Second, how are you creating the flat file?

Third, the following may help, replace the '*' with the columns you need from your table (sorry, typing this on my BlackBerry so I am doing this on the fly):

select

*, -- replace with your columns

'A' as Active

from

dbo.yourtable -- replace with your table name

mashikoo Ten Centuries Points: 1188 More actions · Answer 10

Thanks Lynn,

But there is no column 'A' for active in the dbo.tablename, that is where my problem lies I need to write a new query refrencing this.

Thanks Lynn

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 11

As Lutz suggested, if you just add the static value of 'A' to your SELECT, it'll show up on every record, and they'll all look active. That may or may not be helpful.

In my experience, if you tell these guys what you're *trying* to do, (more than?) half the time they'll have a better way than whatever you (or I) are currently doing. And if you supply them the data they need from that article they're trying to get you to read, they'll give you a solution that they've tested already to make sure it works.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

mashikoo (9/7/2010)
Thanks Lynn,
But there is no column 'A' for active in the dbo.tablename, that is where my problem lies I need to write a new query refrencing this.
Thanks Lynn

First, did you try the query I suggested or did you simply dismisss it out of hand since you don't have a column named 'A'?

By the the way, the 'A' in my query is a literal value, not a column name.

Perhaps you should take the time to read about the SELECT statement in Books Online as well as the article Lutz and I have asked you to read.

mashikoo Ten Centuries Points: 1188 More actions · Answer 13

Hi Lynn,

I didn't dismiss your query. I will run it and let you know the outcome. I appreciate your effort.

Regards

WayneS SSC Guru Points: 95460 More actions · Answer 14

mashikoo (9/7/2010)
Thanks Lynn,
But there is no column 'A' for active in the dbo.tablename, that is where my problem lies I need to write a new query refrencing this.
Thanks Lynn

mashikoo,

We understand that you don't have a status column, or a column A in your master table.

From what we understand, when you create the flat file, you want to add a status column, and you want every value to be the literal 'A'. Is this correct?

This is what has been suggested to you, on several occasions.

Tell you what. Just run the following code and see what it does:

SELECT 'I need to read BOL for more information on the SELECT statement'

FROM sys.databases

Do you see how EVERY record has this literal text? Well, that's what Lutz and Lynn have been trying to get you to try.

(You might also notice that Lutz, Lynn, Jon and myself all have the same first link in our signature. We do it for a reason - if you will actually read it, and follow it's advice, we will be able to help you out better. So, please, HELP US HELP YOU! Read the article, and follow it.)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2