?
Solved

SQL query in VIEW  to return recordset and then Add a Constant

Posted on 2008-06-17
4
Medium Priority
?
324 Views
Last Modified: 2012-05-05
Hello:

I'd like to run a query in SQL View,  and have a recordset returned, then add a constant variable to that set for each Company code. Would I use append function?

Like:
SELECT DISTINCT TOP 100 PERCENT ad_adno, ad_code, ad_rep
FROM         OPENQUERY(mysql, 'select * from cuad') Rowset_1
ORDER BY ad_code, ad_adno

Recordset
      1      4B      JAVIER
      2      4B      JAVIER
      3      4B      JAVIER
        A      4B      JAVIER
Thanks,
Joseph

0
Comment
Question by:CEGE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21806825
simple:
SELECT DISTINCT TOP 100 PERCENT ad_adno, cast( ad_code as varchar(20)) + 'B' , ad_rep 
FROM         OPENQUERY(mysql, 'select * from cuad') Rowset_1
ORDER BY ad_code, ad_adno

Open in new window

0
 
LVL 1

Author Comment

by:CEGE
ID: 21806905
Hello:

No, 4B is the name of the company, what I want to add is the A line, what is returned is 1,2,3,4 and then I want A to be added at the end. Sorry for the mixup.
Thanks,

Joseph
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 21806934
something like this:
SELECT DISTINCT cast(ad_adno as varchar(10)), ad_code, ad_rep
FROM         OPENQUERY(mysql, 'select * from cuad') Rowset_1
UNION ALL
SELECT DISTINCT 'A', ad_code, ad_rep
FROM         OPENQUERY(mysql, 'select * from cuad') Rowset_1
ORDER BY 2, 1

Open in new window

0
 
LVL 1

Author Closing Comment

by:CEGE
ID: 31468118
very cool. Thank you. It was right on.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question