LIMIT and UNION ALL not returning the requested number of records

I have a table with multiple type values and I'm wanting to get a sample records from some of them.

My current query is as follows:

-- Pulling three sample records from each "type"
SELECT * FROM example WHERE type = "A" LIMIT 3
UNION ALL
SELECT * FROM example WHERE type = "B" LIMIT 3
UNION ALL
SELECT * FROM example WHERE type = "C" LIMIT 3
;

I expect this to return a total of 9 records; 3 from type = "A", 3 from type = "B", and 3 from type = "C".

However, the result that I actually receive is 3 records from type = "A" and nothing else.
I know for a fact that the other type values exist because I can run the individual SELECT statements and they return results.

Why is MySQL only returning 3 records and how can I have it return the full 9 records that I want?

I've created a SQL Fiddle to illustrate the issue: http://ift.tt/1SIJUZB

via Chebli Mohamed

Create Table with Parameters

I would like to create an Excel table, using OleDb:

OleDbCommand oleDbCommand = new OleDbCommand();
oleDbCommand.Connection = oleDbConnection;

String commandText = "CREATE TABLE" + " [" + sheetModel.Sheet.Name + "] ";
commandText += "(";

for (int index = 0; index < spalten; index++)
{
    String _header = sheetModel.DT1.Rows[heaader].ItemArray[index].ToString();

    oleDbCommand.Parameters.Add(new OleDbParameter("@var" + (index + 1).ToString(), _header));

    if (index > 0)
    {
        commandText += ", ";
    }

    commandText += "@var" + index.ToString() + " VARCHAR";
}
commandText += ");";


try
{
    oleDbCommand.CommandText = commandText;
    oleDbCommand.ExecuteNonQuery();
    oleDbCommand.Parameters.Clear();
}
catch (Exception exception)
{
    MessageBox.Show(exception.Message);
    return;
}  

The result for the Excel table is,

@var0 @var1 @var2 @var3 @var4 @var5

but I should look like this:

"PREIS/Stk. EURO" "Stk." "" "Produkt" "Artikelmerkmale" "Sonstige"

Where is my mistake?
The code is working fine for inserting values.

via Chebli Mohamed

Need to fetch a row with minimum value in postgreSQL

I need a row with a minimum price value in which product_id will be unique. Look at the below structure which is used so far.

Consider stock of the all row are "1";

Price Table:

product_id  | place_id | price    | special_price | special_date_from   | special_date_to
--------------------------------------------------------------------------------------------
27          |27        |1000.0000 |0.0000         |                     |
26          |27        |500.0000  |129.0000       |2015-05-15 00:00:01  |2015-08-30 23:59:59
26          |24        |1500.0000 |0              |                     |
27          |5         |56224.0000|0              |                     |
27          |128       |1000.0000 |100.0000       |2015-07-31 00:00:01  |2015-08-12 23:59:59
27          |121       |100.0000  |0              |                     |
26          |121       |500.0000  |0              |                     |

My Query IS:

select * 
  from ( 
        (select min(price) price, 
                myt.product_id 
           from ( select (case when 
                              (cpp.special_price_fromdate <= '2015-08-04 19:18:49' 
                               and cpp.special_price_todate >= '2015-08-04 19:18:49' 
                               and cpp.special_price > 0) 
                               then cpp.special_price else cpp.price end
                          ) as price,  
                          cpp.product_id, 
                          cpp.place_id
                   from product_price as cpp 
                  where cpp.in_stock > 0   
                    and cpp.place_id IN (130,27,128,129,126,121,54)
                ) as myt group by product_id
        ) t1 
inner join
    (select DISTINCT on(pps.product_id) 
            (case when (pps.special_price_fromdate <= '2015-08-04 19:18:49' 
                        and pps.special_price_todate >= '2015-08-04 19:18:49' 
                        and pps.special_price > 0) 
                  then pps.special_price 
                  else pps.price end) as price, 
            pps.product_id,
            pps.price as old_price, 
            pps.place_id 
       from product_price pps 
      where pps.in_stock > 0
    ) t2 on t1.price = t2.price 
            and t1.product_id = t2.product_id 
            and t1.product_id in ('26','27')
) AS "pp";

I want the results to be:

product_id  | place_id | price    | old_price     
--------------------------------------------------
26          | 27       | 129.0000 | 500.0000      
27          | 121      | 100.0000 | 100.0000

But i get the results based on the above query:

product_id  | place_id | price    | old_price     
--------------------------------------------------
26          | 27       | 129.0000 | 500.0000

27 product_id has been skipped because of equal price which i have checked in "On Condition". I dont know why :(

via Chebli Mohamed

Selecting Multiple ID's in one Select

I have a Database with entries that have to be grouped togethe

id | Name    | Surname | Time
1  | Michael | Kane    | 3
2  | Torben  | Dane    | 4
3  | Dinge   | Chain   | 5
4  | Django  | Fain    | 5
5  | Juliett | Bravo   | 6
6  | Django  | Fain    | 7
7  | Django  | Fain    | 3
8  | Django  | Fain    | 4
9  | Dinge   | Chain   | 4
10 | Torben  | Dane    | 4

Now I want to group the items while maintaing all Id's. I'm comming close with the following query but I am lossing my ids

SELECT id, Name, Surname, sum(Time) from Names group by(Name)

The Result of the Query is

 id | Name    | Surname | Time 
 9  | Dinge   | Chain   | 9
 8  | Django  | Fain    | 19
 5  | Juliett | Bravo   | 6
 1  | Michael | Kane    | 3
 10 | Torben  | Dane    | 8

while I would need all ids like this

 ids    | Name    | Surname | Time 
 3,9    | Dinge   | Chain   | 9
 4,6,78 | Django  | Fain    | 19
 5      | Juliett | Bravo   | 6
 1      | Michael | Kane    | 3
 2,10   | Torben  | Dane    | 8

How can i accomplish this?

via Chebli Mohamed

Incorrect syntax near 'Name'

I getting errors:

Incorrect syntax near 'nvarchar'.
Incorrect syntax near 'Name'.

Please help to get from this.

I also added scalar to the names (@) but I am not getting anything.

public partial class Form1 : Form
{
    SqlCommand cmd;
    SqlConnection con;

    private void button1_Click(object sender, EventArgs e)
    {
        con = new SqlConnection(@"Data Source=DELL_LAPTOP\sqlexpress;Integrated Security=True");
        con.Open();

        cmd = new SqlCommand("Insert Into newproj (Name,Designation,Gender,Age,Address,Date,Staff Name,Shift,ST,ET,Hours) Values (@Name,@Designation,@Gender,@Age,@Address,@Date,@Staff Name,@Shift,@ST,@ET,@Hours)", con);
        cmd.Parameters.Add("@Name", textBox4.Text);
        cmd.Parameters.Add("@Designation", textBox2.Text);
        cmd.Parameters.Add("@Gender", comboBox1.SelectedItem.ToString ());
        cmd.Parameters.Add("@Age", textBox3.Text);
        cmd.Parameters.Add("@Address", textBox5.Text);
        cmd.Parameters.Add("@Date", dateTimePicker1.Text);
        cmd.Parameters.Add ("@Staff Name", textBox1.Text);
        cmd.Parameters.Add ("@Shift", comboBox2.SelectedItem.ToString());
        cmd.Parameters.Add("@ST", textBox7.Text);
        cmd.Parameters.Add("@ET", textBox8.Text);
        cmd.Parameters.Add("@Hours", textBox6.Text);

        cmd.ExecuteNonQuery();       
    }
}

via Chebli Mohamed

Sum result of SELECT...WHERE in SQL SERVER

Can someone tell me what I'm doing wrong, and if I can get the expect result... (Keep in mind this is a VIEW)

    SELECT
      [Id]
    , [Nome]
    , [Estado]
    , (SELECT COUNT(EstProc) FROM LoginsImp AS LI WHERE (EstProc = 'A1.' OR EstProc = 'A2.') AND LI.LogImpFiles_Id = LIF.Id) AS ItemsProcessamento
    , (SELECT COUNT(EstProc) FROM LoginsImp AS LI WHERE EstProc = 'A3.' AND LI.LogImpFiles_Id = LIF.Id) AS ItemsErroProcessamento
    , (SELECT COUNT(EstProc) FROM LoginsImp AS LI WHERE (EstProc= 'A4' OR EstProc= 'A5') AND LI.LogImpFiles_Id= LIF.Id) AS ItemSucessoProcessamento
    , SUM(ItemsErroProcessamento + ItemSucessoProcessamento) AS [ItemsProcessados]
    , [CreatedOn]
    , [CreatedBy]
FROM
    [dbo].[LogImpFiles] AS LIF
group by [Id], Nome, Estado, CreatedOn, CreatedBy

The result is this:

1   TesteImport1        6   2   3   0   2015-08-04 15:41:41.5130000 110032797

I was expecting something like this:

1   TesteImport1        6   2   3   **5**   2015-08-04 15:41:41.5130000 110032797

via Chebli Mohamed

using mysql query to fill the rows of an empty table

I need to analyze some data in mysql and I'm trying to create a new table based largely on an existing table. Thus far I've created the table with a few dozen or so necessary columns. They are not identical to the columns (I could easily change this) in the query, but every column in the new table is contained in the query. The columns in the query and new table are also not found in the exact same order, but I could also change this if need be. How should I go about filling my table with all the necessary data from the query--100,00ish rows? Here is the format of the query:

select t2.name as agent,t1.* from
(select s.year, s.wk, t.* from sales s 
join transaction t
on s.id = t.id) t1,

(select s.year, s.wk, t.* from sales s 
join transaction t
on s.id = t.id) t2 

where t1.id=t2.id
and 
t1.name<>t2.name

via Chebli Mohamed

How to select a value between a pattern from the right side in SQL

I have table A with following data sample. I want to select the number between the last two /

via Chebli Mohamed

calculate lowesr differents between 5 cells to other 5 cells [on hold]

I have this row - [11,19,59,69,9] lets call it FIRST and i have another ~ 100 million rows with the same format

[10,20,30,20,50],
[15,50,60,70,10]
...
...

I need to compare each number from FIRST row to each number in the corresponding cell in each row from the 100M rows , and take the abs diff between those two values and sum all of the diff.

Example :

FIRST - [11,19,59,69,9]
row   - [10,20,30,20,50]
diff  - [|11-10|,|19-20|,|59-30|,|69-20|,|9-50|]  = [1,1,29,49,41] 
sum(diff) = 1+1+29+49+41 =121

And i need to do this comparison between the FIRST and the rest of the rows and output the row with the smallest diff.

What is the best way to do it ? I need to implement it on my SQL DATABASE . Maybe there is SQL query for this ? or maybe i should develop some inner function via c, or c++?

via Chebli Mohamed

Passing One Stored Procedure’s Result as Another Stored Procedure’s Parameter

Procedure 1:

EXEC Parse
@Part = '0123,4567'
@Qty = '1,1';

returns the following:

Part        Qty
0123         1
4567         1

This procedure simply takes a part and quantity input and parses the strings at each instance of ",".

Procedure 2:

EXEC PA
@Part = '0123'
@Qty = '1';

returns the following:

Top-Level Assembly     TotalQty      MaterialPart     Qty
      0123                1             12A            2
      0123                1             13A           21
      0123                1             14A            5

My overall goal is to have a user enter an assembly part or list of assembly parts (delimited by a comma) and their appropriate quantities. The first procedure creates a result list of all the assembly parts. The second procedure should run off of the result set from the first procedure to get all of the pieces that make up the assembly part.

How can I run my second procedure based off of the result of the first procedure? Any help is greatly appreciated!!

via Chebli Mohamed

View vs Tables in SQLite

I have a Table where I save daily data TDay.

Task    | Time  | Day | Month | Year |
--------------------------------------
Cooking | 20min | 21  | 03    | 2015 |
Reading | 20min | 21  | 03    | 2015 |
Smiling | 20min | 21  | 03    | 2015 |
Washing | 20min | 21  | 03    | 2015 |
Cooking | 20min | 22  | 03    | 2015 |
Reading | 20min | 22  | 03    | 2015 |
Smiling | 20min | 22  | 03    | 2015 |
Washing | 20min | 22  | 03    | 2015 |
Watching| 20min | 01  | 04    | 2015 |
Washing | 20min | 01  | 04    | 2015 |
Reading | 20min | 01  | 04    | 2015 |
Ironing | 20min | 01  | 04    | 2015 |

Now i would need a VIEW or a Table TWeek and TMonth that gathers those results

TWeek

Task    | Time  | Week | Year |
-------------------------------
Cooking | 40min | 12   | 2015 |
Reading | 40min | 12   | 2015 |
Smiling | 40min | 12   | 2015 |
Washing | 40min | 12   | 2015 |

TMonth

Task    | Time  | Month | Year |
-------------------------------
Cooking | 40min | 03    | 2015 |
Reading | 40min | 03    | 2015 |
Smiling | 40min | 03    | 2015 |
Washing | 40min | 03    | 2015 |
Watching| 20min | 04    | 2015 |
Washing | 20min | 04    | 2015 |
Reading | 20min | 04    | 2015 |
Ironing | 20min | 04    | 2015 |

So what I am doing is to gather the results daily and sum the up for a week and a month. The Problem is that I am expecting a lot of data.

Should I use views for that or should I write a small programm that fills tables? Or is it smarter to make a Select where I sum up the data? In the and I will represent a result.

via Chebli Mohamed

SQL Server Query Aid

I have a query in SQL Server to return a list of Reports, it has to return either a string representing a location, or a string representing the store it's referencing.

The issue is my query is only returning reports that references a store id, instead of returning all reports and the relevant location information. I'm convinced its a stupid syntax issue, but I haven't done database work for a while, and can't seem to pick it out. I've tried several different ways to get this to work, but it simply refuses.

SELECT rep.rep_id AS "RepId", ISNULL(rep.rep_status, 'C') AS "RepStatus", ISNULL((loc.location_street + ' ' + loc.location_city), store.Description) AS "Location", rep.date_reported AS "DateReported", rep.reported_by AS "ReportedBy"
FROM Report rep JOIN Report_Location reploc ON reploc.rep_id = rep.rep_id
JOIN Location loc ON loc.location_id = reploc.location_id
LEFT JOIN Store store ON store.StoreID = loc.store_id;

I've tried removing the left join and just adding a where loc.store_id = store.StoreID or loc.store_id IS NULL. Neither worked. Thanks in advance for your help.

via Chebli Mohamed

Which SQL Query is the site running?

The websites (intranet sites or extranet sites - sometimes web portals) at my company return certain results (which is obtained via SQL queries/commands in the back-end systems). I"m trying to find out which queries are being run in the background and how I could track back the query results onto the tables where they come from. How can I achieve that? I tried looking at the "source" but found no queries there. Back-end uses SQL Server if that matters.

via Chebli Mohamed

Sequelize error on model.update

Unhandled rejection TypeError: undefined is not a function

error whe i am trying to update my table in sequelize above MySQL.

here is the snippet of code that triggering the error with commented one that surprisingly works:

topic.update(
        { name: "adfa"},
        { where: { tid: 1} }
);

//topicAttribute.update({ value: "name 2"},{where: {TOPIC: 2,name: "nadpis" }});

Now this is my two models:

var topic = sequelize.define('topic', {
        tid: {  type: Sequelize.INTEGER,
                field: 'tid',
                primaryKey: true                    
        },
        name: { type: Sequelize.STRING,
                field: 'name'       
        },
        added: {    type: Sequelize.DATE,
                    field: 'added'
        },
        addedBy: {  type: Sequelize.STRING,
                    field: 'added_by'
        },
        changed: {  type: Sequelize.DATE,
                    field: 'changed'
        },
        changedBy: {    type: Sequelize.STRING,
                        field: 'changed_by'
        },
        parent: {   type:   Sequelize.INTEGER,
                    field: 'parent'
        },
        enabled: {
                    type:   Sequelize.BOOLEAN,
                    field: 'enabled'
        }
    },
{
        tableName: 'topic',
        timestamps: false
});

var topicAttribute = sequelize.define('tAttribute', {
    parent: {   type: Sequelize.INTEGER,
            field: 'TOPIC',
            primaryKey: true
    },
    name: { type: Sequelize.STRING,
            field: 'name',
            primaryKey: true
    },
    value: {    type: Sequelize.STRING,
                field: 'value'
    },
},
{
    tableName: 'topic_attribute',
    timestamps: false
});

topic.hasOne(topic, {foreignKey: 'tid'});
topic.hasMany(topicAttribute, {foreignKey: 'parent'});
topicAttribute.belongsTo(topic, {foreignKey: 'parent'});

And finally my stacktrace:

Unhandled rejection TypeError: undefined is not a function  
at Instance.set (C:\Users\212443162\Documents\ithd\node_modules\sequelize\lib\instance.js:365:16)
at Instance.set (C:\Users\212443162\Documents\ithd\node_modules\sequelize\lib\instance.js:293:16)
at initValues (C:\Users\212443162\Documents\ithd\node_modules\sequelize\lib\instance.js:63:8) 
at Instance (C:\Users\212443162\Documents\ithd\node_modules\sequelize\lib\instance.js:107:14) 
at new Instance (C:\Users\212443162\Documents\ithd\node_modules\sequelize\li b\model.js:655:14) 
at Model.build (C:\Users\212443162\Documents\ithd\node_modules\sequelize\lib\model.js:1493:10) 
at C:\Users\212443162\Documents\ithd\node_modules\sequelize\lib\model.js:2119:24 
at tryCatcher (C:\Users\212443162\Documents\ithd\node_modules\sequelize\node_modules\bluebird\js\main\util.js:24:31) 
at Function.Promise.attempt.Promise.try (C:\Users\212443162\Documents\ithd\node_modules\sequelize\node_modules\bluebird\js\main\method.js:31:24)    
at Model.update (C:\Users\212443162\Documents\ithd\node_modules\sequelize\li b\model.js:2116:21) 
at C:\Users\212443162\Documents\ithd\routes\topics.js:119:8 at Layer.handle [as handle_request] (C:\Users\212443162\Documents\ithd\node_ modules\express\lib\router\layer.js:95:5) 
at next (C:\Users\212443162\Documents\ithd\node_modules\express\lib\router\r oute.js:131:13) 
at Route.dispatch (C:\Users\212443162\Documents\ithd\node_modules\express\li b\router\route.js:112:3) 
at Layer.handle [as handle_request] (C:\Users\212443162\Documents\ithd\node_ modules\express\lib\router\layer.js:95:5) 
at C:\Users\212443162\Documents\ithd\node_modules\express\lib\router\index.j s:277:22

via Chebli Mohamed

Create a function for generating random number in SQL Server trigger

I have to create a function in a SQL Server trigger for generating random numbers after insert. I want to update the column with that generated random number please help what I have missed in my code.

If you know other ways please suggest a way to complete my task.

This my SQL Server trigger:

ALTER TRIGGER [dbo].[trgEnquiryMaster]
ON [dbo].[enquiry_master]
AFTER INSERT 
AS 
    declare @EnquiryId int;
    declare @ReferenceNo varchar(50);
    declare @GenReferenceNo NVARCHAR(MAX);

    select @EnquiryId = i.enquiry_id from inserted i;
    select @ReferenceNo = i.reference_no from inserted i;
BEGIN
     SET @GenReferenceNo = 'CREATE FUNCTION functionRandom (@Reference VARCHAR(MAX) )
        RETURNS VARCHAR(MAX)
        As
        Begin
        DECLARE @r varchar(8);
        SELECT @r = coalesce(@r, '') + n
        FROM (SELECT top 8 
        CHAR(number) n FROM
        master..spt_values
        WHERE type = P AND 
        (number between ascii(0) and ascii(9)
        or number between ascii(A) and ascii(Z)
        or number between ascii(a) and ascii(z))
        ORDER BY newid()) a

        RETURNS @r
        END
        '

        EXEC(@GenReferenceNo)

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    -- update statements for trigger here
    UPDATE enquiry_master 
    SET reference_no ='updated' 
    WHERE enquiry_id = @EnquiryId
END   

via Chebli Mohamed

display highest value

I have written a function which is used to pick the highest value and display everything from sql based on the ID. If aa[0] is highest it will display everything in ID 0, if not, it will display either 1 or 2. But the problem now is it only displays value in ID 0 although ID 1 is the highest! Anyone can help me to figuring out what;s wrong with my coding ? Thanks

  private void pick_highest_value_here_and_display(ArrayList<Double> value) throws Exception {
                 // TODO Auto-generated method stub
                 double aa[]=value.stream().mapToDouble(v -> v.doubleValue()).toArray(); 
                 double highest=aa[0]; 
                 if(highest==aa[0])
                 {
                     String sql ="Select * from placeseen where ID =0";
                     DatabaseConnection db = new DatabaseConnection();
                     Connection  conn =db.getConnection();
                     PreparedStatement  ps = conn.prepareStatement(sql);
                     ResultSet rs = ps.executeQuery();
                     if (rs.next()) 
                     {  
                      String aaa=rs.getString("place1");  
                      String bbb=rs.getString("place2");
                      String cc=rs.getString("place3");
                      Tourism to =new Tourism();
                      to.setPlace1(aaa);
                      to.setPlace2(bbb);
                      to.setPlace3(cc);
                      DispDay dc=new DispDay();
                      dc.setVisible(true);
                     }
                     ps.close();
                     rs.close();
                     conn.close();
             }   else
             {
                  for(int i=0;i<aa.length;i++)
                 {
                     if(aa[i]>highest)
                     {
                         highest=aa[i];
                         System.out.println(highest);
                         String sql ="Select * from placeseen where ID =?";
                         DatabaseConnection db = new DatabaseConnection();
                         Connection  conn =db.getConnection();
                         PreparedStatement  ps = conn.prepareStatement(sql);
                         ps.setDouble(1, i); 
                         ResultSet rs = ps.executeQuery();
                         if (rs.next()) 
                         {  
                          String aaa=rs.getString("place1");  
                          String bbb=rs.getString("place2");
                          String cc=rs.getString("place3");
                          Tourism to =new Tourism();
                          to.setPlace1(aaa);
                          to.setPlace2(bbb);
                          to.setPlace3(cc);
                          DispDay dc=new DispDay();
                          dc.setVisible(true);
                         }
                         ps.close();
                         rs.close();
                         conn.close();
                 }   

                 }

             }

via Chebli Mohamed

SQL select order by contains num

This is my table:

--------------
    Names
--------------
   "Ben 52"
   "Dan 236"
   "Carter 73"
   "Harry"
   "Peter 53"
   "Connor 27"
   "Morgan"
   "Richard 675"

I want to create a query that puts the columns without numbers at the end of the list. This is basically the idea of what I'm trying to create:

SELECT names FROM table1 ORDER BY ContainsNum(names) DESC

This is the result I should get:

--------------
    Names
--------------
   "Ben 52"
   "Dan 236"
   "Carter 73"
   "Peter 53"
   "Connor 27"
   "Richard 675"
   "Harry"
   "Morgan"

Please comment if I wasn't clear enough. I haven't see anyone ask this question before.

Thank you!

via Chebli Mohamed

How to run single select statement across all the databases in the same schema

I need to run a simple select statement across all the databases in the schema(SQL Server). I have around 30-40 databases. This table has same structure in all the databases.

select * from table1 where condition

Can you please let me know how to get the records from all databases??

via Chebli Mohamed

SQL to find rows with a similar numeric value

I have a table in a database which lists the similarity of an item to another, where each row(s) is essentially a search result, where similarity is a numeric value.

A row is either a parent (no similarity level) which may have "children" results

Or a child, where a numeric similarity percentage is given of its parent

What I need to do is identify all the items which are similar. This can be done as if two items have a near identical similarity score to a parent, then those two items can be said to be similar.

However; I'm having trouble accomplishing this with SQL. I'm using Access, and can split the table into parents and children if need be, but can't do much more

An example of my table is below:

id, parent, score
aaa,,
aab,,
cas,aab,97
cad,aab,96
agd,aab,70
aac,,
aad,aac,100

In the above example, I'd like to pick out items "cas" and "cad" as the results.

Conversely, I can pick out all the results which are similar to a parent (such as aab and aac) via a simple SELECT query.

Thanks for the help.

via Chebli Mohamed

filter data within fraction of minutes in oracle sql

I have some data coming from source. I am changing the scenario here. If a particular Office_ID has been deactivated and it has all three clients (A,B,C) for a particular day, then we have to check whether all clients have gone or not. If yes then time frame for all clients should be within 10 Minutes. And this should repeat three times in a day for a particular office. Then we declare the office as closed. Here is a sample data:

+-----------+---------+--------------+----------------+---------+---------------+
| Office_ID |  NAME   |  FAIL_TIME   |  ACTIVITY_DAY  | CLIENT  | VENTURE_COUNT |
+-----------+---------+--------------+----------------+---------+---------------+
|      1002 |     xyz |     5:39:00  |     23/01/2015 |       A |             3 |
|      1002 |     xyz |     17:49:00 | 23/12/2014     |       A |             3 |
|      1002 |     xyz |     18:41:57 | 1/5/2014       |       B |             3 |
|      1002 |     xyz |     10:32:00 | 1/7/2014       |       A |             3 |
|      1002 |     xyz |     10:34:23 | 1/7/2014       |       B |             3 |
|      1002 |     xyz |     10:35:03 | 1/7/2014       |       C |             3 |
|      1002 |     xyz |     12:08:52 | 1/7/2014       |       B |             3 |
|      1002 |     xyz |     12:09:00 | 1/7/2014       |       A |             3 |
|      1002 |     xyz |     12:26:10 | 1/7/2014       |       B |             3 |
|      1002 |     xyz |     13:31:32 | 1/7/2014       |       B |             3 |
|      1002 |     xyz |     15:24:06 | 1/7/2014       |       B |             3 |
|      1002 |     xyz |     15:55:06 | 1/7/2014       |       C |             3 |
+-----------+---------+--------------+----------------+---------+---------------+

via Chebli Mohamed

Coldfusion - Comma delimiter with comma inside value

I am building a list using SQL data and I am trying to make each value of the list as: 'value1',value2','value,4' and so on. My problem is that I am using this code:

(
    SELECT COUNT(ns.ticket)
    FROM ns_trade ns
    WHERE ns.[login]=mt.[login]
        AND 
        <cfif qGetCommentsAccounting.recordCount gt 0> 
            ns.COMMENT IN ('#listChangeDelims(qGetCommentsAccounting.list_comments, "','")#')
        <cfelse>
            1=2
        </cfif>
)as no_of_tickets_accounting

which is works perfect EXCEPT when my value has comma inside like 'value,4'. Any suggestions how to solve that?

via Chebli Mohamed

android retrieve data using soap

I know that the error has been asked by many people, but I just can't fix it by reading others. Below is the error message and coding.

08-04 22:02:07.405: W/result(16524): SoapFault - faultcode: 'soap:Client' faultstring: 'System.Web.Services.Protocols.SoapException: Server did not recognize the value of HTTP Header SOAPAction: loginCheck. 08-04 22:02:07.405: W/result(16524): at System.Web.Services.Protocols.Soap11ServerProtocolHelper.RouteRequest() 08-04 22:02:07.405: W/result(16524): at System.Web.Services.Protocols.SoapServerProtocol.RouteRequest(SoapServerMessage message) 08-04 22:02:07.405: W/result(16524): at System.Web.Services.Protocols.SoapServerProtocol.Initialize() 08-04 22:02:07.405: W/result(16524): at System.Web.Services.Protocols.ServerProtocol.SetContext(Type type, HttpContext context, HttpRequest request, HttpResponse response) 08-04 22:02:07.405: W/result(16524): at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing)' faultactor: 'null' detail: org.kxml2.kdom.Node@424d65b0

=========================================================================

package com.example.dbConnection;

import org.ksoap2.SoapEnvelope;
import org.ksoap2.SoapFault;
import org.ksoap2.serialization.PropertyInfo;
import org.ksoap2.serialization.SoapObject;
import org.ksoap2.serialization.SoapSerializationEnvelope;
import org.ksoap2.transport.HttpTransportSE;

public class CallSoap 
{
     public final String SOAP_ACTION = "http://ift.tt/1VZcy7R";
public final String test = "loginCheck";
public final String WSDL_TARGET_NAMESPACE = "http://tempuri.org/";
public final String SOAP_ADDRESS = "http://ift.tt/1IhzpV3";
public CallSoap() 
{
}

public String test()
{
    SoapObject request = new SoapObject(WSDL_TARGET_NAMESPACE, test);

    PropertyInfo propertyInfo = new PropertyInfo();
    propertyInfo.setName("projectId");
    propertyInfo.setValue("321");
    propertyInfo.setType(String.class);
    request.addProperty(propertyInfo);

    propertyInfo.setName("loginName");
    propertyInfo.setValue("321");
    propertyInfo.setType(String.class);
    request.addProperty(propertyInfo);

    propertyInfo.setName("loginPw");
    propertyInfo.setValue("321");
    propertyInfo.setType(String.class);
    request.addProperty(propertyInfo);


    SoapSerializationEnvelope envelope = new SoapSerializationEnvelope(SoapEnvelope.VER11);
    envelope.dotNet = true;

    envelope.setOutputSoapObject(request);

    HttpTransportSE httpTransport = new HttpTransportSE(SOAP_ADDRESS);

    String response = null;

    try
    {
        httpTransport.call(test, envelope);
        response = envelope.getResponse().toString();
    }
    catch (SoapFault e)
    {
        response = e.toString();
    }
    catch (Exception e)
    {
        response = e.toString();
    }

    return response.toString();
}

}

via Chebli Mohamed

To permanently remove the row although when i click refresh, it should not be appear

I been told to add a feature where when I click those task in it will disappeared.It indicates that the task completed and do not want to see it again anymore. Besides, when refresh, everything cannot reset back.

strWhere = "Where acti='"& replace(strActi,"'","''") & "'"
    strSQL = " select acti from task "
    objRS.Open strSQL, objConn

I have made a feature to disappear the row, but after refresh everthing is back to usual.

function taskOnclick(row) {
     row.closest('tr').remove(); 
     <%=objRS("acti")%> = '0'

};

So i added a new column called acti and then when click acti will become 0 ,then show that time filter acti=1 but in coding, i cannot handle it. May i know which part i miss it? I sure here might be someone can help in this.Thanks in advance.

<tr id="taskid">
        <%
            Response.Write "<td onclick=""taskOnclick(this)""><a href=""#"">" & objRS("TASK") & "<span>"&objRS("REMK")&"</span></a></td>"
            Response.write "<td>" & objRS("DDAT") & "</td>"
            Response.write "<td class=""days"" style=""text-align:center"">" & objRS("DAYS") & "</td>"
            Response.write "<td >" & objRS("CDAT") & "</td>"


        %>
        </tr>

via Chebli Mohamed

Create table if not exists syntax db2

I wish to write a SQL script that will check whether the table/sequence exists or not before create the table/sequence.

I tried Google and get some solution that work for other people but not work for me:

Method 1:

SELECT *
FROM   tableA
WHERE  EXISTS
 (SELECT * from tableB);

This is work with select statement. Then I try with create table statement:

CREATE TABLE "SMEADM"."JXTEST"  (
          "ACCOUNTSENTRYID" BIGINT NOT NULL  )   
         IN "DATATBSP"
WHERE  EXISTS
 (SELECT * from tableB);

This will hit error 42601.

Method 2:

CREATE TABLE IF NOT EXISTS "SMEADM"."JXTEST"  (
          "ACCOUNTSENTRYID" BIGINT NOT NULL  )   
         IN "DATATBSP" ; 

This also bring me to error 42601.

Method 3:

begin
  declare continue handler for sqlstate '42710' begin end;
  execute immediate 'CREATE TABLE "SMEADM"."JXTEST"  (
          "ACCOUNTSENTRYID" BIGINT NOT NULL  )   
         IN "DATATBSP"';
end

And this also bring me to error 42601.

Kindly advise.

via Chebli Mohamed

Delete a record if its ID does not exist in another table

I have a table called patients with a field id

I have another table info with the field patient_id and other fields.

patient_id is the id from the patients table.

I want to be able to delete a patient but only if it does not exist in info table.

Can anybody give me the sql query for this? I've been trying to figure it out now for a while.

via Chebli Mohamed

query to fetch multiple values from one column

i have two tables. First tables values are 1,2,7. Second table values are 1,2,3,4,5,6,7,8,9,10

what i needed is i want to fetch second table values except first table values.Result should be 3,4,5,6,8,9,10.I do no what is the query for this one.Please help me.

via Chebli Mohamed

Python/SQL: move column in table A to table B

I have connected to a sql database through python using sql alchemy. I have a table called A and another table called B. The data from A and B comes from table F.

         **A**                         **B**
C1  C2  C3  C4  C5             D1    D2    D3   D4   D5

I was wondering if it is possible to take column C5 and insert it into table B such that table B looks like:

       **B**
D1  D2  D3  D4  D5  C5

I haven't come across any direct methods to do this. Has anyone figured out a way to approach this?

via Chebli Mohamed

Difficulty printing one particular query in MSSQL

I'm trying to construct a small query which will pull data from individual fields in a DB and print them in a human readable list format (it's what the operators are used to seeing). The code I have here is far from complete but It seems to me that it should work.

DECLARE @PSUCARD VARCHAR(20)
DECLARE @EQUIPMENT VARCHAR(50)
DECLARE @T1 VARCHAR
SET @PSUCARD = 'PSU-888'
SET @EQUIPMENT = '123_POUCH'

PRINT @PSUCARD + ':'
PRINT @EQUIPMENT
PRINT ''

IF (SELECT TEMPERATURE_MAIN FROM PSU WHERE PSU.PART_ID = @PSUCARD AND     PSU.OPERATION_RESOURCE_ID = @EQUIPMENT)IS NOT NULL  BEGIN
    SET @T1 = (SELECT TEMPERATURE_MAIN FROM PSU WHERE PSU.PART_ID = @PSUCARD AND PSU.OPERATION_RESOURCE_ID = @EQUIPMENT)
    PRINT 'Temperature: ' + @T1
    --(SELECT TEMPERATURE_MAIN FROM PSU WHERE PSU.PART_ID = @PSUCARD AND PSU.OPERATION_RESOURCE_ID = @EQUIPMENT)
END

If I execute the code as is, @T1 returns a * rather than a value. If I remove comments from the line below I am reassured that there is indeed a value there. I have other code very similar to this which works fine. Any ideas?

Also, I don't know if this helps in diagnosing the problem, but despite the temperature field in the DB being an INT, I get a conversion message if I try to treat @T1 an an INT.

via Chebli Mohamed

SQL server, data integrity - table referenced by many tables

I have a table which has some generic data, that must be referenced by a multiple number of other tables. The referenced table can't be simplified to fit columns of the referencing tables. How do I enforce data integrity and relationships in such a scenario?

via Chebli Mohamed

Create new dimension using values from another dimension in SQL?

I currently have a SQL table that looks something like this:

   RuleName      | RuleGroup
---------------------------
Backdated task   | DRFHA  
Incorrect Num    | FRCLSR
Incomplete close | CFPBDO
Appeal close     | CFPBDO
Needs letter     | CFPBCRE
Plan ND          | DO
B7IND            | CORE

I am currently writing a stored procedure in SSMS that pulls these dimensions from the existing table. However, I also want the procedure to create a new dimension that will create a "SuperGroup" dimension for each rule based on the text in it's RuleGroup (and an other column for the rest). For example:

   RuleName      | RuleGroup | SuperGroup
--------------------------------------------
Backdated task   | DRFHA     | Other
Incorrect Num    | FRCLSR    | Fore
Incomplete close | CFPBDO    | DefaultOp
Appeal close     | CFPBDO    | DefaultOp
Needs letter     | CFPBCRE   | Core
Plan ND          | DO        | DefaultOp
B7IND            | CORE      | Core

I have currently tried used the "GROUP BY" function, as well as using SELECT with several "LIKE" statements. However, the issue is that this needs to be scaleable - although I only have 21 groups right now, I want to automatically sort if new groups are added.

Here is the SSMS procedure as well:

CREATE PROCEDURE [Rules].[PullRulesSpecifics]
AS
BEGIN
SELECT
    ru.RuleName
    ru.RuleGroup
FROM RuleData.groupings ru
WHERE 1=1
AND   ru.ActiveRule = 1
AND ru.RuleOpen >= '2015-01-01'

via Chebli Mohamed

Update or delete splitted data

In customers table I have Email column which could contain multiple emails separated by (;).
I used split function to separate emails for each customer:

Cust1 --->email1
cust1 --->email2
cust1 ---> emailN

And I could add more emails to the same customer.
I want to be able to update or delete the splitted emails, in other words if email2= abc@company.com I want to change it to xyz@company.com or delete it.
Is it possible to do using split function? or any other way?

Here is my split function

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Calling the function to split emails:

select tb1.custId, split.splitdata from customers tb1
outer apply [dbo].[fnSplitString] (tb1.email,';') split
where tb1.Email like '%;%'

To add new email to the same customer:

UPDATE Customers set Email=Email+';new Email' Where CustId='customerId'

for updating or deleting existing emails, any suggestions?

Thanks in advance

via Chebli Mohamed

SQL Compressing Table - Removing Like Items

Have a table with ID, IDLicense, Brand, and ExtraBrands

Trying to grab all like records by an IDLicense combined all records by taking all copies of IDLicense deleting all copies but taking the brand name and adding it to the original IDLicense and adding the brand of the deleted copy to the ExtraBrands.

So far I have been able to select all IDLicense that have duplicates. Using a temp table to store all extra info.

INSERT INTO #TempTable (ID, IDLicense, Brand, ExtraBrands) 
    SELECT ID, IDLicense, Brand, ExtraBrands FROM BrandOrders
    WHERE IDLicense IN (SELECT IDLicense FROM BrandOrders GROUP BY IDLicense HAVING COUNT(*) > 1)

is a simple way to instead of using a temp table here to instead just delete all like data and take brands from copies and add them as ExtraBrands? Afterwards deleting the duplicates.

via Chebli Mohamed

How to save items from a CheckBoxList To dataBase

I am using a CheckBoxList that bind from database,

Now saving these values is becoming problematic.

I want to save the selected value to new data table, and than show the items that chacked.

<div style="position: absolute; left: 600px; top: 220px;">
        <asp:CheckBoxList ID="CheckBoxList1" runat="server" Width="200px" TextAlign="Left" CssClass="mycheckbox" DataSourceID="SqlDataSource1" DataTextField="TypeGeare" DataValueField="IdGeare">
        </asp:CheckBoxList>
        <asp:SqlDataSource runat="server" ID="SqlDataSource1" 
        ConnectionString='<%$ ConnectionStrings:RegistrationConnectionString %>'
        SelectCommand="SELECT * FROM [InstituteGeare]" OnSelecting="SqlDataSource1_Selecting">
        </asp:SqlDataSource>
    </div>

<div style="position: absolute; left: 650px; top: 500px;">
<asp:Button ID="pricegearebtn" runat="server" Text="send " Height="50" Width="160" />
</div>

Another thing, I tried to creat a table with 2 forgien key, but without success. I get this error : "SQL72031 :: This deployment may encounter errors during execution because changes to [dbo].[GearReq].[UserId] are blocked by [dbo].[GearReq]'s dependency in the target database."

CREATE TABLE [dbo].[GearReq] (
[UserId] NVARCHAR(50) NOT NULL, 
[GearId] INT NULL
FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ([Id]), 
FOREIGN KEY ([GearId]) REFERENCES [dbo].[InstituteGeare]([IdGeare])

);

via Chebli Mohamed

MySQL Cannot add foreign key constraint because of ERROR 1215

This is my first table.

CREATE TABLE `raw_orders` (
`row_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`order_id` VARCHAR(45) COLLATE utf8mb4_unicode_ci NOT NULL,
`order_revenue` FLOAT NOT NULL,
PRIMARY KEY(`row_id`),
KEY(`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This is my second table

CREATE TABLE `formatted_orders` (
`order_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`client_order_id` VARCHAR(50) COLLATE utf8mb4_general_ci NOT NULL,
`order_revenue` FLOAT NOT NULL,
PRIMARY KEY(`order_id`),
KEY(`client_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I am trying to add foreign key in formatted_orders linking it to raw_orders by using this

ALTER TABLE formatted_orders ADD FOREIGN KEY (client_order_id) REFERENCES raw_orders(order_id);

But I get this error ERROR (HY000): Cannot add foreign key constraint

via Chebli Mohamed

IBM DB2 INSERT when Count(*) = 0

Our development database gets reset weekly. I find myself spending an hour or so on Monday mornings (like today) inserting all of my test data into the various tables and databases from individual scripts.

First, of course, I have to verify that these tables do not have my data:

SELECT Count(*) FROM Table1 WHERE INVLINK LIKE '190701050630%'

If no rows are returned, I can insert my records:

INSERT INTO Table1
(       UPC,    CATALOG, VENDOR,  ADD_DATE, INVLINK, WHO_ADDED, TYPE, VENDOR_ID)
VALUES
('011010017760', 'LOVE', 'TES', '20150609', '19070105063041', 9388, 'P', '1013'),
('011010017760', 'HATE', 'TES', '20150609', '19070105063042', 9388, 'P', '1013'),
('011010017760', 'FEAR', 'TES', '20150609', '19070105063043', 9388, 'P', '1013')

What I would like to know is if there is a syntax that would allow me to call the insert command when the count is zero.

Here is what I've tried, but this will not execute with an SQL call.

SELECT CASE WHEN SELECT Count(*) = 0
THEN 
INSERT INTO TBLBARTRANS
(       UPC,    CATALOG, VENDOR,  ADD_DATE, INVLINK, WHO_ADDED, TYPE, VENDOR_ID)
VALUES
('011010017760', 'LOVE', 'TES', '20150609', '19070105063041', 9388, 'P', '1013'),
('011010017760', 'HATE', 'TES', '20150609', '19070105063042', 9388, 'P', '1013'),
('011010017760', 'FEAR', 'TES', '20150609', '19070105063043', 9388, 'P', '1013')
END
FROM Table1 
WHERE INVLINK LIKE '190701050630%'

I can call the INSERT, SELECT, and UPDATE commands, but I am not a Database Administrator. So, creating a stored procedure to run these 15 to 20 scripts is not an option for me.

via Chebli Mohamed

unreadable fields in VisualFoxPro

I am trying to connect my c# application to visualFoxPro database. After getting data from foxpro table I will put it in SQL Server on realtime basis.

In each table of Foxpro there is unique fields (for example UID and station in given snapshot), which is not readable. I Don't know it is encrypted or it is some format, I am attaching screen shot also.

I have tried opening table in DBFViwer and it shows unreadable character too. I need to know how to get thosese unreadable values, because they are important for me.

via Chebli Mohamed

PL/SQL: procedure to process a comparison between two tables

This is an example what i need :

Table 1 :

Id  champ1 champ2 champ3
   1      A      S      Q
   2      D      K      A
   3      D      A      S

Table 2:

Id  champ1 champ2 champ3
   1      A      S      Q
   2      Q      A      A
   3      A      A      K  

A procedure that feeds a table3 as a result of comparison between the table 1 and table 2

Table 3:

Id_exc name_champ noted_val except_val
   2    champ1       D         Q
   3    champ1       D         A
   2    champ2       K         A
   3    champ3       S         K

Please, i need help if someone had the same challenge :)

Thanks in advanced

via Chebli Mohamed

How do I enforce that timeslots for a reservation are consecutive (booking system)?

So I have a design for a simple booking system as follows:

CREATE TABLE interval {
  interval_number INT UNSIGNED NOT NULL,
  interval_start_time TIME,
  interval_end_time TIME,
  PRIMARY KEY (interval_number)
}

CREATE TABLE session {
  session_id INT UNSIGNED NOT NULL,
  interval_number INT UNSIGNED NOT NULL,
  session_date DATE,
  reservation_id INT UNSIGNED NOT NULL
  PRIMARY KEY (session_id)
  CONSTRAINT fk_session_reservation_reservation_id
    FOREIGN KEY
    REFERENCES reservation(reservation_id)
    ON DELETE CASCADE,
  CONSTRAINT fk_session_interval_interval_number
    FOREIGN KEY
    REFERENCES interval(interval_number)
    ON DELETE CASCADE
}

CREATE TABLE reservation {
  reservation_id INT UNSIGNED NOT NULL,
  username VARCHAR (20) NOT NULL,
  reservation_start_time TIME,
  reservation_end_time TIME,
  reservation_date DATE 
}

To summarize, there are intervals of time that can be booked in each day, and a session refers to an interval on a specific date that has been booked. A reservation consists of many sessions, but they must all be consecutive (for example, the user could book 3 sessions tomorrow 11:00 to 12:00, 12:00 to 13:00, and 13:00 to 14:00, and this would be a reservation with reservation_start_time 11:00 and reservation_end_time 14:00). This is so that groups of sessions like this can be summarized more clearly.

My question is, how do I enforce this? As it stands, a reservation could consist of many sessions spread out sporadically on multiple days and multiple times. Is there a better design that could avoid this problem?

via Chebli Mohamed

Why am I getting a converting data type error when Selecting a decimal column?

I am trying to SELECT a column from a view as shown. The datatype of the column is stored as a decimal. - (decimal(18, 7), null)

SELECT DecimalPercentageColumn FROM dbo.DetailsView

However when I try to do this an error message is returned:

Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to numeric.

via Chebli Mohamed

SQL query to list of all lg and lt based upon rectangular polygon points

I have two tables one having longitude and latitude points (2 columns) and other having gps coordinates which define a rectangular polygon (8 columns , 4 GPS co-ordinate , 2 point each)

Here is TABLE A

zip   state city                 lat    lng
35004   AL  Acmar             33.584132 -86.515570
35005   AL  Adamsville        33.588437 -86.959727
35006   AL  Adger             33.434277 -87.167455
35007   AL  Keystone          33.236868 -86.812861
35010   AL  New Site          32.941445 -85.951086
35014   AL  Alpine            33.331165 -86.208934
35016   AL  Arab              34.328339 -86.489638
35019   AL  Baileyton         34.268298 -86.621299
35020   AL  Bessemer          33.409002 -86.947547
35023   AL  Hueytown          33.414625 -86.999607

and TableB is as follows

locationID  lt_Upleft   lg_UpLeft   lt_UpRight  lg_UpRight  lt_DownLeft lg_DownLeft lt_DownRight    lg_DownRight
        1   41.7127     -75.0059     41.7127    -73.0059    39.7127        -75.0059  39.7127         -75.0059

Now I want to get result form TableA Which falls inside 8 points of rectangular polygon. And Same way I want to get result of TableB which falls under longitude and latitude points.

I am new to google maps things. Not sure how I will get results. I have gone through some sample eg :

DECLARE @POLIGON geography; 
SET @POLIGON = geography::STPolyFromText('POLYGON ((5 5, 10 5, 10 10, 5 5))', 4326);
DECLARE @LLTABLE TABLE
(
    POINT_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
    ,LAT FLOAT NULL
    ,LON FLOAT NULL
)


;WITH NN1(N) AS (SELECT NM.N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS NM(N))
,NUMBERS(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM NN1 N1,NN1 N2, NN1 N3
ORDER BY 1 OFFSET 0 ROWS FETCH FIRST (20) ROWS ONLY)

INSERT INTO @LLTABLE (LAT,LON)
SELECT
   LATN.N  AS LATITUDE
   ,LONN.N  AS LONGITUDE
FROM NUMBERS LATN, NUMBERS LONN;

SELECT 
     LT.POINT_ID
    ,LT.LAT
    ,LT.LON
FROM @LLTABLE LT
WHERE geography::STPointFromText(CONCAT
        (
            'POINT('
           ,CAST(LT.LON AS VARCHAR(12))
           ,CHAR(32)
           ,CAST(LT.LAT AS VARCHAR(12))
           ,')'
        ), 4326).STWithin(@POLIGON)  = 1

But don't how to use it.

via Chebli Mohamed

I need help SELECT querying all ISNULL or empty values in SQL [on hold]

I need help SELECT querying all ISNULL or empty values in all tables of a SQL ArcSDE database. I need this functionality in SQL management studio if possible. The SQL database is 2008R2, the SQL server is SQLEXPRESS locally.I have access to arcCatalog also if it is easier (10.3) and ArcServer 10.3.1.

So far I can highlight to see what I need (null verses not null in image below) and I can see each NULL column highlighted.

SELECT *
FROM [database].[user].[table]

quick query showing all fields in a single table:

SELECT TOP 1000 [OBJECTID]
      ,[EventID]
      ,[GlobalID]
      ,[TagID]
      ,[AlternateAssetID]
      ,[ISATID]
      ,[OriginEventID]
      ,[CreatedBy]
      ,[CreatedDate]
      ,[EffectiveFromDate]
      ,[EffectiveToDate]
      ,[LastModified]
      ,[ModifiedBy]
      ,[HistoricalState]
      ,[ProcessFlag]
      ,[SourceCL]
      ,[Remarks]
      ,[Description]
      ,[InServiceDate]
      ,[OperationalStatus]
      ,[SiteEventID]
      ,[Measure]
      ,[SeriesEventID]
      ,[Station]
      ,[SymbolRotation]
      ,[POINT_X]
      ,[POINT_Y]
      ,[POINT_Z]
      ,[RouteEventID]
      ,[VisualOffset]
      ,[TypeCL]
      ,[ManufacturerCL]
      ,[MaterialCL]
      ,[DiameterInletCL]
      ,[DiameterOutletCL]
      ,[WallThicknessInCL]
      ,[WallThicknessOutCL]
      ,[SpecificationCL]
      ,[PressureRating]
      ,[DateManufactured]
  FROM [test].[test].[REDUCER]

via Chebli Mohamed

SQLCMD results to Excel

I am using SQLCMD command in powershell to run SQL scripts. When i pipe the output to TXT ,the formatting is all messed up. i want to export the results to excel file. how can i do that.

My command Sqlcmd -Q 'select top 10 * from people' -S serverName -d testDB -o c:\output.txt | format-table

Thanks in advance.

via Chebli Mohamed

How to find candidate keys

There is a relation R(A,B,C,D) and given FDs are A -> C, B -> A, A -> D, AD -> C then how to find candidate keys. Please some one help me Thanq

via Chebli Mohamed

SQL 2012 attach two rows of data to one

SQL Procedure I am using to get my current dataset:

    SELECT 
DcProd.Title, 
DcProd.Summary, 
DcProd.Product, 
EfProd.ProductStatus, 
EfProd.ViewOnlyLicenses, 
EfProd.FullLicenses, 
DcProd.ProductType, 
DcProd.Advertise, 
EfProd.CustomerAccount_Id, 
DcRelation.component_property_name, 
DcRelation.child_item_id,  
DcRelation.child_item_additional_info, 
DcPageRelation.url_name_    

  FROM [MC_Sitefinity].[dbo].[mastercontrolproducts_mastercontrolproduct] as DcProd

  LEFT JOIN [MasterControlSitefinity].[dbo].[ProductLicense] as EfProd
    ON DcProd.Product = EfProd.Product_Id  --matching dynamic content (Dc) products to Entity Framework (Ef) products

  LEFT JOIN [MC_Sitefinity].[dbo].[sf_dynamic_content] as DcStatus
    ON DcProd.base_id = DcStatus.base_id -- show only published and visible. 

  LEFT JOIN [MC_Sitefinity].[dbo].[sf_content_link] as DcRelation
    ON DcStatus.original_content_id = DcRelation.parent_item_id --Gets related items for image and page url

  LEFT JOIN [MC_Sitefinity].[dbo].[sf_page_node] as DcPageRelation
    ON DcRelation.child_item_id = DcPageRelation.id --gets page url for query
  where EfProd.CustomerAccount_Id = '0013000000PMwEQAA1' AND DcStatus.visible = 1 AND DcStatus.approval_workflow_state_ = 'Published'
    order by ProductType

Here is the example data set returned:

http://1drv.ms/1MKjAsU (OneDrive excel spreadsheet) Or if you prefer: http://ift.tt/1IDYciW

This is not all the data returned but some of the key records I am trying to show.

You will notice we have multiple tables. What is happening is for each record on the left there are sometimes two records on the right causing duplicated rows.

What I would like if possible is (looking at last two rows) if we could combine child_item_additional_info and url_name_ on one line instead of two. As for the data in the component_property_name it could just be deleted or combined with a pipe (|) or whatever is recommended.

I have looked at cases, pivot tables, but I just am a little green when it comes to SQL and unsure how to do this.

Any help will be greatly appreciated.

via Chebli Mohamed

PHP, prevent javascript from running /delete javascript or php code written by user in a form [duplicate]

This question already has an answer here:

• How to prevent XSS with HTML/PHP? 5 answers

I am making a website where people will publish an article. I make them write their article in a textarea, but I don't want them to add javascript, php or any other langage than html to prevent XSS or SQL injections. I'm trying to delete javascript or php code written by user in a form How can I do this?

via Chebli Mohamed

Calculate value needed to reach target by the end of a year

I have a set of values (scores to a particular KPI), these are reported monthly. I need to calculate what I'd have to score every month from now until the end of the year to reach that target.

I'm more interested in the algorithm/maths involved in the calculation than a programming solution at the moment.

I've provided some example data here.

Example Data

In this example I have manually guesstimated that 9.1316 for the final six months of the year will allow the score to climb to the target by the end of the year.

What I'd like to understand is how to calculate that from the current YTD value, the target and the number of months remaining.

Depending on the way the solution worked, I'd either implement it straight in SQL, or it would be in the code of the program itself (C#)

via Chebli Mohamed

Select records from one table, check if it exists in another table then insert into a 3rd table in C#

I have 3 tables say table A, B, C. Their schema structure is different but they have a unique column ID. Using C# i want to

SELECT * FROM A

Then check if any of the records selected from A exists in B (ID being key). If it exists i insert the records in C. How can i go about this?

via Chebli Mohamed

update multiple table refrencing new table data

I have a table called ADSCHL which contains the school_code as Primary key and other two table as RGDEGR(common field as SCHOOl_code) and RGENRl(Original_school_code) which are refrencing the ADSCHL.

If a school_code will be updated, both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well.

I have been provided a new data that I have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code value (ADSCHL) with new school_code (TESTCEP) and make sure the changes happen across all 3 tables.

Can someone please help me with this?

I tried using Merge Update function but wasn't able to do it. It errored out.

I tried using this query and wasn't sure if this is gonna help me to update all the tables:

UPDATE dbo.ADSCHL 
    SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code
FROM dbo.ADSCHL AD 
INNER JOIN TESTCEP FD
    ON AD.SCHOOL_NAME = FD.School_Name

via Chebli Mohamed

How to get this view to SELECT DISTINCT on first column only in SQL

I am not accustomed to using SQL for View Creation, It's forcing me to use HAVING and GROUP BY, in a standard SQL statement i would use WHERE but apparently that's not allowed. I want to SELECT DISTINCT on dbo.[ShowTex Belgie NV$Item].No_ only. But it is also combining anything i add in GROUP BY it seems. I can't use HAVING on columns unless i include them in GROUP BY. Getting this far has already taken me ages. :/ I just wanted to create a simple WHERE statement ;(

SELECT DISTINCT TOP (100) PERCENT dbo.[ShowTex Belgie NV$Item].No_, SUM(dbo.[ShowTex Belgie NV$Warehouse Entry].Quantity) AS [Quantity Sum]
FROM            dbo.[ShowTex Belgie NV$Item] 
LEFT OUTER JOIN dbo.[ShowTex Belgie NV$Warehouse Entry] ON dbo.[ShowTex Belgie NV$Item].No_ = dbo.[ShowTex Belgie NV$Warehouse Entry].[Item No_]
GROUP BY dbo.[ShowTex Belgie NV$Item].No_, dbo.[ShowTex Belgie NV$Warehouse Entry].[Bin Code]
HAVING        (dbo.[ShowTex Belgie NV$Warehouse Entry].[Bin Code] <> 'SHIPPING') OR
                     (dbo.[ShowTex Belgie NV$Warehouse Entry].[Bin Code] <> 'VERZEND') OR
                     (dbo.[ShowTex Belgie NV$Warehouse Entry].[Bin Code] <> 'WORKSHOP')
ORDER BY dbo.[ShowTex Belgie NV$Item].No_

Using Server Studio Managment 2012 but there doesnt seem to be a hashtag for that.

via Chebli Mohamed

Add columns dynamically based on a condition in SQL

I'm using SSMS 2012. Consider a table A with columns A, B, C. I would like to dynamically add a new column, D to this table based on a condition in one of the existing columns. How can this be achieved?

via Chebli Mohamed