How do i get one row if the resultset returned has no rows.

Hi

I have couple of CTE's which i want to ultimately insert into a temp table... and based on my condition some of the CTE's return some rows and some dont... but i want to have the CTE return atleast one row if there is no data in it.. I have tried doing the following but i am not getting the desired output

 

 

;with cte_rpt4 as (select '4' a,recd_dt as approve_dt, prov_no, prov_name, client_id, claim_mst_id, claim_adj_id, claim_dt, 
claim_amt, rpt4,
(Select count(distinct(Claim_mst_Id)) as crpt4 from #temp1 where datediff(day,isnull(recd_dt,create_dt),isnull(check_dt,getdate()))=4 and status_id =1) crpt4 
from #temp1 where datediff(day,isnull(recd_dt,create_dt),isnull(check_dt,getdate()))=4 and status_id =1 
group by recd_dt , prov_no, prov_name, client_id, claim_mst_id, claim_adj_id, claim_dt, 
claim_amt, rpt4)
select '4',* from cte_rpt4

 But since the condition for this CTE doesnt return any rows i want it to return a dummy row

how can i do it

any help will be appreciated

Thanks,

Karen

0
Karenros
4/29/2009 2:21:03 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

3 Replies
158 Views

Similar Articles

[PageSpeed] 11

what do you mean by CTE.. and what is the use of returning a row if no match found?


Aspiring Architect
(-: Please Mark as Answer if it solves your query :-)
0
Live
4/29/2009 2:40:55 PM

So that I can report a 0 for that row in my report.

CTE is a object which can be used a like table.

 

 

 

0
Karenros
4/29/2009 2:42:50 PM

;with cte_rpt4 as (select '4' a,recd_dt as approve_dt, prov_no, prov_name, client_id, claim_mst_id, claim_adj_id, claim_dt,
claim_amt, rpt4,
(Select count(distinct(Claim_mst_Id)) as crpt4 from #temp1 where datediff(day,isnull(recd_dt,create_dt),isnull(check_dt,getdate()))=4 and status_id =1) crpt4
from #temp1 where datediff(day,isnull(recd_dt,create_dt),isnull(check_dt,getdate()))=4 and status_id =1
group by recd_dt , prov_no, prov_name, client_id, claim_mst_id, claim_adj_id, claim_dt,
claim_amt, rpt4)
select '4',* from cte_rpt4

IF @@ROWCOUNT = 0 -- no records were returned

   select '4', 'Dummy Value 1' as Field1, ... from myTable where Pk = Some known constant -- to get one dummy record


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
4/29/2009 5:26:19 PM
Reply:

Similar Artilces:

SQL: mutiple rows one column to multiple columns one row
In InfoMaker SQL can you take a table with multiple rows for the same key and take a column from the rows and create mutiple columns with one report line with the key(no duplicate keys) on a report? For example Table books (key) (book title) 123 misery 123 titanic 123 roots 456 war 456 1984 456 giant The report I need would look like this Key title1 title2 title 3 123 misery titanic roots 456 war 1984 giant Thanks in advance. -- Kaye Hendry HealthInsight email:kaye.hendry@healthinsight.org ...

single-row subquery returns more than one row
 I want a subquery  to return more than  one value then  use those values to get more values from  the other table. But  I  am receiving the errors saying ORA-01427: single-row subquery returns more than one row. How can I avoid this and achieve  want I want? My sql and Csharp is below:dbcon.Open();            string strreg = "SELECT Amount AS TotalAmount FROM Payment WHERE studentno=(SELECT tempcode FROM  specialstudent WHERE custid='"+ AreaNO +"')";    &...

Get resultset in one row
This one is really freaking me out. I'm trying to "shirk" a dataset before its returned to a dwo. My select is a union: SELECT account, sum(USD) as period 1, 0.00 as period_2, FROM...... WHERE pdate between '1998-01-01' and '1998-01-31' UNION ALL SELECT account, 0.00 as period 1, sum(USD) as period_2, FROM...... WHERE pdate between '1998-02-01' and '1998-02-28' This gives me result in the dw: ACCOUNT PERIOD_1 PERIOD_2 1010 ...

Return single colum from multiple rows into one row?
Often in my apps I'll create a table in SQL to house different variables. I do this so that I don't end up with multiple tables just to store a list of phone types for example. What I mean is let's say I have a form that would store contact information one of the controls might be a phone number and then next to that I'd have a drop down that would list the different phone types like Phone, Mobile, Fax etc rather than hard coding a specific textbox for only phone or only the mobile number. This way the user can determine white type of contact number it is.  With th...

Gridview Rows stretching in size when one row is returned
HiI hope this is a trival problem.My problem with the gridview is that when one row or only a few rows are returned to the gridview the spacing round the data in the row(s) increases. If there are many more rows, say 10 or more, the spacing is how I want it to be. I have tried setting the spacing for the rows in the properties of the row, but with no luck.Does any one have a way of fixing the row spaces regardless of the number of rows returned? Thanks Hi AndyMoireASP, It seems you have resolved the issue. If so, please post the solution and share with us. Thanks,Qin Dian TangMicro...

xml export rows as with template
Ive created a datawindow with a template. Now when i do a save as, it only returns one row? I am expecting all of the rows , using the template I have defined. > Ive created a datawindow with a template. Now when i do a > save as, it only returns one row? I am expecting all of > the rows , using the template I have defined. found the solution. Start Detail option!!! ...

SQL -- getting one row problem
Good afternoon, Here is the output of my script, and can’t understand why this is not working. Thanks for any and all pointers! Dave Gilden someone@monsoon:~/cgi-bin$ mail_guest_to_jk.cgi Use of uninitialized value in concatenation (.) or string at ./mail_guest_to_jk.cgi line 33. Use of uninitialized value in concatenation (.) or string at ./mail_guest_to_jk.cgi line 37. DBD::mysql::st execute failed: You have an error in your SQL syntax near '' at line 1 at ./mail_guest_to_jk.cgi line 60. DBD::mysql::st fetchrow failed: fetch() without execute() at ./mail_guest_to_jk.c...

How to read a dataset one row by one row?
I know you can use DataSet after close sql connection. I want to something after getting dataset, read from one row by one row, just like while datareader.read() Can I read dataset just like this? Thanks in advance! Justin Is this what you're looking for (from the Tips and Tricks Section of ASPNet101.com):http://aspnet101.com/aspnet101/tips.aspx?id=175David WierMCP/ASPInsiderASPNet101.com - where to look first!Control Grouper - easily control properties for multiple controls with one control!Calendar Express - The Best HTML Calendar Generator on the web!(Please 'Mark as Answer&...

Reduce rows displayed but putting 3 rows in one row
Hi all, I'm using PB6.5. I have an SQL DW that fetch some information from the DB but should display only two columns: a code and a value. Also they are displayed in the trailer of a group if that's actually important. In order to reduce displayed rows count, i would like to display each 3 rows in one row(Code,Value,Code,Value,Code,Value). The question is how can i do that?. I've tried to use computed fields and in the second and third pair values to use code[1],value[1],code[2],value[2] but only the code[1],value[1] are OK(Showing next row's data), the third ...

returning one row and actuallying getting data from it.
i don't want to do this via grids or any of that. that's the one complaint i have of asp.net, everyone wants to return data in these dang grids and all i want to do is run a query and extract the data i want. i already have the query: SELECT intUserID FROM tblUsers WHERE (txtUsername = @username) i just need to know how to get the resultset and get the value of the intUserID column and assign it to a variable. read the MSDN documentation about SQLCommand.ExecuteScalar() Method. They have some sample code too. hth***********************Dinakar NethiLife is sh...

FormView Paging Challenge: Get multiple rows out of one row using parsing.
I have a database which returns Articles, one row per Article. Each article has multiple fields, ID, Title, Subtitle, Summary, Text etc.I return the row from an ObjectDataSource which uses a stored procedure on a SQL Box.Inside the "TEXT" field, there is text -  [NEWPAGE] wherever a new page should start. eg. Content Content This is Content This is Content This is Content This is Content This is Content Content Content Content Content Content [NEWPAGE]This is Content  This is Content This is Content This is Content This is Content This is Content [NEWPAGE]This is Content&...

Calculated value of a field in the row is getting displayed in the next row instead of the same one
I have a Gridview in which I am pulling data from an access database. One of the columns in the gridview pulls a field named Last Login Date. I want to calculate the difference between the Last Login Date and the Current Date and display the calculated difference in an additional column in the same row. I have verified that the calculation is happening just fine. But when the page is displayed, the calculated value is getting displayed in the next row instead of the same row. The value of Difference in the first row is showing as 0 (zero). Calculated Values ...

Returning n rows, setting start row, total number of rows
Hi, I'm try to find out how ado.net can perform some of the things i am able to do in ado. Here is the scenario: I will be searching a table that may have 1000's of rows. I want to be able to dictate how many rows (page size) to return and what the starting row number is and be able to find out how many total rows would be returned by the query. Using old ado recordsets I was able to get the recordset and get the count or all the rows (.recordcount - ormaybe it was .rowcount) and then jump to the starting position (.absoluteposition). How can I do the equivalent in .net? From loo...

need help with ORA-01427: single-row subquery returns more than one row error
the following select in oracle 10g gives an error , need some help thanks   select 'AGD' BusUnitCode, 'A' LedgerCode, 'ACTUALS' LedgerName, (select sign(count(OBJECT_NAME)) from ALL_OBJECTS where OBJECT_ID = (select OBJECT_ID from ALL_OBJECTS where OBJECT_NAME='SALHARCAGD' ) ) LedgerHasArchive, ' ' DataAccessGroup from ALL_OBJECTS where (select OBJECT_ID from ALL_OBJECTS where OBJECT_NAME='SALFLDGAGD'and DATA_OBJECT_ID is not null ) is not null unionselect 'AGD', substr(KEY_FIELDS,1,1), substr(SUN_DATA,1,15), 0, substr(SUN_DATA...

Web resources about - How do i get one row if the resultset returned has no rows. - asp.net.sql-datasource

'jdbc' Top Users - Stack Overflow
Q&A for professional and enthusiast programmers

IBM Remote Supervisor Adapter - Wikipedia, the free encyclopedia
This is a full-length ISA or PCI adapter. The ISA version is very rare, and was only ever supported in one or two servers. This adapter can be ...

Java Developer Kit (JDK) Search Engine
Java Developer Kit (JDK) Search Engine


Java 7 Runtime and Cloud Endpoints Preview
... public static void viewTable(Connection con, String query) throws SQLException { try ( Statement stmt = con.createStatement(); ResultSet rs ...

The (Submission) Grinder
The market search page is very powerful and fliexible but can be daunting to new users. Here is a quick run down on how things work. The filters ...

All Packages from $.* to Freenet.*
Package Samples: Freenet Freenet.client Freenet.contrib.fproxy Freenet.message Freenet.crypt Freenet.node Freenet.support ...

The Goldman Sachs Group, Inc. - Selected Consolidated Financial Data
The selected historical consolidated income statement and balance sheetdata set forth below have been derived from Goldman Sachs' consolidatedfinancial ...

Clones of dapper-dot-net - dapper-dot-net - Simple SQL object mapper for ADO.NET - Google Project Hosting ...
My favorites ▼ - Sign in dapper-dot-net Simple SQL object mapper for ADO.NET Project Home Downloads Wiki Issues Source Repository: default wiki ...

A simple Delphi wrapper for Sqlite 3
Most applications use a database, and there are many excellent database engines to choose from, both free and commercial. SQLite is a smal

Resources last updated: 12/3/2015 10:22:46 PM