IS NULL and CASE Usage in Enterprise Manager

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

IS NULL and CASE Usage in Enterprise Manager

Author

Topic

desabhatla
Starting Member

7 Posts

Posted - 2003-01-17 : 09:57:46

Hi,
I am exporting required data from a SQl DB to a flat .dat file with the required queries. I have a requirement of having the fields with NULL values in the DB to be a blank in the flat file.i.e. the field or column with no data should be left blank as it is. It shouldn't happen that the next field replace/move its place the field that has blank value. This works when I run the query in Query Analyzer with the NULL field values remaining blank and the rest of the fields sitting in their positions only. But when I am using Enterprise Manager Export Option to export the SQL DB data to a flat file with the same query,the resultant flat file generated has the blank fields replaced by the next field. This is causing me problems since I have to take the flat file and load it into Oracle DB where I have mentioned the fied fields lengths of the fields that are coming in the Flat file. Please help me out on this.I am attaching the query for your reference below.

"
SELECT APPLCTN_ID,APPLCTN_NAM,IsNull((CASE
APPLCTN_IT_OWNR_EML_DSPLY_NAM WHEN ' ' THEN NULL ELSE APPLCTN_IT_OWNR_EML_DSPLY_NAM
END),' ') email,
IsNull((CASE APPLCTN_SNGL_SGN_ON_FLG
WHEN ' ' THEN NULL ELSE APPLCTN_SNGL_SGN_ON_FLG END),' ')
from tbapp002_applctn
"

Thanks & Regards,
Rajesh

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2003-01-17 : 10:01:41

Stick to Query Analizer.

By the way, where do you put your code in Enterprise Manager?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-17 : 10:06:02

When you say "flat file" do you mean that the data is kept in a fixed-width format, or is it delimited using a comma or tab or some other character?

If it is fixed-length format, then you need to replace a null with a fixed number of spaces to pad out the column properly. Something like this should work:

SELECT APPLCTN_ID,APPLCTN_NAM,
IsNull(NullIf(APPLCTN_IT_OWNR_EML_DSPLY_NAM, ' '), Space(COL_LENGTH('tbapp002_applctn', 'APPLCTN_IT_OWNR_EML_DSPLY_NAM'))) email,
IsNull(NullIf(APPLCTN_SNGL_SGN_ON_FLG END, ' '), Space(COL_LENGTH('tbapp002_applctn', 'APPLCTN_SNGL_SGN_ON_FLG END')))
from tbapp002_applctn

Robwhittaker
Yak Posting Veteran

85 Posts

Posted - 2003-01-17 : 10:08:05

Not sure why it's doing this, but if it works through query analyser just set it to output to a file and not the consol. This can be done through query menu, or execute mode button on the tool bar.

desabhatla
Starting Member

7 Posts

Posted - 2003-01-17 : 10:25:28

Hi,
I am exporting data to the flat file with Fixed Length Option with no delimitations. The Queries Code is put when we select the option "Use Query to specify the Data Transfer" option while exporting the DB data to flat file. Running the query in a Query ANalyzer and then either copying or Saving the selection of resultant records will not in Fixed Width format.
Thanks & Regards
Rajesh

desabhatla
Starting Member

7 Posts

Posted - 2003-01-17 : 10:57:23

robvolk,
I have tried your option too but this too doesn't work. Still I am unable to pad blank spaces and fields get shifted when the previous field is blank in value.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-17 : 11:03:57

I assume you're using DTS for this, Import/Export wizard. I haven't encountered this kind of problem with it so far.

Can you post some sample output that you get and the EXACT query you're using for that output? Just a few rows that demonstrate the problem. The only thing I can think of is that other columns are causing the problem. Also, are any of the columns being exported more than 255 characters in length?

One other option you may want to consider is DTS'ing the data directly to the Oracle database. Unless the Oracle DB is completely inaccessible via a network, this will be much easier than exporting and importing a text file.

Robwhittaker
Yak Posting Veteran

85 Posts

Posted - 2003-01-17 : 11:04:07

I haven't tried, but as Enterprise manager is trying to put columns in fixed positions, maybe, you formating the output in your sql is confusing it. By rights it should put the columns in the correct places, if you just select the columns you want.

Failing that can you not space the columns in the file out afterwards using a good text editor, (with or without CSVing them). Xedit on IBM mainframes is the best thing in the world for doing this, but you probably haven't got access to one. I'm going to be sad when ours is decommissioned :o(

desabhatla
Starting Member

7 Posts

Posted - 2003-01-17 : 11:50:42

robvolk,
Yes I am using DTS for this, Import/Export wizard only. I will paste the output of the file below. There are no fields more than 255 characters. Max field is 150 only. It is also not possible to directly move data to Oracle since that is inaccessible for me. Another team is doing that using ETL programming.

If you see below,You have one value called Manufacturing. Actually there is a field before that having 150 characters in length but since it is blank the next field value-Manufacturing is moving to this place.

The code that I am using is below.
"
SELECT 'EPM' AS SOURCESYSTEMID,A.[APPLCTN_ID],A.[APPLCTN_NAM],IsNull((CASE
A.[APPLCTN_IT_OWNR_EML_DSPLY_NAM] WHEN ' ' THEN NULL ELSE A.[APPLCTN_IT_OWNR_EML_DSPLY_NAM]
END),' ') email,A.[APPLCTN_WBFCTN_INDX_NUM],SUBSTRING(CONVERT(varchar,GETDATE(),101),1,2)+SUBSTRING(CONVERT(varchar,GETDATE(),103),1,2)+CONVERT(varchar,DATEPART(yyyy,GETDATE()))
dt,A.[REMOTE_ACCESS_CD],IsNull((CASE A.[APPLCTN_SNGL_SGN_ON_FLG]
WHEN ' ' THEN NULL ELSE A.[APPLCTN_SNGL_SGN_ON_FLG] END),' ')
SSO,A.[STANDARD_TECHNOLOGY_STACK_CD],replace(A.[TECHNICAL_DOCUMENTATION_FLG],'E','Y')
AS TechDoc, A.[USER_DOCUMENTATION_FLG],
IsNull((CASE [TB_APP027_BUSINESS].[BUSINESS_NAM] WHEN ' ' THEN NULL ELSE [TB_APP027_BUSINESS].[BUSINESS_NAM]
END),'') BusinessName,
IsNull((CASE [TB_APP028_FUNCTION].[FUNCTION_NAM] WHEN ' ' THEN NULL ELSE [TB_APP028_FUNCTION].[FUNCTION_NAM]
END),'') FunctionName
,IsNull((CASE A.[APPLCTN_FNCTNL_OWNR_EML_NAM] WHEN ' ' THEN NULL ELSE A.[APPLCTN_FNCTNL_OWNR_EML_NAM]
END),'') FunctionalOwner
,IsNull((CASE TB_APP032_APPLICATION_BSNSS_PRCSS.[BSNSS_PRCSS_ID] WHEN ' ' THEN NULL ELSE TB_APP032_APPLICATION_BSNSS_PRCSS.[BSNSS_PRCSS_ID]
END),'')BusinessProcess
,IsNull((CASE TB_APP032_APPLICATION_BSNSS_PRCSS.[FLAG_PRCSS] WHEN ' ' THEN NULL ELSE TB_APP032_APPLICATION_BSNSS_PRCSS.[FLAG_PRCSS]
END),'')FlagProcess,
A.APPLCTN_STTS_CD,

IsNull((CASE TB_APP033_SUB_BUSINESS.[sub_business_nam] WHEN ' ' THEN NULL ELSE TB_APP033_SUB_BUSINESS.[sub_business_nam]
END),'')Tier2Name,
IsNull((CASE TB_APP035_SUB_BUSINESS_TIER3.[sub_business_nam_tier3] WHEN ' ' THEN NULL ELSE TB_APP035_SUB_BUSINESS_TIER3.[sub_business_nam_tier3]
END),'')Tier3Name

FROM TBAPP002_APPLCTN AS A
LEFT OUTER JOIN TB_APP030_application_function ON A.applctn_id = TB_APP030_application_function.applctn_id
LEFT OUTER JOIN TB_APP028_function ON TB_APP030_application_function.function_id = TB_APP028_function.function_id
LEFT OUTER JOIN TB_APP032_APPLICATION_BSNSS_PRCSS ON A.applctn_id = TB_APP032_APPLICATION_BSNSS_PRCSS.applctn_id
LEFT OUTER JOIN TBAPP003_BSNSS_PRCSS ON TB_APP032_APPLICATION_BSNSS_PRCSS.BSNSS_PRCSS_ID= TBAPP003_BSNSS_PRCSS.BSNSS_PRCSS_ID
LEFT OUTER JOIN TB_APP036_APPLICATION_SUB_BUSINESS_TIER3 ON A.applctn_id = TB_APP036_APPLICATION_SUB_BUSINESS_TIER3.applctn_id
LEFT OUTER JOIN TB_APP035_SUB_BUSINESS_TIER3 ON TB_APP036_APPLICATION_SUB_BUSINESS_TIER3.sub_business_id_tier3 = TB_APP035_SUB_BUSINESS_TIER3.sub_business_id_tier3
LEFT OUTER JOIN TB_APP033_SUB_BUSINESS ON TB_APP036_APPLICATION_SUB_BUSINESS_TIER3.sub_business_id = TB_APP033_SUB_BUSINESS.sub_business_id
LEFT OUTER JOIN TB_APP027_business ON TB_APP033_SUB_BUSINESS.business_id = TB_APP027_business.business_id
order by a.applctn_id

"

EPM282         Lex                                               Steve.Hambrock@indsys.ge.com                      N01172003                          N NN NNManufacturing                                     Steve.Hambrock@indsys.ge.com                      15          2DD
EPM282         Lex                                               Steve.Hambrock@indsys.ge.com                      N01172003                          N NN NNManufacturing                                     Steve.Hambrock@indsys.ge.com                      16          1DD
EPM282         Lex                                               Steve.Hambrock@indsys.ge.com                      N01172003                          N NN NNManufacturing                                     Steve.Hambrock@indsys.ge.com                      16          2DD

<edit> to fix display </edit>

Edited by - robvolk on 01/17/2003 13:29:14

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-17 : 13:39:32

I'm working on something and I'll post it when I'm done, but in the meantime I think you might look at RobWhitaker's suggestion: what happens if you just SELECT the columns, without using IsNull or any other expression? Just the plain old column name?

And the advice about query analyzer can also apply to using the osql utility: you can put the SQL into a file and use osql to run it and output the data to a file using a fixed length. It will handle nulls automatically without needing IsNull (or certainly an expression that's much simpler than what you have now) You can throw this osql call into a regular job and schedule it as if it were a DTS package.

Also, can you post some sample data that show both the good and bad formatting? The samples you posted previously are all the same format, I don't see any columns being collapsed.

Just a personal note: you made a great start by aliasing the table TBAPP002_APPLCTN AS A, why didn't you keep doing that???? I just reformatted the code simply by aliasing all the tables and damn if it isn't half the length of the original!

Another note: all of your CASE expressions seem to be replacing a ' ' with a Null. Is that a single space character, or is it an empty string? Regardless, is there any difference between that character and a real null? If there isn't, and the column allows nulls, I strongly suggest you just update the table to replace them all with Nulls. It's really making this query, and I'm sure others, a helluva lot harder to write. I truly do not see how NOT allowing nulls there and yet allowing an empty value is beneficial.

Lastly, if you're gonna do this on a regular basis, put this SQL into a view or a stored procedure, it will make life a lot easier.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources