SQL Server 2012 :: View Results Not Displaying Nil Columns

Sep 5, 2014

I have a view I've created which displays client sortname, partner and date added which displays 7 results.

When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.

Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?

Script:
SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added],
dbo.cdbAttribInst.ainTVal AS Inudstry
FROM dbo.cdbClient LEFT OUTER JOIN
dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN

[Code] ....

In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...

View 8 Replies


ADVERTISEMENT

SQL Server 2012 :: View Not Displaying All Results

Aug 7, 2014

I have created a view thats pulling data from two different tables to combine them into one report.

table 1 lists the client code and table 2 lists the client partner and they're linked by a variable.

When running the report the result shows the client codes with their respective partner however any client codes that didn't have a partner are not displaying in the report and I need all client codes to be displayed even if there's no partner.

Is there a way I can make this display all results and if the client partner doesn't exist for it to still display as 'Null' for the partner but still display the client code?

Script:

SELECT TOP (100) PERCENT C.cltCode AS ClientCode, C.cltSortName AS SortName, C.cltTerminationDate AS [Term date], dbo.vcltAttrib6.ainTVal AS Department,
C.objInstID AS ClientID
FROM dbo.cdbClient AS C INNER JOIN
dbo.vcltAttrib6 ON C.objInstID = dbo.vcltAttrib6.ainObjectInstID
GROUP BY C.cltSortName, C.cltTerminationDate, dbo.vcltAttrib6.ainTVal, C.objInstID, C.cltCode
ORDER BY ClientID

View 2 Replies View Related

SQL 2012 :: Displaying Results Between Dates

Aug 6, 2014

I am designing a database to store data from a leak tester.

We want to display the results between dates, I mean, the results of the leaking test are going to be stored as well as the datetime in which they have been performed.

ID (int)
RESULT (float)
TS (datetime)

The query will be, of course:

SELECT * FROM TABLE Where TS BETWEEN DT1 and DT2

This table is growing by 10000 rows a day, it is possible that in a year getting the values between two dates became impossible.

Using a index with a datetime field sounds like a crazy idea.

View 2 Replies View Related

SQL Server 2012 :: How To Write Text Out To Grid View In Results Tab

Dec 1, 2014

I can create a string into a local "@" variable. It can include a date/time, and text for my timing testing.

I need to be able to send that string to the results window where table output goes, rather than to the message window where a PRINT statement goes.

How do I do that?

View 2 Replies View Related

SQL Server 2012 :: How To Join Pivot Results With Dynamic Columns

Mar 5, 2015

I have a lookup table, as below. Each triggercode can have several service codes.

TriggerCodeServiceCode
BBRONZH BBRZFET
BBRONZH RDYNIP1
BBRONZP BBRZFET
BCSTICP ULDBND2
BCSTMCP RBNDLOC

I then have a table of accounts, and each account can have one to many service codes. This table also has the rate for each code.

AccountServiceCodeRate
11518801DSRDISC -2
11571901BBRZFET 5
11571901RBNDLOC 0
11571901CDHCTC 0
17412902CDHCTC1 0
14706401ULDBND2 2
14706401RBNDLOC 3

What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.

I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.

What I would like to get is just the service codes and the appropriate trigger code for each account.

SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode
FROM TriggerTable
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'VARCHAR(MAX)')
,1,2,'')

[Code] ....

View 1 Replies View Related

SQL 2012 :: View Giving Different Results?

Nov 4, 2014

I have a view over 5 tables that has started giving unreliable results. There are three records that should be different, but in a production Access database, the view is giving three identical records where there should be three unique records. I have tested the view within SQL Server Management Studio and it gives the correct records there. But, I have attached this same view into the same Access database with two separate names. One instance of the view within Access database gives the correct records, and the other gives the incorrect (duplicated) records. I have attached screen shots that show these two separately named incarnations of the same SQL View, with the duplicated data, or the unique data highlighted.

I have also included the SQL query specs for this view.

what I can do to this view in order for it to always give us the unique records that we need, rather than sometimes the correct records, and sometimes the incorrect records.

Correct 3 records:

Incorrect 3 records:

[URL]

View 4 Replies View Related

SQL 2012 :: Results To Show Up As Separate Columns

Aug 22, 2014

We have the below query that pulls benefit ids for employees but it will show each benefit on a separate row but we would like to have just one rows for the employee and columns for each of the benefits.

SELECT
hcd.PersonId,
hcd.PlanYear,
hcd.TaxIdNumber,
hcd.LastName,
hcd.FirstName,
hcd.BirthDate,

[code]....

View 3 Replies View Related

SQL 2012 :: Put Results Into Separate Columns Based On Value?

Feb 25, 2015

I am trying to take the results of a query and re-orient them into separate columns.

select distinct
W_SUMMARYDETAILS.FACILITY_ID,
W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year)
W_SUMMARYDETAILS.FACILITY_NAME,
W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year)
From W_SUMMARYDETAILS
full outer join W_DEF_SUMMARYDETAILS
on W_SUMMARYDETAILS.FACILITY_ID=W_DEF_SUMMARYDETAILS.FACILITY_ID and
W_SUMMARYDETAILS.REPORTING_YEAR=W_DEF_SUMMARYDETAILS.REPORTING_PERIOD

As of now the query puts all the years into a single column -- one for DEF_SUMMARY and another for SUMMARY.

I am looking to create 7 additional columns for all the individual years in the results instead of just two columns.

View 9 Replies View Related

Stored Proc Results Are Displaying In The Messages Tab Instead Of Results Tab- URGENT

May 14, 2008




Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..

Thanks
dotnetdev1

View 4 Replies View Related

SQL 2012 :: How To Drop Columns From Alter View

Mar 20, 2015

I have a VIEW which is dynamically generated through complex dynamic SQL. Unfortunately the dynamic SQL uses "Select * from table" to select the columns because the programmer did that to reduce the amount of code in the dynamic SQL string as the code can't be debugged if it's too long.

Therefore, I have a VIEW with columns in it I don't need, and want to remove them from the view - I need to remove all columns with column names matching the syntax '%1%_2' .

The view is called TEMP_EXPORT_1

I can either use the code below to return a list of columns that I want removed:

select column_name from information_schema.columns

where table_name='TEMP_EXPORT_1' and column_name like '%1%_2'

Or I can use the code below to return the list of columns that I want to keep:

select column_name from information_schema.columns

where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2'

Now how would I go about altering TEMP_EXPORT_1 view so that it no longer has these columns? I know views don't have a drop statement...

Therefore I tried the following but I'm not sure of the syntax:

ALTER VIEW dbo.TEMP_EXPORT_1
AS
SELECT (select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2')
FROM dbo.TEMP_EXPORT_1

Am I on the right track? how can I ALTER this view to remove these columns? ... I want to keep this separate from the code that generated the view as I want it as an optional procedure that can be run if needed.

View 2 Replies View Related

Displaying Results Of Map

Feb 16, 2008

I am trying to create a user permission system that is stored in a database.  What is the best table structure for accomplishing this?   How could I display the permissions in a grid?  Currently I have a users table and a permissions table.  I created a map between the two.  However, I don't see how this allows me to display a grid.  All my "columns" for permissions are actually rows from the permissions table. So ideally my grid would look something like this. User | P1 | P2 | P3 |A      | T   |  F  |  T  |B      | T   |  T  |  T  |Thanks for any help.  I am relatively new to SQL so please explain gently. 

View 2 Replies View Related

Displaying Results Of MAP

Jan 8, 2008

I have two tables joined by a map. For example

Table1
[Ball, Frisbee, Kite]

Table2
[Red, Green, Blue]

MAP
[Ball:Green, Ball:Blue, Kite:Red]

I want to return a table that shows each toy and what colors it is available in.
Column1 - Toys | Column2 - Comma separated list of colors
Ball - Green, Blue
Kite - Red
Frisbee -

I have absolutely no idea how to do this even after googling all morning. Please help if you can. There must be a way to do this!

View 4 Replies View Related

SQL Server 2012 :: Displaying Code Of A Stored Procedure To A Single Line

Sep 14, 2015

Any better way to query SQL 2012 to display the code of a stored proc to a single line. I'm trying to write a script to insert the contents of the procs between my devestprod environments. So people can query a single table for any proc that is different between environments. At the moment I am using the syscomments view and the text column but the problem here is if you get a lengthy proc it cuts it up into multiple rows.

I can get around it by converting the text to a varchar(max) and outer joining the query, but as you can see by my code below I have to try and guess what the maximum number of rows I'm going to get back for my largest proc. If someone adds a new one that returns 8 rows I'm going to miss it with this query.

Select col1.[type],col1.[name],convert(varchar(max),col1.text) + isnull(convert(varchar(max),col2.Text),'')
+ isnull(convert(varchar(max),col3.Text),'')
+ isnull(convert(varchar(max),col4.Text),'')
+ isnull(convert(varchar(max),col5.Text),'')
+ isnull(convert(varchar(max),col6.Text),'')
+ isnull(convert(varchar(max),col7.Text),'')

[Code] .....

View 3 Replies View Related

Displaying Results As Currency

May 3, 2004

I'm looking to display the results of the following query as currency. Any help with this would be greatly appreciated...

SELECT SUM (sysTotalCharge) - SUM (sysTotalCredit) AS TotalNet FROM Bookings WHERE (sysRecordDate BETWEEN '01/01/::Year::' AND '01/31/::Year::')

View 2 Replies View Related

SQL 2012 :: SSRS Report Not Displaying Time Values Correctly From Server Table

Feb 26, 2015

I am having problems displaying time values in my SSRS report. below is info. Tried expressions still does not work. I want the values to show what in the SQL Server table 00:00:00.82. I tried stored proc still does not work.

SQL Server table time value shown in milliseconds:
00:00:00.82

Reporting Services report value shown:
00:00:00

View 3 Replies View Related

Displaying Select Results On The Page

Aug 28, 2006

I have the following select statement on my page: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:prbc_hrConnectionString %>"
SelectCommand="SELECT emp_lname + ', ' + emp_fname + ' ' + emp_minitial + '.' AS emp_fullname FROM employee WHERE (emp_username = @emp_username)">
<SelectParameters>
<asp:SessionParameter Name="emp_username" SessionField="Username" Type="String" />
</SelectParameters>I want to say "Welcome, emp_fullname" at the top of the page, but can't figure out how to write the results to the page.  I am moving to ASP.NET 2.0 from PHP and am banging my head against the wall trying to figure out how to do these little things. I appreciate any help you can give.

View 1 Replies View Related

Displaying Database Results (HELP A NEWBE)

Dec 10, 2006

Hey All,
This is really simple basic question, and it's just so I can start to learn about MVS.
I have a sql-database called "Test" with a Table called "Agents" with just three colulms "ID", "FNAME" and "LNAME".
On the Default Page I have three Textboxes and a submit Button,
All I want to do is type "Select" criteria in Textbox1 and have Textbox2 Display the "Fname"  and Textbox3 Display the "Lname".
 (Using VB in ASPX)
Dim i As New SqlDataSource()
i.ConnectionString = ConfigurationManager.ConnectionStrings("Test").ToString()
i.SelectCommandType = SqlDataSourceCommandType.Text
i.SelectCommand =   ((((((Now What???)))))))
 
End Sub
PLease Help,  Thanks in advance
 A....

View 2 Replies View Related

Displaying Results From Multiple Tables

Apr 17, 2008

Hi,

I have created a new reports application project in VS 2008. I have a dataset with 2 tables: Customer and CustomerAddress with one to many relationship. I want to have a simple table in my report which displays data in the following format:
Customer Name Address
----------------------------------------------------------
ABC Add1
ABC Add2

XYZ Add1
XYZ Add2

Here Address is obviously from CustomerAddress table. I have tried few options but it's mainly disgusting to work with Reports Application project when there was an amazing Busines Intelligence Reports project available in VS 2005 and equally good designer interface (Dataset, Design and Preview tabs for each report).
Please give me a solution to this.

Shyam

View 4 Replies View Related

Displaying DMX Query Results In Table...

Apr 14, 2008

Hye Friends,
I'm using a DMX query to get some predictions out of my MiningModel

my DMX query is as follows :

SELECT
predict([x SalaryPredictor].[Emp Gross],20),predict([x SalaryPredictor].[Emp Basic],20)
From
[x SalaryPredictor]

This query is returning me 2 objects of type expressions in my Dataset.
The problem is when I try to drag these 2 fields in my Table i get a "#Error" value

I executed the query in 'Data' tab and found that data is getting returned is a tree like format where "Expression" is at top & values expected ar its child nodes.

I also found out that the returned objects are actually AdomdDataReader , but i'm not able to write a expression to get its values in my tables....

I want to display the values in the following format....

_____________________________
| Emp gross | Emp basic |
---------------------------------------------------
| predicted val 1 | predicted val 2 |
---------------------------------------------------
| .... | ..... |
-- -------------------------------------------------


can anybody help me out in this ????...

Thanks in advance !!!

View 1 Replies View Related

Displaying All Results In Reporting Services

Apr 14, 2008

Im trying to display all twelve results from a dataset in reporting services. On the layout tab I can get the first and last record in my dataset to display by using the commands:


=First(Fields!MonthYear.Value)

=Last(Fields!MonthYear.Value)


But I cant get the ones in between?

View 10 Replies View Related

Displaying Distinct Results From Union Query

Aug 31, 2000

I've got a union query (below)and it returns rows that have duplivate itemno's, descrip's, imsrp3's, and imsrp4's, while the remaining columns are not duplicate for the same row. An Excel report uses this query to populate itself and for a more visually appealing look, I'd like to skip the duplicated columns in the display. I'm not sure how to use the Distinct or Group by in this case, since technically I'm dealing with two separate queries, neither one separately returning any duplicate rows.
thanks for any suggestions...

~
select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco,
watrdj,wapddj,wauorg,wauser
from nowo
where nowo.wasrst <='40'
union
select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco,
watrdj,wapddj,wauorg,wauser
from nopo
where nopo.wasrst <='499'

View 1 Replies View Related

Displaying Results In Aggregate Query Without Grouping Everything?

Mar 13, 2012

I have a query where I have customers, date they ordered a swatch, date they ordered an item, and eh date diff between the two. I want to show the MIN date diff for each customer, and also show the swatch date and item date as well. But to use the MIN aggregate, it forces me to group everything, where I just want to group by customer, and have the 2 dates tag along, because i only want one record per customer. What is the easiest way for me to accomplish this?

SAMPLE:

CustKeySwatchDateRugDateDateDiff
10903963126678366
10903963126837525
10903963126960648
10913962286550322
1115886193625764
1129666456646711
1146986229625324
1146986229627647
11469862296667438
1146986656666711
1146986624666743

DESIRED RESULTS:

CustKeySwatchDateRugDateDateDiff
10903963126678366
1115886193625764
1129666456646711
1146986656666711

View 7 Replies View Related

Displaying SELECT Query Results Conditionally...

Apr 7, 2008


Hello all,

I have a problem... I have a SELECT query I have used in my PHP report. It is as follows:

SELECT DISTINCT callref, CASE WHEN (stage.due_date < stage.completed_date) THEN 'SLA Breach' ELSE ' In SLA' END AS sla
FROM tableX....
WHERE call_status=open.....


I wish to search through all stages (i.e response, fix, end) of the calls logged in our database, & then return 'In SLA' for each call that had all its stages completed within the SLA, & 'Breach' for all calls that had even one of its stages completed outside its SLA.

At the moment the SELECT query above gives me the following results:

Call Ref sla

10001 In SLA
10002 Breach
10002 In SLA
10003 In SLA
10004 In SLA
10005 Breach
10005 In SLA
... ...


What it should look like is this....

Call Ref sla

10001 In SLA
10002 Breach
10003 In SLA
10004 In SLA
10005 Breach
... ...


Please let me know if anyone has the answers or any clues to this! thanks.

View 5 Replies View Related

Querying Table2 With Results From Table1 And Displaying Data From Both Tables

Nov 8, 2006

I need help in writing a query.

The query should get top 10 items and their values from current year and the values for the same items from previous year table.

I was able to write the code for 1st part that gets values from 1st table but I don't know how to get the values from 2nd table.

The 2 tables does not have any primary/foreign key relations. Both tables have same structure and same columns.

I am attaching some images below to give more information.

Image of results from my query.

Image of how the final output should look like.

The Store Procedure code is:

ALTER Procedure [dbo].[free_customsHS4](
@TblName1 varchar(20),
@TblType varchar(20),
@District varchar(6),
@Month varchar(3)
)


AS
Begin
SET NOCOUNT ON;

Declare @SQuery nvarchar(3000)
set @TblName1 = '[' + @TblName1 + ']'
set @TblType = '[' + @TblType + ']'


SELECT @SQuery = 'select top 10 a.commodity1 as HS4, b.descrip_1 as Description,
sum(a.all_val_mo) as [Amount],

(sum(a.all_val_mo)/(select Sum(a.all_val_mo) FROM ' + @TblName1 + 'a
where a.stat_month <=' + @Month + ' and a.district=' + @District +'))*100 as [% Share]

FROM ' + @TblName1 + ' a left outer join ' + @TblType + ' b on a.commodity1=b.commodity1
where a.stat_month <=' + @Month + ' and a.district=' + @District +'
Group by a.commodity1, b.descrip_1
order by [Amount] desc'

EXEC sp_executesql @SQuery
END

View 2 Replies View Related

Displaying Columns Twice

May 9, 2008

Hello All,

I have a little problem. Here is the scenario:

I got two tables Asset and Personnel

Personnel has FirstName, LastName,and username which is unique.

Asset table stores all information related to an asset and the username to whom the asset is assigned and the POC username.

The tables look like this:

Personnel Table
FirstName, Last Name, UserName
John, Doe, jdoe
Bill, Smith, bsmith

Asset Table
POCUserName, AssigneeUserName
jdoe,bsmith

My problem arises when I want to display First Name and Last Name for both the POCusername and the AssigneeUserName since FirstName and LastName columns exists once in the Personnel table. How can I display the columns twice. Lets say POCFirstName, POCLastName, AssigneeFirstName, and AssigneeLastName. Or this is something that can't be done.

The query below only display the Assignee info. Of course, I need to something else to display the POC info as well...don't where to go from here...

select tblPersonnel.FirstName, tblPersonnel.LastName, tblAsset.AssigneeUserName, tblAsset.POCUserName
from tblVendor, tblAsset, tblPersonnel
where and tblPersonnel.UserName = tblAsset.AssigneeUserName
and tblAsset.POCUserName in(select tblPersonnel.UserName from tblAsset, tblPersonnel
where tblPersonnel.UserName= tblAsset.POCUserName)

Any help is greatly appreciated

~
N

View 3 Replies View Related

Write A CREATE VIEW Statement That Defines A View Named Invoice Basic That Returns Three Columns

Jul 24, 2012

Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.

This is what I have so far,

CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID

[code]...

View 2 Replies View Related

Query Is Not Displaying All Columns

Feb 9, 2015

I created the following simple query between two tables:

SELECT vru.User_Name0, vru.Full_User_Name0, vru.department0, vru.employeeNumber0,
vru.title0, vru.manager0, vrs.location0, vrs.Netbios_Name0, vrs.Last_Logon_Timestamp0
FROM v_R_User vru
LEFT JOIN v_R_System vrs
ON vru.ResourceID = vrs.ResourceID
ORDER BY vrs.User_Name0

I don't know how to properly insert a pic, but basically I get output, except the last 3 columns. I've tried every variation of JOIN and I cannot seem to solve this issue.

View 6 Replies View Related

Displaying Calculated Columns In A Matrix

Jan 31, 2008

Hi,

Im trying to create a calulated value in my matrix table

I have the following matrix

ADSL CABLE BROADBAND %of adsl %ofcable %ofbroadband
AWAITING 5 9 11 0.2 0.36 etc

IN PROGRESS 67 10 5 0.8 0.1 etc
CHURNED 8 1 15 0.3 etc etc



I would like to create columns called
% of ADSL

% of cable
% of broadband
which is the count for that product divided by the total off adsl+cable+broadband for that particular status

I have the following problems
1. How do I add columns to a matrix which would allow these calculated columns to display?

thanks

View 3 Replies View Related

SQL Server Admin 2014 :: Selected Columns On View

Mar 18, 2015

I have created row level security on two views and adding these two views to particular role.Today I have got an requirement that, middle level managers shouldn't see the all the columns. So I have created another role for Middle level managers and assign securables as those two views with selected columns by grant, and map all the middle level managers to this role. I thought my job is done. But these managers uses this view on SSAS(tabular model) and Excel, In those applications they are not able to load the data.

Later I come to know we can't use -- select * from ViewA ( in viewA I have restristced few columns in the role level) Work around is creating another view and assigning to the role. But how can we achieve column level security to implement this in either SSAS/SSRS/EXCEL?

View 6 Replies View Related

SQL Server 2012 :: Getting Results From All Tables In Database

Mar 13, 2014

below is my statement to get data from one column (VARCHAR) from table SUPPLY_ITEM_01

SELECT
@@SERVERNAME as ServerName,DB_NAME() AS DatabaseName,
SUM(CASE WHEN CHARINDEX (CHAR(013) , supplydetail) > 0 THEN 1 ELSE 0 END) AS TotalCHAR013,
SUM(CASE WHEN CHARINDEX (CHAR(012), supplydetail ) >0 THEN 1 ELSE 0 END)AS TotalCHAR012,
SUM(CASE WHEN CHARINDEX (CHAR(010), supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR010,
SUM(CASE WHEN CHARINDEX (CHAR(009),supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR009
FROM

[code]...

I need to get result from all the tables and all the columns which has bad data including schemaName, table name and column name in result.

View 9 Replies View Related

SQL Server 2012 :: Get Results In A Specific Order?

Sep 11, 2014

I have a simple example of what I am trying to do. Here is some code to make a quick table to demonstrate:

create table #students
(
lastname varchar(50)
,firstname varchar(50)
,address1 varchar(50)

[Code].....

I want to select all the records, and them them be in alphabetical order first by lastname, then by firstname, then by address. HOWEVER, and this is the tricky part, I want to group names together that have the same address. So, in this example, I want the results to be in this order:

HallC6309 N Olive
HallP6309 N Olive <---- grouped with the C record because they have the same address
HallE5488 W Catalina <---- back to alphabetical by first name
HallJ7222 N Cocopas

View 7 Replies View Related

SQL Server 2012 :: Tab Character In Query Results

Jul 9, 2015

I am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs!

select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME,
CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN
,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I
LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID

[Code] ....

Results:
Gießanzeiger groß ,04260376730475 04260376730475 730475 00
Gießanzeiger klein , 04260376730352 04260376730352 730468 00
Gießanzeiger klein , 04260376730468 04260376730468 730468 00

View 9 Replies View Related

Checking 2 Columns And Displaying Result In 1 Column

Apr 13, 2012

Ihave 2 tables...they are basically the same except for the column name in one of them because they deal with 2 different names, though..the data i want is in columns that have the same name.pretty much what i want to do...is .they also need to be distinct so i dont count duplicates...i can get them as separate tables...but i cant get them together..I need them in 1 column because of how it is sent to the C3 code page and how it reads it...the structure has already been previously set..and there are about 5 other statments that are being executed in this one stored procedure like this (also i wasnt the one who set this up).

image 1 is what is currently set up
imgur: the simple image sharer
top part is what is stored in tables..bottom is more of the result
it basically runs this code to get the bottom

DECLARE @id INT;
DECLARE @invest nvarchar(50);
SET @id = '7633';
SET @invest = '';
SELECT 'a' + CONVERT(nvarchar, orderfindings.risk_rating) AS cat, COUNT(DISTINCT orderfindings.prnt_id) AS stat
FROM orderheader, orderaudits, orderfindings
WHERE orderheader.id = orderaudits.orderheader_id AND orderaudits.ID = orderfindings.prnt_id
AND orderheader.id = @id AND orderfindings.risk_rating > 0 AND orderaudits.Investor_Name LIKE '%' + @invest + '%'
GROUP BY orderfindings.risk_rating

If i want agencies instead of findings..just replace it..agencies and findings are the 2 tables..they are the pretty much identical column wise...but i want the result together..i've tried several ways..but i cant seem to get it.

image 2 - the table at the bottom is more what i'm looking for..it combines them both into 1
imgur: the simple image sharer

if an order has a finding or agency or both in it..then it gets marked as a 1 for that risk rating...if it doesnt..then 0 for that risk rating..and then sum them all up to see what i got..

I've been working with it...did this [SQL] compare2 - Pastebin.com ..got it to display 2 columns..but still not the right result...i'm getting a1 = 1...a2 = 1...so its not running through all the orders...or it needs a way to count it...i put a sum at beginning of case statement..erro because of counts...so i took counts out...

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved