The Heap™ – Consultancy ©®

« first day (1423 days earlier) ← previous day next day → last day (3743 days later) »

ypercube

11:42

-1

Q: i want to create one generic query for all database like Oracle,Mysql,Mssql

I have to create different query for different database like Oracle,Mysql,Mssql .but i want to create only one generic query which runs or should works on all different database like Oracle,Mysql,Mssql.

Paul White

12:26

Good luck :)

2 hours later…

Paul White

14:09

@PaulWhite Good point. Removed from commented section too as it might be useful if user wants table valued-function. Any chance for vote up? Just saying :) — Pavel Nefyodov 18 mins ago

Nope. Not now you asked.

ypercube

@PaulWhite Is the query correct? He has put some effort into it.

Paul White

No idea. Too tired right now. I'll look tomorrow.

ypercube

I looked at the question a few days ago but I couldn't find a way with window functions. It seems not difficult with a recursive but I couldn't get around it with window functions.

Mikael Eriksson

@PaulWhite The problem in that Q is much like some part of the SQLUG (Swedish SQL Server User Group) challenge for this year. Link to something Swedish. It is about calculating reimbursements for doctors based on visits and a code. The T codes have a rule that says that they should only be used if they where not used within the last 90 days.

Paul White

@ypercube You should post a recursive solution. That is what the question asks for, after all. I suppose a window function solution will be more interesting, and probably more efficient if done well.

@MikaelEriksson Yes these problems come up semi-regularly it seems. Ought to be easy, doubt it is.

ypercube

14:19

@MikaelEriksson Nice catch. Is there a date for that (competition?) Is there a prize?

Paul White

The question seems flawed though. Is he expecting the TVF-in-a-computed-column to automatically recalculate when any affected underlying data changes? Cos he'll be disappointed.

ypercube

2014-11-30 kl 22:00, as mentioned in the "start" page?

Mikael Eriksson

@ypercube You have until Sunday but you also have to be a member of the UG. Prize is I believe 950 swedish kronor and the honor of calling yourself the swedish champion of TSQL for one year.

Paul White

Google says that's USD 128.

Mikael Eriksson

Yep in the form of a gift card in SIBA. (computers and electronics and stuff)

I think it is a tax thing to stay below 1000 SEK

I'm in the competition by the way so I will not help this guy, just in case.-.... :)

ypercube

14:26

Just saw, you have place No 2 till now.

Paul White

@MikaelEriksson Not very community-spirited of you ;-)

Mikael Eriksson

@PaulWhite Well... there is money involved and a lot more than regular HEAP fees.

Paul White

Yes I understand of course :)

Mikael Eriksson

@ypercube So far yes. These preliminary results sometimes change because they sometimes add more data for the final run. I also have one more version (max 3 version) that I can file and it looks like that one will cut my times down by 50% or so. At least in my tests with my test data.

Paul White

Something tells me you're not using xml for this.

ypercube

14:30

I wonder if you should comment with the info about the competition or even flag the question.

Lamak

@MikaelEriksson we should bet on this

Mikael Eriksson

@PaulWhite I use some XML :) but I gave up on set based and did RBAR natively compiled SP executed in parallel using service broker.

Paul White

Is SQLCLR allowed?

Mikael Eriksson

@PaulWhite And I used your version of select distinct .. that uses a recursive CTE.

No CLR allowed

Paul White

@MikaelEriksson Cool.

Lamak

14:35

@MikaelEriksson they should put some of the arbitrary restrictions that we see on some SO questions. Like, "no subquery allowed and no group by"

Mikael Eriksson

@Lamak He, in native compiled SP sub-queries are actually not allowed.

And alot of other useful stuff like CASE and OR.

Paul White

Writing native procedures is a great exercise in back-to-basics.

Like being a programmer in the 60s perhaps.

Mikael Eriksson

@PaulWhite Yes the code looks much like this.

Punched card

A punched card, punch card, IBM card, or Hollerith card is a piece of stiff paper that contained either commands for controlling automated machinery or data for data processing applications. Both commands and data were represented by the presence or absence of holes in predefined positions. Now obsolete as a recording medium, punched cards were widely used throughout the 19th century for controlling textile looms and in the late 19th and early 20th century for controlling fairground organs and related instruments. Punched cards were used through most of the 20th century in what became known as...

2 hours later…