SQL Query + default fields in the output

Question

I want to select some rows from a table.

Along with the normal columns that I get back from the query result, I also need to append some additional field in the result set.

I am exporting the table in to the csv file. The output file will have some extra fields along with the normal column values that were returned from the query.

For ex table has column A, B, and C. which has A B C
11 Bob S
12 Gary J
13 Andy K

Now my output file should have

11, Bob , S, "DDD" , 1
12, Gary, J, "DDD" , 2
13, Andy, K , "DDD" , 3

(One way was to first do select A,B,C from test_table and then manipulate the file by appending the 2 fields manually)

Is there any way that I can get the extra 2 values in the query itself as hardcoded values ?

Answer 1

SELECT A, B, C, 'DDD' as "D", 1 as "E"
FROM myTable
WHERE ...

Is the 1, 2, 3 hardcoded, or does this need to be incremented value?

Answer 2

Yes, in fact this is pretty easy. It would be something like this: SELECT field1, field2, 'Default' AS field3, 'Default2' AS field4 FROM table WHERE ...

Answer 3

How do I get the incremental value in the last field?

Answer 4

Use can use a sub query to get the incremental value. This relies upon an order by and unique values in column A.

SELECT
    A,
    B,
    C,
    'DDD' [D],
    (SELECT COUNT(*) FROM test_table test_table2 WHERE test_table2.A <= test_table.A) [E]
FROM
    test_table
ORDER BY
    A

This is blindly answering your question... I have a feeling there is more to your question and much better ways of actually achieving your final goal.

Answer 5

You can get the incrementing field with oracle with the pseudo column rownum or row_num

I keep forgetting. But this will differ for other databases.

Answer 6

SELECT  A,
        B,
        C,
        'DDD' AS D,
        ROW_NUMBER() OVER (ORDER BY A) AS 'E'
FROM    myTable

Also look here for an answer sql-query-that-numerates-the-returned-result

Answer 7

If using SQL Server 2005+, using the ROWNUMBER function should do fine.

SELECT A, B, C, 'DDD' AS D,
ROW_NUMBER() OVER (ORDER BY A) AS E
FROM Table

Answer 8

I'm gussing the last field is a row counter and you don't specify the database, but for Oracle this should work:

select A, B, C, 'DDD', rownum from ex;