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.
| Author |
Topic |
|
yaman
Posting Yak Master
213 Posts |
 Posted - 2010-06-21 : 18:09:19
|
| Hello Sir ,Sir , we have one table in which some field are integer , char , datetime ,decimal .Table have some blank rows .It return all rows when we applied condition like fieldname ='' and fieldname2 is not null Sir how fetch blank rows in sql server .Please help me out Sir .Yaman |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-21 : 23:17:29
|
| What do you mean by blank rows?Rows having NULL values or empty values for all the columns?Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-06-22 : 04:02:52
|
| Post some sample data and expected resultMadhivananFailing to plan is Planning to fail |
|
|
|
yaman
Posting Yak Master
213 Posts |
Posted - 2010-06-22 : 09:32:49
|
| SELECT LWSN_PROD_GLTRANS.COMPANY,LWSN_PROD_GLTRANS.FISCAL_YEAR,LWSN_PROD_GLTRANS.ACCT_PERIOD,LWSN_PROD_GLTRANS.CURRENCY_CODE,LWSN_PROD_GLCHARTDTL.ACCOUNT_DESC,LWSN_PROD_GLTRANS.ACCT_UNIT,LWSN_PROD_GLTRANS.ACCOUNT,LWSN_PROD_GLTRANS.DESCRIPTION,LWSN_PROD_GLTRANS.SUB_ACCOUNT,LWSN_PROD_GLTRANS.SYSTEM,LWSN_PROD_GLTRANS.SOURCE_CODE,LWSN_PROD_GLTRANS.BASE_AMOUNT,LWSN_PROD_GLTRANS.REFERENCE,LWSN_PROD_GLTRANS.OBJ_ID,case when len(LWSN_PROD_GLTRANS.SUB_ACCOUNT)=1 Then substring(convert(varchar(100),LWSN_PROD_GLTRANS.account),0,6)+'-'+'000'+convert(varchar(100),LWSN_PROD_GLTRANS.sub_account)when len(LWSN_PROD_GLTRANS.SUB_ACCOUNT)=2 Then substring(convert(varchar(100),LWSN_PROD_GLTRANS.account),0,6)+'-'+'00'+convert(varchar(100),LWSN_PROD_GLTRANS.sub_account)when len(LWSN_PROD_GLTRANS.SUB_ACCOUNT)=3 Then substring(convert(varchar(100),LWSN_PROD_GLTRANS.account),0,6)+'-'+'0'+convert(varchar(100),LWSN_PROD_GLTRANS.sub_account)ELSE substring(convert(varchar(100),LWSN_PROD_GLTRANS.account),0,6)+'-'+convert(varchar(100),LWSN_PROD_GLTRANS.sub_account) END ACCT ,GL_Vendor_Names.VENDOR,LWSN_PROD_APDISTRIB.INVOICE,GL_Vendor_Names.VENDOR_VNAME,FROM LWSN_PROD_GLTRANS Inner join LWSN_PROD_GLCHARTDTL on LWSN_PROD_GLTRANS.ACCOUNT =LWSN_PROD_GLCHARTDTL.ACCOUNT and LWSN_PROD_GLTRANS.sub_account =LWSN_PROD_GLCHARTDTL.sub_accountIt returns blank rows some rows have data and some rows are blankYaman |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-22 : 09:36:24
|
We still can't see sample data and wanted output.But I can see that there is a syntax error because of the comma after VENDOR_VNAME
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
yaman
Posting Yak Master
213 Posts |
Posted - 2010-06-22 : 09:43:21
|
quote:
Originally posted by Idera
What do you mean by blank rows?Rows having NULL values or empty values for all the columns?Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH
All columns are not null .I am using where condituon like column1='' then it return balank rows .Yaman |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-22 : 09:48:02
|
quote:
Originally posted by webfred
We still can't see sample data and wanted output.But I can see that there is a syntax error because of the comma after VENDOR_VNAME
No, you're never too old to Yak'n'Roll if you're too young to die.
And your posted query has NO where condition...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-06-22 : 09:49:38
|
| Unless oyu post some sample data and expected output, it is very difficult to understand your exact problem. |
|
|
|
yaman
Posting Yak Master
213 Posts |
Posted - 2010-06-23 : 10:59:34
|
quote:
Originally posted by vijayisonly
Unless oyu post some sample data and expected output, it is very difficult to understand your exact problem.
SELECT LWSN_PROD_GLTRANS.COMPANY,LWSN_PROD_GLTRANS.FISCAL_YEAR,LWSN_PROD_GLTRANS.ACCT_PERIOD,LWSN_PROD_GLTRANS.CURRENCY_CODE,LWSN_PROD_GLCHARTDTL.ACCOUNT_DESC,LWSN_PROD_GLTRANS.ACCT_UNIT,LWSN_PROD_GLTRANS.ACCOUNT,LWSN_PROD_GLTRANS.DESCRIPTION,LWSN_PROD_GLTRANS.SUB_ACCOUNT,LWSN_PROD_GLTRANS.SYSTEM,LWSN_PROD_GLTRANS.SOURCE_CODE,LWSN_PROD_GLTRANS.BASE_AMOUNT,LWSN_PROD_GLTRANS.REFERENCE,LWSN_PROD_GLTRANS.OBJ_ID,case when len(LWSN_PROD_GLTRANS.SUB_ACCOUNT)=1 Then substring(convert(varchar(100),LWSN_PROD_GLTRANS.account),0,6)+'-'+'000'+convert(varchar(100),LWSN_PROD_GLTRANS.sub_account)when len(LWSN_PROD_GLTRANS.SUB_ACCOUNT)=2 Then substring(convert(varchar(100),LWSN_PROD_GLTRANS.account),0,6)+'-'+'00'+convert(varchar(100),LWSN_PROD_GLTRANS.sub_account)when len(LWSN_PROD_GLTRANS.SUB_ACCOUNT)=3 Then substring(convert(varchar(100),LWSN_PROD_GLTRANS.account),0,6)+'-'+'0'+convert(varchar(100),LWSN_PROD_GLTRANS.sub_account)ELSE substring(convert(varchar(100),LWSN_PROD_GLTRANS.account),0,6)+'-'+convert(varchar(100),LWSN_PROD_GLTRANS.sub_account) END ACCT ,GL_Vendor_Names.VENDOR,--LWSN_PROD_APDISTRIB.INVOICE,GL_Vendor_Names.VENDOR_VNAMEFROM LWSN_PROD_GLTRANS Inner join LWSN_PROD_GLCHARTDTL on LWSN_PROD_GLTRANS.ACCOUNT =LWSN_PROD_GLCHARTDTL.ACCOUNT and LWSN_PROD_GLTRANS.sub_account =LWSN_PROD_GLCHARTDTL.sub_accountand LWSN_PROD_GLTRANS.fiscal_year='2010'LEFT JOIN LWSN_PROD_APDISTRIBON LWSN_PROD_GLTRANS.ACCOUNT = LWSN_PROD_APDISTRIB.DIS_ACCOUNT and LWSN_PROD_GLTRANS.sub_account = LWSN_PROD_APDISTRIB.DIS_SUB_acct and LWSN_PROD_GLTRANS.fiscal_year='2010' and LWSN_PROD_GLTRANS.COMPANY = LWSN_PROD_APDISTRIB.COMPANY LEFT JOIN GL_Vendor_Names ON LWSN_PROD_APDISTRIB.VENDOR = GL_Vendor_Names.VENDOR WHERE LWSN_PROD_GLTRANS.ACCOUNT >= 10000 AND LWSN_PROD_GLTRANS.ACCOUNT <= 37000and LWSN_PROD_GLCHARTDTL.ACTIVE_STATUS='A' and LWSN_PROD_GLTRANS.fiscal_year='2010' and LWSN_PROD_GLTRANS.STATUS=9It takes too much time and when i forcefully stop these query return duplicate rows and blanks rows .Yaman |
|
|
|
|
|
|
|
|
Fecth blank rows