Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table box . In the edit script I have below one
Data:
LOAD
ID,
Reference;
SQL SELECT *
FROM "table"
where Reference in ('371284',
'373896',
'374493',
'411464',
'411464',
'413172',
'415033',
'415623',
'454035',
'442363',
'455699',
'457311',
'458105',
'326600',
'326600',
'455024',
'453906',
'1851492',
'1851492',
'1851492',
'455839',
'457788',
'330983',
'328251',
'372278',
'376328',
'369175',
'369175',
'369175',
'386311',
'426283',
'425362',
'424456',
'418055',
'426787',
'386026',
'386026',
'387355',
'386161',
'418753',
'472508',
'472508',
'1850066',
'423629',
'504005',
'382816',
'388291');
I need to have the Reference as above order in Table box.could anyone help me is that possible to create like above. I think I have to write expression in Sort tab is that correct?
Thanks.
Please any help???
If your database is MySQL you could use Field(), and use RowNo() in the LOAD statement as a sort key e.g.
Data:
LOAD
RowNo() as SortOrder,
ID,
Reference;
SQL SELECT *
FROM "table"
where Reference in ('371284',
'373896',
'374493',
'411464',
'411464',
'413172',
'415033',
'415623',
'454035',
'442363',
'455699',
'457311',
'458105',
'326600',
'326600',
'455024',
'453906',
'1851492',
'1851492',
'1851492',
'455839',
'457788',
'330983',
'328251',
'372278',
'376328',
'369175',
'369175',
'369175',
'386311',
'426283',
'425362',
'424456',
'418055',
'426787',
'386026',
'386026',
'387355',
'386161',
'418753',
'472508',
'472508',
'1850066',
'423629',
'504005',
'382816',
'388291')
ORDER BY FIELD(Reference,
'371284',
'373896',
'374493',
'411464',
'411464',
'413172',
'415033',
'415623',
'454035',
'442363',
'455699',
'457311',
'458105',
'326600',
'326600',
'455024',
'453906',
'1851492',
'1851492',
'1851492',
'455839',
'457788',
'330983',
'328251',
'372278',
'376328',
'369175',
'369175',
'369175',
'386311',
'426283',
'425362',
'424456',
'418055',
'426787',
'386026',
'386026',
'387355',
'386161',
'418753',
'472508',
'472508',
'1850066',
'423629',
'504005',
'382816',
'388291');
have you tried with loadorder?
Thanks and I have SQL Server 2008. please helpme how to change this as I am trying this in different ways...
There is no direct equivalent in SQL Server but you could try something like this (just using a few Reference values to simplify the example):
Data:
LOAD
RowNo() as SortOrder,
ID,
Reference;
SQL SELECT *
FROM "table"
where Reference in ('371284',
'373896',
'374493',
'411464')
ORDER BY CASE Reference
WHEN '371284' THEN 1
WHEN '373896' THEN 2
WHEN '374493' THEN 3
WHEN '411464' THEN 4
ELSE 5
END;
It's a pain to type so try it with a few values only first to see if it works. I don't have SQL Server installed so cannot test. If it works then the TableBox can be sorted by field SortOrder.
try this
DataLoadOrder:
LOAD * Inline [
'373896',
'374493',
'411464',
'411464',
'413172',
'415033',
...
'423629',
'504005',
'382816',
'388291'
];
DataOrder:
LOAD
ID,
Reference;
SQL SELECT *
FROM "table"
where Reference in ('371284',
'373896',
'374493',
'411464',
'411464',
'413172',
'415033',
...
'423629',
'504005',
'382816',
'388291');
drop table DataLoadOrder;
Good call Bumin.
Slight tweak (add column header and remove commas):
DataLoadOrder:
LOAD * Inline [
Reference
'373896'
'374493'
'411464'
'411464'
'413172'
'415033'
...
'423629'
'504005'
'382816'
'388291'
];
DataOrder:
LOAD
ID,
Reference;
SQL SELECT *
FROM "table"
where Reference in ('371284',
'373896',
'374493',
'411464',
'411464',
'413172',
'415033',
...
'423629',
'504005',
'382816',
'388291');
drop table DataLoadOrder;
thanks