Database

[REPOST] data between 1st jan to December 31st of previous year query --no hard coded dates.

1-Jan-18 7:11

Your requirements are bad. (I would presume and hope that they were made up by a teacher.)

The second two requirements can be expressed as
Req: Given today's date run a query that spans the 'prior' year based on that date.

The first requirement is incompatible with that and it is impossible to implement without other input data. One way to do that is in fact to hard code 2017 as a switch value. The second way is to presume that the target table has no data before 2017. However to implement that latter rule would mean that EVERY query would need to do a second query to determine that no data existed.

Not to mention that for users it would be confusing at least over time.

Richard Deeming8-Jan-18 7:27

Richard Deeming

8-Jan-18 7:27

You have already posted this in QA:
Data between jan 1st yyyy(always previous year) to december 31st yyyy(always previous year) query in where clause[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

OLE DB Data Types to C# Data Type Mapping

User9874325-Dec-17 17:55

25-Dec-17 17:55

I'm struggling with understanding, what I think are difference between the Types used in .NET framework and the OLE DB Data Types used in Access, Excel, etc.

For example:

Type indicator  OLE DB Name      .Net Type  Description
DBTYPE_UI8      BigInt           Int64      64-bit signed integer

What's does Int64 map to in C#? Am I using the correct terminology?

It's difficult to look up an answer to a question when you don't know what something is called.

Built-In Types Table (C# Reference) | Microsoft Docs[^]

Richard MacCutchan25-Dec-17 20:55

Richard MacCutchan

25-Dec-17 20:55

Mycroft Holmes25-Dec-17 21:33

Mycroft Holmes

25-Dec-17 21:33

This mapping may be more useful SQL Server Data Type Mappings | Microsoft Docs

Never underestimate the power of human stupidity
RAH

User9874325-Dec-17 22:57

25-Dec-17 22:57

Well, I just spent the better part of the afternoon yesterday creating this chart myself. LOL

Can't seem to get past this. Everything I do is giving me an invalid cast exception.

I'm using GetOleDbSchemaTable ( OleDbSchemaGuid.Columns, ...)

I thought had a breakthrough, when I read that my cast was failing because it needed unboxed, so this one worked:

long ORDINAL_POSITION = (long)row.Field<Int64> ( "ORDINAL_POSITION" );

But the Data_Type is not working. The documentation says it's a

UInt16

which I should be able to cast like this:

ushort DATA_TYPE = (ushort)row.Field<UInt16> ( "DATA_TYPE" );

but that gives me the same invalid cast exception. I can't even be sure that the cast that is causing the exception is ushort and UInt16 because I'm not understanding where to look.

jschell26-Dec-17 7:39

26-Dec-17 7:39

Presumably you verified that the exception is coming from that exact line.

Extract the column as a generic object and then print that out. That will tell you the exact data type.

If that doesn't help then post the output from the above along with the class cast exception text.

User9874314-Jan-18 10:44

14-Jan-18 10:44

Thank you. I'm not far enough along to know how to do that just yet, but this is something I've been wanting to learn. It turns out that I was fist having cast issues because of db data type vs my C# data types did not always match appropriately, but there was also an issue with DBNull being returned in numeric types in SQLServer, which is not acceptable for C# value types.

The lack of surety in programming is part of the reason software is fragile.

Richard Deeming8-Jan-18 7:38

Richard Deeming

8-Jan-18 7:38

You don't need the extra cast - the Field<T> method already returns the correct type. There's no boxing/unboxing involved.

long ORDINAL_POSITION = row.Field<Int64> ( "ORDINAL_POSITION" );
ushort DATA_TYPE = row.Field<UInt16> ( "DATA_TYPE" );

The C# types are just aliases for the .NET equivalents; so long === System.Int64, etc.

I suspect the documentation is probably wrong, and the DATA_TYPE column is most likely an Int32, which can then be cast to an OleDbType value. Check the value returned by table.Columns["DATA_TYPE"].DataType to see what it's defined as.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

User9874314-Jan-18 10:50

T-SQL and denomalization of Parent and Child hierarchy Tables

14-Jan-18 10:50

I'm not sure I understand that because I I really struggled with trying to get the Int64 to cast to long.

It *may* have been that I was getting DBNull back for value types out of SQL Server, which causes an exception. I left the explicit cases in my code just because I'm afraid of breaking it and I have moved on to something else, finally.

I appreciate your help and will exclude the explicit casts the next time I need to get the values form a database in some code that I feel more comfortable with. This code includes a ton of other C# constructs that are new to me, which lead to a pretty terrible debugging experience.

The lack of surety in programming is part of the reason software is fragile.

Member 1358789920-Dec-17 21:41

Member 13587899

20-Dec-17 21:41

The Problem:
Parent & child relationship currently captured in tblCGs and tbcs tables. The MasterClient are held in tblCGs table while Children are stored in tblCs tables using tblCGs. GroupMasterCustKey as key and CustomerKey as Child key.
• Currently a tblCGs table allows the linking of Parent using (GroupMasterCustKey) to child tblcs - (CustomerKey) table which may or may not exist.
AssociatedClient Field in the tblcs table: These are related clients without any hierarchical relationship.
• Currently the AssociatedClient on the tblCs table is too limiting varchar(20) and
can have implications or cause issues.

Proposed Solution:
• To Implement a new table structure where individual rows such as MIND227 - MIND227(customer ranges) are retrieved from the table rather than IND227 - IND229
• Move the Associatedclients Field in tblcs into a separate table with the following fields (ID, ClientID1, ClientID2..... , ClientRelationshipReasonID, and CreatedDate)

Please see the sample code below

IF OBJECT_ID('tempdb..#tblCGs') IS NOT NULL DROP TABLE #tblCGs
IF OBJECT_ID('tempdb..#tblCs') IS NOT NULL DROP TABLE #tblCs

--Create ClientGroups table.
CREATE TABLE #tblCGs
(
[GroupID] int NOT NULL, ---- IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[GroupMasterCustKey] varchar(100) NOT NULL, ---[MasterCAN]
[GroupFromCustKey] varchar(100) NOT NULL, --- CONSTRAINT [DF_tblCGs_GroupFromCustKey] DEFAULT (' '),
[GroupToCustKey] varchar(100) NOT NULL -- CONSTRAINT [DF_tblCGs_GroupToCustKey] DEFAULT (' '),
)

-- Populate the table with values.
INSERT INTO #tblCGs VALUES
(988, N'MIN036', N'MIND227', N'MIND229')
, (668, N'08035635', N'31036422', N'31036422')
,(669, N'08035635', N'31035623', N'31035623')

-- Create Customer table.
CREATE TABLE #tblCs ---Customers
([CustomerID] int not null ------IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
,[CustomerKey] varchar(100) NOT NULL --- [ChildCAN]
,[CompanyName] varchar(300) NULL
,[AssociatedClient] varchar(20)
,[CreatedOn] Datetime
)

--Populate the table with values.
INSERT INTO #tblCs VALUES
(72894, N'MIND227', N'MIND Mind in Haringey', N'MIN036','2011-11-16 00:00:00.000')
,(93469, N'MIND228', N'Caerphilly Borough Mind', N'MIN036','2014-03-08 00:00:00.000')
,(98199, N'MIND229', N'Brecon and District Mind', N'MIN036,BDC004,POW044','2014-11-26 00:00:00.000')
,(61705, N'31036422', N'Revive', N'Null','2010-04-01 09:13:10.550')
,(61729, N'31035623', N'Jysk Ltd', N'Null','2010-04-01 12:56:16.837')

--- Current Queries
SELECT *
FROM #tblCGs AS CG
INNER JOIN #tblCs AS C
ON C.CustomerKey >= CG.GroupFromCustKey
AND C.CustomerKey <= CG.GroupToCustKey

The desired output should like this and not output of the above query
ID ClientID1 ClientID2 ReasonTypeID
1 201 301 1
2 201 302 1
3 201 303 1
4 303 401 1
5 601 701 3

Thanks for your help

Re: T-SQL and denomalization of Parent and Child hierarchy Tables

Victor Nijegorodov21-Dec-17 1:54

Victor Nijegorodov

21-Dec-17 1:54

Is it your homework?

To convert an Excel file to CSV and then move that data from CSV to oracle database.

sai.201218-Dec-17 8:23

sai.2012

18-Dec-17 8:23

Hi all,

I have one task ,

To convert an Excel file to CSV and then move that data from CSV to oracle database.

EXCEL FILE HAS :3 COLUMNS

NAME,CITY,STATE .

my requirement is I need to convert from excel to csv and csv to oracle datatable I have to do in backend.

asked me to use sqlloader.

earlier I haven't worked on this type of ticket.

can you please give me an idea .

I have to do in backend not in .net application.

I am using ORACLE(TOAD)

What I have tried:

Hi all,

I have one task ,

To convert an Excel file to CSV and then move that data from CSV to oracle database.

EXCEL FILE HAS :3 COLUMNS
NAME,CITY,STATE .
my requirement is I need to convert from excel to csv and csv to oracle datatable I have to do sqlloader .

earlier I haven't worked on this type of ticket.

can you please give me an idea .

I have to do in backend not in .net application.

I am using PLSQL(TOAD)

Re: To convert an Excel file to CSV and then move that data from CSV to oracle database.

jschell18-Dec-17 15:51

Re: To convert an Excel file to CSV and then move that data from CSV to oracle database.

18-Dec-17 15:51

If it is one time...
1. You can save the file as CSV. Do a "save as" and then pick the 'type' via the drop down.
2. Then as noted you can use sqlloader.

If you must automate this to do it over and over then there are other problems you did not touch on like how you know there is a new file and how to recognize an update versus new.

But at any rate it is possible to read an excel file via an "ODBC" driver. I believe this is still possible, once you track down the driver. This would with very simple Excel files.

Richard MacCutchan18-Dec-17 21:35

Richard MacCutchan

18-Dec-17 21:35

Why do you need to convert to CSV, why not just read the data from the Excel file and send it direct to the database? And why have you posted the question twice with slightly different details?

Microsoft SQL Server VS PostgresSQL

Zeyad Jalil18-Dec-17 0:02

Zeyad Jalil

18-Dec-17 0:02

Hi All,

I have a new product must be cloud over an internet and we need to choose the best database for this issue from the pricing and performance issues.
we need to know the best for this, is it the Microsoft SQL Server or the PostgreSQL.
(SQL Server VS PostgreSQL).

also I need the best tutorial and videos about the PostgreSQL database.
Please Help me ASAP.

Thank You All

Re: Microsoft SQL Server VS PostgresSQL

Nathan Minier18-Dec-17 1:13

Nathan Minier

18-Dec-17 1:13

Performance from any database will vary greatly based on what the actual use case is. If you're looking to warehouse data the "best" product will be very different than if you need a speedy cache, for instance.

"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli

Re: Microsoft SQL Server VS PostgresSQL

jschell18-Dec-17 15:52