supers-affiliate

How To Convert Some Records Ins Sql Server

2024-08-21T17:43:00.001-07:00

I have a column with values like this: ## codel ## 1-829549-305-117 1-1196585-305-119 119.305.1983984.1 // this record 1-224594-305-121 1-1999987-305-121 122.306.113416.1 // this r

Solution 1:

Just replace the - with ., because PARSENAME() works only with '.', not '-'

WITH TBL AS
(
  SELECT'1-829549-305-117' Str
  UNIONSELECT'1-1196585-305-119'UNIONSELECT'119.305.1983984.1'UNIONSELECT'1-224594-305-121'UNIONSELECT'1-1999987-305-121'UNIONSELECT'122.306.113416.1'UNIONSELECT'1-158059-305-122'UNIONSELECT'1-1083888-305-126'
),
CTE AS
(
  SELECT Str,
         CASEWHEN CHARINDEX('.', Str) >0THEN
                   Str
              ELSE REPLACE(Str, '-', '.')
         END Str1
         ,
         CASEWHEN CHARINDEX('.', Str) >0THEN'.'ELSE'-'END Sep
  FROM TBL
)
SELECT Str,
       CONCAT(PARSENAME(Str1,1), Sep,
              PARSENAME(Str1,2), Sep,
              PARSENAME(Str1,3), Sep,
              PARSENAME(Str1,4)
              ) ResultFROM CTE;

Returns:

+-------------------+-------------------+|        Str        |Result|+-------------------+-------------------+|1-1083888-305-126|126-305-1083888-1||1-1196585-305-119|119-305-1196585-1||1-158059-305-122|122-305-158059-1||119.305.1983984.1|1.1983984.305.119||1-1999987-305-121|121-305-1999987-1||122.306.113416.1|1.113416.306.122||1-224594-305-121|121-305-224594-1||1-829549-305-117|117-305-829549-1|+-------------------+-------------------+

Live Demo

If you have SQL Server 2017, then you can use CONCAT_WS() as

SELECT Str,
       CONCAT_WS(Sep, 
              PARSENAME(Str1,1), 
              PARSENAME(Str1,2), 
              PARSENAME(Str1,3), 
              PARSENAME(Str1,4)
              ) Result

FROM CTE;

If you want the separator to be always '-'then no need to Sep, just directly '-'

Solution 2:

You just need to add this code

SELECT [Codel],
caseWHEN  (CHARINDEX('.',[Codel]) >0) then 

    CONCAT(PARSENAME([Codel], 1), '-',
           PARSENAME([Codel], 2), '-',
           PARSENAME([Codel], 3), '-',
           PARSENAME([Codel], 4))  
           else [Codel] end
            true_template

  FROM [TestDB].[dbo].[CodelTbl]

Solution 3:

Here is how you can split your string based on "." and use the values separately:

createtable #temp (rn intidentity(1,1), num int)

Declare@productsvarchar(200) ='119.305.1983984.1'Declare@individualvarchar(20) =null

WHILE LEN(@products) >0BEGIN
    IF PATINDEX('%.%', @products) >0BEGINSET@individual=SUBSTRING(@products,
                                    0,
                                    PATINDEX('%.%', @products))
        insertinto #temp
        SELECT@individualSET@products=SUBSTRING(@products,
                                  LEN(@individual+'.') +1,
                                  LEN(@products))
    ENDELSEBEGINSET@individual=@productsSET@products=NULLinsertinto #temp
        SELECT@individualENDENDselect*from #temp

Ref: How do I split a string so I can access item x?

Creating Self-referential Tables With Polymorphism In Sqlalchemy

2024-08-21T16:48:00.001-07:00

I'm trying to create a db structure in which I have many types of content entities, of which one, a Comment, can be attached to any other. Consider the following: from datetime imp

Solution 1:

Try to supplement your Comment.__mapper_args__ to:

__mapper_args__ = {
    'polymorphic_identity': 'comment',
    'inherit_condition': (id == Entity.id),
}

P.S. I haven't understand what you need _idref relationship for? If you want to use a comment somewhere where Entity is expected you could just pass the Comment instance as is.

Solution 2:

To supplement @nailxx's answer: Repeating all that boilerplate is tedious if you have many dependent tables. Answer : move it all to a metaclass.

classEntityMeta(type(Entity)):
    def__init__(cls, name, bases, dct):
        ident = dct.get('_identity',None)
        if'__abstract__'notin dct:
            xid = Column(None, ForeignKey(Entity.id), primary_key=True)
            setattr(cls,'id',xid)
            setattr(cls,'__mapper_args__', { 'polymorphic_identity': dct['_identity'], 'inherit_condition': (xid == Entity.id,), 'primary_key':(xid,) })
            setattr(cls,'__tablename__',name.lower())
        super(EntityMeta, cls).__init__(name, bases, dct)

classEntityRef(Entity):
    __abstract__ = True
    __metaclass__ = EntityMeta

classComment(EntityRef):
    _identity = 'comment'
    [...]

Variations, as an exercise for the reader: You can omit the _identity=… statement and use the class name, as in the setattr(cls,'__tablename__',…) line. Or not overwrite an existing __tablename__ or __mapper_args__ attribute.

Be sure to test dct and not cls for existence: the latter will find attributes in the parent class, which you obviously do not want at this point.

Sqlite3_open: "unable To Open Database File"

2024-08-21T15:19:00.001-07:00

I get 'unable to open database file' when executing rc = sqlite3_open('test.db', &db); ?? sqlite3 *db; // sqlite3 db struct char *zErrMsg = 0; int rc; // Open the test.db fil

Solution 1:

sqlite3_open returns an error if the database does not already exist. To create the database if it doesn't already exist, use sqlite3_open_v2 with the SQLITE_OPEN_CREATE and SQLITE_OPEN_READWRITE flags (both are required):

rc = sqlite3_open_v2(/* DB Path */, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);

Reference

To find /* DB PATH */, you need to base the filename off of the documents directory:

- (NSString *) applicationDocumentsDirectory 
{    
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *basePath = ([paths count] > 0) ? [paths objectAtIndex:0] : nil;
    return basePath;
}

You can use this convenience method to combine the documents path with the database name:

NSString *documents = [self applicationDocumentsDirectory];
NSString *dbPath = [documents stringByAppendingPathComponent:@"test.db"];

rc = sqlite3_open_v2([dbPath UTF8String], &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);

Solution 2:

#define SQLITE_OK 0
sqlite3 *db;
char *zErrMsg=0;
if(sqlite3_open("demo.db", &db) == SQLITE_OK)
{
    if(sqlite3_exec(db, "select * from Users", callback, 0, &zErrMsg) == SQLITE_OK)
            msgbox1("success");
    sqlite3_close(db);
}
elsemsgbox1("failure");

here is the sample code which i use;

Join A Table Using On Multiple Columns Using A Or Syntax

2024-08-21T14:30:00.001-07:00

So what I'm trying to do is :- SELECT * FROM TableA JOIN TableB ON TableA.OriginPhoneNumber=TableB.Id OR TableA.DestinationPhoneNumber=TableB.Id Rather strange query I know! But

Solution 1:

Just do a cross join with a where clause

var results = from a in db.TableA
              from b in db.TableB
              where a.OriginPhonenumber == b.Id 
                    || a.DestinationPhoneNumber == b.Id
              selectnew { A = a, B = b };

It's doubtful that an or in a join condition would be more efficient than this, but it's likely that either would result in the same execution plan. Performance aside it will give the same results.

Solution 2:

I used Union

var firstJoin=from tbl in TableA
    join c in TableB
        on c.OriginPhoneNumber Equals tbl.Id

var secondJoin=from tbl in TableA
    join c in TableB
        on c.DestinationPhoneNumber Equals tbl.Id

var result=firstJoin.Union(secondJoin)

Activity Not Properly Managing Cursor.

2024-08-21T14:11:00.001-07:00

I have an activity that populates a list and does some other things from a cursor. I get the cursor from a method that returns it based on a standard android SQLite query to my dat

Solution 1:

Try closing the cursor in the onPause() (cursor.close()). If you are using a SimpleCursorAdapter you should also detach the cursor from the adapter.

You can requery for the cursor in the onResume() method if you wish.

Solution 2:

Well, it turns out that Cursor.requery() is depreciated and I was getting the same error every time I tried to call it. startManagingCursor() was calling requery, as I suppose it should, and that in turn was causing my problems there... I'm not sure why requery is causing an error in this instance, I've used it successfully before.

I ended up rearranging some code, and putting my cursor = SQLHelper.getwhatevercursor() code into onResume() instead of onCreate, as onResume is called right after onCreate anyway. As recommended by Nikola I am also closing the cursors in onPause().

This seems to be working now...

Interpreting Results From Explain Analyze In Postgres

2024-08-21T13:55:00.001-07:00

Recently did a query which took about 9 minutes to complete. Attempting to determine why, I used EXPLAIN ANALYZE to help solve the problem. From the output, it looks as though ever

Solution 1:

Looking at your query exec plan a lot of time is spent on sorting

"Sort  (cost=18.01..18.01 rows=1 width=86) (actual time=541075.198..541099.504 rows=65354 loops=1)""  Sort Key: r.resource_id, s.start_date""  Sort Method: external merge  Disk: 8016kB"

I would recommend you to increase work_mem param for PostgreSQL as described here

If you do a lot of complex sorts, and have a lot of memory, then increasing the work_mem parameter allows PostgreSQL to do larger in-memory sorts which, unsurprisingly, will be faster than disk-based equivalents.
This size is applied to each and every sort done by each user, and complex queries can use multiple working memory sort buffers. Set it to 50MB, and have 30 users submitting queries, and you are soon using 1.5GB of real memory. Furthermore, if a query involves doing merge sorts of 8 tables, that requires 8 times work_mem. You need to consider what you set max_connections to in order to size this parameter correctly. This is a setting where data warehouse systems, where users are submitting very large queries, can readily make use of many gigabytes of memory.
log_temp_files can be used to log sorts, hashes, and temp files which can be useful in figuring out if sorts are spilling to disk instead of fitting in memory. You can see sorts spilling to disk using EXPLAIN ANALYZE plans as well. For example, if you see a line like Sort Method: external merge Disk: 7526kB in the output of EXPLAIN ANALYZE, a work_mem of at least 8MB would keep the intermediate data in memory and likely improve the query response time (although it may take substantially more than 8MB to do the sort entirely in memory, as data on disk is stored in a more compact format).

How Can I Generate A Date Table With Fields Equals To "dayofweek", "weekofyear" Etc ;and Rows Equals To Date From 2010-01-01 Till Current_date

2024-08-21T13:17:00.001-07:00

How can i generate a Date table with fields like 'Dayofweek', 'weekofyear' etc ;and rows equals to date from 2010-01-01 till current_date like below: Dayofweek Dayof

Solution 1:

Solution for Hive:

set hivevar:start_date=2010-01-01; --replace with your start_date
set hivevar:end_date=current_date; --replace with your end_date

with date_range as 
(--this query generates date range
select date_add ('${hivevar:start_date}',s.i) as dt 
  from (select posexplode(split(space(datediff(${hivevar:end_date},'${hivevar:start_date}')),' ')) as (i,x) ) s
),

holiday as (select stack(5, --add more
             '01-01', 'New Year',
             '01-21', 'Martin Luther King Day',
             '02-18', 'Presidents Day',
             '05-27', 'Memorial Day',
             '07-04', 'Independence Day') as ( mtdt,holiday_name)
) 

select d.dt                                 as date,
       date_format(current_date,'u')        as dayofweek,
       day(dt)                              as dayofmonth,
       date_format(current_date,'D')        as dayofyear,
       date_format(current_date,'W')        as weekofmonth,
       weekofyear(dt)                       as weekofyear, 
       casewhen h.mtdt isnotnull then 'Y' else 'N' end as Holiday,
       h.holiday_name
  from date_range d 
       left join holiday h onsubstr(d.dt,6)= h.mtdt
;

Add more holidays.

Solution 2:

You can get a calendar table with the following solution:

SELECT calendar.*, 
    DATEPART(dw, calendar.date_value) AS DayOfWeek, 
    DATEPART(dd, calendar.date_value) AS DayOfMonth, 
    DATEPART(dy, calendar.date_value) AS DayOfYear,
    DATEPART(wk, calendar.date_value) AS WeekOfYear, 
    CASEWHEN holidays.date_value ISNULLTHEN'N'ELSE'J'ENDAS Holiday 
FROM (
  SELECT DATEADD(DAY, t4 *10000+ t3 *1000+ t2 *100+ t1 *10+ t0, '1970-01-01') AS date_value FROM
    (SELECT0 t0 UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9) t0,
    (SELECT0 t1 UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9) t1,
    (SELECT0 t2 UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9) t2,
    (SELECT0 t3 UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9) t3,
    (SELECT0 t4 UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9) t4
) calendar INNERJOIN (
  SELECT'2010-01-01'AS date_value UNIONSELECT'2010-01-06'
) holidays ON calendar.date_value = holidays.date_value
WHERE calendar.date_value BETWEENCAST('2010-01-01'ASDATE) AND GETDATE()
ORDERBY calendar.date_value

The basic calendar table is based on this solution on StackOverflow. With the INNER JOIN you can add some holidays.

With DATEPART you can get some information about the date.

"object Was Open" Error Using Delphi, Sql Server And Ado

2024-08-21T12:57:00.001-07:00

I am getting an 'Object Was Open' error when opening a TADOQuery which returns a large dataset (around 700,000 rows and 75 columns). 8 of my fields are derived fields as varchar(20

Solution 1:

I was receiving the same error and, for me, some changes to my TAdoQuery properties fixed it. My situation is somewhat different than yours, so I'll describe it before getting to the changes that worked for me.

I have a fairly large table; 684,673 rows, 107 columns and a data size of 636240 KB. It has three sets of repeating columns that I'm going to normalize out to three new tables. The query?

SELECT*FROM MyTable

So this is just a straight run through the table, one direction only. The processing has no need for any particular order, so adding indexes, beyond the primary key, won't help. Since I'm making no changes to this table, it's a read-only proposition. Nothing needs to be displayed.

I was receiving the error in the Delphi IDE when I simply tried to set this table's TADOQuery.Active property to true. In other words, just trying to open it in the IDE threw the error. There's no point in examining any of my code before I can successfully open this in the IDE.

I made the following changes to this table's TADOQuery:

CommandTimeout: 600

CursorLocation: clUseServer

CursorType: ctOpenForwardOnly

EnableBCD: False

LockType: ltReadOnly

The error no longer happens, either in the IDE or in my processing code.

It may be that only one of these changes was necessary. If so, I don't know which because I didn't test them one-at-a-time. I simply made all the changes that looked like candidates to give the query the best chance of success.

Solution 2:

Just change the query of YourADOQuery (if applied) before setting it's Active property to true as below:

YourADOQuery.SQL.Text := 'select top 100 * from ' + YourADOQuery.SQL.Text + ')a';

Note that 100 is NOT '100 PERCENT'! but returns 100 percent of records :)

How Can I Query A Sql Server Xml Column And Return All Values For A Specific Node?

2024-08-21T12:38:00.001-07:00

I have the following XML in a XML column in SQL Server. 111a match was not found

Solution 1:

SQLFiddle for the same: Solution as per @xQbert suggested

create table Temp (col1 xml)
go

insert into Temp (col1)
values('<qualifiers><qualifier><key>111</key><message>a match was not found</message></qualifier><qualifier><key>222</key><message>a match was found</message></qualifier><qualifier><key>333</key><message>error</message></qualifier></qualifiers>')
go

SELECT
    STUFF((SELECT 
              ',' + fd.v.value('(.)[1]', 'varchar(50)')
           FROM 
              Temp
           CROSS APPLY
              col1.nodes('/qualifiers/qualifier/message') AS fd(v)
           FOR XML PATH('')
          ), 1, 1, '')

Solution 2:

I believe that is what you are looking for : -- it is from the LINK : -- http://blog.sqlauthority.com/2009/02/13/sql-server-simple-example-of-reading-xml-file-using-t-sql/

DECLARE @MyXML XML
SET @MyXML = '<SampleXML>
<Colors>
<Color1>White</Color1>
<Color2>Blue</Color2>
<Color3>Black</Color3>
<Color4 Special="Light">Green</Color4>
<Color5>Red</Color5>
</Colors>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3>Grapes</Fruits3>
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>'

SELECT
a.b.value('Colors[1]/Color1[1]','varchar(10)') AS Color1,
a.b.value('Colors[1]/Color2[1]','varchar(10)') AS Color2,
a.b.value('Colors[1]/Color3[1]','varchar(10)') AS Color3,
a.b.value('Colors[1]/Color4[1]/@Special','varchar(10)')+' '+
+a.b.value('Colors[1]/Color4[1]','varchar(10)') AS Color4,
a.b.value('Colors[1]/Color5[1]','varchar(10)') AS Color5,
a.b.value('Fruits[1]/Fruits1[1]','varchar(10)') AS Fruits1,
a.b.value('Fruits[1]/Fruits2[1]','varchar(10)') AS Fruits2,
a.b.value('Fruits[1]/Fruits3[1]','varchar(10)') AS Fruits3,
a.b.value('Fruits[1]/Fruits4[1]','varchar(10)') AS Fruits4
FROM @MyXML.nodes('SampleXML') a(b)

Sql Xml Path With Different Children

2024-08-21T12:18:00.001-07:00

I have done a lot of XML PATH statements, but this one escapes me or might not even be possible with multiple different children. The end result should look like this

Solution 1:

Right, you have a multitude of issues to overcome with your sample!

Firstly, I'll give you the answer, although note that in order to process hierarchically correctly it had to be a recursive function, so the test data you provided had to be created in permanent tables rather than temporary (simpler) and then I'll point out a few useful techniques that I used in it to solve the problem.

ALTERFUNCTION GetTasks (@ParentIdvarchar(255)=NULL) 
RETURNS
XML
BEGINDECLARE@ReturnXML XML

SELECT@ReturnXML= 
(
    SELECT
    (
        SELECTCONVERT(XML,
                --Main task start tag'<'+master_t.TaskType+' Name="'+master_t.TaskName+'">'+CONVERT(VARCHAR(MAX),
                        (

                            SELECT
                            dbo.GetTasks(master_t.TaskId),
                            (
                                SELECTCONVERT(XML,'<'+DetailName+'>'+DetailValue+'</'+DetailName+'>')
                                FROM
                                    TASK_DETAILS t 
                                WHERE
                                    TaskId = master_t.TaskId
                                FOR XML PATH(''),Type
                            ),
                            (
                                SELECT Name,Value,Type FROM TASK_PARAMETERS t 
                                WHERE TaskId=master_t.TaskId
                                FOR XML PATH('Parameter'),Type
                            ) 'Parameters'FOR XML PATH(''),Type 
                        )
                    )
                    +--Main task end tag'</'+master_t.TaskType+'>'
            )
        FROM 
            TASK master_t
        WHERE--Effectively ignore the parentId field if it is not passed.
            ISNULL(ParentTaskId,'') =CASEWHEN@ParentIdISNULLTHEN''ELSE@ParentIdENDFOR XML PATH(''),Type
    ) 'TaskList'FOR XML PATH(''),Type
) 

RETURN@ReturnXMLEND
GO

Call this function like this:

SELECT dbo.GetTasks(NULL)

Right the techniques that I think are worth noting are:

a) You can manually create xml nodes by simply building them from strings- this is useful if the node names are in the table. The only thing you have to be aware of is that to put an open and closing tag around a block, you will probably have to convert the block to a string first, tack on the tags, and then convert the whole thing to xml (piecemeal wont work as the convert-to-xml function would expect you to provide well formed XML.

b) You sometimes have to nest things in brackets to achieve a tag around all the subtags... An example makes this clearer:

SELECT 
    TaskName
    FROM TASK t
    FOR XML PATH('SomeRoot')

would yield:

<SomeRoot><TaskName>Get Report Parameters</TaskName></SomeRoot><SomeRoot><TaskName>Loop Report Creation</TaskName></SomeRoot><SomeRoot><TaskName>Report In Loop</TaskName></SomeRoot><SomeRoot><TaskName>Get Email Addresses</TaskName></SomeRoot><SomeRoot><TaskName>Loop Mail Creation</TaskName></SomeRoot><SomeRoot><TaskName>Send Email In Loop</TaskName></SomeRoot>

To get the "SomeRoot" to appear around it, you could do this:

SELECT 
(
    SELECT 
        TaskName
    FROM TASK t
    FOR XML PATH(''),Type
) 
FOR XML PATH('SomeRoot')

If the node name is static (Note the XML PATH(''),Type, which basically ensures that XML path returns XML type data for further processing and doesn't escape it)

If the node name is NOT static, you're stuck with something like this, with the need to convert to and from string to make it work.

SELECTCONVERT(XML,
        '<'+DynamicName+'>'+CONVERT(VARCHAR(MAX),
                (
                    SELECT 
                        TaskName
                    FROM TASK t
                    FOR XML PATH(''),Type
                )
            ) +'</'+DynamicName+'>'  
    )
FROM
    (SELECT'Test'as DynamicName) a

c) Regarding your question about getting different children tags to appear on the same level, it's pretty trivial, and you just have to remember that the usual problem of multiple layers of select ceases to be a problem with xml as an xml select just returns a single xml object. You can then use XML PATH to combine those results in a tree too.

e.g.

SELECT 
    (SELECT top 1*FROM TASK FOR XML PATH(''),Type),
    (SELECT top 1*FROM TASK_DETAILS FOR XML PATH(''),Type)

Will return a single row with two columns but if you then apply XML PATH('') To the whole, you've combined them on the same level

SELECT 
    (SELECT top 1*FROM TASK FOR XML PATH(''),Type),
    (SELECT top 1*FROM TASK_DETAILS FOR XML PATH(''),Type)
FOR XML PATH('Root')

d) Column names are converted into nodes for you if by XML PATH. Attributes are quite easy in that you just give the column an alias which is the appropriate xsl path e.g. 'MyNodeName\@MyAttributeName' obviously this precludes attributes that are also dynamically named. For that, in this example, I just built the xml from strings again. This incidentally is why dynamic node names is such a bad idea-- you basically allow your routine to create new attribute names and node names via data in the table... this means that you can't create a decent schema for your routine as you don't know in advance what data might be in the table...

Moving on :)

So, given those building blocks, the easiest thing to do is work from the deepest level, and build it block by block, then combine like above.

I did that for your query, and eventually realised that to make it work hierarchically (i.e. n-nested levels) I had to write is as a function returning XML, which is called passing the parentnode to it (so that the function knows what to filter the result set down to). This will die a horrible death if you hierarchy is ill-formed and circular.

Okay- hopefully there's something in there you can work with. This is purely XML PATH() oriented solution - there are alternative XML methods which can be useful in different situations.

Greatest N-per-group With Multiple Joins

2024-08-21T11:59:00.001-07:00

Evening, I am trying to get an output of rows that are limited to n per group in MySQL. I can get it to work without joins, but with it I am just shy. I've pasted a dump of the rel

Solution 1:

This works:

SET@num :=1, @prevCat :=0;
SELECT title, start, end, type, description, linkOut, outType, catRef, row_number
FROM (
SELECT title, start, end, type, description, linkOut, outType, catRef,
@num := if(@prevCat= catRef, @num+1, 1) as row_number,
@prevCatAS tog,
@prevCat := catRef AS dummy
FROM (
    SELECT title, start, end, resources.type, description, linkOut, outType, catRef
    FROM resources LEFTJOIN placesRel ON placesRel.refId = resId LEFTJOIN catRel ON catRel.refId = resId
    WHERE status ='live'AND placesRel.type ='res'AND catRel.type ='res'ORDERBY catRef
) AS w
) AS x WHERE x.row_number <=4;

You need to put your joined query in a sub-query and order it by the column you want to group by. Use it's parent query to add row numbers. Then, the top-level query glues it all together.

If you don't put your joined query in it's own sub-query, the results won't be ordered as you wish, but instead will come out in the order they are in the database. This means the data is not grouped, so row numbers will no be applied to ordered rows.

How To Use Fuzzy Look Up To Find The Sentence In Sql?

2024-08-21T11:39:00.001-07:00

search term=['ISBN number on site'] the variable(column): sentence, in MySQL table. It consist many different sentence. the sentence I want to look for is 'The AutoLink feature

Solution 1:

This is not a simple question. Your best bet is to use some type of fulltext search. Fulltext search can be configured to have stopwords (words that are omitted from search - like the word the) and can have a minimum word length limit as well (words with less than certain characters long are also omitted from the search.

However, if you simply use

SELECT*FROM testtable
WHEREMATCH (sentence)
AGAINST ('ISBN number on site');

Then MySQL will return not just the record with the value you were looking for, but the records that have some of the words only, and in different order. The one you showed will probably be one of the highest ranking one, but there is no guarantee that it will be highest ranked one.

You may want to use Boolean fulltext search and prepend + to every search word to force MySQL to return those records only that have all the search words present:

SELECT*FROM testtable
WHEREMATCH (sentence)
AGAINST ('+ISBN +number +on +site'INBOOLEAN MODE);

But, on should either be a stopword (it is on the default stipword lists) or should be shorter that the minimum word length, so should be omitted from the search expression (you will not get back any results):

SELECT*FROM testtable
WHEREMATCH (sentence)
AGAINST ('+ISBN +number +site'INBOOLEAN MODE);

I know that this requires alteration of the search expression, however this will get you the best results using MySQL's built-in functionality.

The alternative is to use other fulltext search engines, such as sphinx to perform the search for you.

Solution 2:

Try:

SELECT*FROM testtable where Sentence like'%ISBN number on%site%' ;

The wildcard can go in the middle of a string too.

How To Generate Fibonacci Series

2024-08-21T08:42:00.001-07:00

How to Generate Fibonacci series in sql ! I need to generate Fibonacci series 0 1 1 2 3 5 8 13 21 ... N I did this easily using C-code I need to do this using Sql !

Solution 1:

Try This Simple Query:

1) For Result In Row-by-Row (Single Column, Multiple Rows)

WITH Fibonacci (PrevN, N) AS
(
     SELECT0, 1
     UNION ALL
     SELECT N, PrevN + N
     FROM Fibonacci
     WHERE N < 1000000000
)
SELECT PrevN as Fibo
     FROM Fibonacci
     OPTION (MAXRECURSION 0);

Output 1:

2) For Result in Only One Row (Comma sepreted, in Single Cell)

WITH Fibonacci (PrevN, N) AS
(
 SELECT0, 1UNIONALLSELECT N, PrevN + N
    FROM Fibonacci
    WHERE N <1000000000
)
SELECTSubstring(
    (SELECTcast(', 'asvarchar(max)) +cast(PrevN asvarchar(max)
);
FROM Fibonacci
FOR XML PATH('')),3,10000000) AS list

Output 2:

Solution 2:

Try this !

declare@aintdeclare@bintdeclare@cintDeclare@iintset@a=0set@b=1set@i=0set@c=0
Print 'Fibonacci Series'
print @a
print @b
while @i<10Beginset@c=@a+@b
print @cset@i=@i+1set@a=@bset@b=@cend

Outputs !

Fibonacci Series

0 1 1 2 3 5 8 13 21 34 55 89

Solution 3:

Please try:

SELECT0AS fib_number UNIONALLSELECTFLOOR(POWER(( 1+SQRT(5) ) /2.0, number) /SQRT(5) +0.5)
FROM master..spt_values
WHERE TYPE ='p'AND number BETWEEN1AND70

Solution 4:

Try this :-

Declare @Fib int = 5
 ;with cte as
 (
   Select0as Previous,1asNext ,0as Level 
   union all
   SelectNext,Next + Previous,Level +1from cte 
   where Level +1 <@Fib
 )
 Select Previous as FibonacciSeries from cte

Solution 5:

This is for generating the first 10 numbers in the series.

DECLARE@NoOneINT,  @NoTwoINTDECLARE@FibonacciTableTABLE (Number INTNOTNULL)
--Insert first two numbers 0 and 1INSERT@FibonacciTable (Number) SELECT0UNIONALLSELECT1SELECT@NoOne=0, @NoTwo=1

WHILE (SELECTCOUNT(*) FROM@FibonacciTable) <10BEGININSERT@FibonacciTable (Number) VALUES(@NoOne+@NoTwo)
    SELECT@NoTwo=@NoOne+@NoTwo, @NoOne=@NoTwo-@NoOneENDSELECT*FROM@FibonacciTable   
GO

Sql Server Char And Nchar Columns Search Differently

2024-08-21T08:24:00.001-07:00

We have a strange problem. We are upgrading JDE and the database schema is changing - some char columns are changing to nchar types. However, we have found that some of the searc

Solution 1:

From the documentation for LIKE (Transact-SQL):

When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks are not significant.

I reproduced your problem with the following table:

DECLARE@tTABLE(x NCHAR(25));
INSERT@tSELECT N'nanaS';
SELECT x FROM@tWHERE x LIKE N'%S';

Result:

(0 row(s) affected)

However if you use NVARCHAR instead, this problem does not occur:

DECLARE@tTABLE(x NVARCHAR(25));
INSERT@tSELECT N'nanaS';
SELECT x FROM@tWHERE x LIKE N'%S';

Results:

x
-----
nanaS

However the original table didn't produce the desired results even if converting to NVARCHAR in the WHERE clause:

DECLARE@tTABLE(x NCHAR(25));
INSERT@tSELECT N'nanaS';
SELECT x FROM@tWHERECONVERT(NVARCHAR(25),x) LIKE N'%S';

Result:

(0 row(s) affected)

So one potential workaround would be to use the right data type in the first place (and also always prefix Unicode strings N'properly'. If you can't make the data type correct, you can use the RTRIM() workaround posted by Aushin, but keep HLGEM's comments in mind as well.

Solution 2:

EDIT: My explanation below was based of a misreading of your question. While RTRIM will work, I did not realize you weren't using nvarchar but char. Aaron and Gordon have provided better insight.

SELECT*FROM TableName WHERE RTRIM(ItemNumber) LIKE N'%S'

This is because for an nvarchar(25), the last character of 'S' is S.
For nchar(25), 'S' is actually 'S' + 24 spaces. So your last character is a space.

How Do I Truncate A Table Via Linked Server Using A Synonym For The Table Name?

2024-08-21T08:04:00.001-07:00

I know I can do the following: EXEC Server_Name.DBName.sys.sp_executesql N'TRUNCATE TABLE dbo.table_name' But what if I want to use a synonym for the table? I'm on SERVER1 and I w

Solution 1:

The link on the correct answer is broken. I ran into a similar problem. My workaround was using the synonyms table to lookup underlying table name, then running a dynamic sql statement. It is documented that synonyms cannot be used with TRUNCATE, but at least this is a decent workaround.

DECLARE@TableNameVARCHAR(500) = (SELECT TOP 1 base_object_name
    FROM Server_Name.DBName.sys.synonyms WHERE name ='table_name')
DECLARE@Sql NVARCHAR(MAX) ='EXEC Server_Name.DBName.sys.sp_executesql N''TRUNCATE TABLE '+@TableName+''''EXEC sys.sp_executesql @Sql

Solution 2:

Create a Stored Procedure in Server2 Database for Truncate Tables then call the Stored Procedure from Server1.

Like this :-

EXEC [Server2].[DBName].[SchemaName].sp_TruncateTable;

Why Won't This Regex Work In Postgresql

2024-08-21T07:46:00.001-07:00

I'm trying to get a regex to capture the base URL from a URL string. This ^(.+?[^\/:])(?=[?\/]|$) works. REGEX101 But when I try to use it within postgresql regexp_replace(conten

Solution 1:

RegexBuddy gives this warning about the first '?'

PostgreSQL is inconsistent in the way it handles lazy quantifiers in regular expressions with alternation because it attempts to match the longest alternative, instead of being eager and accepting the first alternative that matches

and if you remove it, it seems to work, i.e ^(.+[^\/:])(?=[?\/]|$)

however, if you're trying to parse the baseurl that regex won't work. Use this instead:

selectregexp_replace('....', '^(.*:)//([a-z\-.]+)(:[0-9]+)?(.*)$', '\2')

Solution 2:

PostGreSQL has an interesting regular expression engine. It took me a while to figure out what be escaped and what needs to be double-escaped. The solution that worked for me is:

(regexp_matches(content_url,'(https?:\/\/\\w+(?:\\.\\w+)+)'))[1] AS content_url

Hope this can help someone.

Select Distinct Only First Four Numbers

2024-08-21T07:10:00.001-07:00

Consider the following table: ticker code -------------- AA 151040 AAP 452020 DOW 151010 DVN 151020 EXC 452050 FAD 452070 POE 207010 I would like to

Solution 1:

Use LEFT:

SELECT LEFT(code, 4) AS code, ticker FROM tbl ORDER BY code;

Solution 2:

SELECTSUBSTRING(code,1,5) AS SUB_CODE,ticker
FROMTABLE;

Solution 3:

You can use Left function in mySql to select the first four Left(code,4) Then use order by.

Convert A Series Of Number Values In Text In Oracle Sql Query

2024-08-21T05:37:00.001-07:00

In the Oracle database, I have string values (VARCHAR2) like 1,4,7,8. The number represents as 1=car, 2= bus, 3=BB, 4=SB, 5=Ba, 6=PA, 7=HB, and 8 =G and want to convert the above-s

Solution 1:

Here's one option. Read comments within code. Sample data in lines #1 - 13; query begins at line #14.

SQL>with2  expl (id, name) as3    (select1, 'car'from dual unionall4select2, 'bus'from dual unionall5select3, 'BB'from dual unionall6select4, 'SB'from dual unionall7select5, 'Ba'from dual unionall8select6, 'PA'from dual unionall9select7, 'HB'from dual unionall10select8, 'G'from dual
 11    ),
 12  temp (col) as13    (select'1,4,7,8'from dual),
 14-- split COL to rows15  spl as16    (select regexp_substr(col, '[^,]+', 1, level) val,
 17            level lvl
 18from temp
 19connectby level <= regexp_count(col, ',') +120    )
 21-- join SPL with EXPL; aggregate the result22selectlistagg(e.name, ',') withingroup (orderby s.lvl) result23from expl e join spl s on s.val = e.id;

RESULT--------------------------------------------------------------------------------
car,SB,HB,G

SQL>

Solution 2:

Using the function f_subst from https://stackoverflow.com/a/68537479/429100 :

createor replace
function f_subst(str varchar2, template varchar2, subst sys.odcivarchar2list) return varchar2
as
    res varchar2(32767):=str;
beginfor i in1..subst.count loop
        res:=replace(res, replace(template,'%d',i), subst(i));
    end loop;
    return res;
end;
/

I've replaced ora_name_list_t (nested table) with sys.odcivarchar2list (varray) to make this example easier, but I would suggest to create your own collection for example create type varchar2_table as table of varchar2(4000);

Example:

selectf_subst('1,4,7,8'
     ,'%d'
     ,sys.odcivarchar2list('car','bus','BB','SB','Ba','PA','HB','G')
   ) s 
from dual; 

S
----------------------------------------
car,SB,HB,G

Solution 3:

Assume you have a lookup table (associating the numeric codes with descriptions) and a table of input strings, which I called sample_inputs in my tests, as shown below:

createtable lookup (code, descr) asselect1, 'car'from dual unionallselect2, 'bus'from dual unionallselect3, 'BB'from dual unionallselect4, 'SB'from dual unionallselect5, 'Ba'from dual unionallselect6, 'PA'from dual unionallselect7, 'HB'from dual unionallselect8, 'G'from dual
;

createtable sample_inputs (str) asselect'1,4,7,8'from dual unionallselectnullfrom dual unionallselect'3'from dual unionallselect'5,5,5'from dual unionallselect'6,2,8'from dual
;

One strategy for solving your problem is to split the input - slightly modified to make it a JSON array, so that we can use json_table to split it - then join to the lookup table and re-aggregate.

select s.str, l.descr_list
from   sample_inputs s crossjoinlateral
       ( selectlistagg(descr, ',') withingroup (orderby ord) as descr_list
         fromjson_table( '['|| str ||']', '$[*]'
                            columns code number path '$', ord for ordinality)
                join lookup l using (code)
       ) l
;

STR     DESCR_LIST                    
------- ------------------------------1,4,7,8 car,SB,HB,G                   
                                      
3       BB                            
5,5,5   Ba,Ba,Ba                      
6,2,8   PA,bus,G

How To Change Date Format Of Datetime Data Type In Sql?

2024-08-21T05:18:00.001-07:00

My date format of '03-01-2017 10:24:48' is getting stored in SQL as '2017-03-01 10:24:48.000' where my 'dd-mm-yyyy' format is getting converted to 'yyyy-mm-dd' format. How can I ch

Solution 1:

SQL Server doesn't store a DateTime in any string format - it's stored as an 8 byte numerical (binary) value.

The various settings (language, date format) only influence how the DateTime is shown to you in SQL Server Management Studio - or how it is parsed when you attempt to convert a string to a DateTime.

There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have - therefore, these settings might work some times - and sometimes not.

The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

The ISO-8601 format is supported by SQL Server comes in two flavors:

YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

or:

YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

This is valid for SQL Server 2000 and newer.

If you use SQL Server 2008 or newer and the DATE datatype (only DATE - notDATETIME!), then you can indeed also use the YYYY-MM-DD format and that will work, too, with any settings in your SQL Server.

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

The recommendation for SQL Server 2008 and newer is to use DATE if you only need the date portion, and DATETIME2(n) when you need both date and time. You should try to start phasing out the DATETIME datatype if ever possible

Solution 2:

Changing the format does not change the way the data is stored, just the way it is displayed. You can find date and time styles at https://msdn.microsoft.com/en-us/library/ms187928.aspx?f=255&MSPPError=-2147217396.

Simple Sql Query In Access

2024-08-21T04:23:00.001-07:00

I need some help with figuring out two queries I am trying out from my textbook. I can't seem to figure them out. Any help would be appreciated! The first query deals with getting

Solution 1:

Well you can actually use the 'TOP' keyword if you are using the JET Engine.

SELECT TOP 1 Location
FROM Casino
GROUPBY Location
ORDERBy Location;

For the next one, can you post the table and attributes?

Solution 2:

(Please Note : This is an edited version of my original answer which was corrected to remove a bug I identified after posting which would list, for example, Players who had played at three Casinos one slot machine that had been played at all Casinos and at four Casinos a second similarly qualified Slot machine. It would also fail to list Players who had played a qualified Slot machine at all Casinos if they had also played at least one other qualified Slot machine.

This edited version has been tested on an expanded dataset. It will list list only Players who have Played any particular Slot at each of the Casinos at least once. It should also be easier to understand.)

As for your second problem, I suggest the following as a soultion...

SELECTDISTINCT PlayerNum
FROM (SELECT PlayerNum, SlotNum, Count(*) AS CountSlot
      FROM Plays
      WHERE SlotNum IN 
          (SELECT SlotNum
           FROM
               (SELECTDISTINCT SlotNum, CasinoNum
                FROM Plays) SlotCasino
           GROUPBY SlotNum
           HAVINGCOUNT(CasinoNum) =
              (SELECTCOUNT(CasinoNum)
               FROM Casino))
      GROUPBY PlayerNum, SlotNum) EligiblePlayers
WHERE CountSlot = (SELECTCOUNT(CasinoNum)
                   FROM Casino);

The logic that I followed was that I should determine which Slots had been played at all the Casinos and which Players had played those Slots at all of the Casinos.

To figure out which Slots had been played at all the Casinos I first isolated all combinations of Slot played and Casinos played at using -

SELECTDISTINCT SlotNum, CasinoNum
FROM Plays

I then found out which Slots had been played at each of the current number of Casinos using -

SELECT SlotNum
FROM
    (SELECTDISTINCT SlotNum, CasinoNum
    FROM Plays) SlotCasino
    GROUPBY SlotNum
    HAVINGCOUNT(CasinoNum) =
        (SELECTCOUNT(CasinoNum)
        FROM Casino)

I then found out which Players had Played on these Slots and at how many Casinos using -

SELECT PlayerNum, SlotNum, Count(*) AS CountSlot
FROM Plays
WHERE SlotNum IN 
    (SELECT SlotNum
     FROM
         (SELECTDISTINCT SlotNum, CasinoNum
          FROM Plays) SlotCasino
     GROUPBY SlotNum
     HAVINGCOUNT(CasinoNum) =
        (SELECTCOUNT(CasinoNum)
         FROM Casino))
GROUPBY PlayerNum, SlotNum

I refined this list to only Players who have Played a qualified Slot at each of the Casinos using -

SELECTDISTINCT PlayerNum
FROM (SELECT PlayerNum, SlotNum, Count(*) AS CountSlot
      FROM Plays
      WHERE SlotNum IN 
          (SELECT SlotNum
           FROM
               (SELECTDISTINCT SlotNum, CasinoNum
                FROM Plays) SlotCasino
           GROUPBY SlotNum
           HAVINGCOUNT(CasinoNum) =
              (SELECTCOUNT(CasinoNum)
               FROM Casino))
      GROUPBY PlayerNum, SlotNum) EligiblePlayers
WHERE CountSlot = (SELECTCOUNT(CasinoNum)
                   FROM Casino);

I hope that I have explained this adequately. If not, or if you have any other questions, then please feel free to reply.

Linq To Sql Not Inserting Data Onto The Db

2024-08-21T03:47:00.001-07:00

I have a little / weird behaviour here and Im looking over internet and SO and I didn't find a response. I have to admit that this is my first time using databases, I know how to u

Solution 1:

First; do you have a TransactionScope that spans this, that you haven't committed.

However, more liktely: is this a database file that you are deploying with the system (rather than a separate database server)? If so, every time you hit "build" etc, it copies the version from your solution folder into the build output (bin/debug or bin/release).

Look in the build output copy; see if that has been updated.

Can I Ignore Logins When Publishing A Visual Studio Database Project?

2024-08-21T03:29:00.001-07:00

We have a SQL Server that uses SQL Server Authentication, with users that can deploy, and others that can read, for the sake of simplicity, I'll call them 'deploy' and 'web'. I'm h

Solution 1:

As the user already exists, I suspect that the user that is used to perform the deploy doesn't have rights to view it. The below should be all you need to resolve this.

GRANT VIEW DEFINITION ON LOGIN::web TO deploy

Geting 'entity Class Not Found' Error While Mapping Classes To Database Table By Hibernate

2024-08-21T02:35:00.001-07:00

Simple pojo class package practice041116; public class Cls { public String name; public int roll; public Cls(){} public String getName() { return name; } public void setName(String

Solution 1:

In your mapping file :

<classname="Cls" table="cls">

you don't specify qualified class name but just simple class name. You should replace by :

<classname="practice041116.Cls" table="cls">

Solution 2:

Hibernate is an ORM framework which maps a Java Object (Entity Object) to a relational database table, as you did NOT map any Entity Class to a database table you are getting this exception.

Option(1): Using Annotations

You need to use @Entity (class level mapping) and @Column (element level mapping) to map the java object to a database table.

Option(2): Using mapping xml files

As sugested by David above and also you can look at here

Delete From A Table Based On Date

2024-08-21T01:59:00.001-07:00

Can anyone help me with the script which will delete the data older than the particular date. Thanks

Solution 1:

deletefrom YOUR_TABLE where your_date_column <'2009-01-01';

This will delete rows from YOUR_TABLE where the date in your_date_column is older than January 1st, 2009. i.e. a date with 2008-12-31 would be deleted.

Solution 2:

Delete data that is 30 days and older

DELETEFROMTableWHERE DateColumn < GETDATE()-30

Solution 3:

You could use:

DELETEFROM tableName
where your_date_column <'2009-01-01';

but Keep in mind that the above is really

DELETEFROM tableName
    where your_date_column <'2009-01-01 00:00:00';

Not

DELETEFROM tableName
        where your_date_column <'2009-01-01 11:59';

Solution 4:

or an ORACLE version:

deletefrom table_name
 where trunc(table_name.date) > to_date('01/01/2009','mm/dd/yyyy')

Solution 5:

This is pretty vague. Do you mean like in SQL:

DELETEFROM myTable
WHERE dateColumn <'2007'

Saving Xml Contents With Iso-8859-1 Encoding Using Entity Framework Into Sql Server 2008's Xml Column Gives Encoding Error

2024-08-21T01:03:00.001-07:00

I am trying to save XML content with encoding='ISO-8859-1' standalone='yes' into a SQL Server 2008 XML column. I am doing this using Entity Framework. However doing this throws th

Solution 1:

The encoding width must match the datatype width

So don't send:

UTF-8 and nvarchar/xml
UTF-16 and varchar

Also see Trying to store XML content into SQL Server 2005 fails (encoding problem) whcih links to http://social.msdn.microsoft.com/forums/en-US/sqlxml/thread/d40ef582-4ffe-4f4b-b6b8-03c6c0ba1a32/