?
Solved

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

Posted on 2008-06-17
4
Medium Priority
?
327 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
  • 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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
What we learned in Webroot's webinar on multi-vector protection.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

840 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