Category: Stackoverflow.com

Aug 30 2017

Comment by Seth Spearman on Understanding the recursive CTE termination check

sorry I was looking for a fiddle. The one in the comments above was not from you which I didn't notice...but sure enough your code does work. Here it is in a fiddle... sqlfiddle.com/#!6/b6587/36
Aug 30 2017

Comment by Seth Spearman on Understanding the recursive CTE termination check

,looking at my sqlfiddle, can you get sql fiddle to return in the order I am requesting? Your union would return all "headers" and then all "details". the ORDER is really the imortant thing in this question...header/details ... header/details. I thought recursion "might" solve this problem.
Aug 25 2017

Understanding the recursive CTE termination check

In this sqlfiddle...

http://sqlfiddle.com/#!6/b6587/6

I am getting the following error....

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I understand that there needs to be a "termination check" in the where clause of the second select of the CTE. Even if you uncomment the WHERE clause I get the same error.

I am just trying to understand 1) why it is needed at all...after all each order row has a relationship to each customer row and 2) since a "termination check" IS required what would that look like to get this example to work.

BTW, the output I would like to see for this query is as follows.

1,'George', 'Patton','',''
1,'','','<some date>', 'tank'
1,'','','<some date>', 'plane'
2,'Lewie', 'Puller','',''
2,'','','<some date>', 'Rifle'
2,'','','<some date>', 'Hand Grenade'

By the way, feel free to comment on other ways to get this kind of result (without using a recursive cte) but also without using a cursor or temp table. I would like to do this with a set-based operation.

EDIT

For the record, I know a join would work but I had a reason for asking about recursion. To give you context, I am working on a result set that is an EXPORT file. Each row is only one column called LINE. Furthermore, the export file needs to output in the order of my example...details need to be under header rows and that pattern needs to repeat...header/details ... header/details. I thought that maybe recursion could solve this problem. But I know simple join could solve it too as long as I can get the rows into the correct order...header / detail ... header / detail, maybe by having some way to do a column for sorting that numbers the output in the right way.

My export will NOT be depending on the calling app to format the data...the stored procedure needs to format the data.

Aug 25 2017

Understanding the recursive CTE termination check

In this sqlfiddle...

http://sqlfiddle.com/#!6/b6587/6

I am getting the following error....

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I understand that there needs to be a "termination check" in the where clause of the second select of the CTE. Even if you uncomment the WHERE clause I get the same error.

I am just trying to understand 1) why it is needed at all...after all each order row has a relationship to each customer row and 2) since a "termination check" IS required what would that look like to get this example to work.

BTW, the output I would like to see for this query is as follows.

1,'George', 'Patton','',''
1,'','','<some date>', 'tank'
1,'','','<some date>', 'plane'
2,'Lewie', 'Puller','',''
2,'','','<some date>', 'Rifle'
2,'','','<some date>', 'Hand Grenade'

By the way, feel free to comment on other ways to get this kind of result (without using a recursive cte) but also without using a cursor or temp table. I would like to do this with a set-based operation.

But mostly I am just trying to understand recursive CTEs.

Jun 23 2017

Comment by Seth Spearman on Does Mirth Open-Source support channels that work over https (using ssl)

However, am I correct in saying that to get the SSL Manager Extension will cost a minimum of $20K per year or can you buy/license JUST that extension? In my opinion, if you answer that you have to have a commercial license to use the SSL Manager then that makes Mirth Open-Source almost useless.
Jun 23 2017

Comment by Seth Spearman on Does Mirth Open-Source support channels that work over https (using ssl)

However, am I correct in saying that to get the SSL Manager Extension will cost a minimum of $20K per year or can you buy/license JUST that extension? In my opinion, if you answer that you have to have a commercial license to use the SSL Manager then that makes Mirth Open-Source almost useless.
Jun 23 2017

Does Mirth Open-Source support channels that work over https (using ssl)

This is basically a yes/no question. I am investigating Mirth Connect for my company but, at least to start out, we need this to work using only the Open Source version.

Of course, HIPAA requires that all message sending be done over HTTPS (using SSL).

Does the Open Source version support HTTPS out of the box.

I looked in the Administrator Channel setup and did not see an obvious way to force connections to use HTTPS. So, if it IS supported, how do you set it up?

Apr 19 2017

Answer by Seth Spearman for MS Access tabbed form multiple OnCurrent firings

I believe the proper way to do this is to not dynamically change the rowsource of the combobox. Instead, set the rowsource to a query using the query designer and have the query read the value from the "main form" just as is shown in your sample code...Forms![products main form]!ProductNumber.

Then I would trigger the refresh of the combo box using the MAIN FORMs AfterUpdate event. It will be something like MainForm!MySubForm.Form("mycombobox").requery. (I may not have that syntax exactly right...my access syntax is rusty.)

BTW, I don't like my own answer. The reason is that you are hard-coding a dependency between the subform and its parent. That might be fine if you will NEVER use that subform anywhere else. To get around this some Access Developers use a hidden form that has textboxes that you set from the Main Form's After Update event. Then the query is updated to refer to the the hidden form's textbox that you created for this form.

There are other ways to do this, too (public functions for example). But sorry, that is not really what you were asking for. My first method will work. But first time you need to re-use that subform you will need to refactor and the hidden form solution works pretty well.

Nov 09 2016

Comment by on Is there a way to aggregate a variable range of dates in SQL using a SET operation

...and it is incredibly fast...it runs in less than a second against my several hundred thousand data set. Genius.
Nov 09 2016

Comment by Seth Spearman on Is there a way to aggregate a variable range of dates in SQL using a SET operation

...and it is incredibly fast...it runs in less than a second against my several hundred thousand data set. Genius.