SQL Server 2014 - Poplating a blank field

Hello,

Try this:

Select
IsNull(NULLIF(LTRIM(RTRIM([LookupColumn])), ''),'NON-VHA') as SITECODE,
From Site_CTE

Select allOpen in new window

Thank you

Hi and thanks

so I just tried:
IsNull(NULLIF(LTRIM(RTRIM([SiteCode])), ''),'NON-VHA') as SITECODE

But again that did not come up with:
NON-VHA in the field

So what this query does is first runs though a CTE then I display the CTE at the end.  And what I want it to do is if the field coming from the CTE is blank or Null then put into that field
NON-VHA otherwise give me the value

Please help and thanks

@Hugo: LTRIM() is sufficient.

Hello,

@ste5an
You are completely right. Force of habit I guess :-)

That's very strange.
Can you provide the "real query" that you are running and a sample of the rows that contains at least one with Null and one "empty" so I can check what is happening?

I tested the query before sending it to you and it works perfectly.

EDIT:
Here is an image displaying the query working perfectly in my system:

Thank you

Hi Experts,

I have even tried doing a:

Union All

back to the CTE and checking if:
Non Exist

But that did not work either

unfortunately I am unable to send whole query.  

On this condition there is no data coming back from the CTE and causing the SSRS report to have an error of:
One or more parameters were not specified for the subreport

Please help and thanks

Hi,

This there a difference on:
Null/Blank
and
No record at all?

What if there is no record at all would Null/Blank cover this?  Kind of a silly questin but just checking

Thanks

Hello,

Are you sure that the following query works well?

Select SiteCode
From Site_CTE
WHERE SiteCode IS NOT NULL AND LTRIM([SiteCode]) NOT LIKE ''

Select allOpen in new window

EDIT:
If there is no record then you would have one of two results:
1 - In a inner join there would be no record returned.
2 - In a outer join there would be a record returned but all fields from the table in the join that doesn't have a record would be "NULL".

EDIT2:
If possible always use an example to illustrate what you mean. That makes it a lot easier for us to understand it.

Thank you

Hi,

I do not have any Where statement when displaying the CTE.  It is currently just a flat dump of the CTE.  The reason I created the CTE was to do a UNION ALL back to the CTE to check if Blank/Null.  But that did not work.

On below I am just display the CTE and just testing for Sitecode:

Select 
Region,
IsNull(NULLIF(LTRIM([SiteCode]), ' '),'NON-VHA') as SITECODE,
--IsNull(NULLIF(SiteCode, ' '),'NON-VHA') as SITECODE, 	
IsNull(SiteCode,'No Data From Risk Vision') as FacilityName,
IsNull(SiteCode,-0) as [UserAccountDisabledLastLogonOver180],
IsNull(SiteCode,-0) as [UserAccontPasswordLastSetOver90], 
IsNull(SiteCode,-0) as [UserAccountEnabledLastLogonOver90],
IsNull(SiteCode,-0) as [PivNotRequried],
IsNull(SiteCode,-0) as [ServiceAccountPasswordLastSetOver1095],
IsNull(SiteCode,-0) as [PasswordDoesnotExpire],
IsNull(SiteCode,-0) as [TotalAccounts]
From Site_CTE

Select allOpen in new window

I just want to send some data so I don't get the error in SSRS when there is no data to send to subreport:
One or more parameters were not specified for the subreport

Please help and thanks

it's not:

IsNull(NULLIF(LTRIM([SiteCode]), ' '),'NON-VHA') as SITECODE,

Select allOpen in new window

Is:

IsNull(NULLIF(LTRIM([SiteCode]), ''),'NON-VHA') as SITECODE,

Select allOpen in new window

no space

yes I tried both, thought maybe it was a space issue...

and when I just tried to select the outcome of running the query so I could copy and display that outcome on this post I got:
Value cannot be Null
Parameter Name: data (System.Windows.forms)

Hello,

Two things:
1 - It can't have an space because the function "LTRIM()" removes spaces:
2 - The where clause that I used before is to remove all "NULL" and all empty fields to verifiy if it is really an issue related with those two situations. The query that I gave you would solve the issues created by "NULL" and empty spaces. That tells me that the problem is probably elsewhere.

If you can try the WHERE clause to check if they are really the problem.

EDIT: What did you use to run the query??

Thank you

All that shows up when I run query is the headers looks like there is no record at all...

So, basically, all your records have either a NULL or are empty...

User selects a sitecode from a dropdown then clicks on <Apply>.

If Risk Vision supplies the record then it works fine  (Comes from a website I think).  

But user in this case is selecting a sitecode that has no record (Risk Vision did not supply the record).

Report is looking for a parm to be passed but in this case there is no data.

I am trying to force data into when there is none so the report will pass something/anything....

So the only time when there is no data is when Risk Vision does not supply..

OK. Let start at the beginning...

Is that a Web Application, Windows Application, What??
The DB that you are using is SQL Server, right? Do you have access to the Database Server?

No I am using SQL Server 2014

yes I have access to the Database Server.

The data comes from RiskVision and is placed into a table DimUser.

From 
		DW.DIMUSER D
	Inner Join	
		DW.SRC_VV_USERACCOUNTCONTROL S	
	ON 	
		D.USERACCOUNTCONTROL = S.USERACCOUNTCONTROLVALUE
	Inner join (select distinct Branch,sitecode, Region from dflt.FISMA_Boundary  where SiteCode = @SiteCode and ScanDateID = (select max(id) from dflt.ScanDates) 
	and Region = @region) as ff on ff.SiteCode = d.FISMASiteCode and ff.Region = d.FISMARegion


	Where 
				IsRowCurrent	= 1	AND 
				FISMASiteCode	= @sitecode		AND 
				FISMARegion		= @region		

	Group by 
				d.FISMARegion , D.FISMASiteCode , 	isnull(ff.[Branch], 'Unknown')

Select allOpen in new window

When there is no record from RiskVision that is when I try to force some data into the execution of this query and the reason for this post...

OK.

Can't you do the same that I did above? That is open a query window, then create a select query that returns some rows without NULLs or empty columns and other rows that have NULLs and others that have empty columns. You don't need to show all columns, just select the ID column (if it exists) and then the column in question and maybe one more.
Basically the same that I did before. That way I will be able to see the "real" problem.
I forgot a very important point. Then do a print-screen and attach it to your post for us to see.

From what you wrote this is my understanding, please tell me if and where I'm wrong:

1 - The "@sitecode" is the value chosen by the user;
2 - The code that you added above is the real code that you are using;
3 - "FISMASiteCode" is the "sitecode" in the table "DW.DIMUSER".

If that's correct, this line:

FISMASiteCode	= @sitecode

Select allOpen in new window

would prevent any record from showing if the value for "@sitecode" does not exists in "DW.DIMUSER.FISMASiteCode".

Are you sure this part of the code returns results when "@sitecode" doesn't exists in "DW.DIMUSER".

Looks like the real problem is there is no record at all so therefor doing a IsNull/blank check would not work.  Is there a way to create a record and populate that record?

Here is one that works fine and the sitecode is different then the one I have been showing.

Here is what it looks like when RiskVision does not supply the record:

please help and thanks

yes this is correct and the reason for this post:
Are you sure this part of the code returns results when "@sitecode" doesn't exists in "DW.DIMUSER

it does not return results...

All 3 question are Yes

Hello,

You can force it to return results, but are you sure that that it is what you want?
I would say it is better to check if any result are returned and then do one thing or another (I'm guessing that the results of the query are then processed by an application).

If you insist in having results, and assuming that "DW.SRC_VV_USERACCOUNTCONTROL" and "dflt.FISMA_Boundary" will always return something, you could try changing the inner join to a outer join (in this case an right outer join).

Thank you

Yes the results are processed by an application (SSRS) and I get error when there is no data:
One or more parameters were not specified for the subreport

So I am trying to avoid this error and have the subreport come up anyways with anything.  By flagging the sitecode="NON-VHA" that way I call let the user know when sitecode = "NON-VHA " no data from RiskVision.

you say:
assuming that "DW.SRC_VV_USERACCOUNTCONTROL" and "dflt.FISMA_Boundary" will always return something

remember no record for the sitecode in DimUser so therefore no way the 2 tables would return anything.

I did a Right Outer Join and still no blank record showed up.

I was trying earlier a UNION All
Select *
From Site_CTE
Where Not Exist (select * from Site_CTE)

Would something like that work?

Hello,

"remember no record for the sitecode in DimUser so therefore no way the 2 tables would return anything."

No. Incorrect. DimUser is one table. It's the table that you said it might not have a corresponding value for "sitecode" (here I'm assuming that the sitecode is represented by the variable "@sitecode").

You never said that "dflt.FISMA_Boundary" might not have a corresponding value either.
"DW.SRC_VV_USERACCOUNTCONTROL", in your code doesn't have anything to do with the variable "@sitecode".

So, no. "DW.SRC_VV_USERACCOUNTCONTROL" in your code is not filtered by sitecode, and as such it can return records independently of the value for sitecode. It's the fact that you are doing a inner join with "DimUser" that then removes any records show from "DW.SRC_VV_USERACCOUNTCONTROL" in the case sitecode doesn't exist in "DimUser". That means that if you did a:

Select *
From 
		DW.DIMUSER D
	Right Join	
		DW.SRC_VV_USERACCOUNTCONTROL S	
	ON 	
		D.USERACCOUNTCONTROL = S.USERACCOUNTCONTROLVALUE
WHERE FISMASiteCode = @sitecode

Select allOpen in new window

for a sitecode that doesn't exists in DW.DIMUSER, you would still get results for the query.

EDIT:

I was trying earlier a UNION All
Select *
From Site_CTE
Where Not Exist (select * from Site_CTE)

Would something like that work?

I think you are over complicating things.

Thank you

So I do this:

Select *
From 
		DW.DIMUSER D
	Right Join	
		DW.SRC_VV_USERACCOUNTCONTROL S	
	ON 			D.USERACCOUNTCONTROL = S.USERACCOUNTCONTROLVALUE

Select allOpen in new window

and still no record shows up.

What am I missing?

I don't understand your post: ID: 42225220
???

what does this do?  where am I getting [dbo].[SmallerTable]?

are you saying to create another DataSet in SSRS and refer to this procedure?

TRY:

Select *
From 
		DW.DIMUSER D
	RIGHT OUTER JOIN	
		DW.SRC_VV_USERACCOUNTCONTROL S	
	ON 			D.USERACCOUNTCONTROL = S.USERACCOUNTCONTROLVALUE

Select allOpen in new window

EDIT:
OUTER should be optional, but nothing else makes sense unless, of course, there are no values in "DW.SRC_VV_USERACCOUNTCONTROL".
Is "DW.SRC_VV_USERACCOUNTCONTROL" a table??

Hello,

Sorry, I didn't saw that you had posted again before sending my last post.

1 - "[dbo].[SmallerTable]" is my table. Of course I don't have your database so I had to use my own.
2 - You did not reply to my question. I will ask again. At the moment, are you using a stored procedure to run the query or not?

I will try to explain the logic behind the procedure:

1 - If the sitecode dos not exits in the table A
2 - then
3 -       create a single row with the values that you want, namely "NON-VHA".
4 - else
5 -       show the row with the code
6 - end

In line 3 you would create the output line that you want when no record is found in the table "DIMUSER" for the provided sitecode.

In line 5 you would basically just put your current query.

Thank you

Great that works...

Now that I understood your query this works great... Thanks for staying with me to finish....