Sql query parsing to a tree structure

Question

1.00/5 (1 vote)

See more:

Hi

I need help on converting SQL to tree.
i have an SQL like below

Select DOLLAR_SALES, UNIT_SALES, SUM(DOLLAR_SALES,45) as "SUM OF SALES" From IRVING_MARKET_ADVANTAGE Where PRODUCTS_SUB_BRAND in (Select top 7 PRODUCTS_SUB_BRAND From IRVING_MARKET_ADVANTAGE Where DOLLAR_SALES > 2000 And LATEST_YEAR_444 = 'LATEST 4 WEEKS ENDING 08-09-20') And PROJECTED_GEOGRAPHIES in ('TOTAL US - FOOD') Group by PRODUCTS_SUB_BRAND Order by PRODUCTS_SUB_BRAND desc

I need to identify below

1) Columns
2) SQL functions
3) Alias
4) From
5) Where Columns
6) Where Values
7) Nested SQL
8) Order By members
9) Group BY member

Please guide me or send me the snippet to achieve the above logic.

thanks in advance.

What I have tried:

expected results may be like below:

query :

<pre>Select DOLLAR_SALES, UNIT_SALES, SUM(DOLLAR_SALES,45) as "SUM OF SALES" From IRVING_MARKET_ADVANTAGE Where PRODUCTS_SUB_BRAND in (Select top 7 PRODUCTS_SUB_BRAND From IRVING_MARKET_ADVANTAGE Where DOLLAR_SALES > 2000 And LATEST_YEAR_444 = 'LATEST 4 WEEKS ENDING 08-09-20') And PROJECTED_GEOGRAPHIES in ('TOTAL US - FOOD') Group by PRODUCTS_SUB_BRAND Order by PRODUCTS_SUB_BRAND desc

1) Columns
       a1=DOLLAR_SALES alias = null
       a2=UNIT_SALES alias = null
       a3=SUM(DOLLAR_SALES,45) alias "SUM OF SALES"
2) From
       IRVING_MARKET_ADVANTAGE
3) Where Columns
       PRODUCTS_SUB_BRAND = SUB_QUERY
       PROJECTED_GEOGRAPHIES in ('TOTAL US - FOOD')
       Has_SubQuery = Yes
4) If Sub Query
       <pre>Select top 7 PRODUCTS_SUB_BRAND From IRVING_MARKET_ADVANTAGE Where DOLLAR_SALES > 2000 And LATEST_YEAR_444 = 'LATEST 4 WEEKS ENDING 08-09-20'
5) Order By members
       PRODUCTS_SUB_BRAND descending
6) Group BY member
       PRODUCTS_SUB_BRAND

Posted 26-Aug-20 18:15pm

GururajCV

Updated 2-Sep-20 3:55am

v4

Add a Solution

Comments

Richard MacCutchan 27-Aug-20 5:23am

"Please guide me or send me the snippet to achieve the above logic."
The question is not clear. You need to explain what you mean by "i have not achieved the results"

Dave Kreskowiak 29-Aug-20 15:04pm

It sounds like this is a home assignment and you want us to do the work for you. That's not going to happen.

This is a test of YOUR knowledge, not ours.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2020-09-02T03:55:00

There's several sql parser on Google[^].

If you can't use it, i'd suggest to:
1. create a list or dictionary with keywords to find

C#

Dictionary<string, string> keywords = new Dictionary<string, string>();
keywords.Add("columns", "SELECT");
keywords.Add("tables", "FROM");
keywords.Add("where", "WHERE");
keywords.Add("group", "GROUP BY");
keywords.Add("order", "ORDER BY");

2. create a list which will hold the information about founded keywords

C#

List<Tuple<string, string, int>> found = new List<Tuple<string, string, int>>();
string sComm = "your select statement here";
foreach(string kw in keywords.Values)
{
	int start = 0;
	int end = sComm.Length;
	int at = 0;
	while(start<end)
	{
		at = sComm.IndexOf(kw, start, StringComparison.InvariantCultureIgnoreCase);
		if(at==-1) break;
		found.Add(new Tuple<string, string, int>(keywords.FirstOrDefault(x=>x.Value==kw).Key, kw, at));
		start=at+1;
	}
}
//sort by index of keyword
found = found.OrderBy(x=>x.Item3).ToList();

Found list looks like:

columns SELECT 0 
tables FROM 72 
where WHERE 101 
columns SELECT 132 
tables FROM 164 
where WHERE 193 
group GROUP BY 326 
order ORDER BY 356

Now, you have to implement the mechanism which will get the parts between keywords. I have not enough time to do that for you.
Tip:
- columns in first select are between characters of sComm: 0 and 71
- tables in first select are between characters: 72 and 100
- etc.
In case you want to remove keyword, you have to add the length of that keyword to the starting character ;)

Good luck!