Forum Replies Created

Viewing 15 posts - 1,381 through 1,395 (of 1,413 total)

  • Reply To: Is It Worth Writing Unit Tests?

    In my opinion unit testing in a relational database is basically fruitless.  The whole idea is the data model is schema bound.  So what are you testing but only to...

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

    August 19, 2019 at 4:28 pm

    #3672368

  • Reply To: More performant way to write this where clause

    It's possible to get rid of "or" in where clause using DeMorgan's Law.  If the goal is readable code tho... sometimes that pretty much goes out the window.

    Some test data:

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

    August 19, 2019 at 1:08 pm

    #3672306

  • Reply To: using variables in for openrowset bulk update

    Last year I had to do something similar with a csv file.  Openrowset can't contain sql expressions, only literal strings.  So dynamic sql is the way to go.  Something like:

  • This reply was modified 6 years, 10 months ago by Steve Collins. Reason: Pasted incomplete code by accident

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

August 16, 2019 at 8:40 pm

#3671973

  • Reply To: Generate a string format based on fixed length and numeric units on increment

    Step 1: generate some dummy data

    /* create a table to hold the country and province values  */
    /* as well as a column to store the cntry_prov_code...

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

    August 16, 2019 at 4:39 pm

    #3671909

  • Reply To: SQL Collation different in tables and columns

    Chris Wooding wrote:

    If your database originally resided on a different server (with a different collation), columns created would have used that default. Columns created after the migration would default to the...

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

    August 16, 2019 at 2:27 pm

    #3671856

  • Reply To: Generate a string format based on fixed length and numeric units on increment

    Why do you declare province to be char(2) and then assign it left(col_name, 1) which is only 1 character?

    Does the sequence reset for each country?

    What is the needed sort order...

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

    August 16, 2019 at 1:34 pm

    #3671845

  • Reply To: Generate a string format based on fixed length and numeric units on increment

    Suppose the code you need to calculate is called the "Country Province Number" CPN.  The issue here is the example you provided only declares variables for a single record and...

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

    August 16, 2019 at 12:49 pm

    #3671819

  • Reply To: Generate sequence of stations

    You have 2 tables?

    1. Dates  -- contains the master list of dates.  Table has 1 column.
    2. Stations -- contains the date and # of stations.  Table has 2 columns

    The output...

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

    August 16, 2019 at 12:27 pm

    #3671805

  • Reply To: Insert multiple columns into one column sql

    The "or" we were looking for.  The second example, the one with "coalesce", consolidates the 3 columns (Employed, Not employed, Retired) into 1 column (Employed status) by selecting the first...

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

    August 16, 2019 at 11:58 am

    #3671802

  • Reply To: Insert multiple columns into one column sql

    The first post referenced:

    The employment status depending on the Name would be "Manager", "Fired", "Enjoying Life". I basically need to insert 3 different columns into a single column.

    Then you wrote:

    How...

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

    August 16, 2019 at 2:37 am

    #3671743

  • Reply To: Insert multiple columns into one column sql

    Well it's still not clear to me.  Here are examples of both.  If you're choosing concat then make sure table_b has wide enough column to fit (including the spaces).

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

    August 15, 2019 at 10:34 pm

    #3671711

  • Reply To: Insert multiple columns into one column sql

    Do you want concatenation or coalescence of the 3 columns?  Can be 1 and only 1 value per row in these 3 columns?  Could all 3 columns have values in...

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

    August 15, 2019 at 9:33 pm

    #3671703

  • Reply To: Syntax Inquiry

    You posted 2 code blocks.  It appears there is an issue with the second one.  Where you define the cte:

    VESValidEnrollList AS(
      SELECT 
       ...

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

    August 15, 2019 at 7:23 pm

    #3671665

  • Reply To: Syntax Inquiry

    So it's turtles all the way.  At some point you must have a statement that access the cte's yes?  Because if you define a cte and don't use it sql...

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

    August 15, 2019 at 5:49 pm

    #3671636

  • Reply To: Syntax Inquiry

    It won't let me post code.  Please add a select statement that references the common table expression.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

    August 15, 2019 at 5:34 pm

    #3671633

    • Viewing 15 posts - 1,381 through 1,395 (of 1,413 total)